3 Replies Latest reply: Nov 28, 2011 2:22 AM by user60022 RSS

    One weird index name in execution plan

    user60022
      Hi All,

      Today someone came to me to aks my help on tuning one SQL. The follow is excerp of the whole plan. Although I resovled the performance issue, I still can't understand why
      the index with name 'BIN$ld5VAtb88PngQAB/AQF8hg==$0' as the like appear in the plan.

      If I remember correctly, this kind of names mean the objects are in the recycle bin. But can the index that has been dropped be used in execution plan?
      Could someone see any potential problem implied by this kind of index name, just index name, please?

      Best regards,
      Leon
      ---------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                                      |                                |     1 |   231 |     5   (0)| 00:00:01 |
      |*  1 |  FILTER                                               |                                |       |       |            |          |
      |   2 |   TABLE ACCESS FULL                                   | TBL_SYS_USERS                  |   344 | 79464 |     5   (0)| 00:00:01 |
      |   3 |   NESTED LOOPS                                        |                                |     1 |    80 |    13   (0)| 00:00:01 |
      |*  4 |    TABLE ACCESS FULL                                  | TBL_SYS_DATA_ACCESS_RULES      |     1 |    54 |    13   (0)| 00:00:01 |
      |*  5 |    INDEX UNIQUE SCAN                                  | BIN$ld5VAtb88PngQAB/AQF8hg==$0 |     1 |    26 |     0   (0)| 00:00:01 |
      |   6 |     NESTED LOOPS                                      |                                |     1 |    80 |    13   (0)| 00:00:01 |
      |*  7 |      TABLE ACCESS FULL                                | TBL_SYS_DATA_ACCESS_RULES      |     1 |    54 |    13   (0)| 00:00:01 |
      |*  8 |      INDEX UNIQUE SCAN                                | BIN$ld5VAtb88PngQAB/AQF8hg==$0 |     1 |    26 |     0   (0)| 00:00:01 |
      |   9 |       NESTED LOOPS                                    |                                |     1 |    80 |    13   (0)| 00:00:01 |
      |* 10 |        TABLE ACCESS FULL                              | TBL_SYS_DATA_ACCESS_RULES      |     1 |    54 |    13   (0)| 00:00:01 |
      |* 11 |        INDEX UNIQUE SCAN                              | BIN$ld5VAtb88PngQAB/AQF8hg==$0 |     1 |    26 |     0   (0)| 00:00:01 |
      |* 12 |         TABLE ACCESS BY INDEX ROWID                   | TBL_SYS_EMPLOYEES              |     1 |    26 |     1   (0)| 00:00:01 |
      |* 13 |          INDEX UNIQUE SCAN                            | BIN$ld5VAtba8PngQAB/AQF8hg==$0 |     1 |       |     0   (0)| 00:00:01 |
      |* 14 |          TABLE ACCESS FULL                            | TBL_SYS_DATA_ACCESS_RULES      |     1 |    54 |    13   (0)| 00:00:01 |
      |* 15 |           TABLE ACCESS FULL                           | TBL_SYS_DATA_ACCESS_RULES      |     1 |    54 |    13   (0)| 00:00:01 |
      |* 16 |            TABLE ACCESS FULL                          | TBL_SYS_DATA_ACCESS_RULES      |     1 |    54 |    13   (0)| 00:00:01 |
      |* 17 |             TABLE ACCESS BY INDEX ROWID               | TBL_SYS_EMPLOYEES              |     1 |    26 |     1   (0)| 00:00:01 |
      |* 18 |              INDEX UNIQUE SCAN                        | BIN$ld5VAtba8PngQAB/AQF8hg==$0 |     1 |       |     0   (0)| 00:00:01 |
      |  19 |              NESTED LOOPS                             |                                |     1 |    80 |    14   (0)| 00:00:01 |
      |  20 |               TABLE ACCESS BY INDEX ROWID             | TBL_SYS_EMPLOYEES              |     1 |    26 |     1   (0)| 00:00:01 |
      |* 21 |                INDEX UNIQUE SCAN                      | BIN$ld5VAtba8PngQAB/AQF8hg==$0 |     1 |       |     0   (0)| 00:00:01 |
      |* 22 |               TABLE ACCESS FULL                       | TBL_SYS_DATA_ACCESS_RULES      |     1 |    54 |    13   (0)| 00:00:01 |
      |  23 |                NESTED LOOPS                           |                                |     1 |    80 |    14   (0)| 00:00:01 |
        • 1. Re: One weird index name in execution plan
          BelMan
          Because the index has not deleted yet, it is just renamed and the renamed table or index name is available in the recycle
          • 2. Re: One weird index name in execution plan
            Charles Hooper
            user12064076 wrote:
            Hi All,

            Today someone came to me to aks my help on tuning one SQL. The follow is excerp of the whole plan. Although I resovled the performance issue, I still can't understand why
            the index with name 'BIN$ld5VAtb88PngQAB/AQF8hg==$0' as the like appear in the plan.

            If I remember correctly, this kind of names mean the objects are in the recycle bin. But can the index that has been dropped be used in execution plan?
            Could someone see any potential problem implied by this kind of index name, just index name, please?

            Best regards,
            Leon
            The simple answer is that the table was dropped and then flashed back to before the drop. When this happens, the table name is restored to its original name, but the index names are not restored. A quick test case to demonstrate.

            First, we will create a table and an index, then collect statistics:
            DROP TABLE T1 PURGE;
             
            CREATE TABLE T1 AS
            SELECT
              ROWNUM C1,
              RPAD('A',255,'A') C2
            FROM
              DUAL
            CONNECT BY
              LEVEL<=10000;
             
            COMMIT;
             
            CREATE INDEX IND_T1_C1 ON T1(C1);
              
            EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
            Next, we will verify that the index will be used with a sample SQL statement:
             
            SELECT
              C1,
              SUBSTR(C2,1,2)
            FROM
              T1
            WHERE
              C1<=5;
             
             C1 SU
            --- --
              1 AA
              2 AA
              3 AA
              4 AA
              5 AA
             
            SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));
             
            PLAN_TABLE_OUTPUT
            -----------------------------------------------------------------------------------------
            SQL_ID  g0kkvxqg3v145, child number 0
            -------------------------------------
            SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5
             
            Plan hash value: 683303157
             
            -----------------------------------------------------------------------------------------
            | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
            -----------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT            |           |       |       |     3 (100)|          |
            |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     5 |  1300 |     3   (0)| 00:00:01 |
            |*  2 |   INDEX RANGE SCAN          | IND_T1_C1 |     5 |       |     2   (0)| 00:00:01 |
            -----------------------------------------------------------------------------------------
             
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               2 - access("C1"<=5)
            The index was used.

            Next, let's purge the recycle bin, drop the index, and see if it can still be used in an execution plan:
            PURGE RECYCLEBIN;
             
            DROP INDEX IND_T1_C1;
             
            SELECT
              C1,
              SUBSTR(C2,1,2)
            FROM
              T1
            WHERE
              C1<=5;
             
             C1 SU
            --- --
              1 AA
              2 AA
              3 AA
              4 AA
              5 AA
             
            SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));
             
            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------
            SQL_ID  g0kkvxqg3v145, child number 0
            -------------------------------------
            SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5
             
            Plan hash value: 3617692013
             
            --------------------------------------------------------------------------
            | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------
            |   0 | SELECT STATEMENT  |      |       |       |   137 (100)|          |
            |*  1 |  TABLE ACCESS FULL| T1   |     5 |  1300 |   137   (5)| 00:00:01 |
            --------------------------------------------------------------------------
             
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               1 - filter("C1"<=5)
            Notice in the above that the dropped index was not used.

            Let's recreate the index and regather statistics (when the index is created on Oracle Database 10.1 and greater, the index statistics will be automatically collected, so this is actually an unnecessary step):
            CREATE INDEX IND_T1_C1 ON T1(C1);
              
            EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
            We could verify that the index will be used again, but we can skip that step.

            Now we will drop the table and recover it from the recycle bin. Then we will execute the simple query of the T1 table again, and retrieve the execution plan:
            DROP TABLE T1;
             
            FLASHBACK TABLE T1 TO BEFORE DROP;
             
            SELECT
              C1,
              SUBSTR(C2,1,2)
            FROM
              T1
            WHERE
              C1<=5;
             
             C1 SU
            --- --
              1 AA
              2 AA
              3 AA
              4 AA
              5 AA
             
            SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));
             
            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------------------------------------
            SQL_ID  g0kkvxqg3v145, child number 0
            -------------------------------------
            SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5
             
            Plan hash value: 3441582395
             
            --------------------------------------------------------------------------------------------------------------
            | Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT            |                                |       |       |     3 (100)|       |
            |   1 |  TABLE ACCESS BY INDEX ROWID| T1                             |     5 |  1300 |     3   (0)| 00:00:01 |
            |*  2 |   INDEX RANGE SCAN          | BIN$PESygWW5R0WhbOaDugxqwQ==$0 |     5 |       |     2   (0)| 00:00:01 |
            --------------------------------------------------------------------------------------------------------------
             
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               2 - access("C1"<=5)
            Notice in the above that the index was used, but it now has an odd name.

            Let's fix the odd name and re-execute the query:
            ALTER INDEX "BIN$PESygWW5R0WhbOaDugxqwQ==$0" RENAME TO IND_T1_C1;
             
            SELECT
              C1,
              SUBSTR(C2,1,2)
            FROM
              T1
            WHERE
              C1<=5;
             
             C1 SU
            --- --
              1 AA
              2 AA
              3 AA
              4 AA
              5 AA
             
            SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));
             
            PLAN_TABLE_OUTPUT
            -----------------------------------------------------------------------------------------
            SQL_ID  g0kkvxqg3v145, child number 0
            -------------------------------------
            SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5
             
            Plan hash value: 683303157
             
            -----------------------------------------------------------------------------------------
            | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
            -----------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT            |           |       |       |     3 (100)|          |
            |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     5 |  1300 |     3   (0)| 00:00:01 |
            |*  2 |   INDEX RANGE SCAN          | IND_T1_C1 |     5 |       |     2   (0)| 00:00:01 |
            -----------------------------------------------------------------------------------------
             
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               2 - access("C1"<=5)
            The index is now back to normal.

            Charles Hooper
            http://hoopercharles.wordpress.com/
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: One weird index name in execution plan
              user60022
              Hi Charles,

              Thank you very much.

              Your answer is extremely helpful. And you also have answered that why dropped index can't be used and how they can be used.

              Best regards,
              Leon