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

One quick thought - are you sure that the types of the bind variables match the types of the columns - in particular the bind variable for

    >> WHERE ( POS_MWBT = :1 ) Check also the avg_data_blocks_per_key
and avg_leaf_blocks_per_key for the twi
indexes, and the clustering_factor.

If these are unrealistic you could try using dbms_stats.set_index_stats to write
suitable stats to the indexes. (Much cheaper than running analyze every night).

At what level are you running the analyze ? estimate, estimate percentage, compute ?

How accurate are the low/high values on
user_tab_columns that you get for the
5 columns in the where clause ?

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





>"Joerg Jost" <jost_at_unitrade.com> wrote in message

>news:g39tcu0i7oid3diilu9j13c8hdhioti8ag_at_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

>

>