Hello David,
It would be useful if you could also post the query you are executing, details
of your database version, and your table and indexes definition.
As a side note, the optimizer is including index access for some of the steps
of the execution plan.
Regards,

--Claudio



On Mon, 31 Aug 2009 20:56:45 +0200, David NGUYEN wrote
> hello experts
>
> i run a query that needs 2hours to give me a result
> the table RUBRIQUE_QUANTITE_JOURNALIERE is full access et owns
> 3035000 rows there are indexes created on this table and i don't
> know why the optimizer have choose the table full scan someone has
> any idea about this..?
>
> the execution plan is following:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3120 Card=1
> Bytes=11 7)
>
> 1 0 NESTED LOOPS (OUTER) (Cost=3120 Card=1 Bytes=117)
> 2 1 NESTED LOOPS (Cost=3119 Card=1 Bytes=93)
> 3 2 NESTED LOOPS (OUTER) (Cost=2403 Card=358 Bytes=18616)
> 4 3 NESTED LOOPS (Cost=2403 Card=358 Bytes=16826)
> 5 4 NESTED LOOPS (Cost=2403 Card=358 Bytes=15036)
> 6 5 TABLE ACCESS (FULL) OF
> 'RUBRIQUE_QUANTITE_JOURNA LIERE' (Cost=2403 Card=2143
> Bytes=79291)
>
> 7 5 INLIST ITERATOR
> 8 7 INDEX (UNIQUE SCAN) OF 'PK_RUBRIQUE_QUANTITE'
> (UNIQUE)
>
> 9 4 INLIST ITERATOR
> 10 9 INDEX (UNIQUE SCAN) OF
> 'PK_T_RUBRQTE_LST_TYPE_QT E' (UNIQUE)
>
> 11 3 INDEX (UNIQUE SCAN) OF 'PK_T_RUBRQTE_LST_REF'
> (UNIQU E)
>
> 12 2 INLIST ITERATOR
> 13 12 TABLE ACCESS (BY INDEX ROWID) OF 'T_LAST_RUB_QTEJ'
> ( Cost=2 Card=64637 Bytes=2650117)
>
> 14 13 INDEX (UNIQUE SCAN) OF 'PK_T_LAST_RUB_QTEJ'
> (UNIQU E) (Cost=1 Card=64637)
>
> 15 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_RUBRQTE_LST_PTY'
> (Co st=1 Card=4545 Bytes=109080)
>
> 16 15 INDEX (UNIQUE SCAN) OF 'PK_T_RUBRQTE_LST_PTY' (UNIQUE)
>
> Statistics
> ----------------------------------------------------------
> 119 recursive calls
> 4 db block gets
> 827813972 consistent gets
> 39384 physical reads
> 0 redo size
> 2976388 bytes sent via SQL*Net to client
> 288101 bytes received via SQL*Net from client
> 4283 SQL*Net roundtrips to/from client
> 638 sorts (memory)
> 0 sorts (disk)
> 64223 rows processed
>
> thanks in advance.
> david..
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html


Oracle LazyDBA home page