Create partition in an indexed table

Question

I have a table which holds data for 12 hours. Every 5 minutes, it keeps deleting data which is more than 12 hours old and adds new data. It has almost 15-20 million rows. I want to create partition by hour and also index the table on column(time_stamp), to make the row fetching faster.

I will obviously do interval or range partitioning, but found that interval partitioning doesn't work on indexed table. So please help me with the syntax so that oracle creates 12 partitions and automatically adds new one when new time_stamp data is added which is after first 12 hours. I have already got a procedure to delete oldest partition which i will use so that there is always 12 hours of data.

I am stating the columns below.

CustomerId,ApplicationId,Time_Stamp,Service

I have tried to come up with this, but don't know how it will create new partitions

 CREATE TABLE local_table 
(customerid  VARCHAR2(30), 
applicationid VARCHAR2(30), 
time_stamp  TIMESTAMP, 
service   VARCHAR2(30))
PARTITION BY RANGE(time_stamp) 
(
PARTITION t1 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 00:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t2 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 01:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t3 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 02:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t4 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 03:00:00.0','YYYY-MM- DD HH24:MI:SS.ff')),
PARTITION t5 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 04:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t6 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 05:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t7 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 06:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t8 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 07:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t9 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 08:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t10 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 09:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t11 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 10:00:00.0','YYYY-MM-DD HH24:MI:SS.ff')),
PARTITION t12 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 11:00:00.0','YYYY-MM-DD HH24:MI:SS.ff'))
); 

create index index_time_stamp on local_table(TIME_STAMP);

I am using- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Answer 1

Create table with autopartitiong and LOCAL (partitioning) index. The local_partitioned_index clauses let you specify that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as table. Oracle Database automatically maintains local index partitioning as the underlying table is repartitioned.

CREATE TABLE local_table 
  (customerid    VARCHAR2(30), 
   applicationid VARCHAR2(30), 
   time_stamp    TIMESTAMP, 
   service       VARCHAR2(30))
   PARTITION BY RANGE(time_stamp) 
   INTERVAL(NUMTODSINTERVAL(1, 'HOUR'))
   (PARTITION t1 VALUES LESS THAN(TO_TIMESTAMP('2015-02-25 00:00:00.0','YYYY-MM-DD HH24:MI:SS.ff'))
  ); 

CREATE INDEX index_time_stamp on local_table(TIME_STAMP) LOCAL;

SELECT *
  FROM user_tab_partitions;

INSERT INTO local_table VALUES('1', 'a', sysdate, 'b');

SELECT *
  FROM user_tab_partitions;

INSERT INTO local_table VALUES('2', 'c', sysdate + 1/1440, 'd');

SELECT *
  FROM user_tab_partitions;

INSERT INTO local_table VALUES('3', 'e', sysdate + 1/24, 'f');

SELECT *
  FROM user_tab_partitions;

The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

For example, if you create an interval partitioned table with monthly intervals and the transition point at January 1, 2010, then the lower boundary for the January 2010 interval is January 1, 2010. The lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created. Note, however, that using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.

Some quick DRAFT for your second question about DROP PARTITION. Examine and debug before uncomment ALTER TABLE. You can create scheduler job for run this block of code every hour.

DECLARE
  l_pt_cnt   NUMBER;
  l_pt_name  VARCHAR2(100);
  l_minrowid ROWID;
  l_mindate  TIMESTAMP;
BEGIN
  -- get partition count
  SELECT count(*)
    INTO l_pt_cnt
    FROM user_tab_partitions
  WHERE table_name = 'LOCAL_TABLE';
  IF l_pt_cnt > 12 THEN
    SELECT min(time_stamp)
      INTO l_mindate
      FROM LOCAL_TABLE;
    -- get ROWID with min date
    SELECT min(rowid)
      INTO l_minrowid
      FROM LOCAL_TABLE
     WHERE time_stamp = l_mindate;
    -- get name of partition with row with min date
  SELECT subobject_name
    INTO l_pt_name
    FROM LOCAL_TABLE 
         JOIN user_objects 
           ON dbms_rowid.rowid_object(LOCAL_TABLE.rowid) = user_objects.object_id
   WHERE LOCAL_TABLE.rowid = l_minrowid;
   DBMS_OUTPUT.put_line('ALTER TABLE LOCAL_TABLE DROP PARTITION ' || l_pt_name );
   --EXECUTE IMMEDIATE 'ALTER TABLE LOCAL_TABLE DROP PARTITION ' || l_pt_name; 
  END IF;
END;