表的索引

create table weisi_t as select * from dba_objects;

set autotrace on

set timing on

set linesize 200

select * from weisi_t where object_id=10;

create index index_weisi on weisi_t(object_id);

select * from weisi_t where object_id=10;

update weisi_t set object_id=10 where 1=1;

1：索引的分類

? ? 1）btree(balance tree)索引

? ? ? 索引三大特點：高度低、存儲列值市俊、結(jié)構(gòu)有序

? ? 2）位圖索引

? ? 3）函數(shù)索引

2：詳解BTREE索引

? 2.1）BTREE索引的結(jié)構(gòu)和原理

? ? ? ? 原理：索引入口頭部震鹉、列長度、列值闺金、ROWID、

? ? ? ? ? ? ? ROWID:數(shù)據(jù)詳細(xì)地址，通過ROWID快速定位某行具體的數(shù)據(jù)位置凤粗，64（A-Z,a-z,0-9,+,/),A=0考赛，a=27,0=53,+=62,/=63惕澎。

? ? ? ? ? ? ? ? ? ? 六位 data object_id?

? ? ? ? ? ? ? ? ? ? ? ? 不等于object_id

? ? ? ? ? ? ? ? ? ? ? ? select object_id,data_object_id from dba_objects;

? ? ? ? ? ? ? ? ? ? 三位相對文件號

? ? ? ? ? ? ? ? ? ? ? ? select file#,Rfile#,ts#,name from v$datafile

? ? ? ? ? ? ? ? ? ? 六位數(shù)據(jù)塊號

? ? ? ? ? ? ? ? ? ? ? ? 范圍：數(shù)據(jù)文件

? ? ? ? ? ? ? ? ? ? 三位行號

? ? ? ? ? ? ? rowid轉(zhuǎn)化為文件號和塊號? ? ?

? ? ? ? ? ? ? select

? ? ? ? ? ? ? ? ? dbms_rowid.rowid_object('AAASUVAABAAAU8LAAl') object,

? ? ? ? ? ? ? ? ? dbms_rowid.rowid_relative_fno('AAASUVAABAAAU8LAAl') file_num,

? ? ? ? ? ? ? ? ? dbms_rowid.rowid_block_number('AAASUVAABAAAU8LAAl') block_num,

? ? ? ? ? ? ? ? ? dbms_rowid.rowid_row_number('AAASUVAABAAAU8LAAl') row_number

? ? ? ? ? ? ? from dual;?

? ? ? ? 從ROWID推出各種的ORACLE數(shù)據(jù)限制? ? ? ? ? ? ? ? ?

? ? ? ? ROWID：以64位編碼顯示，但是以二進(jìn)制的方式存儲

? ? ? ? ? ? ? ? rowid用10個字節(jié)存儲颜骤，即80bit

? ? ? ? ? ? ? ? 32bit data_object_id? 4G個對象

? ? ? ? ? ? ? ? 10bit 相對文件號? ? ? 一個表空間最多只有1023個文件唧喉，不是1024，因為沒有O文件號

? ? ? ? ? ? ? ? 22bit 塊號? ? ? ? ? ? 一個數(shù)據(jù)文件最大4M個BLOCK塊及32G

? ? ? ? ? ? ? ? 16bit 行號? ? ? ? ? ? 64K行數(shù)據(jù)

? ? ? ? ? ? ? alter system dump datafile 1 block 85771;

? ? ? ? ? ? ? select file#,Rfile#,ts#,name from v$datafile

? ? ? ? ? ? ? select spid from v$process where addr in (select paddr from v$session where

? ? ? ? ? ? sid=(select sid from v$mystat where rownum=1));

? ? ? ? ? ? ? show parameter dump

? ? ? ? ? ? ? select object_name,dump(object_name,1018) from weisi_t where object_id=222;

? ? ? ? ? ? ? select rowid,id,code,name,dump(name,1018) from characterset_test;

? ? ? ? ? ? ? AAASDEAABAAAVUxAAB

? ? ? ? ? ? ? ? select

? ? ? ? ? ? ? ? ? dbms_rowid.rowid_object('AAASDEAABAAAVUxAAB') object,

? ? ? ? ? ? ? ? ? dbms_rowid.rowid_relative_fno('AAASDEAABAAAVUxAAB') file_num,

? ? ? ? ? ? ? ? ? dbms_rowid.rowid_block_number('AAASDEAABAAAVUxAAB') block_num ,

? ? ? ? ? ? ? ? ? dbms_rowid.rowid_row_number('AAASDEAABAAAVUxAAB') row_number

? ? ? ? ? ? ? from dual;

? ? ? ? ? ? ? ? ? alter system dump datafile 1 block 87345;

? ? ? ? ? ? ? ? select spid from v$process where addr in (select paddr from v$session where

? ? ? ? ? ? ? sid=(select sid from v$mystat where rownum=1));

