我的group by 为什么不使用索引？

[cwwhy]

select PATIENTSEQ,
ADMISSIONDATE,
ADMISSIONTIME,
ENTERDATE,
ENTERTIME,
sum(IBSTEN) as SUM_IBSTEN
from MMINFOMATION
group by PATIENTSEQ,
ADMISSIONDATE,
ADMISSIONTIME,
ENTERDATE,
ENTERTIME

excute plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'MMINFOMATION'


total
----------------------------------------------------------
0 recursive calls
0 db block gets
10490 consistent gets
10382 physical reads
0 redo size
145660 bytes sent via SQL*Net to client
2571 bytes received via SQL*Net from client
190 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2834 rows processed

我索引了五列(PATIENTSEQ,ADMISSIONDATE,ADMISSIONTIME,
ENTERDATE,ENTERTIME)

可是执行计划怎么还是Full access
大家帮帮忙啊

[UandM]

没有Where当然全表扫描了
有Where 就用上了

[cwwhy]

group by 不能用索引吗？
那我这条SQL语句不就是死定了。
有没有办法用到索引阿，这个查询好慢的说

[njhart2003]

分析一下你的表
analyze table MMINFOMATION compute statistics;

[skyboy0720]

先了解一下索引是怎么被调用先~

[cwwhy]

excute plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17578 Card=1350000 B
ytes=47250000)

1 0 SORT (GROUP BY NOSORT) (Cost=17578 Card=1350000 Bytes=4725
0000)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MMINFOMATION' (Cost=17
578 Card=1350000 Bytes=47250000)

3 2 INDEX (FULL SCAN) OF 'IDMMINFOMATION1' (NON-UNIQUE) (C
ost=4370 Card=1350000)

total
----------------------------------------------------------
0 recursive calls
0 db block gets
17955 consistent gets
14416 physical reads
0 redo size
115501 bytes sent via SQL*Net to client
2571 bytes received via SQL*Net from client
190 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2834 rows processed

表分析过了，大哥能不能看出点问题来

[liuyi8903]

这种情况你可以作一个没有意义的where条件来使用前导列.比方PATIENTSEQ>0


可以将表作个分析后再看看.

[bzszp]

楼上的方法可以一试

如果经常使用这个语句
try:
alter table tbname cache;
看看有没有性能上的改进（当然也是全表扫描，只是减少了IO开销）

[liuyi8903]

来看一下这个测试;
Ora92@Test>SELECT WELLNO, USEUNITID, RBXH,SUM(ljjc) FROM p_zjrb GROUP BY WELLNO, USEUNITID, RBXH;

已选择52631行。

已用时间: 00: 00: 05.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'P_ZJRB'




Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
1324 consistent gets
1519 physical reads
0 redo size
1645121 bytes sent via SQL*Net to client
39091 bytes received via SQL*Net from client
3510 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
52631 rows processed

Ora92@Test>ed
已写入文件 afiedt.buf

1* SELECT WELLNO, USEUNITID, RBXH,SUM(ljjc) FROM p_zjrb where wellno>'0' GROUP BY WELLNO, USEUNITID, RBXH
Ora92@Test>/

已选择52631行。

已用时间: 00: 00: 07.05

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'P_ZJRB'
3 2 INDEX (RANGE SCAN) OF 'INDEX_P_ZJRB_WELLNO' (NON-UNIQU
E)





Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
21916 consistent gets
1671 physical reads
0 redo size
1645121 bytes sent via SQL*Net to client
39091 bytes received via SQL*Net from client
3510 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
52631 rows processed

Ora92@Test>

[liuyi8903]

再来看一看使用hint带来的结果:
Ora92@Test>SELECT /*+index(p_zjrb PK_P_ZJRB)*/
2 WELLNO, USEUNITID, RBXH, SUM(LJJC)
3 FROM P_ZJRB
4 GROUP BY WELLNO, USEUNITID, RBXH;

已选择52631行。

已用时间: 00: 00: 04.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=107818 Byte
s=5283082)

1 0 SORT (GROUP BY NOSORT) (Cost=826 Card=107818 Bytes=5283082
)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'P_ZJRB' (Cost=826 Card
=107818 Bytes=5283082)

3 2 INDEX (FULL SCAN) OF 'PK_P_ZJRB' (UNIQUE) (Cost=26 Car
d=107818)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30275 consistent gets
104 physical reads
0 redo size
1644908 bytes sent via SQL*Net to client
39091 bytes received via SQL*Net from client
3510 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52631 rows processed

Ora92@Test>

[liuyi8903]

再来看使用hint带来的效果:

Ora92@Test>SELECT /*+index(p_zjrb PK_P_ZJRB)*/
2 WELLNO, USEUNITID, RBXH, SUM(LJJC)
3 FROM P_ZJRB
4 GROUP BY WELLNO, USEUNITID, RBXH;

已选择52631行。

已用时间: 00: 00: 04.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=107818 Byte
s=5283082)

1 0 SORT (GROUP BY NOSORT) (Cost=826 Card=107818 Bytes=5283082
)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'P_ZJRB' (Cost=826 Card
=107818 Bytes=5283082)

3 2 INDEX (FULL SCAN) OF 'PK_P_ZJRB' (UNIQUE) (Cost=26 Car
d=107818)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30275 consistent gets
104 physical reads
0 redo size
1644908 bytes sent via SQL*Net to client
39091 bytes received via SQL*Net from client
3510 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52631 rows processed

Ora92@Test>

[liuyi8903]

去掉hint和前导列.利用全表扫描:
Ora92@Test>SELECT WELLNO, USEUNITID, RBXH, SUM(LJJC)
2 FROM P_ZJRB
3 GROUP BY WELLNO, USEUNITID, RBXH;

已选择52631行。

已用时间: 00: 00: 03.09

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'P_ZJRB'




Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
1324 consistent gets
251 physical reads
0 redo size
1645121 bytes sent via SQL*Net to client
39091 bytes received via SQL*Net from client
3510 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
52631 rows processed

Ora92@Test>

[liuyi8903]

这种情况下走全表扫描我觉得会更好些.

[bzszp]

我记得如果返回的结果集>20%的话，使用索引 会有反面效果。

[liuyi8903]

嗯是的.15-20%之间.