Re: SQL Query

Oradba Linux wrote:
> Select * from orders where load_id = 174659 order by order_id ;
>
> select * from orders where load_id = 174659 order by order_dt;
>
> I have similar queries. This table has about 19mil rows. Statistics are
> current. Order_id is the primary key. The index exists on load_id. The
> first one does a index full scan on order_id and second one does a index
> range scan on load_id. There is no index on order_dt.
> If needed i can post the explain plans and stats details from
> user_tables/user_indexes. I am wondering why the index on load_id is not
> chosen in the first SQL statement.

With Oracle using the index on the order_id column, the need for the final sort is eliminated. It could be that the calculated cost of the sort was sufficiently high enough to make the index full scan access path appear less costly.

A 10053 trace may be helpful when trying to determine how the calculated costs of the different access paths are generated. It takes a bit of practice to correctly read a 10053 trace file, but it may be worth the time investment.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jan 18 2007 - 05:38:04 CST