Re: Fed Up with being a DBA

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 14 Sep 2003 22:00:15 +0100
Message-ID: <bk2kra$74n$1$8300dec7@news.demon.co.uk>

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


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


"Domenic G." <domenicg_at_hotmail.com> wrote in message
news:c7e08a19.0309131522.60934e40_at_posting.google.com...


>

> Now, I could do this ...

>

> CREATE INDEX null_fidx

> ON a_huge_table (NVL(any_column, 'NULL'))

> TABLESPACE whatever;

>

> ALTER SYSTEM SET query_rewrite_enabled = TRUE;

>

> but can I change the compiled code from ...

>

> "AND any_column IS NULL"

>

> to

>

> "AND NVL(any_column, 'NULL') = 'NULL'";

>

> AND, GUESS WHAT, without a hint, CBO will not pick up the index!!!

>

Received on Sun Sep 14 2003 - 16:00:15 CDT