Re: Fed Up with being a DBA

Jonathan Lewis wrote:

>Try this:
>
>drop table t1;
>
>create table t1
>as
>select
> decode(
> trunc(rownum,1000),
> 0, to_number(null),
> trunc(rownum,1000)
> ) nulled_col,
> rpad(rownum,10) v1,
> rpad('x',100) padding
>from
> all_objects
>where
> rownum <= 10000
>;
>
>create index t1_null_idx on t1(nvl2(nulled_col,null,'NULL'));
>
>analyze table t1 compute statistics;
>
>
>alter session set optimizer_mode = all_rows;
>alter session set query_rewrite_enabled = true;
>
>spool fbi_on_null
>
>set autotrace traceonly explain
>
>select v1
>from t1
>where nvl2(nulled_col,null,'NULL') = 'NULL'
>;
>
>spool off
>
>rem
>rem Execution Plan
>rem ----------------------------------------------------------
>rem 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1
>Bytes=13)
>rem 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1
>Bytes=13)
>rem 2 1 INDEX (RANGE SCAN) OF 'T1_NULL_IDX' (NON-UNIQUE)
>(Cost=1 Card=1)
>rem
>
>
>--
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>

There you go trying to demonstrate how understanding something helps one work with it.

How low can you sink? ;-)

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)