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

最新推荐文章于 2020-07-28 08:15:00 发布

weixin_33862514

最新推荐文章于 2020-07-28 08:15:00 发布

阅读量124

点赞数

文章标签：操作系统数据库

一、实验说明：

操作系统：redhat 5.4 x86

数据库:oracle 11g R2

该实验通过建立一张规则表和一张不规则表，对这两张表分别使用全表扫描和索引访问，重点在于说明全表扫描的效率也可以优于索引访问。

二、实验具体步骤：

2.1、创建一张规则表jack_test和一张不规则表echo_test。

 1 SQL> create table jack_test(x int,y varchar2(2000));   --创建jack_test表      
 2 
 3 Table created.
 4 
 5 SQL> begin 
 6   2    for i in 1..100000
 7   3    loop
 8   4       insert into jack_test values(i,rpad(dbms_random.random,75,'*'));
 9   5    end loop;
10   6   commit;
11   7  end;
12   8  /
13 
14 PL/SQL procedure successfully completed.
15 
16 SQL> alter table jack_test add constraint jack_pks primary key(x);
17 
18 Table altered.
19 
20 SQL> create table echo_test nologging                --创建echo_test表，并随机插入数据 
21   2    as
22   3      select x,y from jack_test
23   4   order by y
24   5  /
25 
26 Table created.
27 SQL> alter table echo_test add constraint echo_pk primary key(x);
28 
29 Table altered.

2.2、比较对规则表用全表扫描与索引访问：

 1 SQL> set timing on;
 2 SQL> set linesize 200;
 3 SQL> set autotrace traceonly;
 4 SQL> select * from jack_test where x between 20000 and 40000;
 5 
 6 20001 rows selected.
 7 
 8 Elapsed: 00:00:00.23
 9 
10 Execution Plan
11 ----------------------------------------------------------
12 Plan hash value: 3796984134
13 
14 -------------------------------------------------------------------------------
15 | Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
16 -------------------------------------------------------------------------------
17 |   0 | SELECT STATEMENT  |          | 28379 |    27M|   342    (1)| 00:00:05 |
18 |*  1 |  TABLE ACCESS FULL| JACK_TEST | 28379 |    27M|   342    (1)| 00:00:05 |            --这里的Cost消耗比较大，还有Bytes也比较大    
19 -------------------------------------------------------------------------------
20 
21 Predicate Information (identified by operation id):
22 ---------------------------------------------------
23 
24    1 - filter("X">=20000 AND "X"<=40000)
25 
26 Note
27 -----
28    - dynamic sampling used for this statement (level=2)
29 
30 
31 Statistics
32 ----------------------------------------------------------
33       9  recursive calls
34       0  db block gets
35        2576  consistent gets
36       9  physical reads
37       0  redo size
38     1813724  bytes sent via SQL*Net to client
39       15082  bytes received via SQL*Net from client
40        1335  SQL*Net roundtrips to/from client
41       0  sorts (memory)
42       0  sorts (disk)
43       20001  rows processed
44 
45 SQL> analyze table jack_test compute statistics;
46 
47 Table analyzed.
48 
49 Elapsed: 00:00:01.47
50 SQL> select /*+ index(jack_test jack_pks) */ * from jack_test where x between 20000 and 40000;
51 
52 20001 rows selected.
53 
54 Elapsed: 00:00:00.20
55 
56 Execution Plan
57 ----------------------------------------------------------
58 Plan hash value: 674810340
59 
60 -----------------------------------------------------------------------------------------
61 | Id  | Operation            | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
62 -----------------------------------------------------------------------------------------
63 |   0 | SELECT STATEMENT        |        | 20002 |  1543K|   282   (0)| 00:00:04 |
64 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK_TEST | 20002 |  1543K|   282   (0)| 00:00:04 |
65 |*  2 |   INDEX RANGE SCAN        | JACK_PKS    | 20002 |    |    43   (0)| 00:00:01 |
66 -----------------------------------------------------------------------------------------
67 
68 Predicate Information (identified by operation id):
69 ---------------------------------------------------
70 
71    2 - access("X">=20000 AND "X"<=40000)
72 
73 
74 Statistics
75 ----------------------------------------------------------
76       1  recursive calls
77       0  db block gets
78        2899  consistent gets
79       0  physical reads
80       0  redo size
81     1893672  bytes sent via SQL*Net to client
82       15082  bytes received via SQL*Net from client
83        1335  SQL*Net roundtrips to/from client
84       0  sorts (memory)
85       0  sorts (disk)
86       20001  rows processed

小结：

对于规则表的查询索引访问效率要高于全表扫描。

2.3、比较不规则表用全表扫描与索引访问：

 1 SQL> select /*+ full(echo_test) */ * from echo_test where x between 20000 and 40000;
 2 
 3 20001 rows selected.
 4 
 5 Elapsed: 00:00:00.15
 6 
 7 Execution Plan
 8 ----------------------------------------------------------
 9 Plan hash value: 3930265464
10 
11 -------------------------------------------------------------------------------
12 | Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
13 -------------------------------------------------------------------------------
14 |   0 | SELECT STATEMENT  |          | 21652 |    20M|   333    (1)| 00:00:04 |
15 |*  1 |  TABLE ACCESS FULL| ECHO_TEST | 21652 |    20M|   333    (1)| 00:00:04 |
16 -------------------------------------------------------------------------------
17 
18 Predicate Information (identified by operation id):
19 ---------------------------------------------------
20 
21    1 - filter("X">=20000 AND "X"<=40000)
22 
23 Note
24 -----
25    - dynamic sampling used for this statement (level=2)
26 
27 
28 Statistics
29 ----------------------------------------------------------
30     246  recursive calls
31       0  db block gets
32        2630  consistent gets
33       9  physical reads
34       0  redo size
35     1813724  bytes sent via SQL*Net to client
36       15082  bytes received via SQL*Net from client
37        1335  SQL*Net roundtrips to/from client
38       6  sorts (memory)
39       0  sorts (disk)
40       20001  rows processed
41 
42 SQL> select /*+ index(echo_test echo_pk) */ * from echo_test where x between 20000 and 40000;
43 
44 20001 rows selected.
45 
46 Elapsed: 00:00:00.17
47 
48 Execution Plan
49 ----------------------------------------------------------
50 Plan hash value: 2911547479
51 
52 -----------------------------------------------------------------------------------------
53 | Id  | Operation            | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
54 -----------------------------------------------------------------------------------------
55 |   0 | SELECT STATEMENT        |        | 21652 |    20M| 19916   (1)| 00:03:59 |
56 |   1 |  TABLE ACCESS BY INDEX ROWID| ECHO_TEST | 21652 |    20M| 19916   (1)| 00:03:59 |          --这里Cost消耗比较大。
57 |*  2 |   INDEX RANGE SCAN        | ECHO_PK    | 21652 |    |    45   (0)| 00:00:01 |          
58 -----------------------------------------------------------------------------------------
59 
60 Predicate Information (identified by operation id):
61 ---------------------------------------------------
62 
63    2 - access("X">=20000 AND "X"<=40000)
64 
65 Note
66 -----
67    - dynamic sampling used for this statement (level=2)
68 
69 
70 Statistics
71 ----------------------------------------------------------
72       7  recursive calls
73       0  db block gets
74       21445  consistent gets                  -- 一致读的数量比较多
75      38  physical reads
76       0  redo size
77     1893672  bytes sent via SQL*Net to client
78       15082  bytes received via SQL*Net from client
79        1335  SQL*Net roundtrips to/from client
80       0  sorts (memory)
81       0  sorts (disk)
82       20001  rows processed