Oracle Datetime – How to Get Current Time and Use in Interval Partition

datetimeoracle

I'm trying to create an interval partitioned table in Oracle where the first partition is bounded to a week from now, but I just can't seem to figure out the syntax.

Here's what I have so far..

CREATE TABLE TRANSACTION
(
....
, TRANSACTION_DATETIME TIMESTAMP(6) NOT NULL
....
)
PARTITION BY RANGE(TRANSACTION_DATETIME)
INTERVAL (NUMTODSINTERVAL(7,'day'))
(PARTITION p_first VALUES LESS THAN (SELECT SYSDATE+7 FROM DUAL));

I've tried that and many other variations, such as..

(PARTITION p_first VALUES LESS THAN (SYSDATE+7));  //OR
(PARTITION p_first VALUES LESS THAN (SYSDATE+7 FROM DUAL));

But none of them seem to work

Best Answer

You can not do that. You need to specify the date yourself, as that clause accepts only literals or MAXVALUE, and not expressions.

So you could just use:

(PARTITION p_first VALUES LESS THAN (DATE'2015-12-12'));

If you really want to calculate it dynamically, you need to use dynamic SQL.

Related Solutions

Simple query with date type requires explicit conversion to_date of date

Your belief that this should not be necessary is correct based on your assumptions. Normally this means the assumptions should be rechecked (unless there is data corruption or a bug). If you run the following you should get the same results from the first query as the second:

DROP TABLE t1;
CREATE TABLE t1 AS (
   SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn 
   FROM dual CONNECT BY level <=120);

select * from t1 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

select myDateColumn from t1 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

To check some of the assumptions, can you show the results of the following query?

SELECT myDateColumn, to_date(myDateColumn)
   , to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY') 
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

The first three columns should all show identical information and the last should verify that the year is correct.

Partitioning vs Indexes

To put it simply, indexes allow fast access to small proportions of a table. This is because they access data that is scattered throughout many block in the data segment, so unless the rows you are looking for are clustered into a small number of blocks the total cost of accessing all of those single blocks will soon become greater than just scanning a table.

At absolute best you might get benefits from an index if you are accessing 20% of a table's rows, but it's more likely that 1-5% is an effective limit.

If you want to access a greater proportion effectively, say 10% of the rows, then if you can use a partitioning scheme to isolate "queryable" groups into partitions then they can be queried very quickly. Even if you are accessing 1% of a table's rows then if the query can retrieve them from a partition that holds just those rows with a full partition scan then that will be quicker than accessing them through an index -- around 1/100th of the time to perform a full table scan (ignoring parallel query).

So if your queries very often include a predicate on the client id then partition on that -- list partitioning, I would suggest. If you also query on ranges of dates, then consider range partitioning on the time column.

So you could composite partition by range-list, or by list-range. If you want to drop old data easily then range-list could be better, but I'm not sure that there's a lot to choose between them.

The token sounds like it could be a good candidate for an index. Indexes are self-maintaining, and modifying data will not invalidate them. Obviously there's an overhead to maintaining them, but data is generally queried many time more often than it is modified so on balance I tend to "index first, ask questions later".

Best Answer

Related Solutions

Simple query with date type requires explicit conversion to_date of date

Partitioning vs Indexes

Related Question