提高查詢速度使用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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 提高跨庫查詢速度，你只需一個Smartbi
- QL Server 百萬級資料提高查詢速度的方法Server
- 優化sql查詢速度優化SQL
- c++map 查詢元素和list查詢元素速度對比C++
- 最佳化拼多多關鍵詞搜尋介面：提高查詢響應速度的技巧
- ClickHouse為什麼查詢速度快？
- 面試官這樣問你：為什麼MySQL新增索引後就可以提高查詢速度面試MySql索引
- 用hash cluster表提高查詢效能 (一)
- Gbase8d如何提高查詢效能？
- 提高 Laravel Eloquent 查詢的5個小技巧Laravel
- 一個小操作，SQL查詢速度翻了1000倍。SQL
- Grafana Loki查詢加速：如何在不新增資源的前提下提升查詢速度GrafanaLoki
- 如何提高MySQL DELETE 速度MySqldelete
- [Hive]Hive中表連線的優化，加快查詢速度Hive優化
- 談談MYSQL索引是如何提高查詢效率的MySql索引
- NULL列時，如何使得IS NULL或者IS NOT NULL可以使用索引來提高查詢效率Null索引
- 在laravel中使用mysql fulltext全文索引代替like查詢提高效能LaravelMySql索引
- laravel 5.8 連線資料庫庫查詢 資料 速度慢，使用mysql 直接查詢響應就快，什麼原因呢？Laravel資料庫MySql
- 千萬級資料庫使用索引查詢速度更慢的疑惑-資料回表問題資料庫索引
- 分分鐘解決MySQL查詢速度慢與效能差MySql
- mysql千萬級資料量根據索引優化查詢速度MySql索引優化
- 嫌 OSS 查詢太慢？看我們如何將速度提升 10 倍！
- UserService 查詢使用者查詢許可權 isGlobalQuery分析
- 使用生成器把Kafka寫入速度提高1000倍GKafka
- 離屏Canvas — 使用Web Worker提高你的Canvas執行速度CanvasWeb
- Python使用多程式提高網路爬蟲的爬取速度Python爬蟲
- 如何使用SymSpell將模糊搜尋速度提高五倍以上 - lnx
- 教你使用SQLite 子查詢SQLite
- mybatis-plus 使用In查詢MyBatis
- 如何提高 Xcode 的編譯速度XCode編譯
- win10如何查詢自己寬頻速度_win10檢視寬頻速度的方法Win10
- 想問一下如何測百度的查詢速度
- 【實測】Python 和 C++ 下字串查詢的速度對比PythonC++字串
- mysql千萬級資料量根據索引最佳化查詢速度MySql索引
- 增加子查詢表條件篩選提高效能
- Oracle OCP(08)：使用子查詢Oracle
- BeautifulSoup使用手冊（查詢篇）
- MyBatis使用四(查詢詳解)MyBatis
- dll預編譯提高webpack打包速度編譯Web