[20140913]Quiz Night.txt

https://jonathanlewis.wordpress.com/2014/09/09/quiz-night-25/

链接提到一个测试:

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条是交错输出的.