Oracle didn't use index

please help me with this query:

select grid_name, grid_lvl2_code
From spatial.grid_master
Where grid_lvl = 2 and
grid_priority = 1

I look at the explain plan table and notice that it does a full tablescan, I have an index on the column grid_lvl.

Please advive what I need to do.

Thanks

Analyze the table and check the plan.If still index not used,
use hints in th query.

It could be like you might small number of rows where cost would be more to use index then full table scan.

Table has been analyzed but it still do a full tablescan.

Jeff or Sam or Julian

do you have any advises ???

thanks

You can either add a composite index on grid_lvl and grid_priority or use a hint for the index.

Hi

Try Creating a compund index on

grid_lvl

and

grid_priority

then try the query

Regards

Use a hint.

is it the right syntax ???

select /*+ grid_name, grid_lvl2_code*/
From spatial.grid_master
Where grid_lvl = 2 and
grid_priority = 1

please advise

I think that you need to name the inndex that you want to use.


select /*+ XIE1_IndexName */
From spatial.grid_master
Where grid_lvl = 2 and
grid_priority = 1

No, your hint syntax is wrong.

BTW, are you realy sure you want oracle to use the index for that query? What if the performance with index will actually be worse? What does the following two queries return for your table?

select count(*) from spatial.grid_master
Where grid_lvl = 2;

select count(*) from spatial.grid_master;

select /*+ index( grid_master XIE1_IndexName) */
From spatial.grid_master
Where grid_lvl = 2 and
grid_priority = 1

make sure u have created index on this column.

regards
anandkl

Thanks all but my query is not working now on explain plan.

Please advise the below query

select /*+ grid_master_lvl_idx (grid_master) */
from grid_master
where grid_lvl=2 and grid_priority=1

it is telling me that missing expression

Can you post the table description ?
How many rows are there in the table ?
How many are with grid_lvl=2 ?

In addition, If u can post please following:

select * from all_tables where table_name = 'GRID_MASTER' and owner = 'SPARTIAL';

select * from all_indexes where table_name = 'GRID_MASTER' and owner = 'SPARTIAL';

May be (but not absolutly needed)
analize index III validate structure;
select * from index_stat;
for index on the column GRID_LML.

Then, in sqlplus do following:

set autotrace TRACEONLY EXPLAIN STATISTICS;
select grid_name, grid_lvl2_code From spatial.grid_master Where grid_lvl = 2 and
grid_priority = 1;

and post result of trace of this query.
If we'll have this information, I hope, we can explain "why oracle optimazer use FULL SCAN TABLE?"

Sometimes, even when you use a hint, it may be performing a full table scan. I guess, if you use the RULE hint, then it is forced to use the index. In your case, the performance may increase.

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.

Please help

Is your GRID_LVL column is of type NUMBER? Is it not perhaps of type VARCHAR2 or CHAR or something like that?

Is your index on GRID_LVL a single column index or a composite one?

JMODIC,

THANKS FOR YOUR QUICK REPLY

grid_lvl is number and it's one column index

please advise

Quote:

---

Originally posted by ashley75
Thanks all but my query is not working now on explain plan.

Please advise the below query

select /*+ grid_master_lvl_idx (grid_master) */
from grid_master
where grid_lvl=2 and grid_priority=1

it is telling me that missing expression

---

You're syntax is wrong:

Code:

---

select /*+index(gm grid_master_lvl_idx) */
from grid_master gm
where gm.grid_lvl=2
and gm.grid_priority=1

---

Jeff,

I ran your query and I got an error

ora-00936: missing expression

please advise

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,

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

Quote:

---

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.

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]

Quote:

---

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.

Quote:

---

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...

Quote:

---

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:

Quote:

---

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.

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]