今天测试了一下访问路径

显示全部楼层 · 2006-12-13 17:04:38

今天测试了一下访问路径,不全的请大家补充
[PHP]

1、全表扫描

scott@PENG>create table t as select * from all_objects;

表已创建。

scott@PENG>set autotrace traceonly

scott@PENG>select count(*) from t;

执行计划

----------------------------------------------------------

Plan hash value: 2966233522

-------------------------------------------------------------------

| Id| Operation
| Name | Rows| Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT || 1 | 132 (2)| 00:00:02 |

| 1 |SORT AGGREGATE|| 1 |
|
|

| 2 | TABLE ACCESS FULL| T| 37402 | 132 (2)| 00:00:02 |

-------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

基于提示的全表扫描

scott@PENG>alter table t add constraint pk_t primary key(owner,object_name);

表已更改。

scott@PENG>select count(*) from t;

执行计划

----------------------------------------------------------

Plan hash value: 949213647

----------------------------------------------------------------------

| Id| Operation
| Name | Rows| Cost (%CPU)| Time |

----------------------------------------------------------------------

| 0 | SELECT STATEMENT|| 1 |57 (2)| 00:00:01 |

| 1 |SORT AGGREGATE || 1 |
|
|

| 2 | INDEX FAST FULL SCAN| PK_T | 37402 |57 (2)| 00:00:01 |

----------------------------------------------------------------------

scott@PENG>select /*+ full(t) */ count(*) from t ;

执行计划

----------------------------------------------------------

Plan hash value: 2966233522

-------------------------------------------------------------------

| Id| Operation
| Name | Rows| Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT || 1 | 132 (2)| 00:00:02 |

| 1 |SORT AGGREGATE|| 1 |
|
|

| 2 | TABLE ACCESS FULL| T| 37402 | 132 (2)| 00:00:02 |

-------------------------------------------------------------------
.
[/PHP]

千问 · 2006-12-13 17:04:38

[PHP]
2、ROWID访问

(1) USER ROWID

scott@PENG>set autotraceoff

scott@PENG>select rowid,owner,object_name from t where rownum select rowid,owner,object_name,DATA_OBJECT_ID from t where rowid ='AA

AM3EAAEAAAAZ7AAa' ;

ROWID
OWNER
OBJECT_NAME

DATA_OBJECT_ID

------------------ ------------------------------ ------------------------------

--------------

AAAM3EAAEAAAAZ7AAa CTXSYS
CONTEXT

执行计划

----------------------------------------------------------

Plan hash value: 3207308387

--------------------------------------------------------------------------------

---

| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time

|

--------------------------------------------------------------------------------

---

| 0 | SELECT STATEMENT || 1 |59 | 1 (0)| 00:00:0

1 |

| 1 |TABLE ACCESS BY USER ROWID| T| 1 |59 | 1 (0)| 00:00:0

1 |

--------------------------------------------------------------------------------

---

(2) ROWID RANGE

scott@PENG>select rowid,owner,object_name,DATA_OBJECT_ID from t where rowid betw

een 'AAAM3EAAEAAAAZ7AAa' and 'AAAM3EAAEAAAAZ7AAb';

ROWID
OWNER
OBJECT_NAME

DATA_OBJECT_ID

------------------ ------------------------------ ------------------------------

--------------

AAAM3EAAEAAAAZ7AAa CTXSYS
CONTEXT

AAAM3EAAEAAAAZ7AAb PUBLIC
CONTEXT

执行计划

----------------------------------------------------------

Plan hash value: 280204748

--------------------------------------------------------------------------------

----

| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time

|

--------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT
|| 6 | 354 | 132 (2)| 00:00:

02 |

|*1 |TABLE ACCESS BY ROWID RANGE| T| 6 | 354 | 132 (2)| 00:00:

02 |

--------------------------------------------------------------------------------

----

.
[/PHP]

千问 · 2006-12-13 17:04:38

[PHP]
3、索引扫描
(1) 索引扫描

scott@PENG>select * from t where owner = 'OLAPSYS' and object_name = 'CWM$OLAP$LEVEL';

执行计划

----------------------------------------------------------

Plan hash value: 2454218153

------------------------------------------------------------------------------------

| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT
|| 1 | 128 | 2 (0)| 00:00:01 |

| 1 |TABLE ACCESS BY INDEX ROWID| T| 1 | 128 | 2 (0)| 00:00:01 |

|*2 | INDEX UNIQUE SCAN | PK_T | 1 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------

(2) 索引唯一扫描

scott@PENG>create unique index idx_uq_obid on t(object_id);

scott@PENG>select *from t where object_id = 2330;

执行计划

----------------------------------------------------------

Plan hash value: 4066194819

-------------------------------------------------------------------------------------------

| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT
|
| 1 | 128 | 2 (0)| 00:00:01 |

| 1 |TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 |

|*2 | INDEX UNIQUE SCAN | IDX_UQ_OBID | 1 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

(3) 索引范围扫描

scott@PENG>select *from t where object_id between 2330 and 2340;

已选择8行。

执行计划

----------------------------------------------------------

Plan hash value: 1022588835

-------------------------------------------------------------------------------------------

| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT
|
| 8 |1024 | 3 (0)| 00:00:01 |

| 1 |TABLE ACCESS BY INDEX ROWID| T | 8 |1024 | 3 (0)| 00:00:01 |

