This discussion is archived
6 Replies Latest reply: Aug 16, 2011 8:39 AM by 603349 RSS

Non-prefixed local indexes and fast full scans

329920 Newbie
Currently Being Moderated
Version 10.2.0.5 EE RHEL (also tested on 11.2.0.2)

I trying to force 'index fast full scans' with partition pruning of a non-prefixed local index, but it doesn't seem to be working.

'Index full scan' or 'Index range scan' work without touching the table, so I am puzzled as to why CBO shouldn't consider FFS.
CJ@DW>CREATE TABLE t_part2 (ID NUMBER,  code VARCHAR2(5), padding VARCHAR2(2000))
  2  PARTITION BY RANGE (ID)
  3  (PARTITION p1 VALUES LESS THAN (10),
  4   PARTITION p2 VALUES LESS THAN (20),
  5   PARTITION p3 VALUES LESS THAN (30),
  6   PARTITION p4 VALUES LESS THAN (MAXVALUE))
  7  TABLESPACE dw_insight;

Table created.

Elapsed: 00:00:00.11
CJ@DW>INSERT INTO t_part2
  2     SELECT MOD (ROWNUM, 30), TO_CHAR (ROWNUM), RPAD ('x', 2000)
  3       FROM dba_objects
  4      WHERE ROWNUM <= 100;

100 rows created.

Elapsed: 00:00:00.20
CJ@DW>COMMIT;

Commit complete.

Elapsed: 00:00:00.01
CJ@DW>BEGIN
  2     DBMS_STATS.gather_table_stats (ownname => USER, tabname => 'T_PART2');
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
CJ@DW>CREATE INDEX nonprefixed ON t_part2(code) LOCAL
  2  TABLESPACE dw_insight;

Index created.

-- Using the non-prefixed index to do a 'partition-pruned' index range scan. See hint.

Elapsed: 00:00:00.07
CJ@DW>EXPLAIN PLAN FOR
  2  SELECT /*+ index(t nonprefixed) */ code
  3  FROM t_part2 t
  4  WHERE ID >= 10 AND ID < 20
  5  AND code = '1';

Explained.

Elapsed: 00:00:00.17
CJ@DW>SELECT *
  2    FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2531890657

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     7 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|             |     1 |     7 |     1   (0)| 00:00:01 |     2 |     2 |
|*  2 |   INDEX RANGE SCAN     | NONPREFIXED |     1 |     7 |     1   (0)| 00:00:01 |     2 |     2 |
------------------------------------------------------------------------------------------------------

-- It worked. Oracle didn't have to touch the table.

-- Using the non-prefixed index to do a 'partition-pruned' index full scan. See hint

CJ@DW>EXPLAIN PLAN FOR
  2  SELECT /*+ index(t nonprefixed) */ code
  3  FROM t_part2 t
  4  WHERE ID >= 10 AND ID < 20
  5  AND code IS NOT NULL;

Explained.

Elapsed: 00:00:00.01
CJ@DW>SELECT *
  2    FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1195770860

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |    31 |   217 |    14   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|             |    31 |   217 |     1   (0)| 00:00:01 |     2 |     2 |
|*  2 |   INDEX FULL SCAN      | NONPREFIXED |    31 |   217 |     1   (0)| 00:00:01 |     2 |     2 |
------------------------------------------------------------------------------------------------------

-- It also worked. Oracle didn't have to touch the table.

-- Using the non-prefixed index to do a 'partition-pruned' index fast full scan. See hint.

CJ@DW>EXPLAIN PLAN FOR
  2  SELECT /*+ index_ffs(t nonprefixed) */ code
  3  FROM t_part2 t
  4  WHERE ID >= 10 AND ID < 20
  5  AND code IS NOT NULL;

Explained.

Elapsed: 00:00:00.03
CJ@DW>SELECT *
  2    FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 3877001361

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    31 |   217 |     5   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|         |    31 |   217 |     5   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T_PART2 |    31 |   217 |     5   (0)| 00:00:01 |     2 |     2 |
--------------------------------------------------------------------------------------------------

-- This time CBO ignores the hint. Why?
Any insight would be much appreciated.

