Index not getting used, Index not getting used Options

[rajahyder]

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

[Manfred]

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

[burleson]

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

[mensrea]

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

[rajahyder]

Hi Don,
U have mentioned that you will adjust the schema statistics, what does that mean?, can you please explain more on that.