How to get Oracle to an Index without hints?

From: Joerg Jost <jost_at_unitrade.com>
Date: Tue, 30 Apr 2002 16:59:06 +0200
Message-ID: <g39tcu0i7oid3diilu9j13c8hdhioti8ag@4ax.com>

Hi there,

we are a company that are developing an ERP - Software with Unify - Vision Environment

We have a performance problem with a "normal" select - statement.

Here the select - statement:
SELECT

POS_ALTART, POS_ALTNUM, POS_ALTPOS, POS_ART, POS_AUSW, POS_BERMG,
POS_BERVK, POS_BESTF, POS_BESTKZ, POS_BETRAG, POS_BETRAG2, POS_BEZ1,
POS_BEZ2, POS_BKEY, POS_CNT, POS_CSBS, POS_DAT, POS_DRUMG,
POS_EINHEIT,  POS_EK, POS_EKVBO, POS_GEW, POS_ISTAW, POS_ISTMG,
POS_ISTVK, POS_KOEK,  POS_KOMNR, POS_KOND, POS_KOSTST, POS_KOVK,
POS_KZ, POS_LFBED, POS_LFID,  POS_LIEFD, POS_LISTE, POS_MATBAS,
POS_MERK, POS_MWBT, POS_MWNO, POS_MWST,  POS_NUM, POS_PABW, POS_PLMIN,
POS_POS, POS_PREIN, POS_RABAT, POS_RGEK,  POS_ROHDM, POS_RSVW,
POS_SKF, POS_SKTFHG, POS_SOFUEB, POS_SOLLAW,  POS_SPANNE, POS_STATUS,
POS_STPL, POS_TECH, POS_USER, POS_VOL, POS_VORMG,  PO_AT_AT_NO,

PO_AT_AT_SE_SE_AG_AG_NO, PO_AT_AT_SE_SE_AG_AG_WG, PO_AT_AT_SE_SE_NO, ROWID
FROM
PO

WHERE  ( POS_MWBT = :1 )  
AND  ( PO_AT_AT_NO = :2 )  
AND  ( PO_AT_AT_SE_SE_AG_AG_NO = :3 )  
AND  ( PO_AT_AT_SE_SE_AG_AG_WG = :4 )  
AND  ( PO_AT_AT_SE_SE_NO = :5 )  
ORDER BY pos_mwbt ASC, po_at_at_se_se_ag_ag_wg  ASC,

po_at_at_se_se_ag_ag_no ASC, po_at_at_se_se_no ASC, po_at_at_no ASC, pos_dat DESC

Sorry for the tremendous number of variables, but we have to fill out a screen with the infos :-)

The problem is, we have an index on table po which looks like the following:
INDEX_NAME COLUMN_NAME COLUMN_POSITION

PO_INDEX018  POS_MWBT                  1
PO_INDEX018  PO_AT_AT_SE_SE_AG_AG_WG   2
PO_INDEX018  PO_AT_AT_SE_SE_AG_AG_NO   3
PO_INDEX018  PO_AT_AT_SE_SE_NO         4
PO_INDEX018  PO_AT_AT_NO               5

But this index won´t be used by oracle to fetch the data. Instead of po_index018 oracle decides to use po_index016 which looks like the following:

INDEX_NAME COLUMN_NAME COLUMN_POSITION

PO_INDEX016 PO_AT_AT_SE_SE_AG_AG_WG 1
PO_INDEX016 PO_AT_AT_SE_SE_AG_AG_NO 2
PO_INDEX016 PO_AT_AT_SE_SE_NO       3
PO_INDEX016 PO_AT_AT_NO             4
PO_INDEX016 POS_ART                 5

Which index is used by oracle to get the data is figured out with the tkprof - utility.

My question is:
The table po is normally very big (around 1,000,000 - 15,000,000 records)
The analyze table statement is running every night for this table. But the sql - statement needs a lot of time to get the data. And even the rest of the system is getting slow, because the hard disks are very busy and effects the hole System.
Unfortunately with the Unify Vision environment we are not able to put hints in the SQL - Statement.
How do i have to design the index exactly for this statement.

Thx a lot for your help

Joerg Jost Received on Tue Apr 30 2002 - 09:59:06 CDT