http://VietEnglish.com

http://Edgar411.com

http://ForumGrouper.com

http://hpricot.com

crEmptyEvent.html
10-20-2001 Bikle
-----------------

This page documents our efforts to build an empty, partitioned event
table.  A partitioned table is useful to us for several reasons:

1. It allows us to physcally separate parts of the event table based 
on its column event_time.

2. Although the initial table contains only one partition, the fact
that it is partitioned means that we may create more partitions in
the future and shuttle data between them while the table is hot.  We
are not allowed to do this to a non-partitioned table.  Notice in the
create statement below that we create the partitioned table based 
on a value for event_time which is 1000 years ahead of today.

3. Partitions full of data may be dropped via an ALTER TABLE command
in a way which allows us to avoid large amounts of rollback segment
activity.

We start this effort by inspecting some text from a ROWS=N export of
the current event table:

CREATE TABLE EVENT (EVENT_TIME DATE, EVENT_TYPE VARCHAR2(16),
VDATA1 VARCHAR2(250), VDATA2 VARCHAR2(250), NDATA1 NUMBER,
NDATA2 NUMBER, MEMBER_ID NUMBER, MEMBER_ID_REAL NUMBER, TAG
VARCHAR2(32)) PCTFREE 10 PCTUSED 40 INITRANS 5 MAXTRANS 255 LOGGING
STORAGE(INITIAL 1510268928 NEXT 755048448 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE BIKLE_DATA

We see that the current event table resides in TABLESPACE BIKLE_DATA
and consumes about 1.5GB.

Next, we fashion a CREATE TABLE script:

--
-- crEmptyEvent.sql
--

-- 10-20-2001 Bikle

SET ECHO ON

CREATE TABLE empty_p_event
PARTITION BY RANGE (event_time)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('3001-01-01','YYYY-MM-DD'))
STORAGE (INITIAL 200M NEXT 200M PCTINCREASE 0 MAXEXTENTS 999)
TABLESPACE bikle_data
)
AS SELECT * FROM event WHERE 1=2
/

Screen dumps of running the script on bikledev and biklepr are displayed below:

testbikle01-oracle-% sqg

SQL*Plus: Release 8.1.6.0.0 - Production on Sat Oct 20 17:48:19 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

17:50:05 SQL> @crEmptyEvent.sql
17:51:22 SQL> --
17:51:22 SQL> -- crEmptyEvent.sql
17:51:22 SQL> --
17:51:22 SQL>
17:51:22 SQL> SET ECHO ON
17:51:22 SQL>
17:51:22 SQL> CREATE TABLE empty_p_event
17:51:22   2  PARTITION BY RANGE (event_time)
17:51:22   3  (
17:51:22   4  PARTITION p1 VALUES LESS THAN (TO_DATE('3001-01-01','YYYY-MM-DD'))
17:51:22   5  STORAGE (INITIAL 200M NEXT 200M PCTINCREASE 0 MAXEXTENTS 999)
17:51:22   6  TABLESPACE bikle_data
17:51:22   7  )
17:51:22   8  AS SELECT * FROM event WHERE 1=2
17:51:22   9  /

Table created.

17:51:22 SQL>


bikle01-oracle-% sqg @crEmptyEvent.sql

SQL*Plus: Release 8.1.6.0.0 - Production on Sat Oct 20 20:49:19 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

20:49:19 SQL> --
20:49:19 SQL> -- crEmptyEvent.sql
20:49:19 SQL> --
20:49:19 SQL>
20:49:19 SQL> -- 10-20-2001 Bikle
20:49:19 SQL>
20:49:19 SQL> SET ECHO ON
20:49:19 SQL>
20:49:19 SQL> CREATE TABLE empty_p_event
20:49:19   2  PARTITION BY RANGE (event_time)
20:49:19   3  (
20:49:19   4  PARTITION p1 VALUES LESS THAN (TO_DATE('3001-01-01','YYYY-MM-DD'))
20:49:19   5  STORAGE (INITIAL 200M NEXT 200M PCTINCREASE 0 MAXEXTENTS 999)
20:49:19   6  TABLESPACE bikle_data
20:49:19   7  )
20:49:19   8  AS SELECT * FROM event WHERE 1=2
20:49:19   9  /

Table created.