Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Index not getting used, Index not getting used
rajahyder
post Jun 13 2005, 08:07 PM
Post #1


Advanced Member
***

Group: Members
Posts: 128
Joined: 26-April 05
Member No.: 1,934



In the follwoing query there is a composite index on the where condition columns. the table has around 25 million records and the data retrieved is 59 , still the index is not getting used, i ned to force via a hint to use the index and i get a better performance on that.whay is that it is not using the index.where should i check for it?
SELECT DECODE(trim (table1.column1),'',table1.column2, table1.column1) column1
FROM table1 , (
SELECT DISTINCT table2.column1 FROM table2
WHERE table2.column2 = '324' ) x
WHERE table1.column1=table2.column1 AND
table1.column3 = '1' AND
table1.column4 = 2 AND
table1.column5 != 0 AND
table1.column6 != 0 AND
table1.column7 = 1

my composite index consits of column1,column3,column4,column5,column6&column7 of table1.

can anyone suggest me???
Go to the top of the page
 
+Quote Post
Manfred
post Jun 14 2005, 05:36 AM
Post #2


Advanced Member
***

Group: Members
Posts: 119
Joined: 23-March 05
From: Switzerland
Member No.: 1,696



Hi

The problem is the "!=". Exclude this attributes off the index or put them at the end of de index definition. Then it should work!

Regards

Manfred rolleyes.gif
Go to the top of the page
 
+Quote Post
burleson
post Jun 14 2005, 09:15 AM
Post #3


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



smile.gif

Hi Rajahyder,

When I have this problem, I first force the index with an index hint and confirm that it the best execution plan. Next I adjust schema statistics so that the query will accept the index without a hint:

http://www.dba-oracle.com/art_disk_io.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
mensrea
post Jun 14 2005, 03:02 PM
Post #4


Advanced Member
***

Group: Members
Posts: 162
Joined: 10-January 05
From: Greenville SC
Member No.: 1,246



One little word of caution with modifying your statistics to force it to use the index... it may also change queries that run "fine" to ones that perform poorly... Do some testing prior to doing that if you do it and after the change to make sure no other problems arise.

Regards,
Jeff
Go to the top of the page
 
+Quote Post
rajahyder
post Jun 15 2005, 02:26 AM
Post #5


Advanced Member
***

Group: Members
Posts: 128
Joined: 26-April 05
Member No.: 1,934



Hi Don,
U have mentioned that you will adjust the schema statistics, what does that mean?, can you please explain more on that.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 06:11 AM