Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    34

    Unanswered: Oracle perf query

    Hi Guyz,

    Need suggestion in tuning this query please,
    Oracle version 10.2.0.4, OS Linux x86_64
    I have enclosed the attachments for the Query & Explain Plan Files along with this,

    Note
    1 the dbms table stats is up to date

    2 if i remove the last two criteria from the query
    (# (T30.STAT_REASON_CD IS NULL OR T30.STAT_REASON_CD = 'Constituent') AND
    (T28.X_SRM_ACTIVITY_ID IS NULL AND T28.ATTRIB_54 = 'Letter Required')
    it runs much faster, .... that's crazy

    3. the concerned table involved in this criteria (s_contact_fnxm) has 2.1 million records



    Earliest help will be highly appreciatable,
    Thanks,
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Oracle does not index null values, so any request for a NULL value against a table results in a full scan. But it looks like T30.STAT_REASON_CD must be included in an index, so you're only performing a full scan on the index instead of the table, so that's a good thing.

    About a month ago, someone posted a method of using CREATE INDEX where it actually tells Oracle to index NULL values. I cannot find that for the life of me. Hopefully someone will post that syntax again. I don't know which db version it was compatible with, though.

    The only other way I know of to get similar results is to create a function-based index using NVL(T30.STAT_REASON_CD).

    --=Chuck

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    My bad, it wasn't a parm or anything, it was the introduction of a constant into the index definition, effectively making a 2-column index with the null-possible value as the first column in the index:

    Code:
    create index foo on t30 (STAT_REASON_CD, 1);
    http://www.dbforums.com/oracle/16552...nce-issue.html

    I haven't experimented with this yet, so test it out first.

    --=Chuck

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    just create a composite index on two columns.

    create index mytable_i1 on mytable (STAT_REASON_CD,a_not_null_column);

    This will allow you to index and search on nulls
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2010
    Location
    NYC
    Posts
    10
    You could also index the NVL of the value:

    CREATE INDEX foo ON t30 (NVL(STAT_REASON_CD,'NULL'));

    Then in your query always join on the NVL() instead of the field by itself.

    This assumes you don't have an STAT_REASON_CD values of "NULL".
    --------------------------------------------------
    Matt
    Case Culture - Fashion Cases for iPhone and BlackBerry

  6. #6
    Join Date
    Aug 2010
    Posts
    34
    Thanx guyz, I have created the composite index,
    create index siebel.stat_rsn_cd_nnc_indx on siebel.s_contact(STAT_REASON_CD, 1);

    Its created succesfully though but when i try to run the query again,
    It was taking ages to finish it,
    I try to ran the table dbms_stats for the concerned table s_contact after the index creation but it threw errors like

    ERROR at line 1:
    ORA-03001: unimplemented feature
    ORA-06512: at "SYS.DBMS_STATS", line 13437
    ORA-06512: at "SYS.DBMS_STATS", line 13457
    ORA-06512: at line 2

    The unimplemented feature I tried google but invain to me ( not clear to me)
    the oracle db i use is 10.2.0.4,

    I appreciate your earlier suggestions, thanx

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    I got the same results in 10.1.0.4.0 and 11.1.0.7.0:

    Code:
    create table test_index (can_be_null number(10));
    
    create index test_index_ix1 on test_index (can_be_null);
    
    
    begin
      for i in 1..10000
      loop
        if mod(i,100)=0 then
          insert into test_index values (null);
        else
          insert into test_index values (i);
        end if;
      end loop;
    end;
    Code:
    select * from test_index where can_be_null is null
    
    Plan
    SELECT STATEMENT  ALL_ROWSCost: 6  Bytes: 1,300  Cardinality: 100  	
    	1 TABLE ACCESS FULL TABLE FORBESC.TEST_INDEX Cost: 6  Bytes: 1,300  Cardinality: 100  
    
    
    
    select * from test_index where can_be_null = 1
    
    Plan
    SELECT STATEMENT  ALL_ROWSCost: 1  Bytes: 13  Cardinality: 1  	
    	1 INDEX RANGE SCAN INDEX FORBESC.TEST_INDEX_IX1 Cost: 1  Bytes: 13  Cardinality: 1
    Code:
    create index test_index_ix2 on test_index (can_be_null,1);
    
    
    select * from test_index where can_be_null is null
    
    Plan
    SELECT STATEMENT  ALL_ROWSCost: 3  Bytes: 1,300  Cardinality: 100  	
    	1 INDEX RANGE SCAN INDEX FORBESC.TEST_INDEX_IX2 Cost: 3  Bytes: 1,300  Cardinality: 100  
    
    
    select * from test_index where can_be_null = 1
    
    Plan
    SELECT STATEMENT  ALL_ROWSCost: 1  Bytes: 13  Cardinality: 1  	
    	1 INDEX RANGE SCAN INDEX FORBESC.TEST_INDEX_IX1 Cost: 1  Bytes: 13  Cardinality: 1
    --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •