oracle表分析 樣本

drop table test;

select count(*) from test;
--建立測試表
create table test
(
id number(9),
nick varchar2(30)
);

--插入測試資料
begin
  for i in 1..100000 loop
        insert into test(id) values(i);
  end loop;
  commit;
end;

select * from test;

--更新nick欄位，使資料發生嚴重傾斜
update test set nick='abc' where rownum<99999;

--建立索引
create index idx_test_nick on test(nick);

update test set nick='def' where nick is null;

--只對索引進行分析
analyze index idx_test_nick compute statistics;

select * from user_indexes;
--查看索引名,對應儲存的資料區塊,不同的key數量,記錄數（行數）的分析資訊
select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS
  from user_indexes
 where index_name = 'IDX_TEST_NICK';

--dba_tab_col_statistics

--查看錶的統計資訊
select COLUMN_NAME, NUM_BUCKETS, num_distinct
  from USER_tab_columns
 where table_name = 'TEST';

select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)

select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--由上可以看到,對索引分析之後,sql的執行路徑都是基於規則的,索引的欄位的位移
--先根據索引找到rowid,然後再根據rowid讀取記錄,這個過程肯定比全表掃描讀取記錄要慢

--user_part_col_statistics  分區分析資訊

--分析表的第二列nick
analyze table test compute statistics for columns size 2 nick;

select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--根據上面的執行計畫，還是按照規則來執行的

--分析表
analyze table test compute statistics for table;

select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
          1499970)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
          99970)
--分析表之後，完全按照成本來執行

--刪除所有的統計資料，並只對錶與列進行分析，不分析索引，
--ORACLE使用CBO的最佳化器，併產生了正確的執行計畫
analyze table test delete statistics;

--分析列nick
analyze table test compute statistics for table for columns size 2 nick;

select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
          1499970)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
          99970)
  
--
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=30)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt
          es=30)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
          =1 Card=2)

--建立TEST表ID列上的索引，但不對索引進行分析
create index idx_test_id on test(id);
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
          000)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
          Bytes=15000)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
           Card=400)

--當條件中即有id，又有nick時，因為nick上有長條圖,ORACLE知道nick='abc'的值特別的多，所以不走IDX_TEST_NICK索引,走IDX_TEST_ID上的索引
select * from test where id=5 and nick='abc';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
          000)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
          Bytes=15000)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
           Card=400)

--當條件中即有id，又有nick時，因為nick上有長條圖,ORACLE知道nick='def'的值特別的少，所以走IDX_TEST_NICK上的索引,不走IDX_TEST_ID索引
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=15)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
          =1 Card=2)

select * from test where nick='def'  and id=5;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=15)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
          =1 Card=2)

--在分析ID列後，ORACLE發現ID列的選擇度更高，所以不再選擇IDX_TEST_NICK索引，而是選擇IDX_TEST_ID
analyze table test compute statistics for columns size 1 id;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=7)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
           Card=1)
          
/*
下面來看另外一種情況，我們刪除所有的統計資料，然後在ID列上建立唯一索引,在此條件下，
只分析表與分析列nick,我們看到ORACLE走了正確的執行計畫，
走了UK_TEST_ID,其實從這裡也給我們帶來很多的啟示：
在主鍵與唯一鍵約束的列上是否需要長條圖的問題?
如果在這些列上有像這樣的查詢where id > 100 and id < 1000,
我們還是需要有長條圖的，但除此之外，好像真的沒有長條圖的必要了!
*/
analyze table test delete statistics;   
drop index idx_test_id;
create unique index uk_test_id on test(id);
--分析表的第二列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=15)

   2    1     INDEX (UNIQUE SCAN) OF 'UK_TEST_ID' (UNIQUE) (Cost=1 Car
          d=100000)
         
從以上一系列的實驗可以看出，對ORACLE的最佳化器CBO來說，表的分析與列的分析才是最重要的，索引的分析次之。還有我們可以考慮我們的哪些列上需要長條圖，對於bucket的個數問題,oracle的預設值是75個，所以根據你的應用規則，選擇合適的桶數對效能也是有協助的。因為不必要的桶的個數的大量增加，必然會帶來SQL語句硬解析時產生執行計畫的複雜度問題。

完全計演算法： analyze table abc compute statistics;
抽樣估演算法(抽樣20%)： analyze table abc estimate statistics sample 20 percent

對錶作完全計算所花的時間相當於做全表掃描，抽樣估演算法由於採用抽樣，比完全計演算法的產生統計速度要快，假如不是要求要有精確資料的話，盡量採用抽樣分析法。建議對錶分析採用抽樣估算，對索引分析可以採用完全計算。
我們可以採用以下方法，對資料庫的表和索引及簇表定期分析產生統計資訊，保證應用的正常效能

產生索引分析，表分析的sql語句：