040：【实验：位图索引提高GROUP效率】

贴子地址：http://www.itpub.net/thread-1743016-1-1.html
【帖子问题】
我有一个Table,数据量大概1000万,在执行以下语句
select biz_date,count(*) from t1 where user_yn='N' group by biz_date


完成时间大概是30S左右，但是实际返回数据只有500条左右.这种语句怎么样才能加快查询效率呢？

还是只能这样？biz_date不是索引列。

【实验】
1）建表
create table t_group1
(id number,
biz_date varchar2(8),
user_yn char(1)
);

2）插入数据
declare
i number;
begin
for i in 1..100000
loop
insert into t_group1 values(i,to_char(ceil(i/200)),'N');
COMMIT;
end LOOP;
end;

insert /*+APPEND*/ into t_group1 nologging
select t.id+10000,t.biz_date,'Y' FROM t_group1 t;
COMMIT;
insert /*+APPEND*/ into t_group1 nologging
select t.id+20000,t.biz_date,'Y' FROM t_group1 t;
COMMIT;
insert /*+APPEND*/ into t_group1 nologging
select t.id+30000,t.biz_date,'Y' FROM t_group1 t;
COMMIT;
insert /*+APPEND*/ into t_group1 nologging
select t.id+40000,t.biz_date,'Y' FROM t_group1 t;
COMMIT;

insert /*+APPEND*/ into t_group1 nologging
select t.id+20000,t.biz_date,'Y' FROM t_group1 t;
COMMIT;
insert /*+APPEND*/ into t_group1 nologging
select t.id+30000,t.biz_date,'Y' FROM t_group1 t;
COMMIT;
insert /*+APPEND*/ into t_group1 nologging
select t.id+40000,t.biz_date,'Y' FROM t_group1 t;
COMMIT;

insert /*+APPEND*/ into t_group1 nologging
select t.id+50000,t.biz_date,'N' FROM t_group1 t;
COMMIT;
--400多万条记录

3）未建索引前
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 't_group1');
end;
SQL> select biz_date,count(1) from scott.t_group1 t where user_yn='N' group by biz_date;

500 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2782928935

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 3000 | 2395 (25)| 00:00:29 |
| 1 | HASH GROUP BY | | 500 | 3000 | 2395 (25)| 00:00:29 |
|* 2 | TABLE ACCESS FULL| T_GROUP1 | 2289K| 13M| 2085 (14)| 00:00:26 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("USER_YN"='N')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9231 consistent gets
6051 physical reads
332 redo size
9086 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
500 rows processed

4）创建位图组合索引
create bitmap index bit_date_yn on t_group1(user_yn,biz_date);
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 't_group1');
end;

SQL> select biz_date,count(rowid) from scott.t_group1 t where user_yn='N' group by biz_date;

500 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078896986

--------------------------------------------------------------------------------
------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |

--------------------------------------------------------------------------------
------------

| 0 | SELECT STATEMENT | | 501 | 5010 | 69 (0)
| 00:00:01 |

| 1 | SORT GROUP BY NOSORT | | 501 | 5010 | 69 (0)
| 00:00:01 |

| 2 | BITMAP CONVERSION TO ROWIDS| | 2260K| 21M| 69 (0)
| 00:00:01 |

|* 3 | BITMAP INDEX RANGE SCAN | BIT_DATE_YN | | |
| |

--------------------------------------------------------------------------------
------------


Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("USER_YN"='N')
filter("USER_YN"='N')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
90 consistent gets
1 physical reads
0 redo size
9090 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
500 rows processed
5）建B树索引：本例中索引的尺寸比表还大，所以效率不如全表扫描
drop index bit_date_yn;
create index tree_yn_date on t_group1(user_yn,biz_date);
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 't_group1');
end;

SQL> select /*+index(t)*/ biz_date,count(rowid) from scott.t_group1 t where user_yn='N' group by biz_date;

500 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3898704142

--------------------------------------------------------------------------------
-----

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
-----

| 0 | SELECT STATEMENT | | 501 | 5010 | 8093 (1)| 00:01
:38 |

| 1 | SORT GROUP BY NOSORT| | 501 | 5010 | 8093 (1)| 00:01
:38 |

|* 2 | INDEX SKIP SCAN | TREE_YN_DATE | 2322K| 22M| 8093 (1)| 00:01
:38 |

--------------------------------------------------------------------------------
-----


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("USER_YN"='N')
filter("USER_YN"='N')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5854 consistent gets
0 physical reads
0 redo size
9090 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
500 rows processed

【实验小结】有限制条件的GROUP语句，需要使用位图组合索引。建多个单值索引不能提高效率。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7901922/viewspace-1060023/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7901922/viewspace-1060023/