Usare gli indici invisibili

Table created.
SQL> create index ind_TEST.index_tables on TEST.tables_empty (a)
Index created.
OWNER                          OBJECT_NAME                    OBJECT_TYPE
—————————— —————————— ———–
TEST                           TABLES_EMPTY                   TABLE
IND_TEST                       INDEX_TABLES                  INDEX
 
SQL> exec dbms_stats.gather_table_stats
(ownname=>’TEST’,tabname=>’TABLES_EMPTY’,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>  exec dbms_stats.gather_index_stats
(ownname=>’IND_TEST’,indname=>’INDEX_TABLES’);
PL/SQL procedure successfully completed.
SQL> conn ind_TEST
Enter password:
SQL>  explain plan for
2  select * from TEST.TABLES_EMPTY;
SQL> @?/rdbms/admin/utlxpls
 
PLAN_TABLE_OUTPUT
———————————————————————–
Plan hash value: 932631555
 
———————————————————————————-
| Id  | Operation    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT  |       |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TABLES_EMPTY |     1 |    13 |     2   (0)| 00:00:01 |
———————————————————————————-
SQL>  explain plan for
2    select /*+ index (a INDEX_TABLES)*/ * from TEST.TABLES_EMPTY a;
 
SQL> @?/rdbms/admin/utlxpls
 
PLAN_TABLE_OUTPUT
———————————————————–
Plan hash value: 932631555
———————————————————————————-
| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT  |              |   1 |    13 |   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TABLES_EMPTY |   1 |    13 |   2   (0)| 00:00:01 |
———————————————————————————-
Il CBO, con questo metodo, non usa l’indice creato e nemmeno l’hint.
Gli indici invisibili sono la soluzione per gestire varie casistiche e migliorare il piano d’accesso del CBO
 
SQL> create index TEST.INDEX_TABLES on TEST.tables_empty (1) invisible;
Index created.
INDEX_NAME                     VISIBILIT LAST_ANAL
—————————— ——— ———
INDEX_TABLES                  INVISIBLE 23-JAN-12
OWNER             OBJECT_NAME                    OBJECT_TYPE
—————————— ——————————
TEST                     TABLES_EMPTY          TABLE
TEST                     INDEX_TABLES          INDEX
SQL> sho user
USER is “IND_TEST”
SQL>  explain plan for
2   select * from TEST.TABLES_EMPTY;
SQL> @?/rdbms/admin/utlxpls
 
PLAN_TABLE_OUTPUT
—————————————————————
Plan hash value: 932631555
——————————————————————-
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT  |       |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TABLES_EMPTY |     1 |    13 |     2   (0)| 00:00:01 |
————————————————————–
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL>  explain plan for
2  select /*+ index (a INDEX_TABLES)*/ * from TEST.TABLES_EMPTY a;
SQL>  @?/rdbms/admin/utlxpls
 
PLAN_TABLE_OUTPUT
————————————————————————-
Plan hash value: 482654189
———————————————————————————————
| Id  | Operation    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————
|   0 | SELECT STATEMENT            |          |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLES_EMPTY  |     1 |    13 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | INDEX_TABLES  |     1 |       |     1   (0)| 00:00:01 |
———————————————————————————————
 
Testiamolo con utenti differenti
 
SQL> sho user
USER is “TEST”
SQL>
 
SQL>  explain plan for
2  select /*+ index (a INDEX_TABLES)*/ * from TEST.TABLES_EMPTY a;
 
SQL> @?/rdbms/admin/utlxpls
 
PLAN_TABLE_OUTPUT
———————————————————————
Plan hash value: 932631555
———————————————————————
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TABLES_EMPTY |     1 |    13 |     2   (0)| 00:00:01 |
———————————————————————————-
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> explain plan for
2   select /*+ index (a INDEX_TABLES)*/ * from TEST.TABLES_EMPTY a;
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
—————————————————————————————–
Plan hash value: 482654189
———————————————————————————————
| Id  | Operation       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————
|   0 | SELECT STATEMENT            |               |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLES_EMPTY  |     1 |    13 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | INDEX_TABLES  |     1 |       |     1   (0)| 00:00:01 |
———————————————————————————————

Potrebbero interessarti anche...