perfomance problem index not used

From: Norbert Kess <nkess_at_gmx.net>
Date: Thu, 24 Aug 2000 15:35:07 GMT
Message-ID: <39a53ed7.31800306@192.168.2.227>

Hello,

I'm using Oracle 8i V8.1.5 on Windows NT.

To emulate a ISAM ISGREAT/ISGTEQ-selection over a key with several key-parts i need to perform a select-statement that basically looks like the follwing example with 3 key-parts:

SELECT *
FROM ( SELECT /*+FIRST_ROWS*/ *

           FROM ( SELECT   /*+FIRST_ROWS*/ *
                    FROM ( SELECT   /*+FIRST_ROWS*/ *
                             FROM ( SELECT   /*+FIRST_ROWS*/ *
                                      FROM testtable
                                     WHERE  (key1 >= keyValue1))
                            WHERE  ( key1 > keyValue1
                                  OR key2 >= keyValue2))
                   WHERE  ( key1 > keyValue1
                         OR key2 > keyValue2
                         OR key3 >= keyValue3))
          ORDER BY key1 ASC, key2 ASC, key3 ASC)

WHERE rownum < someSmallValue;

The columns key1,key,key3 builds a composite-index and there are many non-indexed columns in the table.

It works but the performance in tables with many rows (> 50000) is sometimes very poor (depending from the key-values). It seems like oracle don't use the INDEX (key1,key2,key3) rather than performing a full-table-scan.

I've tried several ways to make the statement running faster:

reverse the order from the subquerys
using unions instead of subquerys
using a AND/OR expression in the where-clause from one select-statement instead of subquerys

but nothing helps. Any suggestions would be appreciated.

Thanks, Norbert Received on Thu Aug 24 2000 - 10:35:07 CDT