第三天3-4索引】【rowid】

select * from dba_tables where tablespace_name='TEST'
select * from dba_tablespaces
select COUNT(*) from TTT
alter table ttt move
insert into ttt select * from ttt
alter table ttt autoextend on next 1m maxsize unlimited
select * from dba_data_files where tablespace_name='TEST'
alter tablespace test add datafile 'C:\ORADATA\TEST\TEST2.DBF' size 50m autoextend on next 1m maxsize unlimited
commit
create index idx_ttt on ttt(object_id) tablespace test; --创建索引

b-tree:B树索引 balance
bitmap:位图索引 olap 数据仓库
SQL> select object_id from dba_objects where object_name='IDX_TTT';

 OBJECT_ID
----------
     13529

SQL> alter session set events 'immediate trace name treedump level 13529';--dump的文件放在C:\oracle\product\10.2.0\admin\test\udump下

会话已更改。
select * from dba_indexes where index_name='IDX_TTT'
----- begin tree dump
branch: 0x2800014 41943060 (0: nrow: 3, level: 2)
   branch: 0x2c0264f 46147151 (-1: nrow: 562, level: 1)
      leaf: 0x2800015 41943061 (-1: nrow: 513 rrow: 513)
      leaf: 0x2800016 41943062 (0: nrow: 513 rrow: 513)
      leaf: 0x2800017 41943063 (1: nrow: 513 rrow: 513)
      leaf: 0x2800018 41943064 (2: nrow: 513 rrow: 513)

select to_number('2800015','xxxxxxx') from dual;--16进制转10进制
TO_NUMBER('2800015','XXXXXXX')
41943061
select dbms_utility.data_block_address_file(41943061) from dual;--10进制数作参数，查询文件编号
DBMS_UTILITY.DATA_BLOCK_ADDRES
10
select dbms_utility.data_block_address_block(41943061) from dual;--10进制数作参数，查询block编号
DBMS_UTILITY.DATA_BLOCK_ADDRES
21
alter system dump datafile 10 block 21 --dump指定的block

kdxlende 0
kdxlenxt 41943062=0x2800016
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  02 80 00 9c 00 2d
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  02 80 01 3d 00 3c
row#2[8000] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  02 80 02 34 00 2d
row#3[7988] flag: ------, lock: 0, len=12

select utl_raw.cast_to_number('c103') from dual;
UTL_RAW.CAST_TO_NUMBER('C103') --最小Object_id
2
select object_id,rowid from ttt where object_id=2 order by object_id --索引实际上就是一个rowid的目录，而rowid实际上是数据的逻辑地址，索引就是给数据作了个目录
--rowid
--AAADTY    AAK    AAAACc   AAt
--段编号 文件编号 快编号 块上的行编号
SQL> set autotrace traceonly --traceonly执行sql时不显示执行结果
SQL> set linesize 200 --默认linesize 80
SQL> select * from ttt where object_id=2;

已选择64行。


执行计划
----------------------------------------------------------
Plan hash value: 774701505

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    85 |    36   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TTT  |     1 |    85 |    36   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       9367  consistent gets
       8287  physical reads
        116  redo size
       2118  bytes sent via SQL*Net to client
        444  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         64  rows processed
         
select * from dba_indexes where index_name='IDX_TTT'
select * from dba_tables where table_name='TTT'
exec dbms_stats.gather_table_stats('SYS','TTT');--重新统计

SQL> select *  from ttt where object_id=2;

已选择64行。


执行计划
----------------------------------------------------------
Plan hash value: 2391132391

-------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Ti
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |    64 |  5440 |    68   (0)| 00  --cost 访问block的次数相当
|   1 |  TABLE ACCESS BY INDEX ROWID| TTT     |    64 |  5440 |    68   (0)| 00
|*  2 |   INDEX RANGE SCAN          | IDX_TTT |    64 |       |     3   (0)| 00  --*表示where子句相关
-------------------------------------------------------------------------------

Predicate Information (identified by operation id): --Predicate位置信息 也就是where信息
---------------------------------------------------

   2 - access("OBJECT_ID"=2)                        --access 说明可以通过索引，过滤条件，如果是filter则不通过索引，不要再索引列添加函数，因为这样会使索引失效


统计信息
----------------------------------------------------------
         42  recursive calls
          0  db block gets
       9370  consistent gets
       7253  physical reads
          0  redo size
       2118  bytes sent via SQL*Net to client
        444  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         64  rows processed