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

I remember experiencing a bind typing problem several years ago with v7. Essentially, Oracle would convert all the data values to the bind variable type in an attempt to evaluate the criteria (rather than converting the bind variable). I had mentioned to someone to watch out for this problem just a couple weeks ago. However, when I attempted to create a simple demonstration of the problem, I couldn't recreate it under 8i or 9i. Did Oracle change the way binds are handled? Can you give an example that demonstrates it under the current versions?

Richard

Jonathan Lewis wrote:
>
> 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
> >
> >
Received on Wed May 01 2002 - 12:16:37 CDT