Re: indexed access instead of FULL TABLE ACCESSES

sorry, i have tried this already. after specifiying all indexes with hints the optimiser takes them but still uses HASH JOINS instead of NESTED LOOPS witch is still very slow.
I have now splitted the statement in two selects. The Subselect and vor every result I do, in my program, the main selection in a seperate SqlStatement.Even if this means 101 Sql statements it performs bether as the first statement.

thanks,
anyway

asprusch_at_my-dejanews.com wrote:

> I think what you want is that the subquery will be the driving
> table. In addition to this, the index on INCLUDEPARTY.OID should
> be used.
>
> Try the follwing:
>
> SELECT /*+ ordered index(A oid) */
> A.OID as col1,
> B.OID as col2,
> C.NUMBER_ as col3,
> C.SURNAME as col4,
> C.FIRSTNAME as col5
> FROM
> (SELECT SEARCHA.oid aiod
> FROM
> INCLUDEDPARTY SEARCHA,
> PARTYSORT SEARCHB
> WHERE
> (SEARCHA.OWNERPARTYLIST = 1055173) AND
> SEARCHB.NUMBER_ = SEARCHA.partynumber_ AND
> ROWNUM < 101) D,
> INCLUDEDPARTY A,
> PERSONALADVISERRELATION B,
> PERSONALPARTY C
> WHERE
> B.OID = A.PERSONALADVISERRELATION AND
> C.NUMBER_ = B.OWNERPARTY AND
> A.OID = D.AOID
> ORDER BY col4,col5
>
> Hope this will help and that i have understand the problem
> Andreas Prusch
>
> In article <35BDA6B3.48474E6D_at_cfc.atnet.at>,
> Richard van Nieuwenhoven <nir_at_cfc.atnet.at> wrote:
> > How can I direct the optimizer to use indexed access instead of
> > FULL TABLE ACCESSES. There is a subquery witch selects 100 rows
> > out of 10.000 (up to 500.000) to select a very big join of these
> > 100 rows. In the example below I reduced the complexity of this
> > join to illustrate my problem. Defining the subselect as a
> > join does not help, the explain plan is the same.
> >
> > Does anybody know how I can rewrite this statement so that it
> > performs, thanks for any help.
> >
> > Ritchie
> > -------simplefied statement---------------------------------------
> > SELECT
> > A.OID as col1,
> > B.OID as col2,
> > C.NUMBER_ as col3,
> > C.SURNAME as col4,
> > C.FIRSTNAME as col5
> > FROM
> > INCLUDEDPARTY A,
> > PERSONALADVISERRELATION B,
> > PERSONALPARTY C
> > WHERE
> > B.OID = A.PERSONALADVISERRELATION AND
> > C.NUMBER_ = B.OWNERPARTY AND
> > A.OID IN (SELECT SEARCHA.oid
> > FROM
> > INCLUDEDPARTY SEARCHA,
> > PARTYSORT SEARCHB
> > WHERE
> > (SEARCHA.OWNERPARTYLIST = 1055173) AND
> > SEARCHB.NUMBER_ = SEARCHA.partynumber_ AND
> > ROWNUM < 101)
> > ORDER BY col4,col5
> > -------------------------------------------------------------------
> > there are indices on
> > INCLUDEDPARTY (oid)
> > INCLUDEDPARTY (OWNERPARTYLIST)
> > PERSONALPARTY (number_)
> > PERSONALADVISERRELATION (oid)
> > PERSONALADVISERRELATION (ownerparty)
> > PARTYSORT is a index organized table
> >
> > ------explained as--------------------------------------------------
> > SELECT STATEMENT Cost = 1843411718
> > SORT ORDER BY
> > HASH JOIN
> > TABLE ACCESS FULL PERSONALPARTY
> > HASH JOIN
> > TABLE ACCESS FULL PERSONALADVISERRELATION
> > HASH JOIN
> > VIEW
> > SORT UNIQUE
> > COUNT STOPKEY
> > HASH JOIN
> > TABLE ACCESS BY INDEX ROWID INCLUDEDPARTY
> > INDEX RANGE SCAN INCLUDEDPARTYIDX2
> > INDEX FULL SCAN PARTYSORTIDX1
> > TABLE ACCESS FULL INCLUDEDPARTY
> > -------------------------------------------------------------------
> >
> >
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Wed Jul 29 1998 - 06:01:31 CDT