This discussion is archived
9 Replies Latest reply: Oct 29, 2013 3:03 AM by jjk RSS

Query not using Index Full Scan

jjk Explorer
Currently Being Moderated

Hi everyone,

 

I'm on 11.2.0.3.0 AIX 6.1.

 

There is a query like :

select :sys_b_0||count(distinct(column_name)) from table;

 

which goes for table full scan. The column has only 104 distinct values with no nulls. To save the buffer gets, I created an index on the table using the column used in the query (i.e. whose distinct values are being retrieved).

The problem is that even after creating index and forcing the index hint it doesn't use the index fast full scan. The hint provided was correct in syntax and was the only hint in the query, so there's no question of conflicts with others.

 

Can anyone suggest me what I might be missing ?

 

thanks

  • 1. Re: Query not using Index Full Scan
    saurabh Pro
    Currently Being Moderated

    check that statistics fortable are up to date or not. And post the explain plan of the query for analysis.

  • 2. Re: Query not using Index Full Scan
    keshwarsan Newbie
    Currently Being Moderated


    Hi,

     

    The problem is that even after creating index and forcing the index hint it doesn't use the index fast full scan. The hint provided was correct in syntax and was the only hint in the query, so there's no question of conflicts with others.

     

    Can you please let us know how you did this?

  • 3. Re: Query not using Index Full Scan
    jjk Explorer
    Currently Being Moderated

    And here are the details of the run:

     

    Original Query and plan:

     

    SQL_ID               CH# EXECUTIONS       BGPE       ETPE       CTPE ROWS_PROCESSED
    ------------- ---------- ---------- ---------- ---------- ---------- --------------
    6wjhpazkcf8ry          1        361 587365.706 18.8499432 12.5122632            361
    select :"SYS_B_0"||count(distinct(print_branch_code)) from
    COST1835.GET_PREDICT_TXN
    Plan hash value: 968793944
    ----------------------------------------------------------------------------------------
    | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                 |       |       |   133K(100)|          |
    |   1 |  SORT AGGREGATE      |                 |     1 |     7 |            |          |
    |   2 |   VIEW               | VW_DAG_0        |   104 |   728 |   133K  (4)| 00:26:38 |
    |   3 |    HASH GROUP BY     |                 |   104 |   520 |   133K  (4)| 00:26:38 |
    |   4 |     TABLE ACCESS FULL| GET_PREDICT_TXN |    36M|   176M|   129K  (1)| 00:25:57 |
    ----------------------------------------------------------------------------------------

     

    After putting hint:

     

    select /*+ index (GET_PREDICT_TXN, TST_IDX_PR_BRAN_CD) */ count(distinct(print_branch_code)) from COST1835.GET_PREDICT_TXN
    Plan hash value: 968793944
    ----------------------------------------------------------------------------------------
    | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                 |       |       |   131K(100)|          |
    |   1 |  SORT AGGREGATE      |                 |     1 |     7 |            |          |
    |   2 |   VIEW               | VW_DAG_0        |   104 |   728 |   131K  (4)| 00:26:23 |
    |   3 |    HASH GROUP BY     |                 |   104 |   520 |   131K  (4)| 00:26:23 |
    |   4 |     TABLE ACCESS FULL| GET_PREDICT_TXN |    36M|   174M|   128K  (1)| 00:25:42 |
    ----------------------------------------------------------------------------------------
    OWNER      TABLE_NAME        NUM_ROWS     BLOCKS PAR TDATE
    ---------- --------------- ---------- ---------- --- ------------------------------
    COST1835   GET_PREDICT_TXN   36881288     585425 NO  25-oct-2013 13:58:49
    OWNER      TABLE_NAME      COLUMN_NAME          DATA_TYPE    N NUM_DISTINCT  NUM_NULLS TDATE
    ---------- --------------- -------------------- ------------ - ------------ ---------- ------------------------------
    COST1835   GET_PREDICT_TXN PIR_NMBR             VARCHAR2     N       844545          0 25-oct-2013 13:58:49
                               INTERNAL_TXN_NMBR    VARCHAR2     N         6000          0 25-oct-2013 13:58:49
                               LIVE_RECORD_KEY_NMBR VARCHAR2     N            6          0 25-oct-2013 13:58:49
                               SECURITY_PRINT_NMBR  VARCHAR2     N     36881288          0 25-oct-2013 13:58:49
                               INST_NMBR            VARCHAR2     Y      1100968        142 25-oct-2013 13:58:49
                               EXPECTED_PRINT_DATE  DATE         Y          567       1240 25-oct-2013 13:58:49
                               PRINT_FIRE_DATE      DATE         Y     15095124          0 25-oct-2013 13:58:49
                               USER_CODE            VARCHAR2     Y          881          0 25-oct-2013 13:58:49
                               PRINT_BRANCH_CODE    VARCHAR2     Y          104          0 25-oct-2013 13:58:49
                               CLIENT_CODE          VARCHAR2     Y         5547          0 25-oct-2013 13:58:49
                               PRODUCT_CODE         VARCHAR2     Y            7          0 25-oct-2013 13:58:49
                               ACCOUNT_NMBR         VARCHAR2     Y            5          0 25-oct-2013 13:58:49
                               INSTRUMENT_CODE      VARCHAR2     Y            3          0 25-oct-2013 13:58:49
                               PRINT_FLAG           CHAR         N            3          0 25-oct-2013 13:58:49
    INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION COLUMN_EXPRESSION
    ------------------------------ ------------------------------ --------------- ------------------------------
    IDX_PRI_AUD_TXN_SEC_PRI_NMBR   SECURITY_PRINT_NMBR                          1
    PK_GET_PREDICT_TXN             PIR_NMBR                                     1
                                   INTERNAL_TXN_NMBR                            2
                                   LIVE_RECORD_KEY_NMBR                         3
                                   SECURITY_PRINT_NMBR                          4
    TST_IDX_PR_BRAN_CD             PRINT_BRANCH_CODE                            1
  • 4. Re: Query not using Index Full Scan
    Saugat Chatterjee Newbie
    Currently Being Moderated

    hi

     

    check the ordering of column in your index,sometimes ordering of colomns do force oracle to ignore the colomns which are not at first position,you can check the colomn ordering of an index by the following view DBA_IND_COLUMNS

  • 5. Re: Query not using Index Full Scan
    John Stegeman Oracle ACE
    Currently Being Moderated

    It looks to me as if print_branch_code is defined as nullable in the table. If this is the case, the optimizer won't go for an index scan even if there are no actual NULL values

  • 6. Re: Query not using Index Full Scan
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    1) you're using a comma in the index hint -- I'm not sure this is allowed syntax

    2) it's not enough that the column doesn't contain NULL values, Oracle must be sure of that. Add either NOT NULL constraint to the table definition or IS NOT NULL condition to your query.

     

    Best regards,

    Nikolay

  • 7. Re: Query not using Index Full Scan
    jjk Explorer
    Currently Being Moderated

    Thank you, JohnStegeman.

     

    When I checked DBA_TAB_COLUMNS for nullable, the column was nullable.

    I did a small test and found that VARCHAR2 columns by default (e.g col1 varchar2(30)) will have nullable true

    when I modified the column to not null, the plan did show a index full scan:

     

    SQL> create table t1 as select * from dba_objects;
    Table created.
    SQL> exec dbms_stats.gather_table_stats('SYS','T1', estimate_percent=>100);
    PL/SQL procedure successfully completed.
      1* select column_name, nullable from dba_tab_columns where table_name='T1' and owner='SYS'
    11:59:28 SQL> /
    COLUMN_NAME                    N
    ------------------------------ -
    OWNER                          Y
    OBJECT_NAME                    Y
    SUBOBJECT_NAME                 Y
    OBJECT_ID                      Y
    DATA_OBJECT_ID                 Y
    OBJECT_TYPE                    Y
    CREATED                        Y
    LAST_DDL_TIME                  Y
    TIMESTAMP                      Y
    STATUS                         Y
    TEMPORARY                      Y
    GENERATED                      Y
    SECONDARY                      Y
    NAMESPACE                      Y
    EDITION_NAME                   Y
    11:59:31 SQL> explain plan for select count(distinct(object_name)) from t1;
    Explained.
    Elapsed: 00:00:00.03
    11:59:54 SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3683549581
    -----------------------------------------------------------------------------------------
    | Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |          |     1 |    66 |       |   549   (2)| 00:00:07 |
    |   1 |  SORT AGGREGATE      |          |     1 |    66 |       |            |          |
    |   2 |   VIEW               | VW_DAG_0 | 37519 |  2418K|       |   549   (2)| 00:00:07 |
    |   3 |    HASH GROUP BY     |          | 37519 |   915K|  2032K|   549   (2)| 00:00:07 |
    |   4 |     TABLE ACCESS FULL| T1       | 64479 |  1574K|       |   204   (1)| 00:00:03 |
    -----------------------------------------------------------------------------------------
    11 rows selected.
    12:00:15 SQL> create index tst_nullables on t1(object_name);
    Index created.
    Elapsed: 00:00:00.13
    12:00:40 SQL>
    12:02:15 SQL> EXPLAIN PLAN FOR select /*+ index (t1, TST_NULLABLES) */ count(distinct(object_name)) from t1;
    Explained.
    Elapsed: 00:00:00.00
    12:02:44 SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3683549581
    -----------------------------------------------------------------------------------------
    | Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |          |     1 |    66 |       |   549   (2)| 00:00:07 |
    |   1 |  SORT AGGREGATE      |          |     1 |    66 |       |            |          |
    |   2 |   VIEW               | VW_DAG_0 | 37519 |  2418K|       |   549   (2)| 00:00:07 |
    |   3 |    HASH GROUP BY     |          | 37519 |   915K|  2032K|   549   (2)| 00:00:07 |
    |   4 |     TABLE ACCESS FULL| T1       | 64479 |  1574K|       |   204   (1)| 00:00:03 |
    -----------------------------------------------------------------------------------------
    11 rows selected.
    Elapsed: 00:00:00.01
    12:02:49 SQL>
    12:03:02 SQL> alter table t1 modify object_name not null;
    Table altered.
    Elapsed: 00:00:00.02
    12:03:17 SQL> EXPLAIN PLAN FOR select /*+ index (t1, TST_NULLABLES) */ count(distinct(object_name)) from t1;
    Explained.
    Elapsed: 00:00:00.01
    12:03:25 SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2716282249
    ----------------------------------------------------------------------------------------
    | Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |               |     1 |    66 |    32   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE        |               |     1 |    66 |            |          |
    |   2 |   VIEW                 | VW_DAG_0      | 37519 |  2418K|    32   (0)| 00:00:01 |
    |   3 |    SORT GROUP BY NOSORT|               | 37519 |   915K|    32   (0)| 00:00:01 |
    |   4 |     INDEX FULL SCAN    | TST_NULLABLES | 64479 |  1574K|    32   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
    11 rows selected.
    

     

    This is quite surprising to me considering that columns are nullable by default, unless specified otherwise, and even when we've statistics showing that the columns don't have any nulls, Oracel optimizer doesn't use them. I think here that the purpose of gathering statistics is defeated.

  • 8. Re: Query not using Index Full Scan
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    "This is quite surprising to me considering that columns are nullable by default, unless specified otherwise, and even when we've statistics showing that the columns don't have any nulls, Oracel optimizer doesn't use them. I think here that the purpose of gathering statistics is defeated."

     

    The statistics will only tell the optimizer how many rows had nulls for that column when the statistics were collected - you could have changed the data after the statistics were collected.

     

    Regards

    Jonathan Lewis

     

     

     

  • 9. Re: Query not using Index Full Scan
    jjk Explorer
    Currently Being Moderated


    Thanks NikolaySawinov, adding "is not null" criteria did use the index w/o forcing the column to be not null

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points