Oracle - Méthodes d'accès (access methods)

Oracle | Méthodes d'accès (access methods)

Notes
Méthodes d'accès : Tables
1. FULL SCAN
2. BY INDEX ROWID
Méthodes d'accès : Indexes

Notes
This table (and its data) is used in the examples bellow:
CREATE TABLE table_access_methods ( id1 NUMBER(5,0) NOT NULL , id2 NUMBER(5,0) NOT NULL , type1 NUMBER(1,0) NOT NULL , desc1 NVARCHAR2(2000) NOT NULL );
DECLARE indexLoop NUMBER(5,0); BEGIN FOR indexLoop IN 1..50000 LOOP IF MOD(indexLoop,2) = 0 THEN INSERT INTO table_access_methods (id1, id2, type1, desc1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 1, 'ABCD'); ELSE INSERT INTO table_access_methods (id1, id2, type1, desc1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 2, 'ZYZW'); END IF; END LOOP; END; / COMMIT;
Méthodes d'accès : Tables
1. FULL SCAN
  Un FULL SCAN de la table est souvent effectué, par exemple, lorsque il n'y a pas d'indexes sur les colonnes sélectionnées et que la clause WHERE ne contient pas de conditions ou que les conditions sont faites sur des colonnes non indexées.
  Le FULL SCAN de la table peut être aussi choisi si Oracle juge que l'utilisation des indexes peut être moins performante ; c'est le cas lorsque la requête devrait retourner une grande partie ou la totalité des données de la table ou que les données de la table sont physiquement ordonnées dans un ordre non optimal ou qu'il y a beaucoup d'espace libre entre les données de la table.
  SELECT * FROM table_access_methods;
  Plan d'exécution : ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 4 | | 1 | TABLE ACCESS FULL| TABLE_ACCESS_METHODS | 1 | 48819 | 50 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------------------
2. BY INDEX ROWID
  Cette méthode d'accès est choisie lorsqu'un index est utilisé : pour chaque ligne de l'index (dont les colonnes matchent avec les conditions de sélection) un accès à la table est effectué en utilisant le ROWID.
  CREATE INDEX ix_table_access_methods_id2 ON table_access_methods (id2); ANALYZE INDEX ix_table_access_methods_id2 COMPUTE STATISTICS;
  SELECT * FROM table_access_methods WHERE ID2 = 2;
  Plan d'exécution : --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 6 | | 1 | TABLE ACCESS BY INDEX ROWID| TABLE_ACCESS_METHODS | 1 | 4 | 4 |00:00:00.01 | 6 | |* 2 | INDEX RANGE SCAN | IX_TABLE_ACCESS_METHODS_ID2 | 1 | 195 | 4 |00:00:00.01 | 2 | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID2"=2)
Méthodes d'accès : Indexes
1. UNIQUE SCAN
  Cette méthode d'accès est choisie lorsque l'index utilisé est unique (ou index d'une clé primaire).
  CREATE UNIQUE INDEX ix_table_access_methods_id1 ON table_access_methods (id1); ANALYZE INDEX ix_table_access_methods_id1 COMPUTE STATISTICS;
  SELECT * FROM table_access_methods WHERE ID1 = 2;
  Plan d'exécution : --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | 1 | TABLE ACCESS BY INDEX ROWID| TABLE_ACCESS_METHODS | 1 | 1 | 1 |00:00:00.01 | 3 | |* 2 | INDEX UNIQUE SCAN | IX_TABLE_ACCESS_METHODS_ID1 | 1 | 1 | 1 |00:00:00.01 | 2 | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID1"=2)
2. RANGE SCAN
  Cette méthode d'accès est choisie lorsque l'index utilisé est non-unique et/ou que la condition sur la colonne est faite sur un ensemble de valeurs ; typiquement en utilisant :
  - les opérateurs >, >=, <, <=, BETWEEN.
  - l'opérateur = dans le cas d'un index non-unique.
  SELECT * FROM table_access_methods WHERE id1 > 2;
  Plan d'exécution : --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 3 | | 1 | TABLE ACCESS BY INDEX ROWID| TABLE_ACCESS_METHODS | 1 | 48812 | 50 |00:00:00.01 | 3 | |* 2 | INDEX RANGE SCAN | IX_TABLE_ACCESS_METHODS_ID1 | 1 | 439 | 50 |00:00:00.01 | 2 | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID1">2)
3. FULL SCAN
  Cette méthode d'accès est choisie dans les cas où les colonnes peuvent être sélectionnées directement de l'indexe.
  SELECT ID1 FROM table_access_methods ORDER BY id1;
  Plan d'exécution : ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 2 | | 1 | INDEX FULL SCAN | IX_TABLE_ACCESS_METHODS_ID1 | 1 | 48819 | 50 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------
4. FAST FULL SCAN
  Cette méthode d'accès est choisie dans les cas où les colonnes sélectionnées font parties d'un indexe et qu'au moins une de ses colonnes a la contrainte NOT NULL.
  CREATE INDEX ix_table_access_methods_type1 ON table_access_methods (type1, id2); ANALYZE INDEX ix_table_access_methods_type1 COMPUTE STATISTICS;
  SELECT type1, id2 FROM table_access_methods;
  Plan d'exécution : ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 7 | | 1 | INDEX FAST FULL SCAN| IX_TABLE_ACCESS_METHODS_TYPE1 | 1 | 48819 | 50 |00:00:00.01 | 7 | ----------------------------------------------------------------------------------------------------------------
5. SKIP SCAN
  Cette méthode d'accès est choisie dans les cas où une condition est faite sur une colonne qui fait partie des colonnes d'un indexe mais qui n'est pas la première colonne de cet indexe.