Oracle Database Performance Tuning Tutorial -7 Optimizer Access Paths (Table and Index Scan)

Hi,

I will explain Optimizer Access Paths (Table and Index Scan) in this article.

 

Read the Following article before this, if you don’t know or want to know Oracle optimizer detailed.

 

Oracle Optimizer has lots of access paths in which data is retrieved from the database. These access paths are as follows.

  • Full Table Scan
  • Rowid Scan
  • Sample Table Scan
  • Index Scan (Unique)
  • Index Scan (Range)
  • Index Scan (Full)
  • Index Scan (Fast Full)
  • Index Scan (Skip)
  • Index Scan (Index Join)
  • Using Bitmap Indexes
  • Combining Bitmap Indexes

 

Oracle Optimizer prefer using Index access paths if Index exists instead of Full table scans for a small subset of table rows, but If optimizer will access a large portion of the table, then full scans are more efficent than Index scan.

In general, OLTP applications are often used index access paths because of short-running SQL statements with high selectivity, OLAP or DSS applications are often used partitioned tables and perform full scans of the relevant partitions. 

 

Full Table Scan

Optimizer reads all rows from a table sequentially in this case. Full table scan is faster than index range scan during retrieving large amount of data. Optimizer can perform multiblock reads same time according to the DB_FILE_MULTIBLOCK_READ_COUNT init parameter during Full Table scan.

 

 

Full table scan is done during the following cases.

  • Small table
  • No suitable index
  • Low selectivity filters (or no filters)
  • Full table scan hint
  • High degree of parallelism

 

Rowid Scan

Rowid scan is the fastest Access paths to retrieve a single row, because the exact location of the row is specified and optimizer does not perform any scan.

 

 

Sample Table Scans
This access path is used when a statement’s FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause, it is used to retrieve a random sample of data from table or view.

 

 

If you don’t know what is the index and and its Advantages, Disadvantages, read the following article before reading Index Scan.

 

 

Index Unique Scan


An index unique scan performs a unique scan to return a single ROWID if SQL statement contains a UNIQUE or a PRIMARY KEY as follows.

 

 

For example; There is an Index unique scan in the following SQL statement, Optimizer found only 1 row in that step and access it via its ROWID using Index unique scan.

 

 

Index Range Scan

Optimizer will use a range scan for accessing selective data ( instead of unique row ) via using Index as follows.

 

 

 

 

Index Full Scan

Index full scan reads the entire index in order and is performed when one of the index columns is not null and All the columns in the table referenced in the query are included in the index as follows.

An index full scan is performed with a query with an ORDER BY clause as follows.

 

 

 

 

 

 

Index Fast Full Scan

Index fast full scans are used when the index contains all columns in the query and at least one column in the index key has a NOT
NULL
constraint, it is an alternative to full table scans.

You can use fast full index scans by setting  the OPTIMIZER_FEATURES_ENABLE initialization parameter or using the INDEX_FFS hint.

 

 

Index Skip Scan

Index skip scan is performed initial (leading) column of the composite index is not specified in a query as follows.

 

 

Index Join Scan

Index Join Scan is a hash join of many indexes that together contain all the table columns that are used by the query.  If you want to use Index join scan, you should use  INDEX_JOIN hint in the query.

I will continue to explain Performance tuning tutorial in the next articles.

 

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.