|*2 | INDEX RANGE SCAN
| IDX_UQ_OBID | 8 | | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

scott@PENG>create index idx_idtype on t(OBJECT_TYPE) ;

索引已创建。

scott@PENG>select owner,object_name from t where object_type ='TABLE';

已选择115行。

执行计划

----------------------------------------------------------

Plan hash value: 3463910453

--------------------------------------------------------------------------------

----------

| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)|

Time |

--------------------------------------------------------------------------------

----------

| 0 | SELECT STATEMENT
|
| 115 |5175 | 5 (0)|

00:00:01 |

| 1 |TABLE ACCESS BY INDEX ROWID| T
| 115 |5175 | 5 (0)|

00:00:01 |

|*2 | INDEX RANGE SCAN
| IDX_IDTYPE | 115 | | 1 (0)|

00:00:01 |

--------------------------------------------------------------------------------

scott@PENG>select max(object_id) from t;

执行计划

----------------------------------------------------------

Plan hash value: 60859129

------------------------------------------------------------------------------------------

| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT |
| 1 |13 | 2 (0)| 00:00:01 |

| 1 |SORT AGGREGATE
|
| 1 |13 |
|
|

| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_UQ_OBID | 37402 | 474K| 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

(4) 索引跳跃式扫描

scott@PENG>create index index_t_on on t(TEMPORARY,OBJECT_NAME);

索引已创建。

scott@PENG>analyze table t compute statistics;

表已分析。

scott@PENG>set autotrace traceonly

scott@PENG>select * from t where object_name = '/5bada5a2_AddressTokenizer';

执行计划

----------------------------------------------------------

Plan hash value: 3315465649

------------------------------------------------------------------------------------------

| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT
|
| 2 | 174 | 4 (0)| 00:00:01 |

| 1 |TABLE ACCESS BY INDEX ROWID| T
| 2 | 174 | 4 (0)| 00:00:01 |

|*2 | INDEX SKIP SCAN | INDEX_T_ON | 2 | | 3 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

(5) 索引全扫描

scott@PENG>select owner,object_name from t order by owner,object_name;

已选择40713行。

执行计划

----------------------------------------------------------

Plan hash value: 432410079

-------------------------------------------------------------------------

| Id| Operation| Name | Rows| Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT || 40713 |1192K| 238 (1)| 00:00:03 |

| 1 |INDEX FULL SCAN | PK_T | 40713 |1192K| 238 (1)| 00:00:03 |

-------------------------------------------------------------------------

(6) 索引快速全扫描

scott@PENG>select ownerfrom t;

已选择40713行。

执行计划

----------------------------------------------------------

Plan hash value: 3191966744

-----------------------------------------------------------------------------

| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT || 40713 | 198K|54 (2)| 00:00:01 |

| 1 |INDEX FAST FULL SCAN| PK_T | 40713 | 198K|54 (2)| 00:00:01 |

-----------------------------------------------------------------------------
.
[/PHP]

千问 · 2006-12-13 17:04:38

[PHP]

(7) 索引连接

scott@PENG>analyze table t compute statistics for table for all indexes for all indexed columns;

表已分析。

scott@PENG>set autotrace off

scott@PENG>select index_name,table_name,column_name,column_position from user_ind_columns

2where table_name = 'T' order by index_name,column_position;

INDEX_NAME
TABLE_NAME COLUMN_NAME
COLUMN_POSITION

------------------------------ ---------- ------------------------------ ---------------

IDX_IDTYPE
T
OBJECT_TYPE
1

IDX_UQ_OBID
T
OBJECT_ID
1

INDEX_T_ON
T
TEMPORARY
1

INDEX_T_ON
T
OBJECT_NAME
2

PK_T
T
OWNER
1

PK_T
T
OBJECT_NAME
2

已选择6行。

scott@PENG>set autotrace traceonly

scott@PENG>select /*+ INDEX_COMBINE(t IDX_UQ_OBID PK_T) */ object_id,owner,object_name

2from t

3where object_id between 100 and 2000

4and owner = 'SYS';

执行计划

----------------------------------------------------------

Plan hash value: 1608151126

----------------------------------------------------------------------------------------------------

----

| Id| Operation
| Name| Rows| Bytes |TempSpc| Cost (%CPU)| Time

|

----------------------------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT
|
| 275 |9350 | | 236 (3)| 00:00:

03 |

| 1 |TABLE ACCESS BY INDEX ROWID | T | 275 |9350 | | 236 (3)| 00:00:

03 |

| 2 | BITMAP CONVERSION TO ROWIDS|
| | | |
|

|

| 3 |BITMAP AND
|
| | | |
|

|

| 4 | BITMAP CONVERSION FROM ROWIDS|
| | | |
|

|

| 5 |SORT ORDER BY
|
| | | |
|

|

|*6 | INDEX RANGE SCAN | IDX_UQ_OBID | | | | 3 (0)| 00:00:

01 |

| 7 | BITMAP CONVERSION FROM ROWIDS|
| | | |
|

|

| 8 |SORT ORDER BY
|
| | | 568K|
|

|

|*9 | INDEX RANGE SCAN | PK_T| | | | 104 (1)| 00:00:

02 |

----------------------------------------------------------------------------------------------------

----

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("OBJECT_ID">=100 AND "OBJECT_ID"

.
[/PHP]