Hence jmodic's comment. Funny thing about the optimizer, sometimes it does know best.
I would measure the time it takes to execute the query and not worry about the cost in this particular case. I would execute the query with the FTS 5 times in a row and average the last four. I would do the same for the HINTed query. See which one yields better performance.
Originally posted by ashley75 I agree , it run much faster now 1/4 of what it was before so you are saying that the OCst is meaningless in a lot of cases???
Most cases the cost is reflective of how long the query will actually take. In this particular case, you knew the distribution of your data better than oracle did.
Could you explain to me why Oracle didn't use the index at the first place ?
Hard to say. The distribution of your data may be such that oracle thought it would be better to FTS. Your statistics might not be up to date as much as you thought. Maybe a different type of index would be appropriate for your query. It's really hard to say.
At one time I saw an article which went down each of the 20 or so reasons Oracle would prefer a FTS over an index. I think it was on metalink, but I can find it now. Maybe somebody else has the link...
Originally posted by ashley75 Could you explain to me why Oracle didn't use the index at the first place ??? and we have to force it.
The following two facts that you provided do explain what went wrong:
1. there was 4 grid lvl on this table
2. COUNT(GRID_LVL)/COUNT(*) COUNT(*)
I assume you have analyze the table, so the optimizer had some of the statistics available. For example, it was avare that there are only 4 distinct values in GRID_LVL and that there is no NULL values in that column.
However I also assume that you didn't collect the statistics about the distribution of your column GRID_LVL. Your actual data distribution is very skewed, yet optimizer was not avare of this. From your statistics optimicer could only conclude the query will retrive 25% of all rows (only 4 distinct values in GRID_LVL, remember), so it decided it is much cheaper to perform a FTS.
Check the manuals about analyzing columns with histograms, then analyze your column GRID_LVL with the appropriate SIZE attribute. With all those information available you'll probably find that CBO will decide to use the index for your query, even without a hint.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Oracle try to calculate
CARDINALITY = CEIL(<#of_rows>/<#of_dist_keys>)*0.25 = CEIL(570432/4)*KOEF = 4900 (as u wrote)
where KOEF < 1
and this is very low selectivity (IN AVERAGE) for using index.
Oracle Optimazer will think that
in first I should read 1/4 from # of index db blocks and then
~ I should read each table block :
because (only for examlpe)
avg row len = 500
db_block_size = 8K
DB_FILE_MULTYBLOCK_READ_COUT = 8
probabitity then each DB_FILE_MULTYBLOCK_READ operation has this index key ~ 100%
for full scan table
COST = #of_table_blocks/DB_FILE_MULTYBLOCK_READ_COUT
and this is less then COST when oracle will use index.
ONE NOTE ABOUT ORACLE OPTIMAZER.
IT THINK "IN AVERAGE" , EXEPT SITUATION, WHEN U HAVE