A database can scan only using the index to perform database operation without acessingTable in Oracle index fast full scan. This is scanning without any order. Indexes are performed when retrieving a small number of rows from a table. oracle index fast full scan reads the entire index because query attributes can be accessed from the index. Instead, use of the table. In this scan read all the indexes using multiblock IO.
Regardless of those index structures oracle needs ROWIDs to fetch data from a table. There are different types of Index scanning methods according to the query filtration. Here let’s discuss the Oracle index fast full scan but there are different index lookup methods
Index Lookups
- Unique Scan
- Range Scan (Index Range Scan Oracle)
- Full Scan
- Fast Full Scan
- Skip Scan (Oracle Skip Scan)
What is an Oracle Index Fast Full Scan?
db_file_multiblock_read_count is the degree of parallelism on a given query. Therefore those results are not in sorted order. If you want to sort it database executes the sorting option as separate processes. Once this parameter enables the database not to think about order therefore creates multiple processors and collects or scans the index in the multi-block method which is the concept of fast full scan in Oracle.
If Oracle could execute the query without reading the table it would be a performance benefit. But there are some conditions to use fast full scan in Oracle. In this case, enable db_file_multiblock_read_count to read records.
- All of the columns in the query should be available in the index. This includes select statements and where Clouse
- Query return should be over 10 percent of the rows within the index. Figure out the 10 percent using multi-block reads.
Index Fast Full Scan hint oracle
The cost base optimizer will decide to invoke index scan or table scan according to statistics. If you want to take values from the index we can force Oracle to use the index by adding the hint index_ffs. There are a lot of index varieties like B-tree, Domain, Bitmap, Function-based index, and so on. See our Create Index Guide
Oracle Index Fast Full Scan Example
In this example, I try to use a fast full scan when fetching data from the database. For that, I use the citizen table to find many citizens according to the city. I’ll add 1 million of data into the table using the below script.
--
CREATE TABLE citizen_table(
citizen_id VARCHAR2(50) ,
citizen_nic VARCHAR2(50) ,
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_city, citizen_name)
VALUES ('CT-'||i,1000000+i, names(round(dbms_random.value(1,10),0)), 'CT-name'||i);
EXIT WHEN i > 999999; END LOOP;
end;
--Before executing the query, I need to add an index. There are a lot of types of indexes but in this case, I can create a BITMAP or B-tree index because in this case, cardinality is low.
-- -- Create index CREATE INDEX citizen_rel_ix1 ON citizen_table (citizen_city); --Execute the query before and after creat index select citizen_city,count(*) from citizen_table group by citizen_city --
Before executing the query let’s run the sample query first and then I‘ll add the index to the table and run the same query again hoping the optimizer uses my index to fetch the data.
This is not the fastest way to execute the query because here all the variables involved to fetch the data therefore you have to check the response time for the queries.
Sometimes Creating a function base index can help to improve the performance in the fast full-index scan. This function-based index improves the performance in a query and helps resolve using an existing index. As we said earlier you can force by adding index_ffs hint to say that we can use index for fetch data. Those are some options that can improve the performance of queries using a fast full index scan.
Conclusion
This is one of the index scan methods in Oracle. db_file_multiblock_read_count is the main concept of a fast full index scan. Therefore those results are not in sorted order. Once this parameter enables the database not to think about order, therefore, creates multiple processors and collect or scans the index in a multi-block method. This is the heart of this scan and data read each block in multi-block reads in a parallel process that is the fast full scan in Oracle
To execute the scan all the fields in the query should be available in the query including select and where clouses. That will enable the above db_file_multiblock_read_count parameter to execute the query. Also, you can add the index_fss hint as well to force them to use it.
