| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: indexed access instead of FULL TABLE ACCESSES
Richard,
What I do not understand about your query is that you mention PARTYSORT
in you subquery.
The join of PARTYSORT with INCLUDEDPARTY seems not necessary to me.
As far as I can see the subquery should be:
...
A.OID IN (SELECT SEARCHA.oid
FROM
INCLUDEDPARTY SEARCHA
WHERE
(SEARCHA.OWNERPARTYLIST = 1055173) AND
ROWNUM < 101)
...
Am I wrong and missing something ?
For example:
1 select ename
2 from emp e
3* where ( e.empno = '7934' )
SQL> /
ENAME
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=20) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=20)
gives the same result as
1 select ename
2 from emp e,
3 dept d
4* where ( e.empno = '7934' and e.deptno = d.deptno )
SQL> /
ENAME
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=46) 1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=46)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=33) 3 1 TABLE ACCESS (FULL) OF 'DEPT'
dept should not be included.
Richard van Nieuwenhoven <nir_at_cfc.at> wrote in article
<35BF010A.8E9A4A08_at_cfc.at>...
> 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 Tue Aug 04 1998 - 07:05:29 CDT
 |
 |