(
X INTEGER,
Y INTEGER,
XY INTEGER
);
/* 新增測試資料 */
SQL>insert into tblbase
select x,y,x*y
from (select level x from dual connect by level = 100),
(select level y from dual connect by level <= 100);
/* 建index */
SQL>create index TBLBASE_IDX on TBLBASE(X,Y) tablespace idx;
/* 由系統tables檢示一下所建立的index */
SQL>select index_name,index_type,status from all_indexes
where table_name = 'TBLBASE';
INDEX_NAME INDEX_TYPE STATUS
------------- -------------- --------------------------
TBLBASE_IDX NORMAL VALID
/* 由系統tables檢示一下所建立的index包含欄位 */
SQL>select INDEX_OWNER,COLUMN_NAME,COLUMN_POSITION
from ALL_IND_COLUMNS where INDEX_NAME = 'TBLBASE_IDX';
INDEX_OWNER COLUMN_NAME COLUMN_POSITION
------------- -------------- ---------------------------------
ORION X 1
ORION Y 2
/* 分析一下INDEX的結構 */
SQL>analyze index TBLBASE_IDX VALIDATE STRUCTURE;
已分析索引.
SQL>select height,blocks,lf_rows,lf_blks,br_rows,
br_blks,btree_space,used_space
from index_stats where name = 'TBLBASE_IDX';
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS BTREE_SPACE USED_SPACE
------ ------ -------- ------- ------- ------- ----------- -----------------
2 80 27821 68 67 1 552032 483450
/* 對TBLBASE進行DML後再分析 */
SQL> analyze index TBLBASE_IDX VALIDATE STRUCTURE;
已分析索引.
SQL>select height,blocks,lf_rows,lf_blks,br_rows,
br_blks,btree_space,used_space
from index_stats where name = 'TBLBASE_IDX';
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS BTREE_SPACE USED_SPACE
------ ------ ------- ------- ------- ------- ----------- ----------
2 80 27889 69 68 1 560032 484649
/* 對INDEX做REBUILD */
SQL> ALTER INDEX TBLBASE_IDX REBUILD;
已更改索引.
/* 對TBLBASE進行分析 */
SQL> analyze index TBLBASE_IDX VALIDATE STRUCTURE;
已分析索引.
SQL>select height,blocks,lf_rows,lf_blks,br_rows,
br_blks,btree_space,used_space
from index_stats where name = 'TBLBASE_IDX';
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS BTREE_SPACE USED_SPACE
------ ------ ------- ------- ------- ------- ----------- -------------
2 72 25106 61 60 1 496032 437230
