Table IOT, Logical Rowid, index secondaire et PCT_DIRECT_ACCESS
Salut amis DBA,
Je travaille actuellement sur une table IOT et j'ai un pb : comment Oracle accède-t-il à la table quand on utilise un index secondaire?
Une table IOT est en vrai un index MAIS je peux créer dessus un autre index, dit index secondaire ou index sur index.
Ce type de table a des logical rowid au lieu de physical rowid (pour les tables classiques) et, avec ce logical rowid, Oracle peut recalculer la pk.
Le pb est que lorsque la table est fortement modifiée via des ordres DML, les données bougent dans les blocs (car l'index est trié) ET il n'y a plus de lien entre le logical rowid de l'index secondaire et le bloc où se trouve les données dans l'index primaire. Oracle utilise pour cela une colonne, dans DBA_INDEXES, appelée PCT_DIRECT_ACCESS. Si elle vaut 100, on accède directement depuis l'index secondaire à l'index primaire sinon il faut recalculer la pk depuis le logical rowid de l'index secondaire puis parcourir l'index primaire. Donc, d'après ce que je comprends, Oracle accède aux données de l'index primaire selon deux façon différentes et c'est ça ce que je veux voir.
Et c'est là où ça coince... D'après mes tests, je ne vois pas de différence dans le plan d'exécution dans la façon dont on accède à l'index primaire selon que la colonne PCT_DIRECT_ACCESS vaut 100 ou bien moins.
Voici mes test.
Création de la table.
Code:
1
2
3
4
5
6
7
8
| SQL> create table ZZ_IOT (id number primary key, first_name varchar2(50 char), last_name varchar2(50 char), created date) organization index;
Table created.
SQL> INSERT INTO zz_iot SELECT rownum, 'First_name' || to_char(rownum), 'Last_name' || to_char(rownum), sysdate FROM dual CONNECT BY LEVEL <=1000;
1000 rows created.
SQL> commit;
Commit complete. |
Nom de l'index correspondant à la table IOT.
Code:
1
2
3
4
5
| SQL> select object_name, object_type from user_objects where object_name in ('ZZ_IOT', 'SYS_IOT_TOP_80205') order by object_name;
OBJECT_NAME OBJECT_TYPE
-------------------- -----------------------
SYS_IOT_TOP_80205 INDEX
ZZ_IOT TABLE |
Exemples de logical rowid.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| SQL> select rowid from zz_iot where rownum < 11 order by rowid;
ROWID
-----------------------------------------
*BAMAJEQDwgQ3/g
*BAMAJEQDwgQ4/g
*BAMAJEQDwgQ5/g
*BAMAJEQDwgQ6/g
*BAMAJEQDwgQ7/g
*BAMAJEQDwgQ8/g
*BAMAJEQDwgQ9/g
*BAMAJEQDwgQ+/g
*BAMAJEQDwgQ//g
*BAMAJEQDwgRA/g
10 rows selected.
SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT');
PL/SQL procedure successfully completed. |
La table IOT est bien considérée comme un index, comme le montre le plan d'exécution : on accède aux données via un INDEX UNIQUE SCAN.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| SQL> select * from zz_iot where id = 200;
ID FIRST_NAME LAST_NAME CREATED
---------- -------------------------------------------------- -------------------------------------------------- ---------
200 First_name200 Last_name200 23-DEC-18
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4u2tuk6g76g5n, child number 0
-------------------------------------
select * from zz_iot where id = 200
Plan hash value: 1555085035
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_80205 | 1 | 226 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=200)
18 rows selected. |
Maintenant on crée un index secondaire : un index sur index puisque la table IOT est en réalité un index.
Code:
1
2
3
4
5
6
7
8
9
10
11
| SQL> create index idx_zz_iot_first_name on zz_iot(first_name);
Index created.
SQL> select index_name, INDEX_TYPE, TABLE_TYPE, UNIQUENESS from dba_indexes where table_name = 'ZZ_IOT' order by 1;
INDEX_NAME INDEX_TYPE TABLE_TYPE UNIQUENES
------------------------- --------------------------- ----------- ---------
IDX_ZZ_IOT_FIRST_NAME NORMAL TABLE NONUNIQUE
SYS_IOT_TOP_80205 IOT - TOP TABLE UNIQUE
SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', cascade=>TRUE);
PL/SQL procedure successfully completed. |
Que se passe-t-il quand je cherche depuis cet index secondaire? Il y a deux parcours d'index... normal?
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| SQL> select * from zz_iot where first_name = 'First_name200';
ID FIRST_NAME LAST_NAME CREATED
---------- -------------------------------------------------- -----------------------------
200 First_name200 Last_name200 23-DEC-18
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4b5xhv7xbfzm0, child number 0
-------------------------------------
select * from zz_iot where first_name = 'First_name200'
Plan hash value: 2156475558
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_80205 | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| IDX_ZZ_IOT_FIRST_NAME | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("FIRST_NAME"='First_name200')
2 - access("FIRST_NAME"='First_name200')
20 rows selected. |
Pourtant j'ai la colonne PCT_DIRECT_ACCESS à 100 donc Oracle devrait pouvoir accéder DIRECTEMENT à la donnée via l'index primaire... oui mais via quelle opération? Index access by rowid? ça n'existe pas.
Code:
1
2
3
4
5
| SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
INDEX_NAME INDEX_TYPE PCT_DIRECT_ACCESS
------------------------------ ---------------------------------
IDX_ZZ_IOT_FIRST_NAME NORMAL 100
SYS_IOT_TOP_80205 IOT - TOP 0 |
Quel est le plan d'exécution si j'utilise un logical rowid dans le WHERE?
Code:
1
2
3
4
5
6
7
8
9
| SQL> select rowid from zz_iot where id = 200;
ROWID
-----------------------------------------
*BAMAJEcCwgP+
SQL> select * from zz_iot where rowid = '*BAMAJEcCwgP+';
ID FIRST_NAME LAST_NAME CREATED
---------- -------------------------------------------------
200 First_name200 Last_name200 23-DEC-18 |
Oh, intéressant, même si on a bien un prédicat d'accès via le ROWID, Oracle met dans le plan d'exécution une opération de type INDEX UNIQUE SCAN.
Cela veut-il dire qu'Oracle doive toujours parcourir l'index lié à la PK et que son logical rowid ne permette pas un accès direct au bloc de l'index de la pk?
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c3r6nb19kc154, child number 0
-------------------------------------
select * from zz_iot where rowid = '*BAMAJEcCwgP+'
Plan hash value: 1555085035
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_80205 | 1 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID=:B1)
18 rows selected. |
On essaye de mettre le champ PCT_DIRECT_ACCESS à 0 pour voir si cela change le plan d'exécution.
Pour se faire on va supprimer 90% de la table puis faire un shrink pour forcer le recalcul de rowid dans la table et donc un décalage avec ceux de l'index.
Code:
1
2
3
4
5
| SQL> delete from zz_iot where mod(id, 10) <> 0;
900 rows deleted.
SQL> commit;
Commit complete. |
Pas de ENABLE ROW MOVEMENT pour une table IOT.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
| SQL> alter table zz_iot ENABLE ROW MOVEMENT;
alter table zz_iot ENABLE ROW MOVEMENT
*
ERROR at line 1:
ORA-14066: illegal option for a non-partitioned index-organized table
SQL> ALTER TABLE zz_iot SHRINK SPACE;
Table altered.
SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_index_stats('', 'IDX_ZZ_IOT_FIRST_NAME');
PL/SQL procedure successfully completed. |
Héhé, on est passé de 100 à 17! Mais le plan d'exécution ne change pas... zut. Faut-il passer à 0?
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
INDEX_NAME INDEX_TYPE PCT_DIRECT_ACCESS
-------------------- --------------------------------------
IDX_ZZ_IOT_FIRST_NAME NORMAL 17
SYS_IOT_TOP_80205 IOT - TOP 0
SQL> set autotrace on
SQL> select * from zz_iot where first_name = 'First_name10';
ID FIRST_NAME LAST_NAME CREATED
---------- -------------------------------------------------- -------------------------------------------------- ---------
10 First_name10 Last_name10 23-DEC-18
Execution Plan
----------------------------------------------------------
Plan hash value: 2156475558
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_80205 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| IDX_ZZ_IOT_FIRST_NAME | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("FIRST_NAME"='First_name10')
2 - access("FIRST_NAME"='First_name10') |
Voilà, c'était long, merci à ceux qui ont tout lu et j'attends avec impatience vos remarques :-)
