Re: perfomance problem index not used

You said you built composite-index on columns key1,key,key3.

Try to build separate index on key1,key and key3, then try your query to see if the performace improves.

In article <39a53ed7.31800306_at_192.168.2.227>,   nkess_at_gmx.net (Norbert Kess) wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Aug 24 2000 - 13:53:28 CDT