1 2 Previous Next 15 Replies Latest reply: Jul 28, 2010 10:34 PM by Hemant K Chitale RSS

    FTS when doing select as NOT NULL values

    oratest
      Hi,

      My Oracle version is 10.2.0.3.0.

      I am firing below SQL in database, this SQL is doing a FTS while running.

      SELECT *
      FROM table_name
      WHERE id_number IS NULL;

      Pease see below for nature of data existing in table.

      SQL> select count(*) from table_name where id_number is null;

      COUNT(*)
      ----------------
      1456

      SQL> select count(*) from table_name;

      COUNT(*)
      ----------------
      9392730

      Though there is an index on id_number column, and the SQL's where clause (where id_number is null) fetches only 0.1% of rows from table. Still that SQL is doing a FTS on table_name table. Is there any way I can remove FTS from this SQL execution.

      Thanks in advance.

      Best Regards,
      oratest
        • 1. Re: FTS when doing select as NOT NULL values
          sb92075
          Though there is an index on id_number column
          Since NULLs are not indexed, FTS is only way to find them
          • 2. Re: FTS when doing select as NOT NULL values
            oratest
            Means there is no other way/workaround to tune this SQL? :(
            • 3. Re: FTS when doing select as NOT NULL values
              sybrand_b
              Though there is an index on id_number column, and the SQL's where clause (where id_number is null) fetches only 0.1% of rows from table. Still that SQL is doing a FTS on table_name table.

              As was to be expected as NULLs aren't indexed. Many times you can use this to your advantage: if your record is OK the status is null, in all other cases Non-null, so included in index.


              Is there any way I can remove FTS from this SQL execution.


              Function-based index:
              nvl(id_number, <somevalue>)

              Your where clause will need to be changed to
              nvl(id_number,<somevalue>) = <somevalue>

              ------------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: FTS when doing select as NOT NULL values
                SomeoneElse
                Another way around this is to use a Bitmap Index as they will index null values:
                SQL> create table t as select * from dba_objects;
                
                Table created.
                
                SQL> update t set object_type = null where object_type = 'TABLE PARTITION';
                
                508 rows updated.
                
                SQL> commit;
                
                Commit complete.
                
                SQL> create bitmap index t_bmp on t(object_type);
                
                Index created.
                
                SQL> explain plan for
                  2  select *
                  3  from t
                  4  where object_type is null;
                
                Explained.
                
                SQL> @xp
                
                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------------
                Plan hash value: 1229012970
                
                --------------------------------------------------------------------------------------
                | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                --------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT             |       |   508 | 50292 |   109   (0)| 00:00:02 |
                |   1 |  TABLE ACCESS BY INDEX ROWID | T     |   508 | 50292 |   109   (0)| 00:00:02 |
                |   2 |   BITMAP CONVERSION TO ROWIDS|       |       |       |            |          |
                |*  3 |    BITMAP INDEX SINGLE VALUE | T_BMP |       |       |            |          |
                --------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   3 - access("OBJECT_TYPE" IS NULL)
                
                15 rows selected.
                Keep in mind though that bitmap indexes are not always suitable, depending on the situation. This is especially true for OLTP environments where tables have frequent and concurrent DML going on.

                They're really intended for data warehouses.
                • 5. Re: FTS when doing select as NOT NULL values
                  Charles Hooper
                  oratest wrote:
                  Means there is no other way/workaround to tune this SQL? :(
                  Great answers in this thread.

                  If you cannot modify the SQL statement or implement a bitmap index (not a good index choice if the table is subject to frequent changes), there is another solution. I think that I first saw this approach on Richard Foote's blog:
                  http://richardfoote.wordpress.com/2008/01/23/indexing-nulls-empty-spaces/

                  Here is a sample:
                  CREATE TABLE T1(
                    C1 NUMBER NOT NULL,
                    C2 VARCHAR2(50) NOT NULL,
                    C3 NUMBER,
                    C4 VARCHAR2(300));
                   
                  INSERT INTO T1
                  SELECT
                    ROWNUM,
                    TO_CHAR(ROWNUM,'0000000')||'A',
                    DECODE(MOD(ROWNUM,1000),0,NULL,ROWNUM),
                    LPAD('A',300,'A')
                  FROM
                    (SELECT
                      ROWNUM RN
                    FROM
                      DUAL
                    CONNECT BY
                      LEVEL <= 1000) V1,
                    (SELECT
                      ROWNUM RN
                    FROM
                      DUAL
                    CONNECT BY
                      LEVEL <= 1000) V2;
                   
                  CREATE INDEX IND_T1_C3 ON T1(C3);
                   
                  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
                  The above created a 1,000,000 row table where 1 out of 1,000 rows contains a NULL value. An index was then created on the table and statistics were updated.

                  Now the first test:
                  SET AUTOTRACE TRACEONLY EXPLAIN
                   
                  SELECT
                    COUNT(*)
                  FROM
                    T1
                  WHERE
                    C3 IS NULL;
                   
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 3724264953
                  
                  ---------------------------------------------------------------------------
                  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                  ---------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT   |      |     1 |     5 | 10994   (2)| 00:00:45 |
                  |   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
                  |*  2 |   TABLE ACCESS FULL| T1   |  2000 | 10000 | 10994   (2)| 00:00:45 |
                  ---------------------------------------------------------------------------
                   
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                     2 - filter("C3" IS NULL)
                  Note that the index was not used. A second test with a slightly modified index definition:
                  CREATE INDEX IND_T1_C3_NULL ON T1(C3,' ');
                   
                  EXEC DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>USER,INDNAME=>'IND_T1_C3_NULL')
                   
                  SELECT
                    COUNT(*)
                  FROM
                    T1
                  WHERE
                    C3 IS NULL;
                   
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 265035506
                   
                  ------------------------------------------------------------------------------------
                  | Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
                  ------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT  |                |     1 |     5 |    25   (0)| 00:00:01 |
                  |   1 |  SORT AGGREGATE   |                |     1 |     5 |            |          |
                  |*  2 |   INDEX RANGE SCAN| IND_T1_C3_NULL |  2000 | 10000 |    25   (0)| 00:00:01 |
                  ------------------------------------------------------------------------------------
                   
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                     2 - access("C3" IS NULL)
                  Notice this time the special index was used without having to modify the SQL statement.

                  Charles Hooper
                  Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                  http://hoopercharles.wordpress.com/
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.
                  • 6. Re: FTS when doing select as NOT NULL values
                    oratest
                    Thanks to all.

                    All suggestion were good & very usefull.

                    Sybrand's And Charles's solutions were workable for me.
                    • 7. Re: FTS when doing select as NOT NULL values
                      Hemant K Chitale
                      Not always a good idea.

                      See Oracle Support Note#551754.1 on Bug#6737251.
                      The note recommends :

                      Do not create an index on column, CONSTANT.
                      Have the index on a pair of columns rather than on a constant.


                      I came across it when re-validating my Blog Post
                      http://hemantoracledba.blogspot.com/2007/08/nulls-are-not-indexed-right-not.html


                      Hemant K Chitale
                      • 8. Re: FTS when doing select as NOT NULL values
                        amardeep.sidhu
                        There was another bug related to (column, constant) type indexes. gather_schema_stats used to fail with ORA-03001: unimplemented feature. [In 10.2.0.3 on AIX 5.3, 64 bit]

                        I wrote about it here

                        http://amardeepsidhu.com/blog/2009/07/08/gather_schema_stats-ora-03001-unimplemented-feature/

                        Regards,
                        Amardeep Sidhu
                        • 9. Re: FTS when doing select as NOT NULL values
                          Hemant K Chitale
                          Bug 5005939 had caused me to use a Literal instead of a Number !
                          You have also pointed out Bug 5767661 which is caused by using a Numeric instead of a Literal !!
                          So we then go to Bug 6737251 where the recommndation is to not use a Constant at all !!!


                          Hemant K Chitale
                          • 10. Re: FTS when doing select as NOT NULL values
                            Charles Hooper
                            Hemant (and Amardeep Sidhu),

                            First, thank you for mentioning this particular problem. I see input like what you provided as very helpful - work-arounds that appear to be helpful intially might have problems that show up at unexpected times.

                            Just to be clear, I would probably use the solution mentioned by Sybrand unless the SQL cannot be touched (or use COALESCE rather than NVL).

                            I see a couple of problems with that Metalink (My Oracle Support) article... it lacks a little clarity (then again I could be seeing things that do not exist). First, the suggestion to use a pair of columns rather than a constant has at least four potential problems that I am able to identify:
                            1. The second column must have a NOT NULL constraint (not mentioned in the article) - it cannot be just any secondary column.
                            2. The secondary column will likely increase the index size a bit more than a single character used for the second column in the index.
                            3. The secondary column will likely affect the clustering factor calculation for the index.
                            4. The secondary column could affect the cardinality estimates for the index access path.

                            The second problem with the Metalink article is that, while it does demonstrate a bug, it does not show why the bug affects the results, nor does it explore other possibilities - like the one that Richard Foote used in his blog article. Here is a quick test case, loosely based on the Metalink test case, to demonstrate (note that I have not copied the statistics output from AUTOTRACE so that I may improve the clarity of the output):
                            DROP TABLE T1 PURGE;
                             
                            CREATE TABLE T1 (C1 NUMBER, C2 NUMBER);
                             
                            INSERT INTO T1 VALUES (NULL, 1);
                             
                            COMMIT;
                             
                            CREATE INDEX IND_T1_1 ON T1(C1,1);
                            CREATE INDEX IND_T1_2 ON T1(C1,' ');
                            We now have a table containing a single row and two indexes - the first index uses a numeric constant for the second column in the index, while the second index uses a blank space for the second column in the index. Now continuing:
                            SET LINESIZE 140
                            SET PAGESIZE 1000
                            SET TRIMSPOOL ON
                            SET AUTOTRACE ON
                             
                            SELECT /*+ INDEX(T1 IND_T1_1) */
                              *
                            FROM
                              T1
                            WHERE
                              C1 IS NULL;
                             
                                    C1         C2
                            ---------- ----------
                                                1
                             
                            Execution Plan
                            ----------------------------------------------------------
                            Plan hash value: 2805969644
                             
                            ----------------------------------------------------------------------------------------
                            | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                            ----------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT            |          |     1 |    26 |     2   (0)| 00:00:01 |
                            |   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     2   (0)| 00:00:01 |
                            |*  2 |   INDEX RANGE SCAN          | IND_T1_1 |     1 |       |     1   (0)| 00:00:01 |
                            ----------------------------------------------------------------------------------------
                             
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                               2 - access("C1" IS NULL)
                             
                            Note
                            -----
                               - dynamic sampling used for this statement
                            The above worked as expected. Continuing:
                            SELECT /*+ INDEX(T1 IND_T1_1) */
                              *
                            FROM
                              T1
                            WHERE
                              C1 IS NULL
                              AND ROWNUM<=1;
                             
                            no rows selected
                             
                            Execution Plan
                            ----------------------------------------------------------
                            Plan hash value: 404994253
                             
                            -----------------------------------------------------------------------------------------
                            | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                            -----------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT             |          |     1 |    26 |     1   (0)| 00:00:01 |
                            |*  1 |  COUNT STOPKEY               |          |       |       |            |          |
                            |   2 |   TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     1   (0)| 00:00:01 |
                            |*  3 |    INDEX RANGE SCAN          | IND_T1_1 |     1 |       |     1   (0)| 00:00:01 |
                            -----------------------------------------------------------------------------------------
                             
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                               1 - filter(ROWNUM<=1)
                               3 - access("C1" IS NULL AND ROWNUM<=1)
                                   filter(ROWNUM<=1)
                             
                            Note
                            -----
                               - dynamic sampling used for this statement
                            Note that this time we encountered the bug - take a close look at the Predicate Information section of the execution plan to see why.

                            Now the test continues with the suggestion from Richard's blog:
                            SELECT /*+ INDEX(T1 IND_T1_2) */
                              *
                            FROM
                              T1
                            WHERE
                              C1 IS NULL;
                             
                                    C1         C2
                            ---------- ----------
                                                1
                             
                            Execution Plan
                            ----------------------------------------------------------
                            Plan hash value: 348287884
                             
                            ----------------------------------------------------------------------------------------
                            | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                            ----------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT            |          |     1 |    26 |     2   (0)| 00:00:01 |
                            |   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     2   (0)| 00:00:01 |
                            |*  2 |   INDEX RANGE SCAN          | IND_T1_2 |     1 |       |     1   (0)| 00:00:01 |
                            ----------------------------------------------------------------------------------------
                             
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                               2 - access("C1" IS NULL)
                             
                            Note
                            -----
                               - dynamic sampling used for this statement
                            We obtained the same result as before, continuing:
                            SELECT /*+ INDEX(T1 IND_T1_2) */
                              *
                            FROM
                              T1
                            WHERE
                              C1 IS NULL
                              AND ROWNUM<=1;
                             
                                    C1         C2
                            ---------- ----------
                                                1
                             
                            Execution Plan
                            ----------------------------------------------------------
                            Plan hash value: 2383334138
                             
                            -----------------------------------------------------------------------------------------
                            | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                            -----------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT             |          |     1 |    26 |     2   (0)| 00:00:01 |
                            |*  1 |  COUNT STOPKEY               |          |       |       |            |          |
                            |   2 |   TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     2   (0)| 00:00:01 |
                            |*  3 |    INDEX RANGE SCAN          | IND_T1_2 |     1 |       |     1   (0)| 00:00:01 |
                            -----------------------------------------------------------------------------------------
                             
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                               1 - filter(ROWNUM<=1)
                               3 - access("C1" IS NULL)
                             
                            Note
                            -----
                               - dynamic sampling used for this statement
                            Note that this time we did not encounter the bug, and a row was returned. Compare the Predicate Information section of the execution plan with the one that failed to produce the correct result.

                            Let's remove the "dynamic sampling used for this statement" note by gathering statistics:
                            SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)
                            BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE); END;
                             
                            *
                            ERROR at line 1:
                            ORA-03001: unimplemented feature
                            ORA-06512: at "SYS.DBMS_STATS", line 13159
                            ORA-06512: at "SYS.DBMS_STATS", line 13179
                            ORA-06512: at line 1
                             
                             
                            SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
                            BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE); END;
                             
                            *
                            ERROR at line 1:
                            ORA-03001: unimplemented feature
                            ORA-06512: at "SYS.DBMS_STATS", line 13159
                            ORA-06512: at "SYS.DBMS_STATS", line 13179
                            ORA-06512: at line 1
                             
                             
                            SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
                            BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1'); END;
                             
                            *
                            ERROR at line 1:
                            ORA-03001: unimplemented feature
                            ORA-06512: at "SYS.DBMS_STATS", line 13159
                            ORA-06512: at "SYS.DBMS_STATS", line 13179
                            ORA-06512: at line 1
                             
                             
                            SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')
                             
                            PL/SQL procedure successfully completed.
                            Well, it appears that we hit another bug. Note that I successfully gathered statistics on another table just to demonstrate that there was not a problem with my statistics gathering syntax. Let's fix that problem:
                            SQL> DROP INDEX IND_T1_1;
                             
                            SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)
                             
                            PL/SQL procedure successfully completed.
                            That is better. Apparently, Oracle Database (at least 10.2.0.2) has problems when the second column in an index definition is a number constant, but not when the second column is a character constant.

                            Charles Hooper
                            Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                            http://hoopercharles.wordpress.com/
                            IT Manager/Oracle DBA
                            K&M Machine-Fabricating, Inc.
                            • 11. Re: FTS when doing select as NOT NULL values
                              635471
                              oratest wrote:
                              Hi,

                              My Oracle version is 10.2.0.3.0.

                              I am firing below SQL in database, this SQL is doing a FTS while running.

                              SELECT *
                              FROM table_name
                              WHERE id_number IS NULL;
                              If you are interested in optimising that query in particular then you could create an index:
                              create index ... on table_name(case when id_number is null then 1 end)
                              Rewrite your query to:
                              Select *
                              From   table_name
                              where case when id_number is null then 1 end = 1
                              This technique allows the efficient creation of an index on null values without indexing non-null values at all. Not exactly your case, but if you want to avoid potentially bloating your existing index with a non-null column in order to avoid the controversy of indexing a constant then it might be a workable solution.
                              • 12. Re: FTS when doing select as NOT NULL values
                                Mohamed Houri
                                Charles,
                                CREATE INDEX IND_T1_C3_NULL ON T1(C3,' ');
                                I am wondering if the addition of this supplementary null column to the orginal index IND_T1_C3, will not damage the clustering factor of the new index and,
                                hence, make the optimiser not using it when it should

                                Or may be this special extra colum will have no influence on how the data will be grapped into this new index

                                What do you think?


                                Best Regards

                                Mohamed Houri
                                • 13. Re: FTS when doing select as NOT NULL values
                                  Charles Hooper
                                  Mohamed,

                                  Good question - that is something that I did not spend a lot of time investigating. Without performing a test, I would estimate that each NULL value that will be in the index structure will increase the clustering factor by a value of 1, unless all of those rows containing the NULL values are contained within a couple of table blocks, in which case the clustering factor should increase by less than 1 per NULL value - such a case would likely assume that the rows were inserted at roughly the same time.

                                  Confusing? The NULL values in the index structure will be logically grouped together in logically adjacent index leaf blocks, when this is considered as well as the algorithm for calculating the clustering factor, the maximum increase in the clustering for this approach should be fairly easy to guess. See the clustering factor chapter in the book "Cost-Based Oracle fundamentals":
                                  http://www.apress.com/resource/bookfile/2410

                                  A quick test case:
                                  CREATE TABLE T1 (
                                    COL1 NUMBER NOT NULL,
                                    COL2 NUMBER,
                                    EMPLOYEE_ID VARCHAR2(20),
                                    SHIFT_DATE DATE NOT NULL,
                                    ROLLING_DATE DATE NOT NULL,
                                    INDIRECT_ID VARCHAR2(20));
                                   
                                  INSERT INTO
                                    T1
                                  SELECT
                                    ROWNUM COL1,
                                    DECODE(MOD(ROWNUM,1000),0,NULL,ROWNUM) COL2,
                                    DECODE(TRUNC(DBMS_RANDOM.VALUE(0,5)),
                                            0,'MIKE',
                                            1,'ROB',
                                            2,'SAM',
                                            3,'JOE',
                                            4,'ERIC') EMPLOYEE_ID,
                                    TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE,
                                    TRUNC(SYSDATE) + (1/1000)*ROWNUM ROLLING_DATE,
                                    DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)),
                                            0,'VAC',
                                            1,'HOL',
                                            2,'BEREAVE',
                                            3,'JURY',
                                            4,'ABS',
                                            5,'EXCUSE',
                                            6,'MIL',
                                            'OTHER') INDIRECT_ID
                                  FROM
                                    DUAL
                                  CONNECT BY
                                    LEVEL<=1000000;
                                   
                                  COMMIT;
                                  1,000,000 rows will be added to the test table, and those rows contain mostly random data. Now an exercise in over-indexing:
                                  CREATE INDEX IND_T1_COL1 ON T1(COL1);
                                  CREATE INDEX IND_T1_COL2 ON T1(COL2);
                                  CREATE INDEX IND_T1_EMPLOYEE_ID ON T1(EMPLOYEE_ID);
                                  CREATE INDEX IND_T1_SHIFT_DATE ON T1(SHIFT_DATE);
                                  CREATE INDEX IND_T1_ROLL_DATE ON T1(ROLLING_DATE);
                                   
                                  CREATE INDEX IND_T1_COL2_NOT_NULL ON T1(COL2,' ');
                                  CREATE INDEX IND_T1_EMPL_NOT_NULL ON T1(EMPLOYEE_ID,' ');
                                   
                                  CREATE INDEX IND_T1_COL2_NOT_NULL2 ON T1(COL2,COL1);
                                  CREATE INDEX IND_T1_COL2_NOT_NULL3 ON T1(COL2,SHIFT_DATE);
                                  CREATE INDEX IND_T1_COL2_NOT_NULL4 ON T1(COL2,ROLLING_DATE);
                                   
                                  CREATE INDEX IND_T1_EMPL_NOT_NULL2 ON T1(EMPLOYEE_ID,COL1);
                                   
                                  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL)
                                  For my test run on Oracle Database 11.1.0.7, I received the following results (I added the index definition to the end of each line of output):
                                  SELECT
                                    INDEX_NAME,
                                    DISTINCT_KEYS,
                                    CLUSTERING_FACTOR,
                                    SAMPLE_SIZE
                                  FROM
                                    USER_INDEXES
                                  WHERE
                                    TABLE_NAME='T1'
                                  ORDER BY
                                    INDEX_NAME;
                                   
                                  INDEX_NAME            DISTINCT_KEYS CLUSTERING_FACTOR SAMPLE_SIZE
                                  --------------------- ------------- ----------------- -----------
                                  IND_T1_COL1                 1000000              5629     1000000  (COL1)
                                  IND_T1_COL2                  999000              5629      999000  (COL2)
                                  IND_T1_COL2_NOT_NULL         999001              6629     1000000  (COL2,' ')
                                  IND_T1_COL2_NOT_NULL2       1000000              6629     1000000  (COL2,COL1)
                                  IND_T1_COL2_NOT_NULL3        999631              6629     1000000  (COL2,SHIFT_DATE)
                                  IND_T1_COL2_NOT_NULL4       1000000              6629     1000000  (COL2,ROLLING_DATE)
                                  IND_T1_EMPLOYEE_ID                5             28135     1000000  (EMPLOYEE_ID)
                                  IND_T1_EMPL_NOT_NULL              5             28135     1000000  (EMPLOYEE_ID,' ')
                                  IND_T1_EMPL_NOT_NULL2       1000000             28137     1000000  (EMPLOYEE_ID,COL1)
                                  IND_T1_ROLL_DATE            1000000              5629     1000000  (ROLLING_DATE)
                                  IND_T1_SHIFT_DATE              1001            916550     1000000  (SHIFT_DATE)
                                  For the 1,000,000 row table, 1 of every 1,000 rows contains a NULL value in column C2, and the clustering factor increased by exactly 1,000 (1,000,000/1,000) for the index on that column that used a single space for the second column in the index (IND_T1_COL2_NOT_NULL). The results probably would have been different if the majority of the NULL values were grouped together. In this particular test case, the clustering factor is about the same regardless of whether a blank space is used for the second column, or an actual table column. Your results in a production environment may be very different from those in my simple test case.

                                  Charles Hooper
                                  Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                                  http://hoopercharles.wordpress.com/
                                  IT Manager/Oracle DBA
                                  K&M Machine-Fabricating, Inc.
                                  • 14. Re: FTS when doing select as NOT NULL values
                                    Mohamed Houri
                                    Charles,

                                    Thanks for you remarkable answer.

                                    Actually, it is Chapter 5 of Jonathan Lewis Cost Based Oracle Fundamentals which prompted me to ask you this particular question

                                    Thanks

                                    Mohamed Houri
                                    1 2 Previous Next