Re: Cost decrease ?

"astalavista" <nobody_at_nowhere.com> wrote in message news:46cc9011$0$427$426a74cc_at_news.free.fr...
> Hi,
>
> In the explain plan below
> why the intermediate cost for index full scan is 5979
> and the final cost is 65.
> Something I did'nt understand ?
>
> Thanks in advance
>
> SQL> explain plan for
> 2 SELECT COUNT (*)
> 3 FROM wx_synd_data_f vnt, wc_prodcat_dh prd_dh
> 4 WHERE vnt.prod_wid = prd_dh.prod_wid AND prd_dh.usage_cd = 'Ville'
> 5 /
>
> Explained.
>
> Elapsed: 00:00:00.00
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ----------------------------------------------------------------------------------------------------
>
> -----------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows
> | Bytes | Cost (%CPU)|
> -----------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1
> | 13 | 65 (16)|
> | 1 | SORT AGGREGATE | | 1
> | 13 | |
> | 2 | MERGE JOIN | |
> 9808M| 118G| 65 (16)|
> | 3 | INDEX FULL SCAN | WX_SYND_DATA_F_F8 |
> 12M| 34M| 5979 (12)|
> |* 4 | SORT JOIN | | 1095
> | 10950 | 5 (60)|
> | 5 | TABLE ACCESS BY INDEX ROWID | WC_PRODCAT_DH | 1095
> | 10950 | 3 (34)|
> | 6 | BITMAP CONVERSION TO ROWIDS| |
> | | |
>
> PLAN_TABLE_OUTPUT
> ----------------------------------------------------------------------------------------------------
> |* 7 | BITMAP INDEX SINGLE VALUE | WC_PRODCAT_DH_BMX_M11 |
> | | |
> -----------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 4 - access("VNT"."PROD_WID"="PRD_DH"."PROD_WID")
> filter("VNT"."PROD_WID"="PRD_DH"."PROD_WID")
> 7 - access("PRD_DH"."USAGE_CD"='Ville')
>
> 20 rows selected.
>
> Elapsed: 00:00:00.02
>

Are you running with first_rows_N optimisation.

It really messes up the way the optimizer presents the information as it works out the cost of completing a step, then forwards a cost of acquiring only the necessary percentage of data needed to satisfy your value of N.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html