-- Let's create a simple test table with 2 columns: a number and a date 
-- PCTFREE is set to 99 so that the table has some reasonable size 

CREATE TABLE t (n, d) PCTFREE 99 PCTUSED 1
  AS SELECT level, sysdate-level FROM dual CONNECT BY level <= 10000
/

Table created.

-- Now, let's create an index on DATE column 

CREATE INDEX t_d_idx ON t (d)
/

Index created.

-- ... And simulate some BAD statistics ...
-- We are artificially "making" table small and index large with the hope
-- that ORACLE will chose a full table scan for our test query exec 

exec dbms_stats.set_index_stats(user, 't_d_idx', numlblks => 1000000);

PL/SQL procedure successfully completed.

exec dbms_stats.set_table_stats(user, 't', numrows => 1, numblks => 1);

PL/SQL procedure successfully completed.

-- And it does! Notice a FULL TABLE SCAN below. Obviously inefficient!

set autotrace on
SELECT /* EASY_FINDER */ * FROM t WHERE d >= sysdate;

no rows selected


Execution Plan
----------------------------------------------------------                                                                                                                                                                  
Plan hash value: 1601196873                                                                                                                                                                                                 
                                                                                                                                                                                                                            
--------------------------------------------------------------------------                                                                                                                                                  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                  
--------------------------------------------------------------------------                                                                                                                                                  
|   0 | SELECT STATEMENT  |      |     1 |    22 |     2   (0)| 00:00:01 |                                                                                                                                                  
|*  1 |  TABLE ACCESS FULL| T    |     1 |    22 |     2   (0)| 00:00:01 |                                                                                                                                                  
--------------------------------------------------------------------------                                                                                                                                                  
                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                         
                                                                                                                                                                                                                            
   1 - filter("D">=SYSDATE@!)                                                                                                                                                                                               


Statistics
----------------------------------------------------------                                                                                                                                                                  
          5  recursive calls                                                                                                                                                                                                
          0  db block gets                                                                                                                                                                                                  
       2507  consistent gets                                                                                                                                                                                                
       2500  physical reads                                                                                                                                                                                                 
      70928  redo size                                                                                                                                                                                                      
        336  bytes sent via SQL*Net to client                                                                                                                                                                               
        408  bytes received via SQL*Net from client                                                                                                                                                                         
          1  SQL*Net roundtrips to/from client                                                                                                                                                                              
          0  sorts (memory)                                                                                                                                                                                                 
          0  sorts (disk)                                                                                                                                                                                                   
          0  rows processed                                                                                                                                                                                                 

-- Ok, let's fix our SQL on the fly

-- First, let's find out query sql_id 

SELECT sql_id, child_number 
FROM v$sql 
WHERE sql_text='SELECT /* EASY_FINDER */ * FROM t WHERE d >= sysdate'
/

SQL_ID        CHILD_NUMBER                                                                                                                                                                                                  
------------- ------------                                                                                                                                                                                                  
c2xx50t7614hu            0                                                                                                                                                                                                  

1 row selected.


-- Define sqlplus vars ...

define SQL_ID='c2xx50t7614hu'
define CHILD_NO=0
set verify off


-- Step 1: Find out query block name to add our hint to


colu operation format A20
colu options format A25
colu object_name format A20
colu object_alias format A20

SELECT operation,options,object_name,object_alias
FROM v$sql_plan
WHERE sql_id='&SQL_ID'
  AND child_number=&CHILD_NO
/

OPERATION            OPTIONS                   OBJECT_NAME          OBJECT_ALIAS                                                                                                                                            
-------------------- ------------------------- -------------------- --------------------                                                                                                                                    
SELECT STATEMENT                                                                                                                                                                                                            
TABLE ACCESS         FULL                      T                    T@SEL$1                                                                                                                                                 

2 rows selected.

-- Our query block name is: @SEL$1

-- Step 2: Construct extended hint

Result: INDEX(@"SEL$1" T@"SEL$1" "T_D_IDX")

-- Step 3: Create SQL profile


DECLARE
  clsql_text CLOB;
BEGIN
  SELECT sql_fulltext INTO clsql_text FROM v$sqlarea where sql_id = '&SQL_ID';

  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    sql_text => clsql_text,
    profile => sqlprof_attr('INDEX(@"SEL$1" T@"SEL$1" "T_D_IDX")'),
    name => 'PROFILE_&SQL_ID',
    force_match => TRUE
  );
END;
/

PL/SQL procedure successfully completed.

-- And finelly, verify that it works

set autotrace on
SELECT /* EASY_FINDER */ * FROM t WHERE d >= sysdate;

no rows selected


Execution Plan
----------------------------------------------------------                                                                                                                                                                  
Plan hash value: 1962269968                                                                                                                                                                                                 
                                                                                                                                                                                                                            
---------------------------------------------------------------------------------------                                                                                                                                     
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                     
---------------------------------------------------------------------------------------                                                                                                                                     
|   0 | SELECT STATEMENT            |         |     1 |    22 |     3  (67)| 00:00:01 |                                                                                                                                     
|   1 |  TABLE ACCESS BY INDEX ROWID| T       |     1 |    22 |     3  (67)| 00:00:01 |                                                                                                                                     
|*  2 |   INDEX RANGE SCAN          | T_D_IDX |    90 |       |  9003   (1)| 00:01:49 |                                                                                                                                     
---------------------------------------------------------------------------------------                                                                                                                                     
                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                         
                                                                                                                                                                                                                            
   2 - access("D">=SYSDATE@!)                                                                                                                                                                                               
                                                                                                                                                                                                                            
Note                                                                                                                                                                                                                        
-----                                                                                                                                                                                                                       
   - SQL profile "PROFILE_c2xx50t7614hu" used for this statement                                                                                                                                                            


Statistics
----------------------------------------------------------                                                                                                                                                                  
          8  recursive calls                                                                                                                                                                                                
          0  db block gets                                                                                                                                                                                                  
         12  consistent gets                                                                                                                                                                                                
          3  physical reads                                                                                                                                                                                                 
        148  redo size                                                                                                                                                                                                      
        336  bytes sent via SQL*Net to client                                                                                                                                                                               
        408  bytes received via SQL*Net from client                                                                                                                                                                         
          1  SQL*Net roundtrips to/from client                                                                                                                                                                              
          0  sorts (memory)                                                                                                                                                                                                 
          0  sorts (disk)                                                                                                                                                                                                   
          0  rows processed                                                                                                                                                                                                 

-- And voila, INDEX is now being used !