Damn Indexes !

[zMonk]

Ladies and Gents,
Can yopu explain me why a quirey would invoke one index instead of another ...

I am joining a big table mill of records and small table 4K they both have indexes both of them are being invoked , but I want to invoke the big table index first ... and then "map" output throug the small table.
I would appreciate any links /explanations how a quiery is being processed e.g. order of the from close, order of the where close.

 

[carp]

It depends a great deal on which optimizer you are using and whether or not your table statistics are up to date.

As a general rule, Oracle will try to use the smallest data set as the driver in the joins. If you are using Cost Based Optimizer (CBO) Oracle will develop several execution plans, assign an anticipated cost to each plan, and then use the plan with the lowest cost. However, if your statistics are out of date then the costs will be calculated incorrectly and it is possible the most efficient plan will not be used.

 

[zMonk]

It is CBO and tables where analyzed 3 days ago...

 

[carp]

You might want to also take a look at using hints to specify which indexes, join method, or optimizer to use.

 

[AOLEURO]

Hi

Carp is right - and I just want to fill up...

Then you got a SMALL table and a FAT/BIG table and you want to make a EQUAL (=) join, then the BEST optimized solution is if Oracle can build a HASH join. Oracle has more than one join option - and use the join with smallest cost.

As Carp says - Oracle optimizer is a smart guy - but it is only as smart as how updated the statistics are on the tables.

Try to use EXPLAIN PLAN to see that the Optimizer is doing.

Even if you got the right indexes, then Oracle maybe do not want to use them, because the cost is more expensive.


Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !