Index analyze and rebuild using Oracle10g

/* 建立測試table */

SQL>CREATE TABLE TBLBASE
　　(
　　　　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


use index or not II using Oracle10g

/* 將先前的TESTIDX_IDX DROP */

SQL>DROP INDEX TESTIDX_IDX;

/* 以總合建一個INDEX */

SQL>CREATE INDEX TESTIDX_I ON TESTIDX(CATEGORY+X+Y);

/* 使用SQLPLUS並開啟TRACE */

SQL> set autotrace traceonly explain

/* 看總和為條件的查詢結果 */

SQL> select * from testidx where category+x+y=9;

執行計畫
----------------------------------------------------------
Plan hash value: 3683099191

----------------------------------------------------------------------------
|　Id　|　Operation　　　　　　　　　|　Name　|Rows|　Bytes　|Cost(%CPU)|Time　|
----------------------------------------------------------------------------
|　0　|SELECT STATEMENT　　　　　　|　　　　　|　4 |　208 　|　7　0)|00:00:01 |
|　1　|TABLE ACCESS BY INDEX ROWID|TESTIDX　|　4 |　208　|　7　(0)|00:00:01 |
|* 2　|INDEX RANGE SCAN　　　　　　|TESTIDX_I|　40|　　　　|　1　(0)|00:00:01|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
　　2 - access("CATEGORY"+"X"+"Y"=9)
Note
-----
　　- dynamic sampling used for this statement


/* 再看看各別為條件的查詢結果 */

SQL> select * from testidx where category=99 and x=99 and y=99;

執行計畫
----------------------------------------------------------
Plan hash value: 3565063929

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

Predicate Information (identified by operation id):
---------------------------------------------------
　1 - filter("CATEGORY"=99 AND "X"=99 AND "Y"=99)
Note
-----
　- dynamic sampling used for this statement

SQL> set autotrace off


use index or not using Oracle10g

/*建立測試table*/

CREATE TABLE TESTIDX
(
CATEGORY INTEGER,
X INTEGER,
Y INTEGER,
TOTAL INTEGER
);

/*新增測試資料*/

INSERT INTO TESTIDX
SELECT X,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 TESTIDX_IDX ON TESTIDX(CATEGORY,X,Y);

/*使用Sql Explain Tool*/

SQL> set autotrace traceonly explain

/*按所鍵index來查詢*/

SQL> select * from testidx where category=99 and x=99 and y=99;

執行計畫
----------------------------------------------------------
Plan hash value: 3158742342

------------------------------------------------------------------------------
|　Id　|　Operation　　　　　　　　|　Name　　　　|Rows|Bytes|Cost(%CPU)|Time　　|
------------------------------------------------------------------------------
|　0　|SELECT STATEMENT　　　　　　|　　　　　　　| 1 | 52 　|2 (0)　| 00:00:01 |
|　1　|TABLE ACCESS BY INDEX ROWID|TESTIDX　　　| 1 | 52 　|2 (0)　| 00:00:01 |
|*2　 |INDEX RANGE SCAN　　　　　　|TESTIDX_IDX　| 1 |　 　|1 (0)　| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
　2 - access("CATEGORY"=99 AND "X"=99 AND "Y"=99)

Note
-----
　- dynamic sampling used for this statement


/*測試使用部份index來查詢是否有用到index*/

SQL> select * from testidx where category = 10;

執行計畫
----------------------------------------------------------
Plan hash value: 3158742342

----------------------------------------------------------------------------------
|　Id　|Operation　　　　　　　　　　|　Name　　　　|Rows|Bytes|Cost(%CPU)| Time |
----------------------------------------------------------------------------------
|　0|SELECT STATEMENT　　　　　　　　|　　　　　　　|100| 5200 |　3　(0) | 00:00:01 |
|　1|TABLE ACCESS BY INDEX ROWID　　|TESTIDX　　　|100| 5200 |　3　(0) | 00:00:01 |
|* 2|INDEX RANGE SCAN　　　　　　　　|TESTIDX_IDX　|100|　　　|　2　(0) | 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
　2 - access("CATEGORY"=10)
Note
-----
　- dynamic sampling used for this statement


/*測試使用部份index來查詢是否有用到index*/

SQL> select * from testidx where category = 10 and y = 5;

執行計畫
----------------------------------------------------------
Plan hash value: 3158742342

-----------------------------------------------------------------------------------
|　Id　|Operation　　　　　　　　　　　|Name　　　　　|Rows|Bytes|Cost(%CPU)|Time |
-----------------------------------------------------------------------------------
|　0　|SELECT STATEMENT　　　　　　　|　　　　　　　　| 1 | 52 　| 3　(0)　　|00:00:01 |
|　1　|TABLE ACCESS BY INDEX ROWID　|TESTIDX　　　　| 1 | 52 　| 3　(0)　　|00:00:01 |
|* 2　|INDEX RANGE SCAN　　　　　　　| TESTIDX_IDX 　| 1 |　　　| 2　(0)　　|00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
　2 - access("CATEGORY"=10 AND "Y"=5)
　　　filter("Y"=5)
Note
-----
　- dynamic sampling used for this statement


/*測試使用總合來查詢是否有用到index*/

SQL> select * from testidx where category+x+y=9;

執行計畫
----------------------------------------------------------
Plan hash value: 3565063929

------------------------------------------------------------------------------
|　Id　|Operation　　　　　|　Name　　|　Rows　|　Bytes　|Cost(%CPU)|Time　　　　|
------------------------------------------------------------------------------
|　0　|SELECT STATEMENT　　|　　　　　|　　4 　|　208　　|　9　(12)　| 00:00:01 |
|* 1　|TABLE ACCESS FULL　| TESTIDX |　　4　 |　208　　|　9　(12)　| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CATEGORY"+"X"+"Y"=9)

Note
-----
- dynamic sampling used for this statement

SQL> set autotrace off