有时走索引没有全表扫描快

最新推荐文章于 2021-04-06 15:39:25 发布

rgb_rgb

最新推荐文章于 2021-04-06 15:39:25 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： sql语句文章标签： sql tuning

本文链接：https://blog.youkuaiyun.com/rgb_rgb/article/details/11868317

sql语句专栏收录该内容

14 篇文章

订阅专栏

本文通过创建表格、建立索引及收集统计信息等步骤，展示了如何在Oracle数据库中进行SQL查询优化，并通过对比不同查询计划来评估优化效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> create table dev.t1 as 
  2  select trunc(dbms_random.value(0,25)) n1,
  3         rpad('x',40) ind_pad,
  4         trunc(dbms_random.value(0,20)) n2,
  5         lpad(rownum,10,'0') small_vc,
  6         rpad('x',200)  padding
  7  from all_objects 
  8  where rownum <=10000; 

Table created.

Elapsed: 00:00:01.12

SQL> CREATE INDEX dev.t1_idx ON dev.t1(n1,ind_pad,n2);

Index created.

Elapsed: 00:00:00.21
SQL> execute  DBMS_STATS.gather_table_stats (ownname => 'DEV', tabname => 'T1', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => DBMS_STATS.AUTO_DEGREE, cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.41

SQL> set serverout on

SQL> exec show_space(p_segname_1=>'T1',p_space=>'AUTO',p_type_1=>'TABLE',p_analyzed=>'Y',p_owner_1=>'DEV');
Total Blocks............................512
Total Bytes.............................4194304
Unused Blocks...........................125
Unused Bytes............................1024000
Last Used Ext FileId....................4
Last Used Ext BlockId...................1920
Last Used Block.........................3
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................371
Total bytes.............................3039232

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 
SQL> exec show_space(p_segname_1=>'T1_IDX',p_space=>'AUTO',p_type_1=>'I',p_analyzed=>'Y',p_owner_1=>'DEV');
Total Blocks............................96
Total Bytes.............................786432
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................4
Last Used Ext BlockId...................2136
Last Used Block.........................3
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............1
25% -- 50% free space bytes.............8192
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................82
Total bytes.............................671744

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> set autotrace traceonly;
SQL> 

SQL> select * from dev.t1 where n1=0;

424 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   400 |   101K|   107   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |   400 |   101K|   107   (0)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("N1"=0)


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

SQL> select /* index(a t1_idx) */ * from dev.t1 a where n1=0;

424 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   400 |   101K|   107   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |   400 |   101K|   107   (0)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("N1"=0)


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

SQL> 
SQL> 
SQL> 

SQL> set linesize 177
SQL> select /*+ index(a t1_idx) */ * from dev.t1 a where a.n1=0;


424 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   400 |   101K|   395   (0)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   400 |   101K|   395   (0)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |   400 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("A"."N1"=0)


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

SQL> set autotrace off
SQL> select (371/16)*4.6   from dual;

(371/16)*4.6
------------
    106.6625

Elapsed: 00:00:00.22
SQL> select 1+ceil(82*1/25)+ceil(9750*1/25)  from dual;

1+CEIL(82*1/25)+CEIL(9750*1/25)
-------------------------------
                            395