| Cost-based optimization slows down my query [message #65093] |
Wed, 28 April 2004 09:07  |
David Peters
Messages: 6
Registered: April 2004
|
Junior Member |
|
|
Under what circumstances would a cost-based optimizer choose to ignore an index and do a full table scan instead?
I have the cost-based optimizer set on a query, and the system is choosing to run a full table scan on an indexed column of my table.
All of the indexes and columns are analyzed, as seen in my explain plan, and the costs are present.
If I set the /*+ index */ hint, it uses the index. If I set my session to OPTIMIZER_GOAL = RULE it uses my index.
|
|
|
|
| Re: Cost-based optimization slows down my query [message #65096 is a reply to message #65093] |
Wed, 28 April 2004 23:06  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
In case of cost based optimizer FTS doesn't mean that ur query is slow. Even if ur tables are analyzed check the cardinality of the columns in where clause and the amount of data in table.
The best and important way is to create a histogram on the columns which are in where clause.
If after that also u see FTS in plan so its mean that FTS is better than using indexes.
Daljit Singh.
|
|
|
|
| Re: Cost-based optimization slows down my query [message #65102 is a reply to message #65096] |
Thu, 29 April 2004 05:11 |
David Peters
Messages: 6
Registered: April 2004
|
Junior Member |
|
|
Daljit,
Thats where I think the problem lies - the CBO believes that a full table scan is better than using the index. If you look at the statistics it generates, this is true.
However, the actual results prove different. If I force it to use an index using the /*+ index(i) +/ hint, the query runs a lot faster.
I dont know why a full scan is so slow, though, as the table is very small.
I changed the parameter for optimizer_index_cost_adj from 100 (default) to 10 and it seems to choose to use indexes, and this fixes the problem.
|
|
|
|
| Re: Cost-based optimization slows down my query [message #65109 is a reply to message #65102] |
Fri, 30 April 2004 10:16 |
shoblock
Messages: 325
Registered: April 2004
|
Senior Member |
|
|
The cost based optimizer has two parts - FIRST_ROWS and ALL_ROWS. ALL_ROWS is gear towards best throughput (ideal for data whses when you want to compare ALL data for a massive report). FIRST_ROWS is geared towards fastest first row retrieval - bringing some data back quickly.
ALL_ROWS assumes that you want to process all the data in the table, all the time. Hence the full table scans.
FIRST_ROWS assumes you just want something back quickly (for a user screen), so it uses indexes more often.
RULE has been around forever, and functions the same as it always has - from a list of rules, which are applied in the same order, always, and makes no assumptions as to your intentions.
The big thing here is this - when your optimizer is in CHOOSE mode (the default) it always chooses ALL_ROWS (this is its definition, and you can't do anything about it). So, you can use the RULE hint, and also try the FIRST_ROWS hint, query by query. Changing the database to FIRST_ROWS mode is an option, however, it will most likely have adverse affects on some other queries, so changing it in a production system is not recommended.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
