1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to skip a condition in where clause when the i/p is null?

Discussion in 'SQL PL/SQL' started by Vicky, Apr 19, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    how to skip a condition in where clause when the i/p is null.,

    Suppose., If I use Query like below.,

    select * from Domian_dtls
    where (' ' is null or Domain_id=' ');

    The query cost is more than 300.,

    If i pass a domain_id means, The query cost is 2.,

    Why these much variation, just for skipping the condition., ??

    Or cud U suggest any better performing functions to skip the condition is if it's a NULL.....
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Your question isn't really clear. You start out talking about an IP being NULL and then switch to a domain being NULL. Your query doesn't help because it's not clear why you would have ''=NULL as a condition. I'll take a stab at what I think the issue might be...

    NULL values in a column are not stored in an index. Therefore if you have a query against a column that is indexed, and you have a condition 'column_name IS NULL' then you have prevented that index from being considered by the optimizer.

    If your app were to store a specific value when the IP is NULL (say '0.0.0.0'), then your condition could then be column_name = '0.0.0.0' -- at which point an index on the column could still be utilized by the optimizer.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your condition


    where ' ' is null


    makes no sense because a space cannot be NULL, ever. Your query is asking to return records where a space is null (which is the same as select * From domain_dtls where 0=1) or where domain_id is equal to a space. There are no NULL conditions stated using columns of the Domain_dtls table so Oracle is performing a table scan to find the data. Your cost of 2 is the 'standard' cost for any query where unequal constants are set equal to each other.


    If you are truly looking for NULL values you need to code for that and the query you've submitted doesn't fill that bill. If we rewrite the query to change the space to the empty string (''):


    Code (SQL):

    SQL> SELECT * FROM Domain_dtls
      2  WHERE ('' IS NULL OR Domain_id='');


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
    Domain1  MyDomain1
    Domain2  MyDomain2
    Domain3  MyDomain3
    Domain4  MyDomain4
    Domain5  MyDomain5
    Domain6  MyDomain6
    Domain7  MyDomain7
    Domain8  MyDomain8
             MyDomain9
    Domain10 MyDomain10
    Domain11 MyDomain11


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
    Domain12 MyDomain12
    Domain13 MyDomain13
    Domain14 MyDomain14
    Domain15 MyDomain15
    Domain16 MyDomain16
    Domain17 MyDomain17
             MyDomain18
    Domain19 MyDomain19
    Domain20 MyDomain20
    Domain21 MyDomain21
    Domain22 MyDomain22


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
    Domain23 MyDomain23
    Domain24 MyDomain24
    Domain25 MyDomain25
    Domain26 MyDomain26
             MyDomain27
    Domain28 MyDomain28
    Domain29 MyDomain29
    Domain30 MyDomain30
    Domain31 MyDomain31
    Domain32 MyDomain32
    Domain33 MyDomain33


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
    Domain34 MyDomain34
    Domain35 MyDomain35
             MyDomain36
    Domain37 MyDomain37
    Domain38 MyDomain38
    Domain39 MyDomain39
    Domain40 MyDomain40
    Domain41 MyDomain41
    Domain42 MyDomain42
    Domain43 MyDomain43
    Domain44 MyDomain44


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
             MyDomain45
    Domain46 MyDomain46
    Domain47 MyDomain47
    Domain48 MyDomain48
    Domain49 MyDomain49
    Domain50 MyDomain50
    Domain51 MyDomain51
    Domain52 MyDomain52
    Domain53 MyDomain53
             MyDomain54
    Domain55 MyDomain55


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
    Domain56 MyDomain56
    Domain57 MyDomain57
    Domain58 MyDomain58
    Domain59 MyDomain59
    Domain60 MyDomain60
    Domain61 MyDomain61
    Domain62 MyDomain62
             MyDomain63
    Domain64 MyDomain64
    Domain65 MyDomain65
    Domain66 MyDomain66


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
    Domain67 MyDomain67
    Domain68 MyDomain68
    Domain69 MyDomain69
    Domain70 MyDomain70
    Domain71 MyDomain71
             MyDomain72
    Domain73 MyDomain73
    Domain74 MyDomain74
    Domain75 MyDomain75
    Domain76 MyDomain76
    Domain77 MyDomain77


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
    Domain78 MyDomain78
    Domain79 MyDomain79
    Domain80 MyDomain80
             MyDomain81
    Domain82 MyDomain82
    Domain83 MyDomain83
    Domain84 MyDomain84
    Domain85 MyDomain85
    Domain86 MyDomain86
    Domain87 MyDomain87
    Domain88 MyDomain88


    DOMAIN_I DOMAIN_NME
    -------- ----------------------------------------
    Domain89 MyDomain89
             MyDomain90
    Domain91 MyDomain91
    Domain92 MyDomain92
    Domain93 MyDomain93
    Domain94 MyDomain94
    Domain95 MyDomain95
    Domain96 MyDomain96
    Domain97 MyDomain97
    Domain98 MyDomain98
             MyDomain99


    99 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 330379090
    ---------------------------------------------------------------------------------
    | Id  | Operation         | Name        | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ---------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |             |    99 |  2772 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| DOMAIN_DTLS |    99 |  2772 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------
    Note
    -----
       - dynamic sampling used FOR this statement (level=2)

    Statistics
    ----------------------------------------------------------
              5  recursive calls
              0  db block gets
             22  consistent gets
              0  physical reads
              0  redo SIZE
           4131  bytes sent via SQL*Net TO client
            590  bytes received via SQL*Net FROM client
              8  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
             99  ROWS processed


    SQL>
     

    As you see the modified query returns all of the table data; this is because Oracle considers '' (the empty string) the same as NULL so the query becomes:


    select * from domain_dtls
    where (NULL is NULL or Domain_id = NULL);


    Nothing is equal to NULL so the second condition is useless and is not evaluated; any time you tell Oracle that a constant is equal to itself the entire table contents are returned.
     
    Vicky likes this.
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Let me explain in detail....

    select * from Domain_dtls
    where Domain_id='TOM'
    and domain_name='SAM'
    AND ('' is null or company='');

    Actually, the domain_id, domain_name is mandatory., and company is optional, i.e., if the value is passed from screen, i should consider that, else I should skip the condition for company.,

    This query helps me to achieve the desired result.,

    But the Query cost is too high if no values are passed from application..i.e., I'm using ('' is null or company='') to skip this company filter condition.,

    Is der any alternate way to achieve this, i.e., to skip a filter if no values been passed from application..
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The best bet if you have conditions that only appear in certain circumstances is to use dynamic SQL. That provides the most flexibility and you can write the tightest queries. That said, you may be able to achieve the results you want with the following:

    Code (Text):
    SELECT *
    FROM  Domain_dtls
    WHERE Domain_id='TOM'
    AND   domain_name='SAM'
    AND   NVL(passed_value, company) = company;
    When the value passed by your application is NULL, the NVL changes it to return the company column... which will then be true for (almost all) rows. The caveat is if you have values in your table where the company field is NULL... in which case you'd get NULL = NULL, which does not evaluate to TRUE. If so (and dynamic SQL isn't an option), you could get a bit uglier and handle that too:

    Code (Text):
    SELECT *
    FROM  Domain_dtls
    WHERE Domain_id='TOM'
    AND   domain_name='SAM'
    AND   NVL(passed_value, NVL(company, 'x')) = NVL(company, 'x');
    In the above -- in cases where the company field is NULL, it is changed to 'x' on both sides to generate a TRUE result.
     
    Vicky likes this.
  6. dev

    dev Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    KUWAIT
    IF COLUMN DATA TYPY---> CHAR U'LL Choose Domain_id=' '
    BUT COLUMN DATA TYPY---> NUMBER Choose IS NULL

    Thanks & Regards,

    Mahmoud Khattab
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    You still have 'useless' conditions: '' is null is saying NULL is NULL and will return everything from the table and company='' is saying company = NULL and no such condition will return true since nothing in the database can equal NULL. The query therefore returns all data meeting the prior two conditions and won't filter on NULL company values. I don't understand why you persist in using this construct when the following query should return the desired results:


    select * from Domain_dtls
    where Domain_id='TOM'
    and domain_name='SAM'
    AND company is null;
     
  8. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I believe based on the context that the OP is using the quoted nothing to represent input from the application.
    I would have represented it as:

    Code (Text):
    AND ('[input from screen/app]' is null or company='[input from screen/app])';
    At the very least, that's the assumption I used for my last reply above.
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tks for UR reply Guys...

    As per Ur suggestn... I'm using NVL to handle NULL values.,

    Ex:


    Code (SQL):

    SELECT * FROM DOMAIN_DTLS
    WHERE COMPANY = NVL('WARNE',COMPANY);
    --NVL(COMPANY,'X')=NVL('WARNE',NVL(COMPANY,'X'));
     
    Even though, Its giving huge query cost(309), for getting just 1 record.,

    For direct Qry.,

    Code (SQL):

    SELECT * FROM DOMAIN_DTLS
    WHERE COMPANY='WARNE';
     
    The query cost is just 2...

    What is the prob here.,

    Should I use any func.Index or anything ?!1
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    in your query the data access method is used: full table scan

    for the correct decision it is necessary to know nature of change of data, data volume with null and not null values, etc.

    for the purpose of study simple example...

    there are some solutions:
    1) to create the functional index
    2) to create the virtual column and on it to construct an index.
    in case of decision-making of substitution of NULL of values it is necessary to consider data volume.

    for example :
    1) cr_tab,ins_tab,get_stats
    Code (SQL):

    prompt CREATE TABLE
    prompt
    DROP TABLE test_idx;
    CREATE TABLE test_idx
    ( id INT NOT NULL,
    x INT,
    isnull_x AS (nvl2(x,NULL,1)),
    CONSTRAINT test_idx_pk PRIMARY KEY(id)
    );
    prompt


    prompt CREATE indexes
    CREATE INDEX test_idx_i1 ON test_idx(x);

    CREATE INDEX test_idx_i2 ON test_idx(isnull_x);
    prompt

    prompt insering
    INSERT INTO test_idx (id,x)
    SELECT
    level,
    CASE
    WHEN MOD(level,2) = 0 THEN level
    END
    FROM dual
    CONNECT BY level < 101;

    commit;

    prompt

    prompt gather_table_stats

    BEGIN
    dbms_stats.gather_table_stats
    (
    ownname => USER,
    tabname => 'test_idx',
    cascade => TRUE
    );
    END;
    /
    prompt


     

    2) view stats
    Code (SQL):

    prompt put stats OF INDEX
    SELECT
        t.TABLE_NAME,t.num_rows
    FROM user_tables t
    WHERE t.TABLE_NAME = 'TEST_IDX';

    SELECT
        i.index_name,i.num_rows,i.distinct_keys
    FROM user_ind_statistics i
    WHERE i.TABLE_NAME = 'TEST_IDX';


     
    3) query : selected rows with x is null
    Code (SQL):


    SET autotrace traceonly EXPLAIN;

    SELECT * FROM test_idx i WHERE i.isnull_x = 1;


     
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I think the major problem here is the incorrect use of nvl() as you have the parameters reversed; it should be:


    nvl(company, 'WARNE')


    since you want WARNE to be the company if the company value is NULL. You're checking to see if the literal string 'WARNE' is null and it never will be.
     
  12. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    David:

    'WARNE' is standing in for the value passed from the application/screen. COMPANY is the table column.
    The logic is to use the value from the column if the passed value is NULL.

    Code (Text):
    SELECT *
    FROM  Domain_dtls
    WHERE Domain_id='TOM'
    AND   domain_name='SAM'
    AND   NVL(passed_value, company) = company;
     
  13. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    As I indicated earlier, dynamic SQL is what will allow you to generate the optimal result. NDS will allow you to put in the condition where appropriate and leave it off where inappropriate.