首页 » ORACLE » oracle 11g index IFS/IFFS

oracle 11g index IFS/IFFS

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

打赏

对不起,这篇文章暂时关闭评论。