Table IOT, Logical Rowid, index secondaire et PCT_DIRECT_ACCESS

**Ikebukuro** · 25/12/2018, 11h44

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :-)

**Ikebukuro** · 31/12/2018, 16h16

Up Up

Alors, pas de réponse? Vous avez trop arrosé Noël?
Je compte sur vous pour 2019!

**pachot** · 01/01/2019, 17h00

_Bonjour,
L'accès via physical rowid guess, plutôt que par logical rowid, intervient au moment de d'exécution, pour chaque entrée d'index. Donc ce n'est pas visible dans le plan d'exécution. Tu verras par contre la diffécence dans les statistiques d'exécution, 'buffers' avec dbms_xplan.display_cursor(format=>'allstats last').
Cordialement,
Franck.

**Ikebukuro** · 02/01/2019, 09h04

Salut Franck,

Merci pour le retour, je n'avais pas compris que cet accès de type guess (devinette en français) n'était pas visible dans le plan d'exécution

Sans toi, j'aurais pu chercher longtemps...

**pachot** · 02/01/2019, 13h04

Envoyé par Ikebukuro

Salut Franck,

Merci pour le retour, je n'avais pas compris que cet accès de type guess (devinette en français) n'était pas visible dans le plan d'exécution

Sans toi, j'aurais pu chercher longtemps...

Le 'guess' veut plutôt dire 'tentative' que 'devinette' ici

Oracle essaie l'adresse physique pour voir si la ligne est toujours là, avant de descendre le B*Tree sur la PK. A noter que pour rafraîchir ces adresses physiques, il suffit de ALTER INDEX ... UPDATE BLOCK REFERENCES de temps en temps.

Table IOT, Logical Rowid, index secondaire et PCT_DIRECT_ACCESS [12c]

Administration Oracle

Discussions similaires

Partager

Partager