indexed access instead of FULL TABLE ACCESSES

From: Richard van Nieuwenhoven <nir_at_cfc.atnet.at>
Date: Tue, 28 Jul 1998 12:23:47 +0200
Message-ID: <35BDA6B3.48474E6D@cfc.atnet.at>

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


-------------------------------------------------------------------

Received on Tue Jul 28 1998 - 05:23:47 CDT