When Full Table Scans Are Better Than Indexes in Oracle

It’s a common assumption that using indexes will always result in faster query performance. However, in certain cases, a full table scan might outperform indexed access, especially when querying large datasets where a significant portion of the table is being accessed. Oracle’s optimizer may automatically determine when a full table scan is beneficial, but sometimes we need to guide the optimizer by manually applying hints.

Why Use a Full Table Scan?

Full table scans can be more efficient than using indexes when:

• A large portion of the table’s rows are being retrieved.
• The overhead of random I/O for index lookups is too high.
• Sequential reading of blocks (full table scans) is faster than jumping between index entries.

Using SQL Patches to Force Full Table Scans

In Oracle, we can use SQL patches to influence the execution plan of a specific query. Here’s an example of how to apply a FULL table hint on a query by creating a SQL patch.

Step 1: Retrieve SQL Text Using SQL ID

To create a SQL patch, you first need to retrieve the SQL text for the query you’re interested in. Use the SQL ID of the query to extract the full SQL text:

DECLARE
   l_patch_name VARCHAR2(32767);
   v_sql_text VARCHAR2(32767);
BEGIN
   -- SQL ID for the query
   SELECT sql_fulltext INTO v_sql_text
   FROM gv$sql
   WHERE sql_id = '3fq3b7k6cf7rs';
END;
/
        

Step 2: Create the SQL Patch

Next, you can create the SQL patch with the FULL table scan hint. In this example, the patch is named SCH_T5750_2, and we are applying a hint to force a full scan on the desired table:

DECLARE
   l_patch_name VARCHAR2(32767);
   v_sql_text VARCHAR2(32767);
BEGIN
   -- SQL ID
   SELECT sql_fulltext INTO v_sql_text FROM gv$sql WHERE sql_id = '3fq3b7k6cf7rs';
 
   l_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch
                 (sql_text => v_sql_text,
                  hint_text => 'FULL(@"SEL$3" "**TABLE**"@"SEL$3")',
                  name => 'SCH_T5750_2');
END;
/
        

Step 3: Dropping a SQL Patch

If you ever need to remove the SQL patch, you can drop it using the following command:

BEGIN
   dbms_sqldiag.drop_sql_patch(name => 'Patch_1');
END;
/
        

Although indexes are a powerful tool in SQL performance optimization, there are situations where full table scans can offer better performance. Using Oracle’s SQL patches and applying hints like FULL, you can take control of the execution plan and force the database to use a full table scan when necessary.