没有Where条件下group by走索引
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》
