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:
Post a Comment