Daniel Morgan wrote:
> 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
>
>
Because he's using a tool that generates SQL; it does so 'on the fly',
as he stated in his fore-last line...
How about:
- analyzing the index (sounds obvious, but is it done?)
- using histograms on your index (is your data evenly distibuted?)
- rewriting the where clause to:
WHERE ( PO_AT_AT_NO = :2 )
AND ( PO_AT_AT_SE_SE_NO = :5 )
AND ( PO_AT_AT_SE_SE_AG_AG_NO = :3 )
AND ( PO_AT_AT_SE_SE_AG_AG_WG = :4 )
AND ( POS_MWBT = :1 )
provided your tool allows you to do that.
If all else fails - replace tool!
Received on Tue Apr 30 2002 - 14:00:32 CDT
