Home » RDBMS Server » Performance Tuning » Index created is not using(I dont want to use hints)
Index created is not using(I dont want to use hints) [message #250235] Mon, 09 July 2007 04:06 Go to next message
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 #250237 is a reply to message #250235] Mon, 09 July 2007 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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')

But Oracle will use the index only if you gather the statistics and there are enough rows/blocks.

Regards
Michel
Re: Index created is not using(I dont want to use hints) [message #250245 is a reply to message #250235] Mon, 09 July 2007 05:19 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
HI,

As I am poor in tuning can u pls let me know how to gather the statstics.

Thanks in advance
Re: Index created is not using(I dont want to use hints) [message #250249 is a reply to message #250245] Mon, 09 July 2007 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
dbms_stats

Regards
Michel
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 Go to previous message
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
Previous Topic: Partitioned table takes longer to load than ordinary table
Next Topic: detecting environment issues
Goto Forum:
  


Current Time: Sat Nov 23 08:00:14 CST 2024