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語句：

?

轉載于:https://www.cnblogs.com/sopost/archive/2010/09/16/2190083.html

總結

以上是生活随笔為你收集整理的oracle表分析 示例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。