hint使用

在object_id上建有索引,使用第一个cost会很少

这里虽然有索引，但是oracle并不知道索引中是否有null值，所以无法走索引

指定not null后会去使用object_id这个索引

select count(*) from hwm where object_id is not null;

select count(*) from hwm

SQL> exec dbms_session.set_identifier('linyu');

PL/SQL procedure successfully completed.

SQL> exec dbms_monitor.client_id_trace_enable('linyu',true,false);

PL/SQL procedure successfully completed.

SQL>  exec dbms_monitor.client_id_trace_disable('linyu');

PL/SQL procedure successfully completed.

当使用别名时,加的hint也要用别名

set autotrace traceonly

select /*+FULL(e)*/ * from SCOTT.EMP e WHERE EMPNO=7934;

hint first_rows会去选择走索引

SQL>  select /*+ FIRST_ROWS(1) */ * FROM TBL_TRADE WHERE ACCOUNT_ID=100000000013
  2  ;

10251 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3996084345

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

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

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

|   0 | SELECT STATEMENT            |                   |     2 |   262 |     5
  (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TBL_TRADE         |     2 |   262 |     5
  (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_TRADE_ACCOUNT |       |       |     3
  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ACCOUNT_ID"=100000000013)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7374  consistent gets
          0  physical reads
          0  redo size
    1916383  bytes sent via SQL*Net to client
       7897  bytes received via SQL*Net from client
        685  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10251  rows processed

SQL> select /*+ FULL_ROWS */ * FROM TBL_TRADE WHERE ACCOUNT_ID=100000000013;

10251 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2259870010

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  4120 |   527K|  2870   (4)| 00:00:35 |
|*  1 |  TABLE ACCESS FULL| TBL_TRADE |  4120 |   527K|  2870   (4)| 00:00:35 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ACCOUNT_ID"=100000000013)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      13319  consistent gets
          0  physical reads
          0  redo size
    1063593  bytes sent via SQL*Net to client
       7897  bytes received via SQL*Net from client
        685  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10251  rows processed

SQL>

 

SQL> select /*+INDEX (TBL_TRADE IND_TRADE_ACCOUNT) */  * FROM TBL_TRADE WHERE ACCOUNT_ID=100000000013;

10251 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3996084345

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

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

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

|   0 | SELECT STATEMENT            |                   |  4120 |   527K|  3158
  (1)| 00:00:38 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TBL_TRADE         |  4120 |   527K|  3158
  (1)| 00:00:38 |

|*  2 |   INDEX RANGE SCAN          | IND_TRADE_ACCOUNT |  4120 |       |    22
  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ACCOUNT_ID"=100000000013)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7374  consistent gets
          0  physical reads
          0  redo size
    1916383  bytes sent via SQL*Net to client
       7897  bytes received via SQL*Net from client
        685  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10251  rows processed

SQL>

 

不使用指定index

 

SQL> select * from scott.emp where empno=7934;

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

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

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

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

|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:0
0:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:0
0:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7934)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        731  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

SQL> select /*+ no_index (emp PK_EMP) */ * from scott.emp where empno=7934;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=7934)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        823  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7301064/viewspace-442670/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7301064/viewspace-442670/