The following statement:
select order_number, order_total, order_date, customer_id, order_type_id
where order_date > sysdate - 7
and order_type_id = 1019 and order_total > 400
order by order_total;
If we look at the where clause: order date has its own index and order_type_id has its own index in coe_orders.
However, no matter which way I'am ordering the columns in the where clasue the optimizer always chooses to use the index on the order_type_id column (which by the way has many more records than sysdate-7).
Beside using a hint is there another way to change the Where clause so the optimizer chooses the most restrictive Index? what's the process that the optimizer chooses between two indexes?
Thank You very much for any help.
Are you using cost based optimizer? Do you have accurate statistics collected about those table and indexes?
Anyway, if you want to prevent optimizer to use index on ORDER_TYPE_ID without using a hint, you can change that part of WHERE clause from
and order_type_id = 1019
and order_type_id+0 = 1019
That way optimizer will not be able to use index on ORDER_TYPE_ID, so it will choose to use the index on ORDER_DATE.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Keep in mind that numerous factors could impact on the Cost Based Optimizer:
- sort_area_size in case you do not set hash_area_size
- hash_join_enabled and hash_area_size
- db_file_multiblock_read_count might influense the full scans
- samples in analyze (I use compute for tables as well, not just for indexes
I'am only using the rule based optimizer.
Thank You, your replies were very helpful.
Click Here to Expand Forum to Full Width