【正文】 集越耗時 ? method_opt=選項 for all [ indexed | hidden ] columns size { 1 254 } ? degree=并行度 默認為表的并行度 gather_index_stats ? ownname=用戶名 ? tabname=表名 ? partname=分區(qū)名 ? estimate_percent=比例 比例越大，收集越耗時 ? degree=并行度 默認為表的并行度 查看表的統(tǒng)計信息 ? { USER | ALL | DBA }_TABLES num_rows / blocks / avg_row_len / last_analyzed 查看索引的統(tǒng)計信息 ? { USER | ALL | DBA }_INDEXES NUM_ROWS / DISTINCT / LEAF_BLOCKS / CF / ALFBKEY ? CF=一個索引葉塊對應的數(shù)據(jù)塊的數(shù)量，越小表示一索引越有效 ? ALFBKEY=每一個值占據(jù)的葉塊的數(shù)量 查看列的統(tǒng)計信息 ? { USER | ALL | DBA }_tab_col_statistics num_distinct / num_nulls / num_buckets / density ? num_buckets = histograms中組的數(shù)量 ? Density = 1 / num_distinct 查看 histograms的信息 ? {user | all | dba}_histograms ? {user | all | dba}_part_histograms ? {user | all | dba}_subpart_histograms ? {user | all | dba}_tab_col_statistics ? 不適合在分布均勻的列或在 where中用 bind variable來查詢的列 ? 在收集時需要在 method_opt中指定 size值 CBO使用實列 ? 使用 Function Index ? 選擇一個例值分布不平均的表 ? 學會收集統(tǒng)計信息 ? 使用 dbms_job來定時收集統(tǒng)計信息 ? 查看統(tǒng)計信息 XSTJXCRB查詢優(yōu)化 ? 思路 使用實體化視圖實現(xiàn) Query Rewrite ? P202104分區(qū)記錄數(shù)： 88512條 ? 按 RQ0000和 SPDM00匯總： 6899條 ? 利用 CBO的 Query Rewrite技術，按 rq0000和 spdm00匯總好數(shù)據(jù)存放在中間表 (實體化視圖 )中 理解實體化視圖 SQL Oracle XSTJXCRB表 XSTJXCRB 實體化視圖 Query Rewrite SELECT COL2,SUM(COL2) COL2 FROM … GROUP BY COL1 SELECT SUM(COL2) FROM … GROUP BY COL1 SELECT COL1,COL2 FROM … 實體化視圖的刷新 ? Complete 可以不用實體化視圖日志 ? Fast 需要實體化視圖日志的支持，可以支持 Join和 Summary操作 ? Fast refresh on mit 需要實體化視圖日志的支持，可以支持 Join和 Summary操作 可以用于 Query Rewrite的條件 ? 不可以使用 Rownum, sysdate等值變化不固定的函數(shù) ? 不支持 raw,long raw和 ref object類型 ? 不支持 union , minus, intersect等類型語句 ? 一個表不要引用兩次 ? 不能在 sys用戶下 ? 不支持 connect by語句 刷新 : Fast (關聯(lián)語句 ) ? 給所有關聯(lián)的表建實體化視圖日志需要有with rowid選項 ? 在 select的列中包括所有表的 rowid列 ? 不能有 group by語句 ? 在外連接的另一方必須有 unique約束 ? 例子 select rowid_a, a.*, rowid_b, b.* from a, b where a.* = b.* 刷新 : Fast (匯總語句 ) ? 所有的表必須有日志 – 日志中被引用的所有列 – 應有 with rowid和 including new values – 如果有 insert,update,delete操作，則指定 with sequence選項 ? 在 select中應當有 count(*), count(字段 ), sum(字段), sum(字段 *字段 ) ? 包括 group by中的所有列 ? 使用 cube或 rollup時，包括 grouping_id(字段 )列 創(chuàng)建實體化視圖日志 ? 語法 create materialized view log on … with [sequence,] rowid (col1,col2,…) including new values ? 例子 Create materialized view log on with sequence, rowid,(QNXS00,RJKC00,RQ0000,SNXS00,SPDM00,SWXS00) including new values 創(chuàng)建實體化視圖 CREATE MATERIALIZED VIEW BBCX.MVIEW_BBCX_XSTJXCRB (RQ0000,SPDM00,RJKC00,SWXS00,SNXS00,QNXS00) PARTITION BY RANGE(RQ0000) ( PARTITION P202101 values less than (TO_DATE(‘20210501’,’YYYYMMDD’)), …… ) BUILD IMMEDIATE REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE TABLESPACE IQS_BASICDATA AS select rq0000,spdm00, count(*) rowt, sum(rjkc00) rjkc00, SUM(SWXS00) swxs00, sum(SNXS00) snxs00, sum(QNXS00) qnxs00 from xstjxcrb group by rq0000,spdm00 使用實體化視圖 ? 修改啟動參數(shù)： query_rewrite_enabled=true query_rewrite_integrity=trusted ? Grant query rewrite to bbcx ? 收集統(tǒng)計信息 (‘bbcx’,’xstjxcrb’) ? 或使用 rewrite提示符 ? 需要注意 SQL的匹配性，可以用 explain plan來看 調整臨時表 ? 盡量減少臨時表的使用 ? 可以適當使用嵌套的 SQL語句 Optimizer Hints 為什么會有 Hints ? 因為 CBO的功能還不十分強大 ? 盡量讓優(yōu)化器選擇，除非你很有自信 ? 例子： 在使用 dblink的環(huán)境中 在使用 bind variable時 Hint : 優(yōu)化器的選擇 ? All_rows ? First_rows(n) n=1,10,100,1000 ? Choose ? Rul