DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Index/performance tuning

  1. #1
    Join Date
    Mar 2004
    Posts
    26

    Index/performance tuning

    I have a below query:

    SELECT * FROM BUSINESS_PL
    WHERE ADDRESS IS NULL;

    from the above table, in the explain plan, it does a full table scan. but the cost is very low b/c the table had less than 100 records. Any way, the question is "can we put the index on the ADDRESS column"??? even the column is NULL???

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You can get nulls indexed in some circumstances, such as ...
    • Using a bitmap index -- but don't do it on an OLTP systems
    • Use a composite index, where the other column is not null when the address column is null
    • Use a function-based index ... in thiscase you can choose to index only null values.


    The second option is the easiest.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    > Any way, the question is "can we put the index on the ADDRESS column"??? even the column is NULL???

    And the answer is, yes. Note, however, there will be no index entries for the NULL values. So an index on a 100% NULL column will be empty.

    Example follows:
    ===

    drop table t;
    create table t as select rownum id, all_objects.* from all_objects where rownum < 12000;
    update t set object_type = NULL;
    create index t_idx1 on t(object_type);
    analyze table t compute statistics for table for all indexes for all indexed columns;

    --- Access dba_indexes.
    --- Identify indexes with zero rows.
    select INDEX_NAME, INDEX_TYPE, TABLE_NAME,
    LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY,
    AVG_DATA_BLOCKS_PER_KEY
    NUM_ROWS, BUFFER_POOL
    from dba_indexes
    where owner = 'your ID here'
    and table_name = 'T'
    and num_rows = 0
    order by table_name desc;

    ===
    Result is:
    INDEX_NAME INDEX_TYPE TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY NUM_ROWS BUFFER_POOL
    T_IDX1 NORMAL T 0 0 0 0 DEFAULT

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by paladin
    > Any way, the question is "can we put the index on the ADDRESS column"??? even the column is NULL???

    And the answer is, yes. Note, however, there will be no index entries for the NULL values. So an index on a 100% NULL column will be empty.
    How about the three methods i just gave for getting those nulls included in an index?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2004
    Posts
    6
    Two of your methods should indeed work, the 1st and 3rd. Of course, the 1st and 3rd will work only with the CBO and not the RBO.

    As for the 2nd, it won't work with the query >>as specified<<. For example, if I add a composite index (t_idx2) as:
    ===
    drop table t;
    create table t as select rownum id, all_objects.* from all_objects where rownum < 1200;
    update t set object_type = NULL;
    create index t_idx1 on t(object_type);
    create index t_idx2 on t(id,object_type);
    analyze table t compute statistics for table for all indexes for all indexed columns;

    select * from t
    where object_type is NULL;
    ===
    It still runs a table scan, and doesn't use the composite index. Of course, if you modify the query like so:
    ===
    select * from t
    where id = 100 and
    object_type is NULL;
    ===
    Then of course it will do a range scan on index t_idx2, thus using the composite index. But then the 2nd argument to the where clause is redundant if 100% of the column is null, which is what the original poster stated.

    I don't think a composite index will help if the only value in the where clause is the 100% NULL column. If you can make it work, I would be interested in seeing the example. Thanks.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Your test is badly formed because ...
    • the optimizer doesn't know that the "id" column is not null.
    • placing the object_type second in the list of columns in the composite index reduces the chances of it being used
    • The table is so small that the optimizer might choose to use an FTS anyway
    • Since all the values of object_type are null, it's more efficient with this data set to FTS anyway



    Code:
    SQL> create table my_table as select * from dba_objects;
    
    Table created.
    
    SQL> update my_table set object_type = null
      2  where rownum < 11;
    
    10 rows updated.
    
    SQL> desc my_table
     Name                                      Null?    Type
     ----------------------------------------- -------- --------------------------
    
     OWNER                                              VARCHAR2(30)
     OBJECT_NAME                                        VARCHAR2(128)
     SUBOBJECT_NAME                                     VARCHAR2(30)
     OBJECT_ID                                          NUMBER
     DATA_OBJECT_ID                                     NUMBER
     OBJECT_TYPE                                        VARCHAR2(18)
     CREATED                                            DATE
     LAST_DDL_TIME                                      DATE
     TIMESTAMP                                          VARCHAR2(19)
     STATUS                                             VARCHAR2(7)
     TEMPORARY                                          VARCHAR2(1)
     GENERATED                                          VARCHAR2(1)
     SECONDARY                                          VARCHAR2(1)
    
    SQL> alter table my_table modify (owner not null);
    
    Table altered.
    
    SQL> create index my_index on my_table (object_type,owner);
    
    Index created.
    
    SQL> analyze table my_table compute statistics;
    
    Table analyzed.
    
    SQL> set autotrace traceonly explain
    SQL> select * from my_table where object_type is null;
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=10 Bytes=860)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MY_TABLE' (Cost=3 Card=1
              0 Bytes=860)
    
       2    1     INDEX (RANGE SCAN) OF 'MY_INDEX' (NON-UNIQUE) (Cost=2 Ca
              rd=10)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by paladin
    As for the 2nd, it won't work with the query >>as specified<<.
    ...
    I don't think a composite index will help if the only value in the where clause is the 100% NULL column. If you can make it work, I would be interested in seeing the example.
    Code:
    SQL> CREATE TABLE t AS SELECT ROWNUM id, ALL_OBJECTS.*
      2  FROM ALL_OBJECTS;
    
    Table created.
    
    SQL> UPDATE t SET id = NULL WHERE id =1;
    
    1 row updated.
    
    SQL> ALTER TABLE t MODIFY object_type NOT NULL;
    
    Table altered.
    
    SQL> CREATE INDEX t_idx1 ON t(id,object_type);
    
    Index created.
    
    SQL> ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL indexed
    
    Table analyzed.
    
    SQL> set autotrace on explain
    SQL> SELECT owner, object_name, object_type FROM t
      2  WHERE id IS NULL;
    
    OWNER      OBJECT_NAME               OBJECT_TYPE
    ---------- ------------------------- -------------
    SYS        /1001a851_ConstantDefImpl JAVA CLASS
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=47)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=
              47)
    
       2    1     INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=2 Card
              =1)
    Your example was totaly nonadequate.

    1. You neglected an important condition that slimdave mentioned: the other column in a composite index must be non-null. This must be known to the optimizer either by the definition of the collumn in the dictionary or by the predicate in a query.

    2. You have update all of the rows to contain nulls in the indexed columns! So how dumb should the optimizer be to choose that index when it knows it will have to scan an entire index (plus visit all the rows in the table)?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Ups! I see I was too late with my answer...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Mar 2004
    Posts
    6
    slimdave,

    You said:
    > # the optimizer doesn't know that the "id" column is not null.
    ># placing the object_type second in the list of columns in the composite index reduces the chances of it being used
    ># The table is so small that the optimizer might choose to use an FTS anyway
    ># Since all the values of object_type are null, it's more efficient with this data set to FTS anyway

    Addressing these issues, here is the SQL:

    drop table t;
    create table t as select rownum id, all_objects.* from all_objects where rownum < 12000;
    update t set object_type = NULL;
    alter table t modify (id not null);
    create index t_idx1 on t(object_type);
    create index t_idx2 on t(object_type,id);

    (1) Here the optimizer does know that 'id' is not null.
    (2) I placed 'object_type' first in the composite index.
    (3) The table has 12,000 rows - not so small that a FTS should be preferred.
    (4) Of course - if all the values are one value, a FTS should be preferred. But that was how the original poster specified the situation - a 100% NULL column.

    Results:
    (a) Using the RBO:
    select * from t where object_type is NULL;
    SELECT STATEMENT Optimizer=RULE
    TABLE ACCESS (FULL) OF T
    (b) Using the CBO:
    select /*+ CHOOSE */ * from t where object_type is NULL;
    SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=7 Card=11999 Bytes=1067911)
    TABLE ACCESS (FULL) OF T (Cost=7 Card=11999 Bytes=1067911)

    So - in conclusion - even with a composite index, if one key (1st or 2nd) is 100% NULL, and that is the only value specified in the where clause, then the index is not used. Of course, that is after all what we would expect the optimizer to do -- if a column is 100% NULL, and the only parameter in the where clause references that column, then what use would an indexed lookup possibly be? Answer - of no use at all, of course.

  10. #10
    Join Date
    Mar 2004
    Posts
    6
    Jurij Modic said:
    Your example was totaly nonadequate.

    1. You neglected an important condition that slimdave mentioned: the other column in a composite index must be non-null. This must be known to the optimizer either by the definition of the collumn in the dictionary or by the predicate in a query.

    2. You have update all of the rows to contain nulls in the indexed columns! So how dumb should the optimizer be to choose that index when it knows it will have to scan an entire index (plus visit all the rows in the table)?

    My response:
    For #1 - you are correct. I fixed that by doing alter table to ensure the optimizer knows that one of the 2 key values is not NULL.
    For #2 - please reread my post. I KNOW that I updated 100% of values for the other key to be 100% NULL. Yes, the optimizer would be dumb to use the index if that was the only parameter in the where clause. But that was my point -- that was the condition specified by the original poster. I am saying I don't think that the composite index gets used UNDER THAT CONDITION, and have demonstrated that.

Posting Permissions

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


Click Here to Expand Forum to Full Width