--使用B*Tree索引的情况简析
--什么情况下使用
--1.如果你要访问表中非常少的一部分数据。 -->通过读索引来访问表中的数据，前提是当问的数据只占表的很小一部分。
--2.如果你要处理表中的大量数据，但这些数据可以通过索引直接拿到。索引包含了足够的信息来回答整个查询，我们根本不用去访问整个表。
1.
EODA@PROD1> set autotrace traceonly explain
EODA@PROD1> select owner,status from t where owner=USER;
Execution Plan
----------------------------------------------------------
Plan hash value: 1049179052
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2209 | 24299 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2209 | 24299 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DESC_T_IDX | 9 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_DESCEND("OWNER")=SYS_OP_DESCEND(USER@!))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))=USER@!)
/* TABLE ACCESS BY INDEX ROWID表示oracle会先去读索引，然后根据索引条目中的信息，去读取一个数据块来拿到行数据。
如果你要通过索引访问表中大量数据，那这就不是最高效的方法了。 */
2.
EODA@PROD1> select count(*) from t where owner=user;
Execution Plan
----------------------------------------------------------
Plan hash value: 293504097
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 18 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| T_IDX | 2209 | 13254 | 18 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"=USER@!)
/* 这个查询并没有访问底层表，只访问了T_IDX一个查询 */
--物理组织结构影响分析
--如何在磁盘上物理的组织数据，同样也会大大地影响索引访问的开销。
--实验环境
EODA@PROD1> create table colocated ( x int, y varchar2(80) ); --创建一个有序表
Table created.
EODA@PROD1>
EODA@PROD1> begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated(x,y)
5 values (i, rpad(dbms_random.random,75,'*') ); --i为顺序并设置为主键
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
EODA@PROD1>
EODA@PROD1> alter table colocated
2 add constraint colocated_pk
3 primary key(x);
Table altered.
EODA@PROD1>
EODA@PROD1> begin
2 dbms_stats.gather_table_stats( user, 'COLOCATED');
3 end;
4 /
PL/SQL procedure successfully completed.
EODA@PROD1>
EODA@PROD1> create table disorganized --创建一个无序表
2 as
3 select x,y
4 from colocated
5 order by y;
Table created.
EODA@PROD1>
EODA@PROD1> alter table disorganized
2 add constraint disorganized_pk
3 primary key (x);
Table altered.
EODA@PROD1>
EODA@PROD1> begin
2 dbms_stats.gather_table_stats( user, 'DISORGANIZED');
3 end;
4 /
PL/SQL procedure successfully completed.
--分别执行五次并收集统计信息
exec dbms_monitor.session_trace_enable;
select * from colocated where x between 20000 and 40000;
select * from colocated where x between 20000 and 40000;
select * from colocated where x between 20000 and 40000;
select * from colocated where x between 20000 and 40000;
select * from colocated where x between 20000 and 40000;
select /*+ index( disorganized disorganized_pk ) */ * from disorganized where x between 20000 and 40000;
select /*+ index( disorganized disorganized_pk ) */ * from disorganized where x between 20000 and 40000;
select /*+ index( disorganized disorganized_pk ) */ * from disorganized where x between 20000 and 40000;
select /*+ index( disorganized disorganized_pk ) */ * from disorganized where x between 20000 and 40000;
select /*+ index( disorganized disorganized_pk ) */ * from disorganized where x between 20000 and 40000;
exec dbms_monitor.session_trace_disable;
--查看统计信息
SQL ID: 3h89xtkd42j3h Plan Hash: 1550765370
select *
from
colocated where x between 20000 and 40000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 6675 0.11 0.28 0 14495 0 100005
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6685 0.11 0.29 0 14495 0 100005
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 97
Number of plan statistics captured: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
20001 20001 20001 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=2899 pr=0 pw=0 time=46227 us cost=283 size=1620162 card=20002)
20001 20001 20001 INDEX RANGE SCAN COLOCATED_PK (cr=1374 pr=0 pw=0 time=17344 us cost=43 size=0 card=20002)(object id 85935)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6675 0.00 0.01
SQL*Net message from client 6675 0.23 6.51
/* 有序表CPU时间为0.29s，逻辑I/O14495次。 */
********************************************************************************
SQL ID: 39b8uv1uavsf4 Plan Hash: 2594580634
select /*+ index( disorganized disorganized_pk ) */ *
from
disorganized where x between 20000 and 40000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 6675 0.15 0.48 0 106830 0 100005
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6685 0.15 0.48 0 106830 0 100005
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 97
Number of plan statistics captured: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
20001 20001 20001 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=21366 pr=0 pw=0 time=92990 us cost=20039 size=1620162 card=20002)
20001 20001 20001 INDEX RANGE SCAN DISORGANIZED_PK (cr=1374 pr=0 pw=0 time=15963 us cost=43 size=0 card=20002)(object id 85937)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6675 0.00 0.01
SQL*Net message from client 6675 0.09 6.24
/* 无序表CPU时间为0.48s，逻辑I/O106830次。 */
********************************************************************************
--显而易见表组织结构的影响之大！
--当对无序表进行全表扫描又会怎么样呢？
SQL ID: 5x91z5wavhc9w Plan Hash: 2727546897
select *
from
disorganized where x between 20000 and 30000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.06 0.05 0 1852 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.07 0.06 0 1852 0 10001
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 97
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
10001 10001 10001 TABLE ACCESS FULL DISORGANIZED (cr=1852 pr=0 pw=0 time=34752 us cost=334 size=810162 card=10002)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 668 0.00 0.00
SQL*Net message from client 668 0.00 0.72
/* 无序表CPU时间为0.07s，逻辑I/O1852次。效率同样很高 */
********************************************************************************
使用B*Tree索引的情况简析
最新推荐文章于 2020-01-15 21:50:29 发布
1294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
