1.Oracle访问表的方式
全表扫描、通过ROWID访问表、索引扫描
2.全表扫描(Full Table Scans, FTS)
为实现全表扫描,Oracle顺序地访问表中每条记录,并检查每一条记录是否满足WHERE语句的限制条件。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描,而不是只读取一个数据块,这极大的减少了I/O总次数,提高了系统的吞吐量,所以利用多块读的方法可以十分高效地实现全表扫描。需要注意的是只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下,每个数据块只被读一次。
使用FTS的前提条件:在较大的表上不建议使用全表扫描,除非取出数据的比较多,超过总量的5% -- 10%,或你想使用并行查询功能时。
全表扫描实例(TABLE ACCESS FULL)
1 --创建表并插入数据,并进行查询。
2
3 --创建数据库
4 SQL> create table t_captain
5 2 (
6 3 NO int,
7 4 NAME VARCHAR2(32),
8 5 WORKDAY DATE
9 6 )
10 7 /
11
12 --创建序列
13 SQL> CREATE SEQUENCE SEQ_USERINFO_NO
14 2 INCREMENT BY 1 --每次加1
15 3 START WITH 1 --从1开始计数
16 4 /
17
18 Sequence created.
19
20 SQL>
21
22 --插入100000条数据
23 begin
24 for i in 1..100000 loop
25 INSERT INTO T_CAPTAIN VALUES(SEQ_USERINFO_NO.nextval,'captain',SYSDATE);
26 end loop;
27 end;
28 /
29
30
31 commit;
32
33 ----手动收集表的统计信息
34 SQL> exec dbms_stats.gather_table_stats('NC60','T_CAPTAIN');
35
36 PL/SQL procedure successfully completed.
37
38 SQL>
39
40
41 --查询NO=5000的结果
42 set autotrace traceonly --只看查询计划
43 select * from T_CAPTAIN where no = 5000;
44
45 SQL> select * from T_CAPTAIN where no = 5000;
46
47
48 Execution Plan
49 ----------------------------------------------------------
50 Plan hash value: 3680104071
51
52 -------------------------------------------------------------------------------
53 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
54 -------------------------------------------------------------------------------
55 | 0 | SELECT STATEMENT | | 1 | 21 | 103 (1)| 00:00:02 |
56 |* 1 | TABLE ACCESS FULL| T_CAPTAIN | 1 | 21 | 103 (1)| 00:00:02 |
57 -------------------------------------------------------------------------------
58
59 Predicate Information (identified by operation id):
60 ---------------------------------------------------
61
62 1 - filter("NO"=5000)
63
64
65 Statistics
66 ----------------------------------------------------------
67 1 recursive calls
68 0 db block gets
69 376 consistent gets
70 0 physical reads
71 0 redo size
72 551 bytes sent via SQL*Net to client
73 419 bytes received via SQL*Net from client
74 2 SQL*Net roundtrips to/from client
75 0 sorts (memory)
76 0 sorts (disk)
77 1 rows processed
78
79 SQL>
从查询计划我们可以看到所采用的查询方式是“TABLE ACCESS FULL”。也正是因为采用全表扫描,所以consistent gets会大些
3.通过ROWID访问表(table access by ROWID)
ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置,所以通过ROWID来存取数据可以快速定位到目标数据上,是Oracle存取单行数据的最快方法。为了通过ROWID存取表,Oracle 首先要获取被选择行的ROWID,或者从语句的WHERE子句中得到,或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。下面给出使用rowid访问表的实例。
3.1.单个rowid的情形
1 --查看表上rowid
2 SQL> select rowid,no,name from T_CAPTAIN where no < 10;
3
4 ROWID NO NAME
5 ------------------ ---------- --------------------------------
6 AAAWOMAAGAAA//ZAAA 1 captain
7 AAAWOMAAGAAA//ZAAB 2 captain
8 AAAWOMAAGAAA//ZAAC 3 captain
9 AAAWOMAAGAAA//ZAAD 4 captain
10 AAAWOMAAGAAA//ZAAE 5 captain
11 AAAWOMAAGAAA//ZAAF 6 captain
12 AAAWOMAAGAAA//ZAAG 7 captain
13 AAAWOMAAGAAA//ZAAH 8 captain
14 AAAWOMAAGAAA//ZAAI 9 captain
15
16
17 --根据rowid查询记录
18 SQL> set autotrace on
19 SQL> set line 200
20 SQL> select rowid,no,name from T_CAPTAIN where rowid='AAAWOMAAGAAA//ZAAA';
21
22
23 ROWID NO NAME
24 ------------------ ---------- --------------------------------
25 AAAWOMAAGAAA//ZAAA 1 captain
26
27
28 Execution Plan
29 ----------------------------------------------------------
30 Plan hash value: 2487506745
31
32 ----------------------------------------------------------------------------------------
33 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
34 ----------------------------------------------------------------------------------------
35 | 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
36 | 1 | TABLE ACCESS BY USER ROWID| T_CAPTAIN | 1 | 21 | 1 (0)| 00:00:01 |
37 ----------------------------------------------------------------------------------------
38
39
40 Statistics
41 ----------------------------------------------------------
42 0 recursive calls
43 0 db block gets
44 1 consistent gets
45 0 physical reads
46 0 redo size
47 558 bytes sent via SQL*Net to client
48 419 bytes received via SQL*Net from client
49 2 SQL*Net roundtrips to/from client
50 0 sorts (memory)
51 0 sorts (disk)
52 1 rows processed
53
54 SQL>
查询计划中说明该查询是的表访问方式是”TABLE ACCESS BY USER ROWID“,也就是直接通过USER ROWID来访问,这也是为什么只需要1次consistent gets的原因。
3.2.多个rowid的倾向
1 SQL> select rowid,no,name from T_CAPTAIN where rowid in ('AAAWOMAAGAAA//ZAAG','AAAWOMAAGAAA//ZAAD','AAAWOMAAGAAA//ZAAI');
2
3 ROWID NO NAME
4 ------------------ ---------- --------------------------------
5 AAAWOMAAGAAA//ZAAD 4 captain
6 AAAWOMAAGAAA//ZAAG 7 captain
7
8
9 Execution Plan
10 ----------------------------------------------------------
11 Plan hash value: 2350621837
12
13 -----------------------------------------------------------------------------------------
14 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
15 -----------------------------------------------------------------------------------------
16 | 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
17 | 1 | INLIST ITERATOR | | | | | |
18 | 2 | TABLE ACCESS BY USER ROWID| T_CAPTAIN | 1 | 21 | 1 (0)| 00:00:01 |
19 -----------------------------------------------------------------------------------------
20
21
22 Statistics
23 ----------------------------------------------------------
24 1 recursive calls
25 0 db block gets
26 2 consistent gets
27 0 physical reads
28 0 redo size
29 621 bytes sent via SQL*Net to client
30 419 bytes received via SQL*Net from client
31 2 SQL*Net roundtrips to/from client
32 0 sorts (memory)
33 0 sorts (disk)
34 2 rows processed
35
36 SQL>
查询计划分析:
1.上面的执行计划中出现了INLIST ITERATOR,即INLIST迭代,该操作说明其子操作多次重复时,会出现该操作。
2.由于我们使用了in运算,且传递了2个rowid,故出现INLIST迭代操作
3.迭代操作意味着条件中的对象列表一个接一个的迭代传递给子操作
4.此时统计信息中的consistent gets为2,并不是因为传入的rowid有2个,假如传入的rowid有4个,consistent gets也等于2。
注意:使用ROWID进行查询的前提是我们明确知道了一个正确的ROWID,然后通过这个ROWID进行查询。所以这里所提到的所有ROWID 必须是真实存在的,否则会报错。
整理自网络