Full table scan in a database can be defined as the process of scanning all the rows present in the table. Full table scan in database is very slow when compared to the normal database scan. Using indexes in database can replace the use of full table scan. The following scenarios will force the full table scan in the database.
- Read: More Interview Questions
Lack of Updates in Statistics
Statistics in database are normally kept in tables and indexes. If these statistics are not updated regularly then it may result in full table scan. Full table scan are forced because the RDBMS has query optimizer which uses statistics to estimate the worth of indexes present in the database. So it is very obvious that query optimizer will opt for full table scan if statistics are not updated regularly. The absence of WHERE clause in a query will also result in full table scan.
Full table scan with Index
Even presence of indexes in a database will force full table scan at times. The presence of WHERE clause in a query and absence of matching column index in a database will force full table scan. The following scenarios will force full table scan even with the presence of WHERE clause with matching index column.
- The use of NOT EQUAL <> operator along with the WHERE clause will result in full table scan. For example: WHERE NAME <> “Trinity” will result in full table scan. THe reason behind this scan is that indexes can only find what is inside a table. To find what is not inside a table cannot be done using indexes.
- The use of NOT OPERATOR along with WHERE clause will result in full table scan.
- The WILDCARD OPERATOR used in the first position of comparison string will also result in full table scan.
How to Prevent Full Table Scan?
A professional SQL tuner will avoid the unnecessary full table scan. The unnecessary full table scan can be prevented by avoiding system wide dictionary issues and individual SQL statements.
The following are the dictionary issues which influence the SQL optimizers to initiate full table scan:
- Tables with high water mark
- Absence of Column Histograms on Skewed Indexes
The following optimizer modes will favor full table scan
- Setting Parallel Query
- SQL Syntax and Full Table Scans
- Queries with NULL Conditions
- Queries Against Unindexed Columns
- Queries with Like Conditions
- Queries with Not Equal Conditions
- Invalidating an Index with BIF
- Using All Rows hint
- Using Parallel hint
By avoiding the above mentioned dictionary issues and optimizer modes, full table scan in a database can be avoided.
Why large full table scans are considered as evil?
Large full table scans will force unnecessary I/O operations which may become a burden to the operation of database. Still database offer a lot of tuning operations to prevent or reduce the unnecessary full table scans. The inefficiency of full table scan can be determined using following technique.
Comparing the number of data block touches along with rows returned by the SQL. When the number of rows returned by the SQL is smaller than the number of rows present might give a view about the efficiency of a query. If the number of rows fetched by a full table scan is less than 20% of the total rows present in the table then a further investigation is required to check the legitimacy of the full table scan.
Full table scans can be tracked using AWR
The SQL access method in a database can be tracked and counted. The tracking of SQL access method is extremely important in LFTS because of missing indexes. If LFTS is found legitimate then execution of DBA in selective parallel query can be implemented. The implementation of selective parallel query depends upon the CPU consumption on the server. Full table scans may increase the CPU consumption so it is better to track it regularly in order to improve the execution of the database.