Thanks,
CJ.
  • 1. Re: Non-prefixed local indexes and fast full scans
    603349 Explorer
    Currently Being Moderated
    The short answer is you have two choices to use an index FFS in this case:
    1) use a local prefixed index
    2) use partition extended syntax with a local nonprefixed index

    The long answer ... I'll check into why this restriction exists and see if there is any chance of lifting it at some point.

    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 2. Re: Non-prefixed local indexes and fast full scans
    329920 Newbie
    Currently Being Moderated
    As suggested by Jonathan, given below are the plans including the predicate section:

    -- The plan with INDEX hint
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |             |    31 |   217 |    14   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE|             |    31 |   217 |     1   (0)| 00:00:01 |     2 |     2 |
    |*  2 |   INDEX FULL SCAN      | NONPREFIXED |    31 |   217 |     1   (0)| 00:00:01 |     2 |     2 |
    ------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("CODE" IS NOT NULL)
    --The plan with INDEX_FFS hint:
    --------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |         |    31 |   217 |     5   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE|         |    31 |   217 |     5   (0)| 00:00:01 |     2 |     2 |
    |*  2 |   TABLE ACCESS FULL    | T_PART2 |    31 |   217 |     5   (0)| 00:00:01 |     2 |     2 |
    --------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("CODE" IS NOT NULL)
    Note that the query is the same in both cases. Only the hint is changed.

    Interesting to note that the predicate section does not contain the partition pruning filter in both the cases.
  • 3. Re: Non-prefixed local indexes and fast full scans
    603349 Explorer
    Currently Being Moderated
    More on the long answer:

    Currently, in order for an INDEX FAST FULL SCAN to be considered, all the columns in the query need to be present in the index -- that means both projections (the select list) and the predicates (unless you use partition extended syntax).

    If this is an issue for you, then I'd suggest that you file an SR and cite you are hitting bug 10037603 (technically a slight variation of, but it's really the same root cause underneath).

    Hope that helps.

    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 4. Re: Non-prefixed local indexes and fast full scans
    329920 Newbie
    Currently Being Moderated
    Thank you Greg.

    This feature was never there in Oracle DB before (index-ffs with non-prefixed indexes and partition pruning), so if we have managed so long without it, then we can wait a bit more.

    Any ideas in which version this feature/bug would be fixed?

    I guess the lack of this feature is one argument in favour of prefixed local indexes and against non-prefixed {message:id=9246885}

    Regards,
    CJ
  • 5. Re: Non-prefixed local indexes and fast full scans
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    joshic wrote:
    Thank you Greg.

    I guess the lack of this feature is one argument in favour of prefixed local indexes and against non-prefixed {message:id=9246885}
    Good point - but not entirely valid - I think you would get partition pruning whether you put the partitioning column first or last in this index.
    (I've linked the other post forward to this one - since this is a good follow-up to that discussion).

    Regards
    Jonathan Lewis
  • 6. Re: Non-prefixed local indexes and fast full scans
    603349 Explorer
    Currently Being Moderated
    joshic wrote:
    This feature was never there in Oracle DB before (index-ffs with non-prefixed indexes and partition pruning), so if we have managed so long without it, then we can wait a bit more.

    Any ideas in which version this feature/bug would be fixed?

    I guess the lack of this feature is one argument in favour of prefixed local indexes and against non-prefixed {message:id=9246885}
    IIRC its only a P4 bug so it could be some time. That priority depends on people's request for a fix, so if you think it is important, then you have to let Oracle Support know.

    Do note that in this case the local index does not have to be prefixed - it just needs all the required columns. Here is an example based on yours, but it uses single key list partitions.
    SQL> CREATE INDEX ix_code_id ON t_part(code,id) LOCAL;
    
    Index created.
    
    SQL> 
    SQL> EXPLAIN PLAN FOR
     2  SELECT /*+ index_ffs(t ix_code_id) */ code
     3  FROM t_part t
     4  WHERE ID = 1
     5  ;
    
    Explained.
    
    SQL> SELECT * FROM TABLE (DBMS_XPLAN.display(format=>'outline'));
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2720840169
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |            |    34 |   204 |     2   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION LIST SINGLE|            |    34 |   204 |     2   (0)| 00:00:01 |     2 |     2 |
    |   2 |   INDEX FAST FULL SCAN| IX_CODE_ID |    34 |   204 |     2   (0)| 00:00:01 |     2 |     2 |
    ----------------------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
     /*+
         BEGIN_OUTLINE_DATA
         INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_PART"."CODE" "T_PART"."ID"))
         OUTLINE_LEAF(@"SEL$1")
         ALL_ROWS
         DB_VERSION('11.2.0.2')
         OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
         IGNORE_OPTIM_EMBEDDED_HINTS
         END_OUTLINE_DATA
     */
    --
    Regards,
    Greg Rahn
    http://structureddata.org

Legend

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