索引的两个知识点

最新推荐文章于 2022-05-31 11:14:16 发布

wangwenhui11

最新推荐文章于 2022-05-31 11:14:16 发布

阅读量916

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： sql oracle statistics table 优化 access

本文链接：https://blog.youkuaiyun.com/wangwenhui11/article/details/3940156

oracle 专栏收录该内容

9 篇文章

订阅专栏

5 附表（索引什么时候不工作）

首先要声明两个知识点：

（1）RBO&CBO。
Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO.

    （2）AUTOTRACE。
    要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE：
    ① 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。
    ② AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。
    ③ AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。

    SQL> set autotrace on
    SQL> select * from test;
    A
    ----------
    1
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 TABLE ACCESS (FULL) OF 'TEST'
    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    0 bytes sent via SQL*Net to client
    0 bytes received via SQL*Net from client
    0 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    rows processed

    SQL> set autotrace traceonly
    SQL> select * from test.test;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 TABLE ACCESS (FULL) OF 'TEST'

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

Hints是Oracle提供的一个辅助用法，按字面理解就是‘提示’的意思，确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径，特别适用于sql调整的时候。使用方法如下：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

具体可参考Oracle SQL Reference。有了前面这些知识点，接下来让我们来看一下什么时候索引是不起作用的。以下列出几种情况。

（1）类型不匹配时。

    SQL> create table test.testindex (a varchar(2),b number);
    表已创建。
    SQL> create index ind_cola on test.testindex(a);
    索引已创建。
    SQL> insert into test.testindex values('1',1);
    已创建 1 行。
    SQL> commit;
    提交完成。
    SQL> analyze table test.testindex compute statistics for all indexes;
    表已分析。
    SQL> set autotrace on;
    SQL> select /**//*+RULE */* FROM test.testindex where a='1';(使用基于rule的优化器，数据类型匹配的情况下)
    A B
    -- ----------
    1 1
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=HINT: RULE
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX'
    2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)（使用了索引ind_cola）
    ――――――――――――――――――――――――――――――――――
    SQL> select /**//*+RULE */* FROM test.testindex where a=1;（数据类型不匹配的情况）
    A B
    -- ----------
    1 1
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=HINT: RULE
    1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'（优化器选择了全表扫描）