oracle数据库性能优化实务第23讲索引和访问路径

华章培训网、[]华章培训网访问路径和SQL性能访问路径决定了执行计划的性能调整访问路径是SQL优化中最为重要的工作在一般OLTP系统中，超过60%的问题是通过调整访问路径解决的调整访问路径的优化成本最低调整访问路径的方法调整索引调整表连接顺序和连接方式选择访问路径（通过HINT,OUTLINES,表分析等方式）什么是索引索引对单表访问的影响根枝页索引单表访问的几种主要方式索引唯一性访问索引范围扫描全索引扫描快速全索引扫描索引跳跃扫描索引访问案例分析(1)SQL>

select

object_id,object_name

from

ts2

where

object_id=5295;|Id|Operation|Name|Rows|Bytes|Cost

(%CPU)|Time||0|SELECT

STATEMENT||1|79|2(0)|00:00:01||1|TABLE

ACCESS

BYINDEXROWID|TS2|1|79|2(0)|00:00:01||*2|INDEX

UNIQUE

SCAN|IDX_TS2|1||1(0)|00:00:01|Statistics0 recursive

calls0 db

block

gets3 consistent

gets3 physical

reads0 redo

size486 bytes

sent

via

SQL*Net

to

client400 bytes

received

via

SQL*Net

from

client2 SQL*Net

roundtrips

to/from

client0 sorts

(memory)sorts

(disk)rows

processed索引访问案例分析（2）通过10046事件分析SQL访问的数据块SQL>

ALTER

SESSION

SET

EVENTS

'10046

TRACE

NANE

CONTEXTFOREVER,LEVEL

12;SQL>SELECT

OBJECT_ID,OBJECT_NAME

FROM

TS2

WHEREOBJECT_ID=5295SQL>ALTER

SESSION

SET

EVENTS

'10046

TRACE

NAME

CONTEXT

OFF';索引访问案例分析（3）select

object_id,object_name

from

ts2

where

object_id=5295END

OFSTMTPARSE

#2:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1270717957248561BINDS

#2:EXEC#2:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1270717957248679WAIT

#2:

nam='SQL*Net

message

to

client'

ela=

2

driver

id=1650815232

#bytes=1

p3=0obj#=10209

tim=1270717957248711WAIT

#2:

nam='db

filesequentialread'ela=21file#=4

block#=124484blocks=1obj#=135967

tim=1270717957248897WAIT

#2:

nam='db

filesequentialread'ela=18file#=10

block#=50359blocks=1obj#=135967

tim=1270717957249032WAIT

#2:

nam='db

filesequentialread'ela=13file#=4

block#=124455blocks=1obj#=135965

tim=1270717957249097SQL>colobject_nameformata30

truncSQL>

select

object_id,object_name

from

dba_objects

where

object_id

in

(135965,135967);OBJECT_ID

OBJECT_NAME135965

TS2135967

IDX_TS2索引范围扫描SQL>

select

object_id,object_name

from

ts2

where

object_id>=5293

and

object_id<

7000;|Id

|Operation|Name|Rows|Bytes|Cost(%CPU)|Time||0|SELECTSTATEMENT||1200|94800|30(0)|00:00:01||1|TABLEACCESS

BYINDEX

ROWID|TS2|1200|94800|30(0)|00:00:01||2|INDEX

RANGE

SCAN|IDX_TS2|1200||4(0)|00:00:01|Statistics0recursive

calls0db

block

gets186consistent

gets20physical

reads0redo

size40819bytes

sent

viaSQL*Nettoclient1269 bytes

received

via

SQL*Net

from

client81 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)1200 rowsprocessed根枝页索引范围扫描索引范围扫描的例子WAIT

#55:

nam='db

file

sequential

read'

file#=13

block#=780

blocks=1

obj#=135969WAIT

#55:

nam='db

file

sequential

read'

file#=13

block#=791

blocks=1

obj#=135969WAIT

#55:

nam='db

file

sequential

read' file#=13

block#=79

blocks=1

obj#=135968WAIT

#55:

nam='db

file

sequential

read'

file#=13

block#=792

blocks=1

obj#=135969WAIT

#55:

nam='db

file

sequential

read' file#=13

block#=81

blocks=1

obj#=135968WAIT

#55:

nam='db

file

scattered

