SQL> conn anbob/anbob
已连接。
SQL> select count(*) from all_objects;
COUNT(*)
----------
53769
SQL> create table testidx as select * from all_objects where 1=0;
表已创建。
SQL> insert into testidx select * from all_objects where object_id is not null order by object_id desc;
已创建53770行。
SQL> create index ind_testidx_oid on testidx(object_id);
索引已创建。
SQL> analyze table test compute statistics for table for all columns for all indexes;
表已分析。
SQL> select /*+index(t)*/ object_id from testidx t;
已选择53770行。
执行计划
----------------------------------------------------------
Plan hash value: 3045815126
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48334 | 613K| 135 (1)| 00:00:02 |
| 1 | INDEX FULL SCAN | IND_TESTIDX_OID | 48334 | 613K| 135 (1)| 00:00:02 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3699 consistent gets
0 physical reads
0 redo size
783831 bytes sent via SQL*Net to client
39840 bytes received via SQL*Net from client
3586 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
53770 rows processed
SQL> select object_id from testidx t;
已选择53770行。
执行计划
----------------------------------------------------------
Plan hash value: 3014170145
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48334 | 613K| 38 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IND_TESTIDX_OID | 48334 | 613K| 38 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3705 consistent gets
0 physical reads
0 redo size
783831 bytes sent via SQL*Net to client
39840 bytes received via SQL*Net from client
3586 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
53770 rows processed
SQL> col segment_name for a30
SQL> select segment_name,segment_type,bytes,blocks from user_segments where segment_name='IND_TESTIDX_OID'
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------------ ------------------ ---------- ----------
IND_TESTIDX_OID INDEX 2097152 256
SQL> select object_id from testidx where rownum<10;
OBJECT_ID
----------
99
115
116
271
356
358
359
361
362
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 3647722124
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FAST FULL SCAN| IND_TESTIDX_OID | 48334 | 613K| 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
138 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> select /*+ index(testidx)*/object_id from testidx where rownum<10;
OBJECT_ID
----------
99
115
116
271
356
358
359
361
362
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 2506675983
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN| IND_TESTIDX_OID | 48334 | 613K| 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
133 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> run
1* select /*+index_ffs(testidx ind_testidx_oid)*/object_id from testidx order by object_id
已选择53770行。
执行计划
----------------------------------------------------------
Plan hash value: 3496029220
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48334 | 613K| | 271 (2)| 00:00:04 |
| 1 | SORT ORDER BY | | 48334 | 613K| 1912K| 271 (2)| 00:00:04 |
| 2 | INDEX FAST FULL SCAN| IND_TESTIDX_OID | 48334 | 613K| | 38 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
127 consistent gets
0 physical reads
0 redo size
783831 bytes sent via SQL*Net to client
39840 bytes received via SQL*Net from client
3586 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
53770 rows processed
SQL> select object_id from testidx order by object_id;
已选择53770行。
执行计划
----------------------------------------------------------
Plan hash value: 3045815126
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48334 | 613K| 135 (1)| 00:00:02 |
| 1 | INDEX FULL SCAN | IND_TESTIDX_OID | 48334 | 613K| 135 (1)| 00:00:02 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3699 consistent gets
0 physical reads
0 redo size
783831 bytes sent via SQL*Net to client
39840 bytes received via SQL*Net from client
3586 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
53770 rows processed
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
SQL> col segment_name for a30
SQL> run
1 select segment_name,segment_type,bytes,blocks from user_segments
2* where segment_name='IND_TESTIDX_OID'
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------------ ------------------ ---------- ----------
IND_TESTIDX_OID INDEX 2097152 256
SQL> select object_id from user_objects where object_name='IND_TESTIDX_OID';
OBJECT_ID
----------
71073
SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 71073';
ERROR:
ORA-01031: 权限不足
SQL> conn / as sysdba
已连接。
SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 71073';
会话已更改。
trace file 内容如下：
Trace file d:\\oracle\\diag\\rdbms\\orcl\\orcl\\trace\\orcl_ora_2880.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows XP Version V5.1 Service Pack 3
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:477M/2008M, Ph+PgF:1270M/2859M, VA:1281M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 2880, image: ORACLE.EXE (SHAD)
*** 2011-06-24 15:40:07.334
*** SESSION ID:(555.17) 2011-06-24 15:40:07.334
*** CLIENT ID:() 2011-06-24 15:40:07.334
*** SERVICE NAME:(SYS$USERS) 2011-06-24 15:40:07.334
*** MODULE NAME:(sqlplus.exe) 2011-06-24 15:40:07.334
*** ACTION NAME:() 2011-06-24 15:40:07.334
----- begin tree dump
branch: 0x10003b4 16778164 (0: nrow: 120, level: 1)
leaf: 0x10003b5 16778165 (-1: nrow: 479 rrow: 479)
leaf: 0x10003b6 16778166 (0: nrow: 479 rrow: 479)
leaf: 0x10003b7 16778167 (1: nrow: 479 rrow: 479)
leaf: 0x10003b8 16778168 (2: nrow: 479 rrow: 479)
leaf: 0x10003b9 16778169 (3: nrow: 479 rrow: 479)
leaf: 0x10003ba 16778170 (4: nrow: 479 rrow: 479)
leaf: 0x10003bb 16778171 (5: nrow: 479 rrow: 479)
leaf: 0x10003bc 16778172 (6: nrow: 479 rrow: 479)
leaf: 0x10003bd 16778173 (7: nrow: 479 rrow: 479)
leaf: 0x10003be 16778174 (8: nrow: 472 rrow: 472)
leaf: 0x10003bf 16778175 (9: nrow: 449 rrow: 449)
leaf: 0x10003c0 16778176 (10: nrow: 449 rrow: 449)
leaf: 0x10003c2 16778178 (11: nrow: 449 rrow: 449)
leaf: 0x10003c3 16778179 (12: nrow: 449 rrow: 449)
leaf: 0x10003c4 16778180 (13: nrow: 449 rrow: 449)
leaf: 0x10003c5 16778181 (14: nrow: 449 rrow: 449)
leaf: 0x10003c6 16778182 (15: nrow: 449 rrow: 449)
leaf: 0x10003c7 16778183 (16: nrow: 449 rrow: 449)
leaf: 0x10003c8 16778184 (17: nrow: 449 rrow: 449)
leaf: 0x10003c9 16778185 (18: nrow: 449 rrow: 449)
leaf: 0x10003ca 16778186 (19: nrow: 449 rrow: 449)
leaf: 0x10003cb 16778187 (20: nrow: 449 rrow: 449)
leaf: 0x10003cc 16778188 (21: nrow: 449 rrow: 449)
leaf: 0x10003cd 16778189 (22: nrow: 449 rrow: 449)
leaf: 0x10003ce 16778190 (23: nrow: 449 rrow: 449)
leaf: 0x10003cf 16778191 (24: nrow: 449 rrow: 449)
leaf: 0x10003d0 16778192 (25: nrow: 449 rrow: 449)
leaf: 0x10003d2 16778194 (26: nrow: 449 rrow: 449)
leaf: 0x10003d3 16778195 (27: nrow: 449 rrow: 449)
leaf: 0x10003d4 16778196 (28: nrow: 449 rrow: 449)
leaf: 0x10003d5 16778197 (29: nrow: 449 rrow: 449)
leaf: 0x10003d6 16778198 (30: nrow: 449 rrow: 449)
leaf: 0x10003d7 16778199 (31: nrow: 449 rrow: 449)
leaf: 0x10003d8 16778200 (32: nrow: 449 rrow: 449)
leaf: 0x10003d9 16778201 (33: nrow: 449 rrow: 449)
leaf: 0x10003da 16778202 (34: nrow: 449 rrow: 449)
leaf: 0x10003db 16778203 (35: nrow: 449 rrow: 449)
leaf: 0x10003dc 16778204 (36: nrow: 449 rrow: 449)
leaf: 0x10003dd 16778205 (37: nrow: 449 rrow: 449)
leaf: 0x10003de 16778206 (38: nrow: 449 rrow: 449)
leaf: 0x10003df 16778207 (39: nrow: 449 rrow: 449)
leaf: 0x10003e0 16778208 (40: nrow: 449 rrow: 449)
leaf: 0x10003e2 16778210 (41: nrow: 449 rrow: 449)
leaf: 0x10003e3 16778211 (42: nrow: 449 rrow: 449)
leaf: 0x10003e4 16778212 (43: nrow: 449 rrow: 449)
leaf: 0x10003e5 16778213 (44: nrow: 449 rrow: 449)
leaf: 0x10003e6 16778214 (45: nrow: 449 rrow: 449)
leaf: 0x10003e7 16778215 (46: nrow: 449 rrow: 449)
leaf: 0x10003e8 16778216 (47: nrow: 449 rrow: 449)
leaf: 0x10003e9 16778217 (48: nrow: 449 rrow: 449)
leaf: 0x10003ea 16778218 (49: nrow: 449 rrow: 449)
leaf: 0x10003eb 16778219 (50: nrow: 449 rrow: 449)
leaf: 0x10003ec 16778220 (51: nrow: 449 rrow: 449)
leaf: 0x10003ed 16778221 (52: nrow: 449 rrow: 449)
leaf: 0x10003ee 16778222 (53: nrow: 449 rrow: 449)
leaf: 0x10003ef 16778223 (54: nrow: 449 rrow: 449)
leaf: 0x10003f0 16778224 (55: nrow: 449 rrow: 449)
leaf: 0x10003f2 16778226 (56: nrow: 449 rrow: 449)
leaf: 0x10003f3 16778227 (57: nrow: 449 rrow: 449)
leaf: 0x10003f4 16778228 (58: nrow: 449 rrow: 449)
leaf: 0x10003f5 16778229 (59: nrow: 449 rrow: 449)
leaf: 0x10003f6 16778230 (60: nrow: 449 rrow: 449)
leaf: 0x10003f7 16778231 (61: nrow: 449 rrow: 449)
leaf: 0x10003f8 16778232 (62: nrow: 449 rrow: 449)
leaf: 0x10003f9 16778233 (63: nrow: 449 rrow: 449)
leaf: 0x10003fa 16778234 (64: nrow: 449 rrow: 449)
leaf: 0x10003fb 16778235 (65: nrow: 449 rrow: 449)
leaf: 0x10003fc 16778236 (66: nrow: 449 rrow: 449)
leaf: 0x10003fd 16778237 (67: nrow: 449 rrow: 449)
leaf: 0x10003fe 16778238 (68: nrow: 449 rrow: 449)
leaf: 0x10003ff 16778239 (69: nrow: 449 rrow: 449)
leaf: 0x1000400 16778240 (70: nrow: 449 rrow: 449)
leaf: 0x1000402 16778242 (71: nrow: 449 rrow: 449)
leaf: 0x1000403 16778243 (72: nrow: 449 rrow: 449)
leaf: 0x1000404 16778244 (73: nrow: 449 rrow: 449)
leaf: 0x1000405 16778245 (74: nrow: 449 rrow: 449)
leaf: 0x1000406 16778246 (75: nrow: 449 rrow: 449)
leaf: 0x1000407 16778247 (76: nrow: 449 rrow: 449)
leaf: 0x1000408 16778248 (77: nrow: 449 rrow: 449)
leaf: 0x1000709 16779017 (78: nrow: 449 rrow: 449)
leaf: 0x100070a 16779018 (79: nrow: 449 rrow: 449)
leaf: 0x100070b 16779019 (80: nrow: 449 rrow: 449)
leaf: 0x100070c 16779020 (81: nrow: 449 rrow: 449)
leaf: 0x100070d 16779021 (82: nrow: 449 rrow: 449)
leaf: 0x100070e 16779022 (83: nrow: 449 rrow: 449)
leaf: 0x100070f 16779023 (84: nrow: 449 rrow: 449)
leaf: 0x1000710 16779024 (85: nrow: 449 rrow: 449)
leaf: 0x1000712 16779026 (86: nrow: 449 rrow: 449)
leaf: 0x1000713 16779027 (87: nrow: 449 rrow: 449)
leaf: 0x1000714 16779028 (88: nrow: 449 rrow: 449)
leaf: 0x1000715 16779029 (89: nrow: 449 rrow: 449)
leaf: 0x1000716 16779030 (90: nrow: 449 rrow: 449)
leaf: 0x1000717 16779031 (91: nrow: 449 rrow: 449)
leaf: 0x1000718 16779032 (92: nrow: 449 rrow: 449)
leaf: 0x1000719 16779033 (93: nrow: 449 rrow: 449)
leaf: 0x100071a 16779034 (94: nrow: 449 rrow: 449)
leaf: 0x100071b 16779035 (95: nrow: 449 rrow: 449)
leaf: 0x100071c 16779036 (96: nrow: 449 rrow: 449)
leaf: 0x100071d 16779037 (97: nrow: 449 rrow: 449)
leaf: 0x100071e 16779038 (98: nrow: 449 rrow: 449)
leaf: 0x100071f 16779039 (99: nrow: 449 rrow: 449)
leaf: 0x1000720 16779040 (100: nrow: 449 rrow: 449)
leaf: 0x1000722 16779042 (101: nrow: 449 rrow: 449)
leaf: 0x1000723 16779043 (102: nrow: 449 rrow: 449)
leaf: 0x1000724 16779044 (103: nrow: 449 rrow: 449)
leaf: 0x1000725 16779045 (104: nrow: 449 rrow: 449)
leaf: 0x1000726 16779046 (105: nrow: 449 rrow: 449)
leaf: 0x1000727 16779047 (106: nrow: 449 rrow: 449)
leaf: 0x1000728 16779048 (107: nrow: 449 rrow: 449)
leaf: 0x1000729 16779049 (108: nrow: 449 rrow: 449)
leaf: 0x100072a 16779050 (109: nrow: 449 rrow: 449)
leaf: 0x100072b 16779051 (110: nrow: 449 rrow: 449)
leaf: 0x100072c 16779052 (111: nrow: 449 rrow: 449)
leaf: 0x100072d 16779053 (112: nrow: 449 rrow: 449)
leaf: 0x100072e 16779054 (113: nrow: 449 rrow: 449)
leaf: 0x100072f 16779055 (114: nrow: 449 rrow: 449)
leaf: 0x1000730 16779056 (115: nrow: 449 rrow: 449)
leaf: 0x100078b 16779147 (116: nrow: 449 rrow: 449)
leaf: 0x100078c 16779148 (117: nrow: 449 rrow: 449)
leaf: 0x100078d 16779149 (118: nrow: 46 rrow: 46)
----- end tree dump
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
