Re: indexed access instead of FULL TABLE ACCESSES

From: <asprusch_at_my-dejanews.com>
Date: Tue, 28 Jul 1998 14:46:06 GMT
Message-ID: <6pko7e$s5p$1@nnrp1.dejanews.com>

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 Tue Jul 28 1998 - 09:46:06 CDT