8.1.7.2 on WinNT.
I will be inputting a large amount of data each day, and since most queries
are run against single days in the last week, (excluding the current day), I
have decided to partition on a daily basis. One day's worth of data could
typically be up to 3Gb
I am trying to achieve the rolling window scenario, so somewhere during the
quiet weekend period I will run a maintenance job that will archive/purge 7
days, and create the next 7 partitions. In total I will probably have
somewhere between 30-60 days online at anyone time. (The data is really only
useful for a week or two)
If possible I would like to have meaningful names for all of my database
objects, and want to avoid any SYS_LOBxxx$, and SYS_ILOBxxx$ names.
I have the following columns in my table:
instrument_code varchar2(30),
snap_time number,
tick_data clob,
tick_type number,
id number,
capture_date date
Since virtually all queries will use capture_date to filter data, I plan to
make some local prefixed indexes.
My questions are:
- Is the following table definition along the right tracks, or should I
give it some more thought?
- Can I still use function based indexes e.g. (capture_date,
UPPER(instrument_code)). Trivial example, I know for a fact all my data will
be in uppercase in that field anyway.
*Note* I havn't put in the tablespace names yet, as the underlying storage
is still under review pending confirmation of the number of disks I am going
to get etc.
With regards the other parameters, there is only one data-loading
application, and it is Inserting data like there is no tomorrow.
Updates/deletes are very rare.
All comments welcome.
Adrian
CREATE TABLE tick_history
(
instrument_code varchar2(30),
snap_time number,
tick_data clob,
tick_type number,
id number,
target_partition number,
capture_date date
)
PCTFREE 5 PCTUSED 40
TABLESPACE xxxxxxxxxx
STORAGE (INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
PARTITION BY RANGE(capture_date)
(PARTITION part_20020329 VALUES LESS THAN
(to_date('30-mar-2002', 'dd-mon-yyyy'))
LOB(tick_data) STORE AS tick_data_lob_20020329(TABLESPACE xxxxxxxxxx
ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 0 CACHE
STORAGE(INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
INDEX tick_data_lob_i_20020329),
PARTITION part_20020330 VALUES LESS THAN
(to_date('31-mar-2002', 'dd-mon-yyyy'))
LOB(tick_data) STORE AS tick_data_lob_20020330(TABLESPACE xxxxxxxxxx
ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 0 CACHE
STORAGE(INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
INDEX tick_data_lob_i_20020330),
PARTITION part_20020331 VALUES LESS THAN
(to_date('01-apr-2002', 'dd-mon-yyyy'))
LOB(tick_data) STORE AS tick_data_lob_20020331(TABLESPACE xxxxxxxxxx
ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 0 CACHE
STORAGE(INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
INDEX tick_data_lob_i_20020331),
PARTITION part_20020401 VALUES LESS THAN
(to_date('02-apr-2002', 'dd-mon-yyyy'))
LOB(tick_data) STORE AS tick_data_lob_20020401(TABLESPACE xxxxxxxxxx
ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 0 CACHE
STORAGE(INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
INDEX tick_data_lob_i_20020401),
PARTITION part_20020402 VALUES LESS THAN
(to_date('03-apr-2002', 'dd-mon-yyyy'))
LOB(tick_data) STORE AS tick_data_lob_20020402(TABLESPACE xxxxxxxxxx
ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 0 CACHE
STORAGE(INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
INDEX tick_data_lob_i_20020402),
PARTITION part_20020403 VALUES LESS THAN
(to_date('04-apr-2002', 'dd-mon-yyyy'))
LOB(tick_data) STORE AS tick_data_lob_20020403(TABLESPACE xxxxxxxxxx
ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 0 CACHE
STORAGE(INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
INDEX tick_data_lob_i_20020403),
PARTITION part_20020404 VALUES LESS THAN
(to_date('05-apr-2002', 'dd-mon-yyyy'))
LOB(tick_data) STORE AS tick_data_lob_20020404(TABLESPACE xxxxxxxxxx
ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 0 CACHE
STORAGE(INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
INDEX tick_data_lob_i_20020404),
PARTITION part_20020405 VALUES LESS THAN
(to_date('06-apr-2002', 'dd-mon-yyyy'))
LOB(tick_data) STORE AS tick_data_lob_20020405(TABLESPACE xxxxxxxxxx
ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 0 CACHE
STORAGE(INITIAL 32m NEXT 32m
MINEXTENTS 1 MAXEXTENTS 1000
PCTINCREASE 0
BUFFER_POOL RECYCLE)
INDEX tick_data_lob_i_20020405)
);
Received on Fri Mar 29 2002 - 13:18:26 CST
