Re: How to get Oracle to an Index without hints?

Joerg Jost wrote:

> 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

What I don't understand about your question is why, in your Subject, you reject the simplest solution to the problem. Why don't you want to try using a Hint?

Daniel Morgan Received on Tue Apr 30 2002 - 10:13:30 CDT