SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
2、测试数据创建：
表创建SQL如下：
如下：
create table T65_F_S_PGZ10023_M
(
STATISTIC_DT DATE,
INDICKEY VARCHAR2(20),
OBJKEY VARCHAR2(64),
INDICVAL1 NUMBER(30,2),
INDICVAL2 NUMBER(30,2),
INDICVAL3 NUMBER(30,2),
INDICVAL4 NUMBER(30,2),
INDICVAL5 NUMBER(30,2)
)
partition by range (STATISTIC_DT)
(
partition PT_20111231 values less than (TO_DATE(\'2012-01-01 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120101 values less than (TO_DATE(\'2012-01-02 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120102 values less than (TO_DATE(\'2012-01-03 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120103 values less than (TO_DATE(\'2012-01-04 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120104 values less than (TO_DATE(\'2012-01-05 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120105 values less than (TO_DATE(\'2012-01-06 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120106 values less than (TO_DATE(\'2012-01-07 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120107 values less than (TO_DATE(\'2012-01-08 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120108 values less than (TO_DATE(\'2012-01-09 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120109 values less than (TO_DATE(\'2012-01-10 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120100 values less than (TO_DATE(\'2012-01-11 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
partition PT_20120111 values less than (TO_DATE(\'2012-01-12 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01
);
CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 PARALLEL LOCAL;
测试数据生成SQL如下：
SQL>insert into T65_F_S_PGZ10023_M(INDICKEY,STATISTIC_DT,OBJKEY,INDICVAL1)
select 'PGZ10023' as INDICKEY, to_date('2012-01-08','yyyy-mm-dd') as STATISTIC_DT ,'81'||lpad(to_char(rownum),7,'0') as OBJKEY ,trunc(dbms_random.value(1,rownum)) as INDICVAL1 from dual connect by rownum <100000;
修改日期可以得到2012-01-01 到2012-01-08这8天的测试数据
测试数据按天汇总的记录数
SQL> select statistic_dt,count(*) from t65_f_s_pgz10023_m group by statistic_dt;
STATISTIC_DT COUNT(*)
------------ ----------
2012-01-01 99999
2012-01-02 99999
2012-01-03 99999
2012-01-04 99999
2012-01-05 99999
2012-01-06 99999
2012-01-07 99999
2012-01-08 99999
打开CMD命令窗口，使用 aml 用户连接数据库
C:\Users\Administrator>sqlplus aml/abc
开启执行计划
SQL> set autotrace traceonly
设置行长度
SQL> set lines 300;
SQL> CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 local;
SQL> select * from T65_F_S_PGZ10023_M where objkey='810000836';
得到如下的执行计划，此索引是本地索引
SQL> select * from T65_F_S_PGZ10023_M where objkey='810000836';
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2061015615
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 960 | 14 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 8 | 960 | 14 (0) | 00:00:01 | 1 | 12 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | T65_F_S_PGZ10023_M | 8 | 960 | 14 (0) | 00:00:01 | 1 | 12 |
|* 3 | INDEX RANGE SCAN | PI_T65_F_S_PGZ10023_M | 8 | | 13 (0) | 00:00:01 | 1 | 12 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJKEY"='810000836')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
197 consistent gets
32 physical reads
0 redo size
1182 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
SQL>
我们删除本地索引，现在创建局部索引
SQL> drop index PI_T65_F_S_PGZ10023_M;
SQL> CREATE INDEX I_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 global;
将缓冲池清空
SQL> alter system flush buffer_cache;
再次执行刚才的SQL
SQL> select * from T65_F_S_PGZ10023_M where objkey='810000836';
全局索引的执行计划
8 rows selected.
Execution Plan