-
What do you want to select?
Code:
select /*+index(gm grid_master_lvl_idx) */
gm.grid_lvl,
gm.grid_priority
from grid_master gm
where gm.grid_lvl=2
and gm.grid_priority=1
Jeff Hunter
-
Jeff,
thanks so much but the cost to use the index was even higher than the full table scan. now the cost is 4900 whereas the cost for full table scan only 2200
Do you have any recomendation ????
Thanks
-
Originally posted by ashley75
There were about 600,000 rows in the table,
and 51 rows where grid_lvl=2
It's doing a full tablespace even I had the index for grid_lvl in there.
Still without anwer followig questions:
1) "only 51 rows where grid_lvl=2" but what about # of DISTINCT KEYS in this index
2) How many (or %) null values in this field.
Try to execute:
select distinct(grid_lvl) from spatial.grid_master;
select count(grid_lvl)/count(*), count(*) from spatial.grid_master;
Probably this information will be very helpful for u and u can deside :
Why oracle didn't use index?
-
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.
Jeff Hunter
-
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???
Thanks so much for your help as always, I do owe you a lot Jeff and all of the gurus in here.
one more quick question:
Could you explain to me why Oracle didn't use the index at the first place ??? and we have to force it.
Thanks
Shestakov,
1. there was 4 grid lvl on this table
2. COUNT(GRID_LVL)/COUNT(*) COUNT(*)
1 570432
[Edited by ashley75 on 04-02-2002 at 04:39 PM]
-
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...
Jeff Hunter
-
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(*)
1 570432
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
1)
in first I should read 1/4 from # of index db blocks and then
2)
~ I should read each table block :
because (only for examlpe)
avg row len = 500
db_block_size = 8K
DB_FILE_MULTYBLOCK_READ_COUT = 8
THEN
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
COLUMN'S HISTOGRAMMS.
[Edited by Shestakov on 04-02-2002 at 05:24 PM]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|