January 13, 2011
(Back to the Previous Post in the Series)
I found a couple of more Oracle Database related quizzes on the Internet. These quizzes, I would guess, are designed for Oracle Database 8.1 and earlier. I missed one question in each of the quizzes, but I suspect that I could have missed many more if someone had simply handed the quizzes to me and asked that I answer the questions. I think that I would intentionally answer the questions as if the quiz were designed for Oracle Database 11.2, because the database release version for the quiz is not specified. How well would you do if:
- You answer the questions knowing that the quizzes were designed for Oracle Database 8.1 and earlier?
- You answer the questions believing that the quizzes were designed for the latest release version of Oracle Database?
The Quizzes:
Consider providing feedback on the individual questions if you decide to post your results here.
Thank you for that – i was not aware of this kind of optimization in 11gR2 ( maybe 11.1 – can’t test it at the moment)
– if an indexed column is not nullable, the index range/unique scan can be used for nvl(column,value), as optimizer probably understand
now, the null can’t occur anyway.
Best regards
Maxim
Hi Maxim,
I am thinking that optimization must be specific to 11g R2, although I am not sure that I recall testing it on 11g R2. A brief test case table:
Test case script:
This is the output that I receive on Oracle Database 10.2.0.2 (very similar results were seen on 11.1.0.7):
The unhinted execution plan from 11.1.0.7:
The index hinted execution plan (note that the calculated cost is lower):
I will have to test 11.2.0.1/11.2.0.2 later to see if I am able to reproduce the behavior – thanks for the tip.