没有Where条件下groupby走索引

C:\\Users\\Administrator>sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on 星期⼆ 3⽉ 23 21:42:27 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. SQL> conn /as sysdba 已连接。

SQL> create table t as select * from dba_objects; 表已创建。

SQL> insert into t select * from t; 已创建59262⾏。 SQL> /

已创建118524⾏。 SQL> /

已创建237048⾏。 SQL> /

已创建474096⾏。 SQL> /

已创建948192⾏。 SQL> commit; 提交完成。

SQL> alter session set \"_gby_hash_aggregation_enabled\"=false; 会话已更改。

SQL> set autot on

SQL> select owner,max(object_id) from t group by owner; OWNER MAX(OBJECT_ID) ------------------------------ -------------- ACMS 59869 DBSNMP 11991 EXFSYS 53969 XXXX 58952

ORACLE_OCM 11757 OUTLN 5724 PUBLIC 53918 SYS 59884 SYSTEM 12528 TSMSYS 5185 WMSYS 12520 已选择11⾏。

执⾏计划

---------------------------------------------------------- Plan hash value: 1476560607

--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1679K| 48M| 7805 (5)| 00:01:34 | | 1 | SORT GROUP BY | | 1679K| 48M| 7805 (5)| 00:01:34 | | 2 | TABLE ACCESS FULL| T | 1679K| 48M| 7573 (2)| 00:01:31 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement

统计信息

---------------------------------------------------------- 0 recursive calls

0 db block gets 27669 consistent gets 0 physical reads 0 redo size

683 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk)

11 rows processed

SQL> create index t_idx on t (owner,object_id); 索引已创建。

SQL> select owner,max(object_id) from t group by owner; OWNER MAX(OBJECT_ID) ------------------------------ -------------- ACMS 59869 DBSNMP 11991 EXFSYS 53969 XXXX 58952

ORACLE_OCM 11757 OUTLN 5724 PUBLIC 53918 SYS 59884 SYSTEM 12528 TSMSYS 5185 WMSYS 12520 已选择11⾏。

执⾏计划

---------------------------------------------------------- Plan hash value: 1476560607

--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1679K| 48M| 7805 (5)| 00:01:34 | | 1 | SORT GROUP BY | | 1679K| 48M| 7805 (5)| 00:01:34 | | 2 | TABLE ACCESS FULL| T | 1679K| 48M| 7573 (2)| 00:01:31 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement

统计信息

---------------------------------------------------------- 5 recursive calls 0 db block gets 27739 consistent gets 26 physical reads 0 redo size

683 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk)

11 rows processed

SQL> alter table t modify owner not null; 表已更改。

SQL> select owner,max(object_id) from t group by owner; OWNER MAX(OBJECT_ID) ------------------------------ -------------- ACMS 59869 DBSNMP 11991 EXFSYS 53969 XXXX 58952

ORACLE_OCM 11757 OUTLN 5724 PUBLIC 53918 SYS 59884 SYSTEM 12528 TSMSYS 5185 WMSYS 12520

已选择11⾏。

执⾏计划

---------------------------------------------------------- Plan hash value: 847563073

------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1679K| 48M| 1790 (16)| 00:00:22 | | 1 | SORT GROUP BY | | 1679K| 48M| 1790 (16)| 00:00:22 |

| 2 | INDEX FAST FULL SCAN| T_IDX | 1679K| 48M| 1558 (3)| 00:00:19 | ------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement

统计信息

---------------------------------------------------------- 146 recursive calls 0 db block gets 5705 consistent gets 5601 physical reads 0 redo size

683 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk)

11 rows processed

SQL> alter table t modify owner null; 表已更改。

SQL> alter table t modify object_id not null; 表已更改。

SQL> select owner,max(object_id) from t group by owner; OWNER MAX(OBJECT_ID) ------------------------------ -------------- ACMS 59869 DBSNMP 11991 EXFSYS 53969 XXXX 58952

ORACLE_OCM 11757 OUTLN 5724 PUBLIC 53918 SYS 59884 SYSTEM 12528 TSMSYS 5185 WMSYS 12520 已选择11⾏。

执⾏计划

---------------------------------------------------------- Plan hash value: 847563073

------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1679K| 48M| 1790 (16)| 00:00:22 | | 1 | SORT GROUP BY | | 1679K| 48M| 1790 (16)| 00:00:22 |

| 2 | INDEX FAST FULL SCAN| T_IDX | 1679K| 48M| 1558 (3)| 00:00:19 | ------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement

统计信息

---------------------------------------------------------- 146 recursive calls 0 db block gets 5705 consistent gets

0 physical reads 0 redo size

683 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk)

11 rows processed SQL> select * from v$version;

BANNER

-------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production

TNS for 32-bit Windows: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production

转⾃《http://www.itpub.net/thread-1282845-1-1.html》