设为首页收藏本站
查看: 88|回复: 0

[PHP] oraclehints的那点事

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2018-7-11 00:16:00 | 显示全部楼层 |阅读模式
    引言:hints翻译成中文就是提示,暗示的意思,它在数据库中作用就是更改SQL语句的执行方式,你可以使用hints强制sql按照你所设置的方式执行sql,一般用来做性能

    引言:hints翻译成中文就是提示,暗示的意思,它在数据库中作用就是更改SQL语句的执行方式,你可以使用hints强制sql按照你所设置的方式执行sql,一般用来做性能诊断和调优,不建议在开发中使用。


    1.写一条SQL,使它通过全表扫描方式的效率优于索引访问,分别给出各自的执行计划。


    LEO1@LEO1> create table leo1 as select * from dba_objects; 创建leo1表
    Table created.
    LEO1@LEO1> create index idx_leo1 on leo1(object_id); 在这个object_id列上创建索引
    Index created.
    LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true); 分析表和索引
    PL/SQL procedure successfully completed.
    LEO1@LEO1> select count(*) from leo1; 表上有71958行记录
      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 5762次一致性读
      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 |
    ----------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
      2 - access("OBJECT_ID">100)
    Statistics
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      10735 consistent gets 10735次一致性读
      0 physical reads
      0 redo size
      8241805 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
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表