What is Full Table Scan in Oracle? Simply it reads all the rows in the table. If you have a Where condition, then filter records according to that. Normally oracle chooses a full table scan when there is no alternate access path. Also, if statistics show a full scan faster than other access paths, then execute the full table to fetch the required rows from a table.
There are different types of table scans in Oracle. Index scans are one of them there are different types of index scans doing faster table scans compared to full table scans. But it depends on the number of records in the table. Therefore, oracle uses statistics before executing a query. There are index scan types like below.
- Unique Scan ( -> Click)
- Range Scan (Index Range Scan)
- Full Scan (-> Click)
- Fast Full Scan (Index Fast Full Scan)
- Skip Scan (Index Skip Scan)
What is Full Table Scan
Suppose Table Access Full is available in the Execution plan usually because there are no other efficient paths. Sometimes queries need to read all the data in the table, and then the full table scan decision is correct. But most of the cases are not like that.
If the query is looking for a small subset of data in a huge table, full table scan is not definitely efficient. Then you must fix it by creating an index for the table. You must avoid the full table scan to fix it otherwise, the query may do badly. If you would like there are 10 TIPS to improve the PERFORMANCE.
Sometimes there are indexes on the table but still have a FULL TABLE SCAN. This is because of poor join order in tables. So, the optimizer is unable to choose a good access path with indexes. Check how to help index on order by clauses.
What is Full Table Scan in Oracle and how it is happening?
When a query needs a subset of data from a table then it can be Using index scan or by FULL TABLE SCAN. oracle can fetch values from memory or from Disk. If data is not available in memory, then must fetch them from the Disk.
Compared to the total data count if the subset count is high then Oracle uses a full table scan or if there is a small amount of data in the table oracle uses a full table scan. But it changes according to the statistics and a lot of other parameters.
IMPORTANT: If Oracle needs to fetch data using a full table scan oracle does not scan block by block. It uses batch reading and fetches thousands of data in a single IO. But when it is done with INDEX SCAN oracle has to read block by block and get the ROWID. Then there are a lot of IO’s to get final data.
It’s faster than an INDEX scan when there are lot of records that need to fetch from a table. Index scan does a lot of IO’s it is not good for fetching more data in a table. This full table scan does sequential reads. In other words, many blocks simultaneously from the disk. Sequential read is the fastest IO since it takes many blocks at once by cutting a lot of IO just like index scan and random IO.
This image shows how to read blocks and What is Full Table Scan in Oracle. Sequential read fetches many blocks at once and the DB_FILE_MULTIBLOCK_READ_COUNT parameter specifies the number of blocks to read at a single IO. A database can speed up Multiblock Read. Same as we discussed in FAST FULL SCAN in Oracle.
Why Full Table Scan
Now you know What is Full Table Scan in Oracle? but do you know why Explain Plan shows a full table scan? here are a few of them but here are the most of cases refer to the full table scan.
- There is NO Index.
- The table is too SMALL.
- The index column contains NULL.
- Queries must read MOST of the blocks in a table.
- Optimizer statistics STALE
- Full table scan HINT.
- High Parallelism.
Oracle Full Table Scan Example
it is very simple to take a Full table scan. I used the same example that I used for the Index scan. End of the article, available Sample data script for Citizen Table. This table is available to all the people in a Country with a city name. I want to find citizens living in ‘City_5’. So I execute the below query without creating an index.
-- select citizen_id, citizen_name, Citizen_nic from citizen_table where citizen_city=’City_5’; --
I could get the below Explain Plan easily. This is because Oracle doing a full table scan on the citizen table. According to the Explain Plan, there is nothing related to ROWID because this plan does sequential reading. Not fetch records from an index with ROWIDs.
How to make Full Table Scan Faster in Oracle
Keep in mind the full table scan is less expensive than an Index scan. If you start reading from Start, you know Full Table Scan sequentially reads multiple blocks. So, there are some parameters below that can change performance, but you have to think about the balance of everything. You must use index or sometimes table access full. However poor use of databases can reduce the performance like hints.
- DB_FILE_MULTIBLOCK_READ_COUNT
- Parallelism
SELECT /*+ PARALLEL */ * FROM Table1; - Hardware like RAM
- Database allocated memory size.
- Etc.
How to avoid Full Table Scan in Oracle
It is always Indexes are not good, but full table scans not always bad. Therefore, we need to believe in Oracle cost-based optimizer and Oracle statistics. Sometimes oracle has old statistics. In such a case you can update with the latest. We hope Oracle is doing its best.
If you want to force Oracle to use indexes, then you can add a hint in your queries. Also, you can update statistics on a database. Because if your table has a lot of data recently and statistics don’t know about those then there can be a poor performance in the database. See our performance improvement tips.
Hints to avoid Full Table Scan in Oracle
Below are some of the Hints you can avoid full table scan. But that is not guaranteed to do an index scan. But you have the option to give some suggestions. Oracle decides whether to use it or not.
INDEX(alias index_name)- index_ss (Skip Scan)
INDEX_FFS(table_name index_name)– Index fast full scan- INDEX_JOIN(table_name)
INDEX_DESC(tbl_aliasix_name)
Full Table Scan hint in Oracle
Below you can see a hint for full table scans. As we explained earlier it is not sure full table scan is going to be used.
-- SELECT /*+ FULL(e) */ employee_id, first_name FROM employees e WHERE last_name = 'Jonh'; --
Sample Data Script
--
CREATE TABLE citizen_table(
citizen_id VARCHAR2(50) ,
citizen_nic VARCHAR2(50) ,
citizen_tax NUMBER ,
citizen_city VARCHAR2(50),
citizen_name VARCHAR2(100) ,
PRIMARY KEY(citizen_id)
);
declare
-- insert 1000000 records into database
i integer:=0;
type namesarray IS VARRAY(10) OF VARCHAR2(50);
names namesarray;
begin
names := namesarray('City_1', 'City_2', 'City_3', ' City_4', 'City_5','City_6',' City_7','City_8','City_9','City_10');
LOOP
i:= i+1;
INSERT INTO citizen_table (citizen_id, citizen_nic, citizen_tax,citizen_city, citizen_name)
VALUES ('CT-'||i,1000000+i, round(dbms_random.value(100,100000),0), names(round(dbms_random.value(1,10),0)),'CT-name'||i);
EXIT WHEN i > 99999; END LOOP;
end;
--
Conclusion
Simply What is Full Table Scan in Oracle is, that it reads all the rows in the table. According to the Where condition, filter records in the table. Normally oracle chooses a full table scan when there is no alternate access path. Also, if statistics show a full scan faster than other access paths then also execute the full table to fetch the required rows from a table.
Ref to What is Full Table Scan in Oracle: Read more from Oracle Doc