read' file#=13

block#=82

blocks=7

obj#=135968访问路径：索引IDX_TS2的根节点(13/780)索引IDX_TS2的叶节点（13/791)表TS2（13/79)索引IDX_TS2的叶节点（13/792）表TS2（13/81-13/88）全索引快速扫描全索引快速扫描的例子（1）SQL>

select

object_id

from

ts2;53147

rows

selected.Execution

PlanPlan

hash

value:

1208602780|Id|

Operation |

Name |

Rows

|Bytes

|Cost(%CPU)|

Time

||0

|

SELECT

STATEMENT

| |

53147

|259K|9 (12)|

00:00:01

||1

| INDEX

FAST

FULL

SCAN|

IDX_TS2

|

53147

|259K|9 (12)|

00:00:01

|全索引快速扫描的例子（2）WAIT

#2:

nam='db

file

sequential

read'

ela=

19

file#=13

block#=779

blocks=1obj#=135969

tim=1270728725384584WAIT

#2:

nam='db

file

scattered

read'ela=67file#=13block#=780blocks=5obj#=135969

tim=1270728725384827WAIT

#2:

nam='db

file

scattered

read'ela=85file#=13block#=785blocks=8obj#=135969

tim=1270728725669783WAIT

#2:

nam='db

file

scattered

read'ela=89file#=13block#=794blocks=7obj#=135969

tim=1270728727064985WAIT

#2:

nam='db

file

scattered

read'ela=94file#=13block#=801blocks=8obj#=135969

tim=1270728728098677全索引扫描根枝页全索引扫描的例子SQL>

select

object_id

from

ts2

order

by

object_id;53147

rows

selected.Execution

PlanPlan

hash

value:

2548674868|Id|Operation|Name|Rows|Bytes|Cost

(%CPU)|Time||0|SELECT

STATEMENT||53147|259K|120(1)|00:00:02||1|INDEX

FULL

SCAN|IDX_TS2|53147|259K|120(1)|00:00:02|全索引扫描的例子SELECT

OBJECT_ID

FROM

TS2ORDER

BY

OBJECT_IDWAIT

#6:

nam='db

file

scattered

read'ela=66file#=13block#=777blocks=8obj#=135976

tim=1270729802699001WAIT

#6:

nam='db

file

scattered

read'ela=61file#=13block#=785blocks=8obj#=135976

tim=1270729803088893WAIT

#6:

nam='db

file

scattered

read'ela=90file#=13block#=793blocks=8obj#=135976

tim=1270729804339874WAIT

#6:

nam='db

file

scattered

read'ela=81file#=13block#=801blocks=8obj#=135976

tim=1270729804571606SELECT

OBJECT_ID

FROM

TS2ORDER

BY

OBJECT_ID

DESCWAIT

#1:

nam='db

file

sequential

read'

ela=

8

file#=13

block#=897

blocks=1obj#=135976

tim=1270729917271376WAIT

#1:

nam='db

file

scattered

read'

ela=

68

file#=13

block#=889

blocks=8obj#=135976

tim=1270729917277919WAIT

#1:

nam='db

file

scattered

read'

ela=

84

file#=13

block#=881

blocks=8obj#=135976

tim=1270729918143853WAIT

#1:

nam='db

file

scattered

read'

ela=

81

file#=13

block#=873

blocks=8obj#=135976

tim=1270729919154423问题：怎么访问小表问题：小表是不加索引放到KEEP

POOL里好呢还是通过索引访问好呢？答案1：不用索引在KEEPPOOL中好答案2：使用索引好答案3：上面都不对，使用索引放到KEEP池更好到底哪个对，还是实验来说话实验环境数据库：任意Oracle

8i以上版本（本测试环境为）操作系统：任意（本测试环境为centos

linux

4.5）其他要求必须设置DB_CACHE_KEEP_SIZE（10M以上，本环境为32M）实验准备（1）创建测试表drop

table

test_small1;create

table

test_small1

(id

integer,code

varchar(100)

)storage

(buffer_pool

keep);storage

(buffer_poolcreate

unique

index

idx_small1

on

test_small1(id)keep);storagecreateunique

index

idx_small12

on

test_small1(code)(buffer_pool

keep);实验准备（２）准备数据装载脚本create

or

replace

procedureload_data1(

rows

number)

