Introduction to Oracle Hints

Hints translated into Chinese is the hint, implied meaning, it is in the database role is to change the execution of SQL statements, you can use hints to force SQL in the way you set up to execute SQL, generally used to do performance diagnostics and tuning, not recommended in development.

1. Write a SQL, so that it through the full table scanning mode of efficiency better than the index access, respectively, give their own implementation plan.

Leo1@leo1> CREATE TABLE Leo1 as SELECT * from Dba_objects; Create a Leo1 table

Table created.

Leo1@leo1> CREATE index Idx_leo1 on leo1 (object_id); Create an index on this object_id column

Index created.

Leo1@leo1> Execute dbms_stats.gather_table_stats (' LEO1 ', ' LEO1 ', cascade=>true); Analysis Tables and indexes

Pl/sql procedure successfully completed.

Leo1@leo1> Select COUNT (*) from LEO1; There are 71958 lines of records on the table

COUNT (*)

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

71958

Leo1@leo1> Select/*+ Full (LEO1)/* from Leo1 where object_id>100;

71859 rows selected.

Execution Plan

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

Plan Hash value:2716644435

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |      SELECT STATEMENT | |  71862 |   6807k| 287 (1) | 00:00:04 |

|* 1 | TABLE ACCESS full| LEO1 |  71862 |  6807k| 287 (1) | 00:00:04 |

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

predicate information (identified by Operation ID):

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

1-filter ("object_id" >100)

Statistics

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

1 Recursive calls

0 db Block gets

5762 consistent gets 5,762-time consistency reading

0 physical Reads

0 Redo Size

3715777 Bytes sent via sql*net to client

53214 bytes received via sql*net from client

4792 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

71859 rows processed

Leo1@leo1> Select/*+ Index (leo1 idx_leo1) * from Leo1 where object_id>100;

71859 rows selected.

Execution Plan

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

Plan Hash value:1434365503

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |          SELECT STATEMENT | |  71862 |  6807k| 1232 (1) | 00:00:15 |

|  1 | TABLE ACCESS by INDEX rowid| LEO1 |  71862 |  6807k| 1232 (1) | 00:00:15 |

|* 2 | INDEX RANGE SCAN | Idx_leo1 |       71862 |  | 160 (0) | 00:00:02 |

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