• oracle查询使用索引和不使用索引的比较


    前言:关于查询走不走索引里面有很多的东西,也分为很多种情况。这里只是简单的一个比较,后续会进一步分析。

    1、创建表:SQL> create table test1 (id number,c1 varchar2(50),c2 varchar2(50),c3 varchar2(5
    0),c4 varchar2(50));

    2、模拟插入10000条记录:

    SQL> begin
             for i in 1..10000 loop
                 insert into test1 values(i,'阿斯顿飞就快了','及卡拉加水电费了健康','就爱看lsd减肥辣椒水代理费','阿斯顿飞卡死就到了费劲啊lsd就分开了');
             end loop;
         end;
         /

    3、打开执行计划,并查询:

      SQL> set autotrace on;

      SQL> select * from scott.test1 where id=500;

    执行计划
    ----------------------------------------------------------
    Plan hash value: 4122059633

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

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

       1 - filter("ID"=500)

    Note
    -----
       - dynamic sampling used for this statement


    统计信息
    ----------------------------------------------------------
            171  recursive calls
              0  db block gets
            283  consistent gets
              0  physical reads
              0  redo size
            708  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
              1  rows processed
    4、创建在id上的索引:

    SQL> create index i_test1 on test1(id);

    5、再次查询,并查看执行计划:

    SQL> select * from scott.test1 where id=500;

    执行计划
    ----------------------------------------------------------
    Plan hash value: 550433309

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

    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
    e     |

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

    |   0 | SELECT STATEMENT            |         |     1 |   121 |     2   (0)| 00:
    00:01 |

    |   1 |  TABLE ACCESS BY INDEX ROWID| TEST1   |     1 |   121 |     2   (0)| 00:
    00:01 |

    |*  2 |   INDEX RANGE SCAN          | I_TEST1 |     1 |       |     1   (0)| 00:
    00:01 |

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


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

       2 - access("ID"=500)

    Note
    -----
       - dynamic sampling used for this statement


    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            712  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

    6、可以看到,创建索引后效率有很大提高。

  • 相关阅读:
    第二阶段个人总结7
    第二阶段个人总结6
    第十七周进度报告
    个人总结
    十六周进度报告
    人月神话阅读笔记3
    十五周进度报告
    十四周进度报告
    人月神话阅读笔记2
    购买图书
  • 原文地址:https://www.cnblogs.com/cont/p/2196694.html
Copyright © 2020-2023  润新知