Avant de créer un index, il est parfois intéressant (pour ceux qui ont des tables de plusieurs dizaines de Gigabytes) d’évaluer

• l’impact d’un index sur un ordre SQL (ou une charge)
• l’espace nécessaire pour cet index.

Et bien répondre à ces 2 questions est possible et même très simple. Pour les besoins de ce qui suit, nous allons créer une table comme ceci :

SQL> create table gark(id number, lib varchar2(4000));

SQL> begin
for i in 1..50000 loop
insert into gark values (i, ‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
|| ‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
|| ‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
|| ‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
|| ‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
|| ‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
|| ‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’
||’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
end loop;
commit;
end;
/

SQL> exec dbms_stats.gather_table_stats(USER, ‘GARK’);

Impact d’un index sur un ordre SQL
Il est possible de créer un index virtuel, c’est à dire, référencé dans le dictionnaire mais pas rempli. Pour ça, il suffit d’utiliser le mot clé NOSEGMENT dans l’ordre de création de l’index.

Voici un requête et son plan d’exécution (sans surprise !) :
SQL> select * from gg where id=10;

Execution Plan
———————————————————-
Plan hash value: 1795868617

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 1016 | 1580 (1)| 00:00:19 |
|* 1 | TABLE ACCESS FULL| GARK | 1 | 1016 | 1580 (1)| 00:00:19 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————
1 – filter(« ID »=10)

On veux voir l’effet d’un index sans le créer réellement, on peut utiliser les 2 commandes
create unique index gark_idx on gark(id) nosegment;
alter session set "_use_nosegment_indexes"=true;

Le plan d’exécution de la requête devient le suivant :

SQL> select * from gg where id=10;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
—————————–
Plan hash value: 2270987589

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1016 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| GG | 1 | 1016 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | GARK_IDX | 1 | | 1 (0)| 00:00:01 |
—————————————————————————————-

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
—————————–
2 – access(« ID »=10)

Taille d’un index
Pour connaitre la taille d’un index, il suffit d’utiliser le package DBMS_SPACE en lui passant comme paramètre l’ordre CREATE INDEX comme ceci :

SQL> set serveroutput on
SQL> declare
v_used_bytes number;
v_alloc_bytes number;
begin
dbms_space.create_index_cost(‘CREATE UNIQUE INDEX GARK_IDX ON GARK(ID)’,
v_used_bytes, v_alloc_bytes);
dbms_output.put_line(‘La taille des donnees indexees est de : ‘||to_char(v_used_bytes) ||’ Octets’);
dbms_output.put_line(‘La taille de de l »index une fois cree sera de : ‘||to_char(v_alloc_bytes) ||’ Octets’);
end;
/

La taille des donnees indexees est de : 246395 Octets
La taille de de l’index une fois cree sera de : 851968 Octets

Et biensûr, pour continuer sur les index, regardez le SQL Access Advisor et la surveillance de l’utilisation des index.

GarK!