? ? ? ? select object_type from dba_objects group by object_type order by 1;

? ? ? ? select * from dba_index

? ? ? 2.2）索引結(jié)構(gòu)

? ? ? ? ? ? 索引是物理結(jié)構(gòu)還是邏輯結(jié)構(gòu)忍抽？

? ? ? 2.3）索引的三大特性

? ? ? ? ? ? 1：索引高度低

? ? ? ? ? ? ? 2.3.1.1)構(gòu)建大表和普通表

? ? ? ? ? ? ? create table normal_t as select * from dba_objects;

? ? ? ? ? ? ? create table normal_t_100 as select * from dba_objects where rownum< 100;

? ? ? ? ? ? ? create table big_t as select * from dba_objects;

? ? ? ? ? ? ? insert into big_t select * from big_t;

? ? ? ? ? ? ? select count(1) from big_t;

? ? ? ? ? ? ? 2.3.1.2)創(chuàng)建索引

? ? ? ? ? ? ? ? create index big_t_index on big_t(object_id);

? ? ? ? ? ? ? ? create index normal_index on normal_t(object_id);

? ? ? ? ? ? ? ? create index normal_t_100_index on normal_t_100(object_id);

? ? ? ? ? ? ? ? select segment_name,sum(bytes)/1024/1024 M from user_segments

? ? ? ? ? ? ? ? where segment_name in ('BIG_T_INDEX','NORMAL_INDEX','NORMAL_T_100_INDEX')group by segment_name;

? ? ? ? ? ? ? 2.3.1.3) 查看索引高度

? ? ? ? ? ? ? col index_name format a40

? ? ? ? ? ? ? set linesize 150

? ? ? ? ? ? ? select?

? ? ? ? ? ? ? ? ? ? ? index_name

? ? ? ? ? ? ? ? ? ? ? ,blevel

? ? ? ? ? ? ? ? ? ? ? ,leaf_blocks

? ? ? ? ? ? ? ? ? ? ? ,num_rows

? ? ? ? ? ? ? ? ? ? ? ,distinct_keys

? ? ? ? ? ? ? from user_ind_statistics

? ? ? ? ? ? ? where table_name? in ('BIG_T','NORMAL_T','NORMAL_T_100');? ?

? ? ? ? ? ? ? 2.3.1.4) 索引是性能提升利器

? ? ? ? ? ? ? ? cat $ORACLE_HOME/sqlplus/admin/plustrce.sql

? ? ? ? ? ? ? ? sqlplus / as sysdba

? ? ? ? ? ? ? ? @$ORACLE_HOME/sqlplus/admin/plustrce.sql

? ? ? ? ? ? ? ? grant plustrace to weisi;

? ? ? ? ? ? ? ? set autotrace on

? ? ? ? ? ? ? ? set linesize 200

? ? ? ? ? ? ? ? set timing on

? ? ? ? ? ? ? ? select * from big_t where object_id=111;

? ? ? ? ? ? ? ? create index big_t_index on big_t(object_id);

? ? ? ? ? ? ? ? select * from big_t where object_id=111;

? ? ? ? ? ? ? ? select * from normal_t where object_id=111;

? ? ? ? ? ? ? ? select * from normal_t_100 where object_id=111;

? ? ? ? ? ? ? ? 2.3.1.5) 分區(qū)索引(誤區(qū))

? ? ? ? ? ? ? ? ? 分區(qū)索引：全局索引和局部索引

? ? ? ? ? ? ? ? ? 分區(qū)條件和索引不同字段

? ? ? ? ? ? ? ? ? create table part_t

? ? ? ? ? ? ? ? ? ? ? ? partition by range(object_id)

? ? ? ? ? ? ? ? ? ? ? ? (

? ? ? ? ? ? ? ? ? ? ? ? ? partition p1 values less than (10000),

? ? ? ? ? ? ? ? ? ? ? ? ? partition p2 values less than (20000),

? ? ? ? ? ? ? ? ? ? ? ? ? partition p3 values less than (30000),

? ? ? ? ? ? ? ? ? ? ? ? ? partition p4 values less than (40000),

? ? ? ? ? ? ? ? ? ? ? ? ? partition p5 values less than (50000),

? ? ? ? ? ? ? ? ? ? ? ? ? partition p6 values less than (60000),

? ? ? ? ? ? ? ? ? ? ? ? ? partition p7 values less than (70000),

? ? ? ? ? ? ? ? ? ? ? ? ? partition p_max values less than (maxvalue)

? ? ? ? ? ? ? ? ? ? ? ? ? )

? ? ? ? ? ? ? ? ? ? ? ? ? as select * from dba_objects;

? ? ? ? ? ? ? ? ? ? create index part_t_local on part_t(data_object_id) local;

? ? ? ? ? ? ? ? ? create table part_t1