-
Notifications
You must be signed in to change notification settings - Fork 0
/
cd_eds_part.sql
90 lines (71 loc) · 3.66 KB
/
cd_eds_part.sql
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
79
80
81
82
83
84
85
86
87
88
89
90
CREATE TABLE "TABELNAAM"
( "TTIME" DATE NOT NULL ENABLE,
"BINDATA" BLOB,
"LOBJID" NUMBER(38,0) NOT NULL ENABLE,
"LVERSION" NUMBER(38,0) NOT NULL ENABLE,
"TSTOPTIME" DATE NOT NULL ENABLE,
"TMODIFIED" DATE NOT NULL ENABLE,
"LWORSTSTATUS" NUMBER(38,0) NOT NULL ENABLE,
"TFIRSTBETTERSTATUS" DATE NOT NULL ENABLE,
"TNEXTWORSTSTATUS" DATE NOT NULL ENABLE,
"TFIRSTNOTNULLVALUE" DATE NOT NULL ENABLE,
"TNEXTNULLVALUE" DATE NOT NULL ENABLE,
"TLASTNOTNULLVALUE" DATE
--,
-- CONSTRAINT "FOREIGNKEYNAAM" FOREIGN KEY ("LOBJID")
-- REFERENCES "SCOTT"."TABELNAAM_2" ("LOBJID") ON DELETE CASCADE ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
-- COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING
LOB ("BINDATA") STORE AS SECUREFILE "SECUREFILENAAM" (
TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 16384
CACHE LOGGING NOCOMPRESS KEEP_DUPLICATES)
PARTITION BY RANGE ("TTIME") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION "P0" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NO INMEMORY SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
-- COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING LOGGING
TABLESPACE "USERS"
LOB ("BINDATA") STORE AS SECUREFILE (
TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 16384
CACHE NOCOMPRESS KEEP_DUPLICATES
) ) ;
CREATE UNIQUE INDEX "TABELNAAM_PK" ON "TABELNAAM" ("LOBJID", "TTIME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
;
ALTER TABLE "TABELNAAM" ADD CONSTRAINT "TABELNAAM_PK" PRIMARY KEY ("LOBJID", "TTIME")
USING INDEX "TABELNAAM_PK" ENABLE;
CREATE INDEX "SCOTT"."G_TS_BINDATA_10_INDX" ON "TABELNAAM" ("LOBJID", "TTIME", "LVERSION")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 20971520 NEXT 20971520 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
--BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "USERS" ;
CREATE INDEX "G_TS_BINDATA_10_INTERVAL_INDX" ON "TABELNAAM" ("LOBJID", "TTIME", "TSTOPTIME")
;
CREATE INDEX "G_TS_BINDATA_10_NULLS_INDX" ON "TABELNAAM" ("LOBJID", "TFIRSTNOTNULLVALUE", "TNEXTNULLVALUE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
;
-- added insert stmnts to check partitioning..
prompt insert 40K
insert into tabelnaam
select
to_date ( '2021-JAN-01', 'YYYY-MON-DD' ) + (4*rownum/1440) -- ttm
, '' -- blob, first empty
, trunc ( rownum -1) -- id
, mod ( rownum-1, 10 ) -- lversion, 0-9
, (sysdate - rownum/1440 ) -- tst, some date
, (sysdate - rownum/1440 - 2 ) --
, mod ( rownum, 100 ) -- lworstatus 0-99
, (sysdate - rownum/1440 - 4 ) -- tfn, some date
, (sysdate - rownum/1440 - 4 ) -- tfn, some date
, (sysdate - rownum/1440 - 4 ) -- tfn, some date
, (sysdate - rownum/1440 - 4 ) -- tfn, some date
, (sysdate - rownum/1440 - 6 ) -- tnn, some date
from dual
connect by rownum <= 40000 ;
commit ;
EXEC DBMS_STATS.gather_table_stats(user, tabelnaam, null, 1);
@segsizes scott tabelnaam