Bill... Thanks for taking time to explain ...
In db2, runstats command collects information about tables and indexes and stores them in database system tables ... for example, the number of rows in a table, number of pages used by the table, number of free pages etc. Index(b-tree) statistics will be number of levels, number of leaf pages, first key cardinality, second key cardinality etc ...
It is based on these the optimizer decides a specific access path ... For example, if a table has million rows now but the runstats was done when the table had 50000 rows, the access path will be optimized based on the statistics available and not on the current actual table data...
I would like to know if there is a command in Oracle to collect such statistics to help the optimizer decide on an access plan ... If there are no statistics, then what is the basis on which Oracle decides , ie how does Oracle know that the table has a million rows, index on column one can return 100,000 rows for 'A' etc ..
Thanks for your valuable time ..
Originally posted by billm
A full answer to your question would run into many volumes. I would suggest Oracle technet or Google for a good place to start reading about Oracle's query optimisation.
There are many different ways that Oracle does it, but the short and usual answer is that it looks at the predicates, considers available indexes and the selectivity for satisfying those predicates and forms an execution plan based on that.
Ie, a table has a million rows.
-Column1 is indexed.
-Column2 is indexed.
Your query says select something from table where colum1 = 'A' and column2 = 'B'.
By looking at the indexes for Colum1 and Colum2 Oracle thinks that using Colum1='A' on the column1 index would return about 100,000 rows - then it would have to 'filter' them in memory to get all the Column2 = 'B'.
It then looks at the column2 index and determines that using that index for value='B' would return about 50 rows. It would then have to 'filter' them in memory for column1 = 'A'.
Usually, in memory 'filters' are going to be quicker than disk reads so it would opt to use column2 index.
This is about as simplistic as saying a petrol engine works by burning fuel.
I don't know what db2 runstats are but the name suggests post query analysis, whereas your question is related to pre-query execution plans.
I truly don't mean to sound dismissive, but query optimisation on Oracle is a huge area, I don't think your question could be answered in any satisfactory form in a single post on here.