提高查詢速度使用materizlized
2010年9月17日 星期五提高查詢速度使用materizlized
第一步：授權給使用者
SQL> grant create any materialized view to scott;
SQL> grant query rewrite to scott;
第二步：構建儲存巨大數量的表並插入大量的資料
select count(*) from all_objects;
create table my_all_objects nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/
SQL> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects
3 /
SQL> commit;
SQL> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects
3 /
SQL> commit;
SQL> select count(*) from my_all_objects;
第三步 分析構建的大表
SQL> analyze table my_all_objects compute statistics;
第四步：檢視構建大表所使用的時間已經讀取次數
SQL> set autot on
SQL> set timing on
SQL> select owner,count(*) from my_all_objects group by owner;
OWNER COUNT(*)
------------------------------ ----------
MDSYS 10308
TSMSYS 24
DMSYS 2268
PUBLIC 239844
OUTLN 84
CTXSYS 4056
OLAPSYS 8616
HR 408
SYSTEM 5100
EXFSYS 3348
SCOTT 216
OWNER COUNT(*)
------------------------------ ----------
DBSNMP 552
ORDSYS 20028
ORDPLUGINS 120
SYSMAN 15492
OE 1344
PM 108
SH 3588
XDB 4164
IX 552
BI 96
SYS 273948
OWNER COUNT(*)
------------------------------ ----------
WMSYS 2784
SI_INFORMTN_SCHEMA 96
24 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2509106709
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 120 | 1922 (5)| 00:00:24 |
| 1 | HASH GROUP BY | | 24 | 120 | 1922 (5)| 00:00:24 |
| 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 597K| 2915K| 1848 (2)| 00:00:23 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8213 consistent gets
0 physical reads
0 redo size
953 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
第五步：開啟query_rewrite的開關
SQL> alter session set query_rewrite_enabled=true;
SQL> alter session set query_rewrite_integrity=enforced;
第六步：建立materialized view
SQL> create materialized view my_all_objects_aggs
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner,count(*) from my_all_objects group by owner
7 /
第七步：分析物化檢視
SQL> analyze table my_all_objects_aggs compute statistics;
第八步：檢視構建好的大表，查詢需要了多長的時間與邏輯讀取次數以及訪問的表
SQL> set autot on
SQL> set timing on
SQL> select owner,count(*) from my_all_objects group by owner;
OWNER COUNT(*)
------------------------------ ----------
MDSYS 10308
TSMSYS 24
DMSYS 2268
PUBLIC 239844
OUTLN 84
CTXSYS 4056
OLAPSYS 8616
HR 408
SYSTEM 5100
EXFSYS 3348
SCOTT 216
OWNER COUNT(*)
------------------------------ ----------
DBSNMP 552
ORDSYS 20028
ORDPLUGINS 120
SYSMAN 15492
OE 1344
PM 108
SH 3588
XDB 4164
IX 552
BI 96
SYS 273948
OWNER COUNT(*)
------------------------------ ----------
WMSYS 2784
SI_INFORMTN_SCHEMA 96
24 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 2431068629
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 216 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MY_ALL_OBJECTS_AGGS | 24 | 216 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
133 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
953 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
24 rows processed
得出結論：沒有物化檢視前： 起初使用的時間是： Elapsed: 00:00:00.26 訪問的表是：MY_ALL_OBJECTS 邏輯讀取次數是： 8213 consistent gets
建立物化檢視後： 後來使用的時間是： Elapsed: 00:00:00.06 訪問的表是：MY_ALL_OBJECTS_AGGS 邏輯讀取次數是： 18 consistent gets
第九步：通過插入大表資料，時間依然會大大縮短
SQL> insert into my_all_objects (owner,object_name,object_type,object_id) values ('New Owner','New Name','New Type',111111);
第九步：通過查詢大表資料，時間依然會大大縮短
SQL> select owner,count(*) from my_all_objects where wner='New Owner' group by owner;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20976446/viewspace-674318/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 提高sql查詢速度SQL
- 提高count查詢速度
- 提高查詢速度方法總結
- 優化sql提高查詢速度優化SQL
- 提高跨庫查詢速度，你只需一個Smartbi
- QL Server 百萬級資料提高查詢速度的方法Server
- 提高ORACLE資料庫的查詢統計速度（轉）Oracle資料庫
- 使用Bulk Collect提高Oracle查詢效率Oracle
- 提高SQL查詢效能SQL
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- 優化sql查詢速度優化SQL
- db_file_multiblock 設定多塊讀取來提高查詢速度BloC
- 使用子查詢可提升 COUNT DISTINCT 速度 50 倍
- Oracle提高查詢效率的方法Oracle
- 檔案上傳速度查詢方法
- 最佳化拼多多關鍵詞搜尋介面：提高查詢響應速度的技巧
- 提高mysql查詢效率及一些使用技巧記錄MySql
- 面試官這樣問你：為什麼MySQL新增索引後就可以提高查詢速度面試MySql索引
- ClickHouse為什麼查詢速度快？
- Oracle臨時表 優化查詢速度Oracle優化
- 用hash cluster表提高查詢效能 (一)
- 提高mysql查詢效率的六種方法MySql
- 如何將 MySQL 查詢速度提升 300 倍MySql
- Oracle臨時表最佳化查詢速度Oracle
- hibernate跟jdbc的查詢速度相差10？？？JDBC
- Oracle臨時表 最佳化查詢速度Oracle
- 【WITH Clause】使用WITH子句提高查詢統計效率-顛覆思維定勢
- MySQL加速查詢速度的獨門武器：查詢快取(QueryCache)MySql快取
- 提高 Laravel Eloquent 查詢的5個小技巧Laravel
- Gbase8d如何提高查詢效能？
- 請教如何提高查詢系統的效能？
- 加快dba_hist_active_sess_history的查詢速度
- 利用Redis cache優化app查詢速度實踐Redis優化APP
- 找出Mysql查詢速度慢的SQL語句MySql
- 使用謂詞(NSPredicate)來提高集合遍歷與過濾查詢的效率
- 使用子查詢
- 處理上百萬條的資料庫如何提高處理查詢速度資料庫
- 提高gradle編譯速度Gradle編譯