没有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》

posted @ 2016-06-21 16:08  PoleStar  阅读(11927)  评论(0编辑  收藏  举报