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>

Analyze versus dbms_stats

Many years ago, if you wanted to collect optimizer statistics for a table, you had to use the analyze command. Then Oracle brought out dbms_stats and said you should use that instead as it produced better statistics. I decided to test this out on an Oracle 10.2.0.3.0 database. I did this by analyzing a table then running a query on it. Then I ran dbms_stats on the table, ran the query again and compared the results. First I set sql_trace to true to give me a trace file to run through tkprof later:

SQL> alter session set sql_trace = true

  2  /

Session altered.

SQL>

Then I created a table, added lots of data to it and counted the rows:

SQL> create table andrews_table

  2  storage (maxextents unlimited)

  3  as select * from dba_tables

  4  /

Table created.

SQL> begin

  2  for a in 1..9 loop

  3  insert into andrews_table

  4  select * from andrews_table;

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from andrews_table

  2  /

  COUNT(*)

----------

   1074688

SQL>

Then I counted the number of rows with SYSTEM as the owner. The percentage was quite small so using an index to access them should be better than doing a full table scan:

SQL> select owner, count(*)

  2  from andrews_table

  3  where owner = 'SYSTEM'

  4  group by owner

  5  /

OWNER                            COUNT(*)

------------------------------ ----------

SYSTEM                              75264

SQL>

Next I created an index, analyzed the table and checked that both the table and the index had been analyzed:

SQL> create index andrews_index

  2  on andrews_table(owner)

  3  /

Index created.

SQL> analyze table andrews_table

  2  compute statistics

  3  /

Table analyzed.

SQL> select last_analyzed from user_tables

  2  where table_name = 'ANDREWS_TABLE'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select last_analyzed from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL>

Then I queried the rows with SYSTEM as the owner. Sometimes when I run tests like this, I give Oracle a perfectly good index to use but it does a full table scan instead and I never know why so I added a hint to make sure that Oracle used the index:

SQL> select /*+ index(andrews_table andrews_index) */

  2  count(*) after_analyze from andrews_table

  3  where owner = 'SYSTEM'

  4  and pct_used = 40

  5  /

AFTER_ANALYZE

-------------

        50176

SQL> alter session set sql_trace = false

  2  /

Session altered.

SQL>

The next step was to repeat the test using dbms_stats instead:

SQL> alter session set sql_trace = true

  2  /

Session altered.

SQL> create table andrews_table

  2  storage (maxextents unlimited)

  3  as select * from dba_tables

  4  /

Table created.

SQL> begin

  2  for a in 1..9 loop

  3  insert into andrews_table

  4  select * from andrews_table;

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from andrews_table

  2  /

  COUNT(*)

----------

   1074688

SQL> select owner, count(*)

  2  from andrews_table

  3  where owner = 'SYSTEM'

  4  group by owner

  5  /

OWNER                            COUNT(*)

------------------------------ ----------

SYSTEM                              75264

SQL> create index andrews_index

  2  on andrews_table(owner)

  3  /

Index created.

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', -

>  tabname=>'andrews_table', -

>  cascade=>true);

PL/SQL procedure successfully completed.

SQL> select last_analyzed from user_tables

  2  where table_name = 'ANDREWS_TABLE'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select last_analyzed from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select /*+ index(andrews_table andrews_index) */

  2  count(*) after_dbms_stats from andrews_table

  3  where owner = 'SYSTEM'

  4  and pct_used = 40

  5  /

AFTER_DBMS_STATS

----------------

           50176

SQL> alter session set sql_trace = false

  2  /

Session altered.

SQL>

Finally I ran the trace file through tkprof and compared the results. CPU time, elapsed time, disk reads and buffer gets were all quite a bit less when dbms_stats was used to collect statistics:

********************************************************************************

select /*+ index(andrews_table andrews_index) */

count(*) after_analyze from andrews_table

where owner = 'SYSTEM'

and pct_used = 40

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      2.54      31.74      12696      13209          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      2.55      31.75      12696      13209          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 32  (ORACLE)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=13209 pr=12696 pw=0 time=31745246 us)

  50176   TABLE ACCESS BY INDEX ROWID ANDREWS_TABLE (cr=13209 pr=12696 pw=0 time=43170772 us)

  75264    INDEX RANGE SCAN ANDREWS_INDEX (cr=192 pr=191 pw=0 time=633026 us)(object id 120507)

Rows     Execution Plan

-------  ---------------------------------------------------

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   SORT (AGGREGATE)

  50176    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

               'ANDREWS_TABLE' (TABLE)

  75264     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'ANDREWS_INDEX'

                (INDEX)

********************************************************************************

select /*+ index(andrews_table andrews_index) */

count(*) after_dbms_stats from andrews_table

where owner = 'SYSTEM'

and pct_used = 40

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      2.12      24.39      11777      12711          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      2.12      24.39      11777      12711          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 32  (ORACLE)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=12711 pr=11777 pw=0 time=24395897 us)

  50176   TABLE ACCESS BY INDEX ROWID ANDREWS_TABLE (cr=12711 pr=11777 pw=0 time=26819905 us)

  75264    INDEX RANGE SCAN ANDREWS_INDEX (cr=192 pr=134 pw=0 time=378268 us)(object id 120510)

Rows     Execution Plan

-------  ---------------------------------------------------

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   SORT (AGGREGATE)

  50176    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

               'ANDREWS_TABLE' (TABLE)

  75264     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'ANDREWS_INDEX'

                (INDEX)

********************************************************************************