Quoting the same Dave Ensor and Jan Stevenson. "Oracle Design"
| producte_code | DATE_FROM | DATE_TO | Price |
| 1 | 13.01.2007 | 01.02.2007 | 1.12 |
| 1 | 02.02.2007 | 31.10.2007 | 2.24 |
| 1 | 01.11.2007 | 01.01.3000 | 5.47 |
select price from prices
where sysdate between date_from and date_to
It seems to be efficient to use index for the columns (PRODUCT_CODE, DATE_TO) since search parameter is located near the upper index bounds. However, note that on query processing still each line which has DATE_TO value grater than SYSDATE will be read. In other words, the optimizer will continue lines reading after we found the required string.
Maximum efficiency is achieved witр recent prices selecting, if column DATE_TO is indexed, and with oldest prices selecting, if DATE_FROM is indexed
No comments:
Post a Comment