Thursday, February 26, 2015

FULL and NO_INDEX Hints

I was reading about hints and decided to try out a couple on an Oracle 11.2 database. First I created a table, added some data and created an index:

SQL> create table t1 (c1 varchar2(30))
  2  /
 
Table created.
 
SQL> insert into t1 select table_name from dba_tables
  2  /
 
3159 rows created.
 
SQL> create index i1 on t1(c1)
  2  /
 
Index created.
 
SQL>

I ran a SELECT statement on the table. I thought it would use the index and it did:

SQL> set autotrace on explain
SQL> select count(*)
  2  from t1
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2349582935
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    17 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    17 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |    17 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

I then added a full hint to force the optimizer to choose a full table scan and this worked too:
 
SQL> select /*+ full(t1) */ count(*)
  2  from t1
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3693069535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    17 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

I added an alias after the table name. The hint stopped working and the query used the index again:

SQL> select /*+ full(t1) */ count(*)
  2  from t1 my_alias
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2349582935
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    17 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    17 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |    17 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

That was because, if you use an alias, the hint must specify the alias, not the table name. I changed the hint to do this and the optimizer chose a full table scan again:

SQL> select /*+ full(my_alias) */ count(*)
  2  from t1 my_alias
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3693069535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    17 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

It occurred to me that I might also be able to force a full table scan by telling Oracle not to use a particular index. I did this with a no_index hint and it worked as expected:

SQL> select /*+ no_index(t1 i1) */ count(*)
  2  from t1
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3693069535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    17 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

No comments: