| Index created is not using(I dont want to use hints) [message #250235] |
Mon, 09 July 2007 04:06  |
donind
Messages: 95
Registered: February 2007
|
Member |
|
|
HI,
The index i created is not used by querry.
Table Tab1 (col1, col2, col3, col4) col1 primary key
Table tab2 (col1, cola, colb, colc) col1 primary key
select a.col1, a.col2.... from tab1 a where exists (select 'A' from tab2 b where a.col1=b.col2 and a.col2='Z' and (a.col3='X' or a.col4='Z'))
Index created
create index myindex on tab1(col2,col3,col4);
But its not using this index.
Pls just let me know where i went wrong.
any help really appreciated.
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: Index created is not using(I dont want to use hints) [message #250359 is a reply to message #250235] |
Mon, 09 July 2007 14:53  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
My guess:
1. Only the first index column col2='Z' may be used by optimizer ( OR condition prevents the usage of col3 and col4).
2. col2='Z' is NOT selective enough.
Try:
1. Instead of a single index on (col2,col3,col4) create 2 indexes (hopefully col3 and col4 are selective ones):
DROP INDEX myindex;
CREATE INDEX myindex1 ON tab1 (col2, col3);
CREATE INDEX myindex2 ON tab1 (col4,col2);
2. Rewrite the query as:
select a.col1, a.col2....
from tab1 a
where exists (select 'A' from tab2 b where a.col1=b.col2)
and a.col2='Z' and a.col3='X'
UNION
select a.col1, a.col2....
from tab1 a
where exists (select 'A' from tab2 b where a.col1=b.col2)
and a.col2='Z' and a.col4='Z'
HTH.
Michael
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
