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;

1. TABLE ACCESS FULL
   A TABLE ACCESS FULL operation reads all blocks in a table segment sequentially from the first block to the high water mark. This method is chosen when:
   

   • No suitable indexes exist for the query predicates.
     
   • The WHERE clause contains no conditions or uses non-indexed columns.
     
   • The optimizer estimates that a large percentage of rows will be returned.
     
   • Parallel processing is enabled for large table scans.
     
   • The cost-based optimizer determines it's more efficient than index access.
     

   Full table scans use multiblock I/O operations, making them efficient for processing large portions of a table. However, they can be resource-intensive for large tables when only a small subset of data is needed.
   

   SELECT * FROM table_access_methods;
   

   Execution plan:

   ----------------------------------------------------------------------------------------------------
   | 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. TABLE ACCESS BY INDEX ROWID
   This access method retrieves table rows using ROWIDs obtained from index entries. The process involves two steps:
   

   • Access the index to find ROWIDs that match the query criteria.
     
   • Use those ROWIDs to directly access the corresponding table rows.
     

   This method is highly efficient for selective queries because ROWIDs provide the exact physical location of rows, eliminating the need to scan multiple blocks. It's typically chosen when:
   

   • An appropriate index exists on the queried columns.
     
   • The query is selective (returns a small percentage of total rows).
     
   • The index access cost is lower than a full table scan.
     

   The ROWID access is a single-block I/O operation, making it very fast for retrieving specific rows.
   

   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;
   

   Execution plan:

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

1. INDEX UNIQUE SCAN
   An INDEX UNIQUE SCAN is used when Oracle accesses a unique index (including primary key indexes) with an equality predicate. This operation:
   

   • Guarantees at most one matching index entry.
     
   • Uses a direct path through the B-tree structure to locate the entry.
     
   • Is the most efficient index access method.
     
   • Stops immediately after finding the matching entry or determining no match exists.
     

   This scan type provides optimal performance for point lookups on unique columns.
   

   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;
   

   Execution plan:

   ---------------------------------------------------------------------------------------------------------------------
   | 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. INDEX RANGE SCAN
   An INDEX RANGE SCAN reads index entries within a specified range of values. This method is used when:
   

   • Query predicates involve range conditions (>, >=, <, <=, BETWEEN).
     
   • Equality predicates are used on non-unique indexes.
     
   • The leading columns of a composite index match the query predicates.
     

   The scan starts at the first qualifying index entry and continues until no more entries satisfy the condition. It's highly efficient for selective range queries and supports both ascending and descending access paths depending on query requirements and available indexes.
   

   SELECT * FROM table_access_methods WHERE id1 > 2;
   

   Execution plan:

   ---------------------------------------------------------------------------------------------------------------------
   | 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. INDEX FULL SCAN
   An INDEX FULL SCAN reads all entries in an index in sorted order, from the first leaf block to the last. This method is chosen when:
   

   • All required columns are available in the index (covering index).
     
   • The query requires sorted results that match the index order.
     
   • It's more efficient than accessing the table data.
     

   Unlike a table full scan, an index full scan reads only the index blocks, which are typically much smaller and fewer in number than table blocks. This makes it particularly useful for queries that only need indexed columns or for providing pre-sorted data to eliminate separate sorting operations.
   

   SELECT ID1 FROM table_access_methods ORDER BY id1;
   

   Execution plan:

   ----------------------------------------------------------------------------------------------------------
   | 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. INDEX FAST FULL SCAN
   An INDEX FAST FULL SCAN reads all index blocks using multiblock I/O operations, but not necessarily in sorted order. This method is chosen when:
   

   • All required columns are contained in the index.
     
   • At least one indexed column has a NOT NULL constraint.
     
   • Sorted output is not required.
     
   • The goal is maximum throughput rather than ordered access.
     

   This scan type can utilize parallel processing and is often faster than INDEX FULL SCAN when sorted results are not needed. The NOT NULL constraint requirement ensures that no qualifying rows are missed, since NULL values might not be stored in the index.
   

   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;
   

   Execution plan:

   ----------------------------------------------------------------------------------------------------------------
   | 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. INDEX SKIP SCAN
   An INDEX SKIP SCAN allows Oracle to use a composite index even when the leading column is not specified in the WHERE clause. This method:
   

   • Logically splits the index into smaller sub-indexes based on distinct values of the leading column.
     
   • Performs separate range scans on each sub-index.
     
   • Is most effective when the leading column has low cardinality (few distinct values).
     
   • Becomes less efficient as the number of distinct leading column values increases.
     

   Skip scans are particularly useful for queries on composite indexes where only non-leading columns are specified in predicates. The optimizer considers this method when it estimates fewer distinct leading column values than would make a full table scan more efficient.
   

   SELECT * FROM table_access_methods WHERE id2 = 100;
   

   This query would use INDEX SKIP SCAN on ix_table_access_methods_type1 (type1, id2) because id2 is the second column in the index but type1 is not specified in the WHERE clause.