贴子地址: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语句,需要使用位图组合索引。建多个单值索引不能提高效率。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7901922/viewspace-1060023/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7901922/viewspace-1060023/