Forum Stats

  • 3,750,083 Users
  • 2,250,104 Discussions
  • 7,866,770 Comments

Discussions

How to "where any of(b,c,d,e,f,g,h..) is null" with SQL(not PL/SQL)

13»

Comments

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Sep 7, 2007 10:37AM
    SQL> with WorkView as (select 1 b, 2 c, 1 d, 1 e, 1 f, 1 g, 1 h from dual
    2 union all select 1, 1, 1, 1, null, 1, 1 from dual
    3 union all select 1, 1, 1, 1, 1, 1, 1 from dual
    4 union all select null, null, null, null, null, null, null from dual
    5 union all select 1, null, 1, null, 1, null, 1 from dual)
    6 select b,c,d,e,f,g,h,
    7 case when greatest(b, c, d, e, f, g, h) is null then 1 else 0 end as "null1",
    8 case when Least(b, c, d, e, f, g, h) is null then 1 else 0 end as "null2"
    9 from WorkView;

    B C D E F G H null1 null2
    ---- ---- ---- ---- ---- ---- ---- ----- -----
    1 2 1 1 1 1 1 0 0
    1 1 1 1 null 1 1 1 1
    1 1 1 1 1 1 1 0 0
    null null null null null null null 1 1
    1 null 1 null 1 null 1 1 1
    On greatest function and Least function,
    if parameters have null,
    these functions return null.
  • 426850
    426850 Member Posts: 1,242
    Thank you all for your interests and time,
    JS1 : where x+y+z is null;
    If the columns are not numeric you get "ORA-01722: invalid number" error
    Rob van Wijk : Then the question will boil down to: is one full table scan a problem in your case? If not, the "several or" approach looks sufficient. If it is a problem, then you might need a function based index on the nvl2 expression.
    Rob van Wijk : But no matter how brilliant, or how fun-to-write these solutions are, I cannot think of a valid reason in terms of performance and future maintenance, why not to write the "several or" variant.
    Alessandro Rossi : Where do you want to find something quicker than this
    I thought there could be an Analytics alternative versus Generic SQL for this need but upto now yes or method seems to be the best and since this is a daily report on an oltp(update, insert intensive) table function based indexing is not prefered.

    best regards.
    create table demo_table nologging as
    select * from dba_source union all
    select * from dba_source union all
    select * from dba_source
    /

    update demo_table set owner = NULL where mod(line, 43) = 0 ;
    update demo_table set text = NULL where mod(line, 11) = 0 ;
    update demo_table set type = NULL where mod(line, 93) = 0 ;

    analyze table demo_table compute statistics ;

    select num_rows from user_tables where table_name = 'DEMO_TABLE' ;

    NUM_ROWS
    ----------
    462099

    set timing on
    set autotrace traceonly statistics
    select count(*) from demo_table
    where nvl2(owner, nvl2(name, nvl2(type, nvl2(line, text, null), null), null), null) is null
    /

    -- each scenerio have the same consistent gets amount - 29903
    -- avg. of 5 runs is .37

    select count(*) from demo_table
    where owner is null
    or name is null
    or type is null
    or line is null
    or text is null
    /

    -- avg. of 5 runs is .17

    select count(*) from demo_table
    where exists
    ( select 'a null value'
    from table(sys.dbms_debug_vc2coll (owner, name, type, line, text))
    where column_value is null
    )
    /

    -- avg. of 5 runs is .87

    select count(*) from (
    select case when greatest(owner, name, type, line, text) is null then 1 else 0 end as x,
    case when least(owner, name, type, line, text) is null then 1 else 0 end as y
    from demo_table )
    where x is null or y is null
    /

    -- avg. of 5 runs is .65

    set autot off
  • 535698
    535698 Member Posts: 28
    LOL @ padders

    ;-)
This discussion has been closed.