h2 { margin-top: 0.46cm; margin-bottom: 0.46cm; direction: ltr; line-height: 173%; text-align: justify; page-break-inside: avoid }
h2.western { font-family: "Cambria", serif; font-size: 16pt }
h2.cjk { font-family: "宋体"; font-size: 16pt }
h2.ctl { font-size: 16pt }
h1 { margin-top: 0.6cm; margin-bottom: 0.58cm; direction: ltr; line-height: 241%; text-align: justify; page-break-inside: avoid }
h1.western { font-family: "Calibri", serif; font-size: 22pt }
p { margin-bottom: 0.25cm; direction: ltr; line-height: 120%; text-align: justify }
a:link { color: rgb(0, 0, 255) }
1.创建测试表
SQL>
CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
2.创建测试索引
SQL>
CREATE INDEX ind_test_id ON TEST(object_id);
3.插入测试数据
SQL>
INSERT INTO TEST SELECT * FROM dba_objects WHERE
object_id IS NOT NULL AND object_id > 10000 ORDER BY object_id
DESC;
17837
rows created.
4.分析表
附带索引等等
SQL>
analyze table test compute statistics for table for all columns for
all indexes;
Table
analyzed.
5.打开执行计划
SQL>
set autotrace trace;
6.FFS示例
SQL>
select object_id from test;
17837
rows selected.
Execution
Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=68 Card=17837
Bytes=71348)
1
0 TABLE ACCESS (FULL) OF 'TEST' (Cost=68 Card=17837 Bytes=71348)
这时候
Oracle会选择全表扫描，因为
object_id
列默认是可以为null的，来修改成
not null
6.1修改字段属性
not null
SQL>alter
table test modify(object_id not null);
6.2再次验证
FFS
SQL>
select object_id from test;
17837
rows selected.
Execution
Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=17837
Bytes=71348)
1
0 INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11
Card=17837 Bytes=71348)
没有问题
7. IFS
示例
SQL>
select/*+ index(test ind_TEST_ID)*/ object_id from test;
17837
rows selected.
Execution
Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=17837
Bytes=71348)
1
0 INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=101
Card=17837 Bytes=71348)
没有问题
我们看到了两者都可以在这种情况下使用，那么他们有什么区别呢？有个地方可以看出两者的区别，
来看一下两者的输出结果，为了让大家看清楚一点，我们只取10行。
8结果验证
SQL>
set arraysize 1000;
SQL>
alter system flush buffer_cache; ----一定要刷新，不然观察不到
db file sequential
read
SQL>
alter system flush shared_pool;
SQL>
alter session set events '10046 trace name context forever, level 8';
8.1FFS(INDEX
FAST FULL SCAN)
SQL>
select object_id from test where rownum<11;
OBJECT_ID
----------
66266
66267
66268
66269
66270
66271
66272
66273
66274
66275
10
rows selected.
SQL>
alter session set events '10046 trace name context off';
检查该索引所属文件号、段头快
SQL>
select owner,header_file,header_block from dba_segments where
segment_name='IND_TEST_ID';
OWNER
HEADER_FILE HEADER_BLOCK
------------------------------
----------- ------------
OWNER
4 3562
段头块为
3562,后退一个即
索引的 root block
3563
SQL>
set arraysize 1000;
SQL>
alter system flush buffer_cache; ----一定要刷新，不然观察不到
db file sequential
read
SQL>
alter system flush shared_pool;
SQL>
alter session set events '10046 trace name context forever, level 8';
以下内容取自
10046 event trace文件
|
===================== PARSING select END PARSE EXEC WAIT WAIT WAIT WAIT WAIT WAIT FETCH WAIT WAIT FETCH STAT STAT WAIT *** 结论：FFS会读取 最开始扫描的是3562，它是索引的段头，并且是单块读(注意：段头都是单块读)，然后才是从3563 |
8.2FS(INDEX
FULL SCAN)
SQL>
set arraysize 1000;
SQL>
alter system flush buffer_cache; ----一定要刷新，不然观察不到
db file sequential
read
SQL>
alter system flush shared_pool;
SQL>
alter session set events '10046 trace name context forever, level 8';
SQL>
select/*+ index(test ind_TEST_ID)*/ object_id from test where
rownum<11;
OBJECT_ID
----------
10616
12177
12178
12179
12301
13495
13536
13539
13923
16503
10
rows selected.
SQL>
alter session set events '10046 trace name context off';
以下内容取自
10046 event trace文件
|
===================== PARSING select/*+ END PARSE EXEC WAIT WAIT WAIT FETCH WAIT WAIT FETCH STAT STAT WAIT *** 结论：这个索引的段头块是3562，root |
结论：两者的结果完全不一样，这是为什么呢？
|
这是因为当进行index 而进行index |
9.原因考证
归纳：
|
索引类别 |
访问方式 |
是否排序 |
|
FFS |
先扫描 segment |
多一步 sort |
|
FS |
不扫描 segment |
自动的执行 sort |
详情
|
为什么 为什么 |