7 Replies Latest reply: Oct 8, 2010 1:57 AM by Mohamed Houri RSS

    Index_combine hint

    Mohamed Houri
      Dears
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      I have a table TableT with the following indexes on it
       Ind_tab_aladat_fi   on trunc(a_date)
      Ind_order_by_fi     on (accepted asc, a_date desc, label desc)
      And I want for my query (see below) to use the first index in order to satisfy the where clause and to use the second index to avoid doing sort order by. But I didn't succeed
      SELECT  
          tab.*
      FROM tableT tab
         WHERE TRUNC (tab.a_date) = TO_DATE ('22092010', 'ddmmrrrr')
      ORDER BY 
            accepted     asc
           ,a_date       desc
           ,label        desc;
      
      11295 rows selected.
      
      Elapsed: 00:00:07.93
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2259490324
      
      ----------------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |                    |  4318 |   581K|       |   278   (7)| 00:00:01 |
      |   1 |  SORT ORDER BY               |                    |  4318 |   581K|  1736K|   278   (7)| 00:00:01 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| tableT             |  4318 |   581K|       |   154   (6)| 00:00:0
      |*  3 |    INDEX RANGE SCAN          | Ind_tab_aladat_fi  |  4318 |       |       |    25   (8)| 00:00:01 |
      ----------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access(TRUNC(INTERNAL_FUNCTION("a_date"))=TO_DATE('22092010','ddmmrrrr'))
      
      
      SELECT  /*+ index (tab Ind_order_by_fi) */
          tab.*
      FROM tableT tab
         WHERE TRUNC (tab.a_date) = TO_DATE ('22092010', 'ddmmrrrr')
      ORDER BY 
            accepted asc
           ,a_date   desc
           ,label    desc;
      
      11295 rows selected.
      
      Elapsed: 00:00:08.79
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2070438661
      
      ----------------------------------------------------------------------------------------------------
      | Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |                             |  4318 |   581K|  9306  (18)| 00:00:04 |
      |   1 |  TABLE ACCESS BY INDEX ROWID| tableT                      |  4318 |   581K|  9306  (18)| 00:00:04 |
      |*  2 |   INDEX FULL SCAN           | Ind_order_by_fi             |  4318 |       |  6426  (26)| 00:00:03 |
      ----------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter(TRUNC(SYS_OP_UNDESCEND(SYS_OP_DESCEND("a_date")))=TO_DATE('22092010','ddmmrrrr'))
      
      
      SELECT /*+ index_combine(tab Ind_tab_aladat_fi  Ind_order_by_fi) */ 
          tab.*
      FROM tableT tab
         WHERE TRUNC (tab.alarm_date) = TO_DATE ('22092010', 'ddmmrrrr')
      ORDER BY 
            accepted asc
           ,alarm_date desc
           ,train_label desc;
      
      11295 rows selected.
      
      Elapsed: 00:00:08.15
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2259490324
      
      ----------------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |                    |  4318 |   581K|       |   278   (7)| 00:00:01 |
      |   1 |  SORT ORDER BY               |                    |  4318 |   581K|  1736K|   278   (7)| 00:00:01 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| tableT             |  4318 |   581K|       |   154   (6)| 00:00:0
      |*  3 |    INDEX RANGE SCAN          | Ind_tab_aladat_fi  |  4318 |       |       |    25   (8)| 00:00:01 |
      ----------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access(TRUNC(INTERNAL_FUNCTION("A_DATE"))=TO_DATE('22092010','ddmmrrrr'))
      Have you any idea?

      Thanks

      Edited by: Mohamed Houri on 7 oct. 2010 10:47
        • 1. Re: Index_combine hint
          sb92075
          INDEX use is prevented by TRUNC (tab.a_date)

          With the exception of FBI, any function applied to column precludes direct index lookup.
          • 2. Re: Index_combine hint
            askraks
            Hi,

            Yes i will agree with last post and create function based index.
            You will see index skip scan in explain plan.

            Kind Regards,
            Rakesh Jayappa
            • 3. Re: Index_combine hint
              Pavan Kumar
              Hi,

              try for this - some testing

              SELECT /*+ INDEX_JOIN(tab Ind_order_by_fi Ind_tab_aladat_fi ) */
              tab.*
              FROM s102_tabtification tab
              WHERE TRUNC (tab.alarm_date) = TO_DATE ('22092010', 'ddmmrrrr')
              ORDER BY
              accepted asc
              ,alarm_date desc
              ,train_label desc;

              - Pavan Kumar N
              • 4. Re: Index_combine hint
                Jonathan Lewis
                Mohamed Houri wrote:

                I have a table TableT with the following indexes on it
                Ind_tab_aladat_fi   on trunc(a_date)
                Ind_order_by_fi     on (accepted asc, a_date desc, label desc)
                And I want for my query (see below) to use the first index in order to satisfy the where clause and to use the second index to avoid doing sort order by. But I didn't succeed
                Unfortunately the plan you want is not available to Oracle.

                Essentially it HAS to walk the Ind_order_by_fi (full scan) to get the data in the right order without sorting it - and Oracle doesn't have a way of merging the ordered rowids with the rowids from the ind_tab_aladat_fi index to eliminate the unwanted rowids before visiting the table.

                In principle you could write something like:
                select
                    /*+ leading (t1, t2, t3) 
                        use_hash(t2) no_swap_join_inputs(t2)
                        use_nl(t3) rowid(t3)
                    */
                    *
                from
                    (select /*+ no_merge */ rowid from tableT where trunc(a_date) = to_date('22092010','ddmmyyyy')) t1,
                    (select /*+ no_merge index(Ind_order_by_fi ) */ rowid from tableT order by ...) t2,
                    (select * from tablet) t3
                where
                    t2.rowid = t1.rowid
                and t3.rowid = t2.rowid
                ;
                With a little fiddling around, this will give you the data in the right order because it will
                <ul>
                build a hash table of rowids that match the date
                scan the index in the right order getting the rowids in the correct order, and checking them against the desired set
                access the rest of the row data by rowid
                </ul>

                But you shouldn't do it - and Oracle might find some clever mechanical optimisation (readahead/batching/prefetching) that spoils the order. One day, of course, the optimizer might be enhanced to do this after examining your original query.

                ( I'll leave it to you to point out that you already have the function-based indexes you are being advised to build. ;)

                Regards
                Jonathan Lewis
                • 5. Re: Index_combine hint
                  Pavan Kumar
                  Hi Jonathan,

                  From ORacle Documentation INDEX_COMBINE - hint explicitly chooses a bitmap access path for the table. As we two indexes which are B-tree, there me issue in order to build up bitmap for those columns (so Optimizer might be choosen a different) plan. As such we go for INDEX_JOIN - making optimizer to use both of them at a time (combined).

                  Awaiting for your inputs..

                  - Pavan Kumar N
                  • 6. Re: Index_combine hint
                    Jonathan Lewis
                    Pavan Kumar wrote:

                    From ORacle Documentation INDEX_COMBINE - hint explicitly chooses a bitmap access path for the table. As we two indexes which are B-tree, there me issue in order to build up bitmap for those columns (so Optimizer might be choosen a different) plan. As such we go for INDEX_JOIN - making optimizer to use both of them at a time (combined).
                    Pavan Kumar,

                    I didn't say anything about the index_combine hint because it is not relevant to the OP's requirement - but you are correct to point out that it relates to bitmap combination. You can use it with b-tree indexes (following btree/bitmap conversion) but it's not going to help the OP do what he wants to do.

                    The index_join hint is also irrelevant to the needs of the OP - it's intended use is for cases where all the columns in the query can be found in a subset of the indexes on the table; it would only be of benefit "by accident" in this case, not by design.

                    Regards
                    Jonathan Lewis
                    • 7. Re: Index_combine hint
                      Mohamed Houri
                      Dear Jonathan,

                      Perfect.

                      This is the explain plan I got
                      11295 rows selected.
                      
                      Elapsed: 00:00:01.85
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 2735232810
                      
                      ----------------------------------------------------------------------------------------------------
                      | Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
                      ----------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT            |                             |  4299 |   680K|  9647   (6)| 00:00:04 |
                      |   1 |  NESTED LOOPS               |                             |  4299 |   680K|  9647   (6)| 00:00:04 |
                      |*  2 |   HASH JOIN                 |                             |  4299 |   100K|  5277   (9)| 00:00:02 |
                      |   3 |    VIEW                     |                             |  4299 | 51588 |    25   (8)| 00:00:01 |
                      |*  4 |     INDEX RANGE SCAN        | IND_TAB_ALADAT_FI           |  4299 | 85980 |    25   (8)| 00:00:01 |
                      |   5 |    VIEW                     |                             |   661K|  7757K|  5112   (6)| 00:00:02 |
                      |   6 |     INDEX FULL SCAN         | IND_ORDER_BY_FI             |   661K|    18M|  5112   (6)| 00:00:0
                      |   7 |   TABLE ACCESS BY USER ROWID| TABLET                      |     1 |   138 |     1   (0)| 00:00:01 |
                      ----------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - access("T2".ROWID="T1".ROWID)
                         4 - access(TRUNC(INTERNAL_FUNCTION("A_DATE"))=TO_DATE(' 2010-09-22 00:00:00', 'syyyy-mm-dd
                                    hh24:mi:ss'))
                      And, as advised, I will not use it

                      Thanks a lot

                      Mohamed Houri