drop table t1; create table t1( n1 number , n2 number , n3 number , constraint t1p primary key(n1) , constraint t1u unique (n2)); select count(*) from t1;I answered wrongly mentioning also that it depends on the version. As so often my mind started to think howcome I answered such an answer. Maybe I was thinking a question: How many different execution plan possibilities does Oracle have in this example? Unfortunately I am not attending UKOUG to throw this to the OakTable Challenge. Feel free to use "Give me at least one that was not in the list of the Jonathan's answer and how to get there." I will be coming back to the issue after 7th of December.
2011-11-25
A Question
I attended the Jonathan Lewis seminar three weeks ago and wrote just after that about information overload. The things learned there have helped me understanding issues I have to deal with the Oracle optimizer. The amount of information gained need some time to melt into mind. One issue talked there was a question.
The original written question was "How many choises does Oracle have in this example?" With addition there was mentioned "for all versions".
Subscribe to:
Post Comments (Atom)
About Me
- Rafu
- I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.
In addition Jonathan had a bitmap index on column n3. So the correct answer for the original question is five. Oracle has five choises to choose from. 1. Full table scan, 2. index scan on primary key index, 3. index fast full scan on primary key index, 4. index scan on the bitmap index and 5. index fast full scan on the bitmap index.
ReplyDeleteMy change to the question changes also the answer. It is possible to have a result cache in the 11g access path without changing the query. So it is possible to double the different access plan alternatives just by modifying the result cache on or off. You will not propably be doing that system wide but for instance "alter system set result_cache_mode=force;" This does not change the amount of possible choises Oracle optimizer has.
Why is t1u not in this list?