Re: indexed access instead of FULL TABLE ACCESSES

I think you need an optimizer hint
I found this in 'Advanced oracle tuning and administration' from Oracle Press (very good BTW) :

The INDEX hint can be used in three different ways:

1. if a single index is listed, that index will be used.
2. If multiple indexes are listed, the optimizer will choose which indexes to use. 3 If a table is listed, but no indexes are listed, the optimizer will choose an index or indexes to use for that table.

Given the query in the following listing, the optimizer may choose to use the index on City, the index on State, or both, as appropriate.

select /*+ INDEX(COMPANY) */

        Name, City, State
  from COMPANY
where City = 'Roanoke'

    and state = 'VA';

hope this helps

Richard van Nieuwenhoven <nir_at_cfc.atnet.at> wrote in article <35BDA6B3.48474E6D_at_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 - 07:50:21 CDT