Re: Interview Index problem

aman.oracle.dba wrote:
> what I understand, if your have short table then index is not in use
> (full table scan) but if you have large table then that index will be
> used which has less no. of records like if in table if we have 1000
> entries where deptno=10 and 1500 entries where sal>1000 then deptno
> index will be in use.
> Is it correct.............................

Not quite so simple. Review the docs about how the CBO works (such as http://oraclesvca2.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm "The Query Optimizer" in the Oracle Database Performance Tuning Guide), then if, or rather, when you want to know how it really works, get Jonathan's book: http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

In a nutshell, the optimizer uses as much information as it is given to find a (not necessarily "the") least cost access to the data. There are too many variables in general to predict inflection points of using one index over another. Even testing a stable data set and configuration doesn't give an incontrovertible answer, because things can happen at runtime that can cause the optimizer to change its decision. Not only are there many things that can affect the decision, there are things you can do to be sure a particular plan is used (cf Stored Outlines).

So you can see, your interview question is really a trick question if there is an implication that a particular index will be used. With the information given, the best answer is "it depends." The "why" part... volumes are being written about that.

jg

--
@home.com is bogus.
"One of the ironies of natural disasters is they're often good for real
estate."
http://www.signonsandiego.com/uniontrib/20060528/news_1h28katrina.html