Dans ce Post, vous allez activer l’utilisation des index. Pour cela, vous allez créer une table DEMO#8. Activer la surveillance des index. Utiliser l’index surveillé et visualiser la surveillance.

Création du schéma
create table DEMO#8 (col1 number primary key, col2 number, col3 varchar2(4000)) tablespace users;

begin
for i in 1..100000 loop
insert into DEMO#8 values (i, i,
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
end loop;
commit;
end;
/

exec dbms_stats.gather_table_stats(USER, ‘DEMO#8’, cascade=>true,-
method_opt=>’FOR ALL COLUMNS SIZE AUTO’);

select index_name from user_indexes where table_name=’DEMO#8′;

INDEX_NAME
————-
SYS_C005443

Utiliser le monitoring de l’index
Dans cette section, vous allez activer la surveillance de l’index et l’utiliser. A toutes les étapes de la manipulation, vous pouvez effectivement savoir si l’index a été utilisé.

alter index SYS_C005443 monitoring usage;

col INDEX_NAME format A13
col TABLE_NAME format A10
select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
————- ———- — — ——————- ——————-
SYS_C005443 DEMO#8 YES NO 02/04/2007 23:28:48

SQL> set autotrace traceonly explain
SQL> select /*+ INDEX(DEMO#8)*/ * from demo#8 where col1=2;

Plan d’exÚcution
———————————————————-
Plan hash value: 101176399

——————————————————————-
| Id | Operation | Name | Rows | Bytes |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1010 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO#8 | 1 | 1010 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005443 | 1 | |
——————————————————————-

select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
————- ———- — — ——————- ——————-
SYS_C005443 DEMO#8 YES YES 02/04/2007 23:28:48

alter index SYS_C005443 nomonitoring usage;

select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
————- ———- — — ——————- ——————-
SYS_C005443 DEMO#8 NO YES 02/04/2007 23:28:48 02/04/2007 23:29:48

Supprimez la table
drop table demo#8 purge;

GarK!