isvname

varchar2(100);beginfor

i

in

1..

rows

loopvname:='name_head_of_name_just_for_test_case1';vname:=vname||'_repeat_more_times_just_for_index';vname:=vname||'_test_only:';vname:=vname||to_char(i,'0

99999999');insert

into

test_small1

values(i,vname);end

loop;commit;end;/实验准备（３）装载少量数据truncate

table

test_small1;exec

load_data1(50);exec

dbms_stats.gather_table_stats

-(ownname=>'scott',tabname=>'test_small1',cascade=>true);实验准备（4）编写测试脚本create

or

replace

procedure

test_case1

(ct

integer)

isvid

integer;vname

varchar2(200);vpid

integer;vpname

varchar2(100);beginfor

i

in

1..ct

loopvpid:=trunc(dbms_random.value(0.01,0.5)*100);vpname:='name_head_of_name_just_for_test_case1';vpname:=vpname||'_repeat_more_times_just_for_index';vpname:=vpname||trim(to_char(vpid,'0

99999999'));select/*+

full(a)

*/

id,code

into

vid,vnamefrom

test_small1

a

where

id=vpid;select

/*+

index(

a,idx_smaill1)

*/

id,code

into

vid,vnamefrom

test_small1

a

where

id=vpid;select

/*+

index(

a,idx_smaill12)

*/

id,code

into

vid,vnamefrom

test_small1

a

where

code=vpname;end

loop;end;/实验准备（5）编写PROFILER脚本create

or

replace

procedureexec_test(ct

integer)iserr

number;beginerr:=DBMS_PROFILER.START_PROFILER

('small

tab

test

case1-1');dbms_output.put_line('runid

is:'||to_char(err));test_case1(ct);err:=DBMS_PROFILER.STOP_PROFILER

;end;/检查数据分布dbms_rowid.rowid_block_number(rowid)SQL>

select

distinctfrom

test_small1;DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)920测试逻辑读（1）declarevid

integer;

vname

varchar2(200);

vpy1

integer;

vgets1

integer;vcr1

integer;

vpy2

integer;

vgets2

integer;

vcr2 integer;

vpid

integer;beginselect

value

into

vpy1

fromv$mystat

where

STATISTIC#=54;selectvalue

intovgets1from

v$mystat

where

STATISTIC#=47;select

value

into

vcr1

from

v$mystat

where

STATISTIC#=50;for

i

in

1..100000

loopvpid:=trunc(dbms_random.value(0.01,0.5)*100);select

/*+

index(

a,idx_smaill1)

*/

id,code

into

vid,vnamefrom

test_small1

a

where

id=vpid;end

loop;select

value

into

vpy2

fromv$mystat

where

STATISTIC#=54;selectvalue

intovgets2from

v$mystat

where

STATISTIC#=47;select

value

into

vcr2

from

v$mystat

where

STATISTIC#=50;dbms_output.put_line('py

read

:'||to_char(vpy2-vpy1));dbms_output.put_line('db

blockget:'||to_char(vgets2-vgets1));dbms_output.put_line('CR

get

:'||to_char(vcr2-vcr1));end;测试逻辑读（1）结果pyread:0dbblockget:0CRget:200000测试逻辑读（2）declarevid

integer;

vname

varchar2(200);

vpy1

integer;

vgets1

integer;vcr1

integer;

vpy2

integer;

vgets2

integer;

vcr2 integer;

vpid

integer;beginselect

value

into

vpy1

fromv$mystat

where

STATISTIC#=54;selectvalue

intovgets1from

v$mystat

where

STATISTIC#=47;select

value

into

vcr1

from

v$mystat

where

STATISTIC#=50;for

i

in

1..100000

loopvpid:=trunc(dbms_random.value(0.01,0.5)*100);select/*+

full(a)

*/

id,code

into

vid,vnamefrom

test_small1

a

where

id=vpid;end

loop;select

value

into

vpy2

fromv$mystat

where

STATISTIC#=54;selectvalue

intovgets2from

v$mystat

where

STATISTIC#=47;select

value

into

vcr2

from

v$mystat

where

STATISTIC#=50;dbms_output.put_line('py

read

:'||to_char(vpy2-vpy1));dbms_output.put_line('db

blockget:'||to_char(vgets2-vgets1));dbms_output.put_line('CR

get

:'||to_char(vcr2-vcr1));end;测试逻辑读（2）结果pyread:0dbblockget:0CRget:700000测试逻辑读（3）declarevid

integer;

vname

varchar2(200);

vpy1

integer;

vgets1

integer;vcr1 integer;

vpy2

integer;

vgets2

integer;

vcr2 integer;

vpid

integer;

vpnamevarchar2(100);beginselect

value

into

vpy1

from

v$mystat

where

STATISTIC#=54;selectvalueintovgets1from

v$mystat

where

STATISTIC#=47;selectvalueintovcr1fromv$mystat

where

STATISTIC#=50;

for

i

in

1..100000

loopvpid:=trunc(dbms_random.value(0.01,0.5)*100);vpname:='name_head_of_name_just_for_test_case1';vpname:=vpname||'_repeat_more_times_just_for_index';vpname:=vpname||trim(to_char(vpid,'0

99999999'));select/*+

index(a

idx_small12)

*/id,code

into

vid,vnamefrom

test_small1

a

where

code=vpname;end

loop;select

value

into

vpy2

from

v$mystat

where

STATISTIC#=54;selectvalueintovgets2from

v$mystat

where

STATISTIC#=47;selectvalueintovcr2fromv$mystat

where

STATISTIC#=50;dbms_output.put_line('py

read

:'||to_char(vpy2-vpy1));dbms_output.put_line('db

block

get:'||to_char(vgets2-vgets1));dbms_output.put_line('CRget

:'||to_char(vcr2-vcr1));end;测试逻辑读（3）结果pyread:0dbblockget:0CRget:200000执行PROFILER测试set

serveroutput

on;exec

exec_test(100000);/查看RUNIDSQL>

column

RUN_COMMENT

format

a40

truncate;SQL>

select

runid,

run_date,

RUN_COMMENT

fromplsql_profiler_runs

order

by

runid;RUNIDRUN_DATERUN_COMMENT-105-DEC-10SEQ

CACHE

IS

1k205-DEC-10SEQ

CACHE

IS

10k305-MAY-11index

test

case1405-MAY-11small

tab

test

case1-1505-MAY-11small

tab

test

case1-1605-MAY-11small

tab

test

case1-1705-MAY-11small

tab

test

case1-1805-MAY-11index_test_case_1查询测试结果column

unit_name

format

a15

truncate;column

occured

format

999999

;column

line#

format

99999

;column

tot_time

format

999999.999999

;col

text

format

a60

truncselectp.unit_name,

p.occured,p.tot_time,

p.line#

line,substr(s.text,

1,75)

textfrom(select

u.unit_name,

d.TOTAL_OCCUR

occured,(d.TOTAL_TIME/1000000000)

tot_time,

d.line#from

plsql_profiler_units

u,

plsql_profiler_data

dwhere

d.RUNID=u.runid

and

d.UNIT_NUMBER

=u.unit_numberand

d.TOTAL_OCCUR

>0and

u.runid=

&RUN_ID)

p,

user_source

swhere

p.unit_name

=

(+)

and

p.line#

=

s.line

(+)order

by

p.unit_name,

p.line#;测试结果TOT_TIMELINE

TEXT56test_case1(ct);err:=DBMS_PROFILER.STOP_PROFILER

;1

procedure

test_case1

(ct

integer)isfor

i in

1..50

loopselect

id,code

into

vid,vname

from

test_small1

where

i781011121314151719for

i in

1..ct

loopvpid:=trunc(dbms_random.value(0.01,0.5)*100);vpname:='name_head_of_name_just_for_test_case1';vpname:=vpname||'_repeat_more_times_just_for_index';vpname:=vpname||trim(to_char(vpid,'0select

/*+

full(a)

*/

id,code

into

vid,vnameselect

/*+

index(

a,idx_smaill1)

*/

id,code

into

vid,select

/*+

index(

a,idx_smaill12)

*/

id,code

into

vid.000008.000003.000004.000056.001658.100517.349403.113059.139251.4438624.8690053.2813543.431544.00000322

end;阶段性结论对于只占用一个块，只有几十条数据的情况，索引也能提高访问效率索引字段越大，访问开销越大本测试的启示不要轻信索引对小表也是有效的在某些极端情况下，合理使用索引，减少小表的全表扫描也会对性能有所帮助对小表建索引，并将表和索引全部放到KEEPPOOL可以提高访问效率空值的访问问题：空值字段的访问能用索引吗？select

*

from

test_small1

where

id

is

null;测试准备（1）create

or

replace

procedureload_data2(

rows

number)

isvname

varchar2(100);

vid

integer;beginfor

i

in

1..

rows

loopvname:='name_head_of_name_just_for_test_case1';vname:=vname||'_repeat_more_times_just_for_index';vname:=vname||trim(to_char(i,'099999999'));if(mod(i,100)=0)

thenvid:=null

;

else

vid:=i;

end

if;insert

into

test_small1

values(vid,vname);end

loop;commit;end;/测试准备truncate

table

test_small1;exec

load_data2(100000);SQL>

select

count(*)

from

test_small1;COUNT(*)100000SQL>

select

count(*)

from

test_small1

where

id

is

null;COUNT(*)200测试结果（1）SQL>

select

* from

test_small1

where

id

is

null;200

rows

selected.|

Id |

Operation |

Name |

Rows |

Bytes

|

Cost

(%CPU)|

Time|| 0

|

SELECT

STATEMENT

|

||* 1

| TABLE

ACCESS

FULL|

TEST_SMALL1

|1

| 101

|1

| 101

|2 (0)|

00:00:01

|2 (0)|

00:00:01

|Statistics1 recursive

calls0 db

block

gets1523

consistent

gets0 physical

reads0 redo

size22734 bytes

sent

via

SQL*Net

to

client543 bytes

received

via

SQL*Net

from

client15 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)200 rows

processed使用函数索引create

index

idx_small13

on

test_small1(nvl2(id,1,0));修改SQL以适应索引SQL>

select

/*+

index(a

idx_small13)

*/

*

from

test_small1

where

nvl2(id,1,0)=0;200

rows

selected.-|

Name|

Rows

| Cost

(%CPU)|

Time|Id |

Operation|SELECT

STATEMENT||1|1(0)|00:00:01TABLE

ACCESS

BYINDEXROWID|TEST_SMALL1|1|1(0)|00:00:01INDEX

RANGE

SCAN|IDX_SMALL13|400|98(0)|00:00:01-| 0

||| 1

||| 2

||-Statistics1 recursive

calls0 db

block

gets217 consistent

gets0 physical

reads124 redo

size22734 bytes

sent

via

SQL*Net

to

client543 bytes

received

via

SQL*Net

from

client空值使用索引的条件空值在表中的比例比较少通过<FIELD>IS

NULL查找空值的行需要修改应用复合索引同样有效课后作业场景：一张表，有几十万条记录其中只有几条记录的STATUS＝1，其他的＝0应用总是读取STATUS=1的记录，然后处理，最后将STATUS设置为0基于上述原理，如何优化这个SQL生成数据create

table

testidx

(id,status)

as

select

object_id,'0'

fromdba_objects;insert

into

testidx

select

object_id,'0'

from

dbaobjects;insert

into

testidx

select

object_id,'0'

from

dbaobjects;insert

into

testidx

select

object_id,'0'

from

dbaobjects;insert

into

testidx

select

object_id,'1'

from

dba_objectswhere

rownum<10;commit;select

count(*),status

from

testidx

group

by

status;COUNT(*)

S-9

12127880提示可以考虑使用位图索引普通索引是否可以？能否让索引的大小更小一些？感谢您对华章培训网的支持!.课后作业场景：一张表，有几十万条记录其中只有几条记录的STATUS＝1，其他的＝0应用总是读取STATUS=1的记录，然后处理，最后将STATUS设置为0基于上述原理，如何优化这个SQL不使用索引SQL>

select/*+

full(testidx)

*/

id

from

testidx

where

status='1';|

Id |

Operation |

Name |

Rows |

Bytes

|

Cost

(%CPU)|

Time

|| 0

|

SELECT

STATEMENT

|

||* 1

| TABLE

ACCESS

FULL|

TESTIDX

|1

|1

|6

|6

|29 (25)|

00:00:01

|29 (25)|

00:00:01

|Statistics1recursive

calls0db

block

gets376consistent

gets0physical

reads0redo

size694bytes

sent

via

SQL*Nettoclient411 bytes

received

via

SQL*Net

from

client3 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)18 rows

processed使用位图索引SQL>

select/*+

index(testidx

idx_testidx_bitmap)

*/

id

from

testidx

where

status='1';|

Id|Operation|Name|Rows|Cost

(%CPU)|Time||0|SELECT

STATEMENT||1|773(1)|00:00:08||*1|TABLE

ACCESS

BYINDEXROWID

|TESTIDX|1|773(1)|00:00:08||2|BITMAP

CONVERSION

TO

ROWIDS||||||3|BITMAP

INDEX

FULL

SCAN

|IDX_TESTIDX_BITMAP||||Statistics0recursive

calls0db

block

gets213061consistent

gets0physical

reads0redo

size672bytes

sent

via

SQL*Net

toclient411 bytes

received

via

SQL*Net

from

client3 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)18 rows

processed使用普通索引SQL>

create

index

idx_testidx_normal

on

testidx(status);select /*+

index(testidx

idx_testidx_normal)

*/

id

from

testidx

where

status='1';|Id|Operation|Name|Rows|Cost

(%CPU)|Time||0|SELECT

STATEMENT

||1|2(0)|00:00:01||1|TABLE

ACCESS

BY

INDEX

ROWID|TESTIDX|1|2(0)|00:00:01||*2|INDEX

RANGE

SCAN

|IDX_TESTIDX_NORMAL|1|1(0)|00:00:01|Statistics1 recursive

calls0 db

block

gets7 consistent

gets1 physical

reads0 redo

size694 bytes

sent

via

SQL*Net

to

client411 bytes

received

via

SQL*Net

from

client3 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)18 rows

processed索引的大小SQL>exec

dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'testidx',

cascade=>true,

estimate_percent=>100);SQL>

select

index_name,leaf_blocks,distinct_keys

from

user_indexes

wheretable_name='TESTIDX';INDEX_NAMELEAF_BLOCKS

DISTINCT_KEYSIDX_TESTIDX_NORMAL3862使用函数索引进一步优化SQL>

create

index

idx_testidx_func

ontestidx(decode(status,1,1,0,null));SQL>

execdbms_stats.gather_table_stats(ownname=>'scott',tabname=>'testidx',cascade=>true,estimate_percent=>100);SQL>select

index_name,leaf_blocks,distinct_keys

fromuser_indexes

where

table_name='TESTIDX';INDEX_NAME LEAF_BLOCKS

DISTINCT_KEYSIDX_TESTIDX_FUNC11改写SQLSQL>

select /*+

index(testidx

idx_testidx_func)

*/

id

from

testidx

wheredecode(status,1,1,0,null)='1';|Id|Operation|Name|Rows

|Cost

(%CPU)|Time||0|SELECT

STATEMENT||18|2(0)|00:00:01||1|TABLE

ACCESS

BYINDEXROWID|TESTIDX|18|2(0)|00:00:01||*2|INDEX

RANGE

SCAN|IDX_TESTIDX_FUNC|18|1(0)|00:00:01|Statistics0 recursive

calls0 db

block

gets6 consistent

gets0 physical

reads0 redo

size694 bytes

sent

via

SQL*Net

to

client411 bytes

received

via

SQL*Net

from

client3 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)18 rows

processed使用柱状图SQL>exec

dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'testidx',cascade=>true,

-estimate_percent=>100

,

method_opt=>'for

all

columns

size

auto');SQL>

select id

from

testidx

where

status='1';|Id|Operation|Name|Rows|Cost

(%CPU)|Time||0|SELECT

STATEMENT

||18|2(0)|00:00:01||1|TABLE

ACCESS

BY

INDEX

ROWID|TESTIDX|18|2(0)|00:00:01||*2|INDEX

RANGE

SCAN

|IDX_TESTIDX_NORMAL|18|1(0)|00:00:01|Statistics0 recursive

calls0 db

block

gets7 consistent

gets0 physical

reads0 redo

size694 bytes

sent

via

SQL*Net

to

client411 bytes

received

via

SQL*Net

from

client3 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)18 rows

