Understand access method in Execution plan like Full table scan, index scan, bitmap scan, unique scan, fast full index scan, cluster scan and partition
Oracle uses various access methods to retrieve data from tables and indexes efficiently. Each access method has its own characteristics and use cases. Understanding the differences between these access methods is crucial for optimizing query performance. Here are some of the main access methods in Oracle:
Full Table Scan:
Description: Oracle reads the entire table sequentially, scanning every row.
Use Cases: Typically used when a significant portion of the table needs to be accessed or when no suitable index is available. It’s efficient for retrieving a large portion of the data.
Considerations: It can be slower for small tables or when specific rows need to be fetched.
Index Scan (Index Range Scan):
Description: Oracle uses an index to locate specific rows that match the query’s criteria.
Use Cases: Effective for queries with selective criteria (e.g., WHERE clause) that can be satisfied by the index. Suitable for fetching a small subset of rows.
Considerations: May involve additional I/O operations to retrieve the actual data from the table (table access by rowid).
Unique Scan (Unique Index Scan):
Description: Similar to an index scan, but used for unique or primary key indexes where only one row is expected to match the criteria.
Use Cases: Ideal for primary key or unique key lookups. Ensures that only one row is retrieved.
Considerations: Very efficient for ensuring data integrity and fetching specific rows.
Bitmap Index Scan:
Description: Utilized with bitmap indexes where a bitmap is used to represent sets of rows that meet specific criteria.
Use Cases: Effective for low cardinality columns or when multiple criteria are combined using logical operators (AND, OR).
Considerations: Suitable for read-heavy workloads with infrequent updates.
Fast Full Index Scan:
Description: Reads the entire index structure without using the table. Useful when all required columns are present in the index itself.
Use Cases: Suitable when all the necessary data can be obtained from the index without accessing the table.
Considerations: Efficient for certain queries but may not be applicable in all scenarios.
Cluster Scan:
Description: Used with clustered tables, where related rows from multiple tables are stored together on disk.
Use Cases: Ideal for queries that retrieve data from multiple related tables in a clustered table configuration.
Considerations: Requires proper table design and clustering to be effective.
Hash Cluster Scan:
Description: Used with hash clusters, which group rows into buckets based on a hash value of one or more columns.
Use Cases: Suitable for hash-clustered tables when data distribution among buckets is relatively even.
Considerations: Requires proper design and maintenance of hash clusters.
Partition Pruning (Partitioned Tables):
Description: Accesses only relevant partitions based on query conditions.
Use Cases: Beneficial for partitioned tables when queries involve date ranges or other partitioning keys.
Considerations: Requires proper partitioning strategy and indexing.
The choice of access method depends on factors such as the query, data distribution, indexing, and table design. Optimizing queries often involves selecting the most appropriate access method and possibly creating or modifying indexes to improve performance. Oracle’s query optimizer plays a crucial role in selecting the best access method based on statistics and query complexity.