Oracle表片段起因及解決辦法

今天發現在一個SQL查詢用不到索引，classid是建了索引的，如下：
select * from infobase where classid in(10001,10002,10003,10004,10005);
奇怪的發現在classid in(10001,10002)的值兩以上就用不索引，兩個以下就可以用到，開始懷疑是索引有問題，於是就重建下了下classid上的索引還是不行。從網上找到一篇文章才知道可能是表中存在片段的問題
於是用下面的步驟解決：
1、重建表：
create table infobase2 select * from infobase;
2、改以前的表名：
alter table infobase rename to infobase3;
3、改建立表名為以前表名：
alter table infobase2 rename to infobase;
4、建上索引：
create index classid_ind on infobase(classid);

可是過了一天問題又出現了，索引又是不能使用了，然後執行下面的語句解決：
ANALYZE TABLE INFOBASE compute Statistics;
或是
ANALYZE TABLE INFOBASE estimate STATISTICS SAMPLE 50 PERCENT ;
--注意：50 PERCENT 值太小索引可能還是不起作用，我就開始用20 PERCENT 時，索引還是用不上。

跟表片段有關的基礎知識：
什麼是水線(High Water Mark)?
----------------------------
所有的Oracle段(segments，在此，為了理解方便，建議把segment作為表的一個同義字) 都有一個在段內容納資料的上限，我們把這個上限稱為"high water mark"或HWM。這個HWM是一個標記，用來說明已經有多少沒有使用的資料區塊分配給這個segment。HWM通常增長的幅度為一次5個資料區塊，原則上HWM只會增大，不會縮小，即使將表中的資料全部刪除，HWM還是為原值，由於這個特點，使HWM很象一個水庫的曆史最高水位，這也就是HWM的原始含義，當然不能說一個水庫沒水了，就說該水庫的曆史最高水位為0。但是如果我們在表上使用了truncate命令，則該表的HWM會被重新置為0。

HWM資料庫的操作有如下影響：
a) 全表掃描通常要讀出直到HWM標記的所有的屬於該表資料庫塊，即使該表中沒有任何資料。
b) 即使HWM以下有閒置資料庫塊，鍵入在插入資料時使用了append關鍵字，則在插入時使用HWM以上的資料區塊，此時HWM會自動增大。

如何知道一個表的HWM？
a) 首先對錶進行分析:
ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;
b) SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = <tablename>;

BLOCKS 列代表該表中曾經使用過得資料庫塊的數目，即水線。
EMPTY_BLOCKS 代表分配給該表，但是在水線以上的資料庫塊，即從來沒有使用的資料區塊。

讓我們以一個有28672行的BIG_EMP1表為例進行說明：
1)SQL> SELECT segment_name,segment_type,blocks
FROM dba_segments
WHERE segment_name='BIG_EMP1';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
----------------------------- ----------------- ---------- -------
BIG_EMP1 TABLE 1024 2
1 row selected.

2) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

3) SQL> SELECT table_name,num_rows,blocks,empty_blocks
FROM user_tables
WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 28672 700 323
1 row selected.

注意：
BLOCKS + EMPTY_BLOCKS (700+323=1023)比DBA_SEGMENTS.BLOCKS少個資料庫塊，這是因為有一個資料庫塊被保留用作segment header。DBA_SEGMENTS.BLOCKS 表示分配給這個表的所有的資料庫塊的數目。USER_TABLES.BLOCKS表示已經使用過的資料庫塊的數目。

4) SQL> SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
FROM big_emp1;
Used
----------
700
1 row selected.

5) SQL> DELETE from big_emp1;
28672 rows processed.

6) SQL> commit;
Statement processed.

7) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

8) SQL> SELECT table_name,num_rows,blocks,empty_blocks
FROM user_tables
WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 0 700 323
1 row selected.

9) SQL> SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
FROM big_emp1;
Used
----------
0 -- 這表名沒有任何資料庫塊容納資料，即表中無資料
1 row selected.

10) SQL> TRUNCATE TABLE big_emp1;
Statement processed.

11) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

12) SQL> SELECT table_name,num_rows,blocks,empty_blocks
FROM user_tables
WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 0 0 511
1 row selected.

13) SQL> SELECT segment_name,segment_type,blocks
FROM dba_segments
WHERE segment_name='BIG_EMP1';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
----------------------------- ----------------- ---------- -------
BIG_EMP1 TABLE 512 1
1 row selected.

注意:
TRUNCATE命令回收了由delete命令產生的空閑空間，注意該表分配的空間由原先的1024塊降為512塊。
為了保留由delete命令產生的空閑空間，可以使用
TRUNCATE TABLE big_emp1 REUSE STORAGE
用此命令後，該表還會是原先的1024塊。

行連結(Row chaining) 與行遷移(Row Migration)
當一行的資料過長而不能插入一個單個資料區塊中時，可能發生兩種事情：行連結(row chaining)或行遷移(row migration)。

1 2 3 4 下一頁