processed案例总结当返回记录数占整个表的记录集很少部分的时候，适合使用索引范围扫描值域很少的字段在某些业务需求下也可以使用索引位图索引不一定能够提升方位效率通过分析柱状图，可以有效使用此类索引使用函数索引可以进一步优化性能索引范围扫描中的集簇因子范围扫描是最常用的索引扫描方式之一范围扫描的效率取决于两方面的因素每次扫描返回的记录数索引的范围扫描效率（索引的集簇因子）集簇因子某个索引的集簇因子的大小和该字段在表中的存储是否有序有关如果集簇因子接近表的BLOCK的数量，说明该表中的数据大多数是按照索引字段的顺序排序的，这种情况下范围扫描的成本较低如果集簇因子接近表中记录数，那么说明该表中这个字段是乱序的，这种情况下，范围扫描的成本较高实验－集簇因子对范围扫描性能的影响分析集簇因子不同对范围扫描的影响实验方法创建一张基础表，里面的某个字段是按照顺序排序的通过基础表生成两张表，记录数相同，但是一张表中是按照索引字段排序的，一张表是乱序的（通过随机函数）实验准备（1）生成一个基准表droptable

test_cf1;create

table

test_cf1

(id

integer,name

varchar2(100),p1

varchar2

(100),p2varchar2(100));create

