[20140913]Quiz Night.txt
[20140913]Quiz Night.txt
連結提到一個測試:
select bit_1, id, small_vc from bit_tab where bit_1 between 1 and 3 ;
select bit_1, id, small_vc from bit_tab where bit_1 = 1 or bit_1 > 1 and bit_1 <= 3 ;
--提示查詢第2個更有效,自己開始也寫一個測試,無法知道答案,最後作者給出了答案,選擇的是點陣圖索引,
--利用bitmap or,導致查詢輸出結果更加聚合,減少了邏輯讀.自己做一個例子看看.
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table bit_tab (id number, bit_1 number , small_vc varchar2(30));
exec dbms_random.seed(0);
insert into bit_tab select rownum,trunc(dbms_random.value(1,10)),lpad(rownum,30,'a') from dual connect by level<=1e5;
commit;
SCOTT@test01p> select bit_1,count(*) from bit_tab group by bit_1 order by 1;
BIT_1 COUNT(*)
---------- ----------
1 9924
2 10063
3 10015
4 9964
5 9839
6 10042
7 10071
8 10178
9 10002
10 9902
10 rows selected.
-- 資料分佈比較均勻,這樣執行以上查詢不會使用索引,即使是點陣圖索引(除非使用提示.)
--改變資料分割槽,減少bit_1=1,2,3資料.
SCOTT@test01p> update (select * from bit_tab where substr(small_vc,30,1) not in ('0') ) set bit_1=bit_1+10 where bit_1 in (1,2,3);
26982 rows updated.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select bit_1,count(*) from bit_tab group by bit_1 order by 1;
BIT_1 COUNT(*)
---------- ----------
1 996
2 977
3 1047
4 9964
5 9839
6 10042
7 10071
8 10178
9 10002
10 9902
11 8928
12 9086
13 8968
13 rows selected.
SCOTT@test01p> create bitmap index ib_bit_tab_bit_1 on bit_tab(bit_1);
Index created.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'BIT_TAB', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1 for columns bit_1 size 254');
PL/SQL procedure successfully completed.
SCOTT@test01p> set autotrace trace
SCOTT@test01p> select bit_1, id, small_vc from bit_tab where bit_1 between 1 and 3 ;
3020 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 282677569
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3021 | 115K| 152 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BIT_TAB | 3021 | 115K| 152 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 3 | BITMAP INDEX RANGE SCAN | IB_BIT_TAB_BIT_1 | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BIT_1">=1 AND "BIT_1"<=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1525 consistent gets
0 physical reads
0 redo size
135880 bytes sent via SQL*Net to client
709 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3020 rows processed
SCOTT@test01p> select bit_1, id, small_vc from bit_tab where bit_1 = 1 or bit_1 > 1 and bit_1 <= 3 ;
3020 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2757365504
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 114K| 152 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BIT_TAB | 3000 | 114K| 152 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP MERGE | | | | | |
|* 5 | BITMAP INDEX RANGE SCAN | IB_BIT_TAB_BIT_1 | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | IB_BIT_TAB_BIT_1 | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("BIT_1">1 AND "BIT_1"<=3)
6 - access("BIT_1"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
622 consistent gets
0 physical reads
0 redo size
135880 bytes sent via SQL*Net to client
709 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3020 rows processed
--對比可以發現consistent gets,第1條sql語句1525,而第2條僅僅622.
--如果我們檢視輸出還可以發現第1條按照bit_1從1到3順序輸出,而第2條是交錯輸出的.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1269732/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20220531]測試quiz night.txtUI
- 一道神奇的async quizUI
- A2A (SOUL-Quiz FeatureUI
- rust-quiz:032-or-pattern-guard.rsRustUI
- rust-quiz:004-dotdot-in-tuple.rsRustUI
- rust-quiz:028-underscore-prefix.rsRustUI
- rust-quiz:030-clone-pointers.rsRustUI
- rust-quiz:031-method-lookup.rsRustUI
- rust-quiz:003-mutate-const.rsRustUI
- rust-quiz:008-tokenize-punctuation.rsRustUI
- rust-quiz:009-opaque-metavariable.rsRustUIOpaque
- rust-quiz:013-mutable-zst.rsRustUI
- rust-quiz:017-unary-decrement.rsRustUIREM
- rust-quiz:002-bitand-or-reference.rsRustUI
- 如何寫好科研論文答案_ Quiz4UI
- rust-quiz:019-dropped-by-underscore.rsRustUI
- rust-quiz:021-closure-or-logical-or.rsRustUI
- rust-quiz:027-subtrait-dispatch.rsRustUIAI
- rust-quiz:033-range-full-method.rsRustUI
- rust-quiz:005-trait-resolution-hrtb.rsRustUIAI
- rust-quiz:006-value-of-assignment.rsRustUI
- rust-quiz:016-prefix-decrement.rsRustUIREM
- rust-quiz:001-macro-count-statements.rsRustUIMac
- rust-quiz:018-method-or-function-pointer.rsRustUIFunction
- rust-quiz:022-macro-tokenize-number.rsRustUIMac
- rust-quiz:024-local-and-const-hygiene.rsRustUI
- rust-quiz:025-unit-infallible-match.rsRustUI
- rust-quiz:026-iterator-lazy-map.rsRustUI
- rust-quiz:014-trait-autoref.rsRustUIAI
- rust-quiz:015-inference-of-number-type.rsRustUI
- rust-quiz:020-break-return-in-condition.rsRustUI
- rust-quiz:011-function-pointer-comparison.rsRustUIFunction
- rust-quiz:012-binding-drop-behavior.rsRustUI
- Go Quiz: 從Go面試題看鎖的注意事項GoUI面試題
- rust-quiz:023-inherent-vs-trait-method.mdRustUIAI
- Sensitive-Topic-History-Quiz: 完全由ChatGPT編寫的網頁遊戲UIChatGPT網頁遊戲
- Go Quiz: 從Go面試題搞懂slice range遍歷的坑GoUI面試題
- Quiz- Win32記憶體表示與數值大小UIWin32記憶體