make sure u have created index on this column.
regards
anandkl
Printable View
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
You're syntax is wrong: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
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