1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
|
>CREATE TABLE TEST1
(
COL_1 VARCHAR2(40 BYTE) ,
DEB_DAT DATE ,
COL_2 NUMBER(15,5),
COL_3 NUMBER(15,5),
COL_4 NUMBER(15,5),
COL_5 NUMBER(15,5),
COL_6 NUMBER(15,5),
COL_7 NUMBER(15,5)
)
TABLESPACE USERS
;
>insert /*+ append */ into test1 select * from test;
3176393 rows created.
commit
;
>CREATE INDEX OWNER_HISTO.IDX_TEST1 ON OWNER_HISTO.TEST1
2 ( COL_1,DEB_DAT)
3 LOGGING
4 TABLESPACE USERS
5 PCTFREE 10
6 INITRANS 2
7 MAXTRANS 255
8 STORAGE (
9 INITIAL 64K
10 NEXT 1M
11 MINEXTENTS 1
12 MAXEXTENTS UNLIMITED
13 PCTINCREASE 0
14 BUFFER_POOL DEFAULT
15 )
16 NOPARALLEL;
Index created.
>SELECT col_1,deb_dat
FROM test1
WHERE TO_CHAR(DEB_DAT,'YYYYMM') = TO_CHAR(add_months(to_date ('01/09/2004','DD/MM/YYYY'),-1),'YYYYMM'); 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 246 | 7626 | 6205 (3)| 00:01:15 |
|* 1 | TABLE ACCESS FULL| TEST1 | 246 | 7626 | 6205 (3)| 00:01:15 |
---------------------------------------------------------------------------
> alter table test1 modify col_1 not null ;
Table altered.
>alter table test1 modify deb_dat not null;
Table altered.
>SELECT col_1,deb_dat
FROM test1
WHERE TO_CHAR(DEB_DAT,'YYYYMM') = TO_CHAR(add_months(to_date ('01/09/2004','DD/MM/YYYY'),-1),'YYYYMM'); 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 3190404073
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 246 | 7626 | 4006 (3)| 00:00:49 |
|* 1 | INDEX FAST FULL SCAN| IDX_TEST1 | 246 | 7626 | 4006 (3)| 00:00:49 |
---------------------------------------------------------------------------------- |
Partager