May 052014
 

There’s a neat optimization in Oracle I found while tinkering around (look closely at the predicate section):

select oracle_can_now_use,
       a_function_based_index
from   queries_with_no_functions
where  a_date_col_with_an_fbi = :a_date
and    oracle_version >= '11.2.0.2';

-------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |     1 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| QUERIES_WITH_NO_FUNCTIONS |     1 |
|*  2 |   INDEX RANGE SCAN          | QUWF_DATE_FBI             |     1 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ORACLE_VERSION">='11.2.0.2' AND "A_DATE_COL_WITH_AN_FBI"=:A_DATE)
   2 - access(TRUNC(INTERNAL_FUNCTION("A_DATE_COL_WITH_AN_FBI"))=TRUNC(:A_DATE))

Line 2 of the predictates states that the INDEX RANGE SCAN was accessed via TRUNC(INTERNAL_FUNCTION()) – a function-based index was used when no function is present in the where clause!

This optimization appeared in 11.2.0.2 (fix 9263333) – I have to thank the people of Oracle-L (particularly Velikikh Mikhail) identifying when this improvement came in.

This optimization isn’t restricted to TRUNC() on date columns however.
Continue reading »

Jan 172014
 

Here’s another quiz. The procedure below is executed, “something” is run and the output changes on the next execution:

exec why_does_the_output_change;

from this

-- What happens here?

exec why_does_the_output_change;

to this

To cover off the easy options – no change is made to the code of the procedure and there’s no SQL within it (so the “mystery code” isn’t updating/inserting/deleting table data for example).

What did I do? Put your answers in the comments!

(Just as I finished writing this I thought of a second way this could be done, though arguably it involves a code change – can you get them both? Are there more options?)