Originally posted by fossil
Pando,


Point(according to original poster) is what would it take to run the query faster if you are having
multi million rows tables.

Having indexes on the columns of the where clause would apply the predicate first and it will reduce the resultset, however, its going to make query run much slower than not having indexes on it. 15% for a multi million rows table is quite a big result set.

Not having indexes on the columns in the where clause would have CBO do Full table scan and join the tables first and then apply filter(see the explain plan).


Indexes are preferred when selecting a very very small number of rows may be upto 5%.

Moreover, must try query both ways to and compare the timing.



Thanks,
No, no no. If you put an index on the large table the CBO does not have to use it. It will look at the predicate, the table and index statistics, and make a choice based on minimum cost.

This stuff about "Indexes are preferred when selecting a very very small number of rows may be upto 5%." is all wrong .. index usage is based on block selectivity not row selectivity. Read the link I posted previously.

What you need to do is go and read the section in the Concepts Guide on how the optimizer works.