Re: Cost=BLANK on query plan display

Jason,

    Oracle uses cost based optimization only if you have analyzed the table using analyze command. Otherwise it uses rule base optimization. To analyze the table use

analyze table <tablename> compute statistics.

I read in ORACLE DBA Handbook that you should not analyze ORACLE Financials tables. Also it needs the 4 times the size of a table space in temporary tablespace. It can take quite some time.

raghuvir

Jason Selby <jselby_at_cams.co.uk> wrote in article <3549E185.ABCB220B_at_cams.co.uk>...
> Hi
>
> When I build a query plan as follows
>
> explain plan set statement_id = 'cav032' for
> SELECT
> term_key||'|'||term_id||'|'||key_type||'|'||rank||'|'||medical_type
> FROM cav_mrkey m
> WHERE NOT EXISTS (
> SELECT * FROM cav_keys k
> WHERE m.term_key = k.term_key
> AND m.term_id = k.term_id
> AND m.key_type = k.key_type);
>
> And display it in SQL*PLUS like so
>
> select lpad(' ',2*(level-1))||operation||' '||options||' '||
> object_name||' '||decode(id,0,'Cost = '||position) "Query Plan"
> from plan_table
> start with id = 0 and statement_id = 'cav032'
> connect by prior id = parent_id and statement_id = 'cav032';
>
> I get
>
> Query Plan
> ----------------------------------------------
> SELECT STATEMENT Cost =
> FILTER
> TABLE ACCESS FULL CAV_MRKEY
> INDEX RANGE SCAN I_CAV_KEYS_TERMIDTYPE
>
> Why is 'Cost=' always a blank figure is there another way of getting the
> overall cost of a query
>
> TIA
>
> Jason
>
>
>
Received on Sat May 02 1998 - 00:51:54 CDT