index

idx_test_cf1_1

on

test_cf1(id);create

or

replaceprocedure

test_cf1_ldisbeginfor

i

in

1..

20000

loopfor

j

in

1..20

loopinsertinto

test_cf1

values(i,'

111111111','

22222222','

33333333');end

loop;end

loop;commit;end;/实验准备（2）生成对比表droptable

test_cf2;create

table

test_cf2

(id

integer,name

varchar2(100),p1

varchar2

(100),p2varchar2(100));create

index

idx_test_cf2_1

on

test_cf2(id);create

or

replaceprocedure

test_cf2_ldisvid

integer;beginfor

i

in

1..

400000

loopvid:=trunc(dbms_random.value(0.0001,2)*10000);insert

into

test_cf2

values(vid,'111111111','

22222222','

33333333');end

loop;commit;end;/基准表测试结果SQL>

alter

system

flush

buffer_cache;SQL>select

/*+

index(a

idx_tet_cf1_1)

*/

*from

test_cf1a

where

id=11366;|Id|Operation|Name|Rows|Cost

(%CPU)|Time||0|SELECT

STATEMENT

||20|2(0)|00:00:01||1|TABLE

ACCESS

BY

INDEX

ROWID|TEST_CF1|20|2(0)|00:00:01||*2|INDEX

RANGE

SCAN

|IDX_TEST_CF1_1|20|1(0)|00:00:01|Statistics0 recursive

calls0 db

block

gets8 consistent

gets4 physical

reads0 redo

size936 bytes

sent

via

SQL*Net

to

client411 bytes

received

via

SQL*Net

from

client3 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)20 rows

processed对比表测试结果SQL>

alter

system

flush

buffer_cache;SQL>select

/*+

index(a

idx_tet_cf2_1)

*/

*from

test_cf2

a

where

id=11366;|Id|Operation|Name|Rows

|Cost

(%CPU)|Time||0|SELECT

STATEMENT

||20|22(0)|00:00:01||1|TABLE

ACCESS

BY

INDEX

ROWID|TEST_CF2|20|22(0)|00:00:01||*2|INDEX

RANGE

SCAN

|IDX_TEST_CF2_1|20|3(0)|00:00:01|Statistics0 recursive

calls0 db

block

gets25 consistent

gets23 physical

reads0 redo

size936 bytes

sent

via

SQL*Net

to

client411 bytes

received

via

SQL*Net

from

client3 SQL*Net

roundtrips

to/from

client0 sorts

(memory)0 sorts

(disk)20 rows

processed索引跳跃扫描INDEX

SKIP

SCAN9i后出现的新特性支持非索引引导字段使用索引准备测试数据drop

table

test_skip;create

table

test_skip

(id

integer,id2

integer,p1

varchar2(100),p2varchar2(100),p3

varchar2(100),p4

varchar2(100),p5

varchar2(100));create

index

idx_test_skip

on

test_skip(id,id2);create

or

replace

procedure

test_skip_ldisbeginfor

i

in

1..

50000

loopinsert

into

test_skip

values(i,trunc(i/2)*2,'

33333333','

33333333','

33333333','

33333333','

33333333');end

loop;commit;