1

I am trying to create a partitioned table as follows - 

CREATE TABLE test1 (
    id     NUMBER(18,0),
    text   VARCHAR2(1024)
)

partition BY RANGE 
( TO_DATE(substr(id,1,8),'yyyymmdd')        
) INTERVAL ( numtodsinterval(1,'DAY') )
( PARTITION "P0"
    VALUES LESS THAN ( TO_DATE(' 2016-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') )
);

When I try and run this statement I get the error as follows -

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

My guess is no functions are allowed while specifying partition key column.

Now, the data in the id column is of the following format -

YYYYMMDDNNNNNNN

YYYY is the year

MM is the month

DD is the day

NNNNNNN is a sequence/random number

I am trying to extract the first 8 digits convert it to date and partition the table on that derived column value.

Is this something possible? Any suggestions on how this can be achieved will be greatly appreciated.

shrek
  • 887
  • 6
  • 12
  • 2
    Why are you storing dates in a NUMBER column? That makes no sense - and will cause numerous problems in the future. –  Aug 23 '18 at 19:08
  • Also, if you want to partition by month (and the data only has information down to month anyway), what's with the `INTERVAL` clause that seems to want to create partitions for each DAY? –  Aug 23 '18 at 19:32
  • @mathguy .... The date along with sequence/random number are combined to form a unique id (existing design). With volume expected to grow big, the idea is to partition on the date part so that anything older than 100 days can be dropped. – shrek Aug 23 '18 at 20:46
  • Well, first of all, you edited your post to add the day component (without any mention of your edit, which is asinine - it makes Answers given to your original post look idiotic, when in fact they were fine given the question you posted originally). Second, the Answer I gave can be modified easily to answer your edited question. –  Aug 23 '18 at 20:58
  • Apologies, I missed hitting the Add comment button ..... yes, i updated the question to include to Day component which looked meaningless otherwise, thanks for pointing out. – shrek Aug 23 '18 at 21:25
  • I edited your question further (there was a place where you still left 6 when in fact you want 8 digits). I also updated my Answer (simply deleting two zeros from the numbers). You do NOT need to convert the first eight digits to a date for this to work; you can work directly with the ID as is, as I show in my Answer. (However, note that this means the numbers do in fact have 18 digits, not 11 or 15... otherwise you should not store as numbers but as VARCHAR2.) –  Aug 23 '18 at 21:30
  • Dropping partitions which are older than 100 days is rather simple: https://stackoverflow.com/questions/48089186/drop-multiple-partitions-based-on-date/48089939#48089939 – Wernfried Domscheit Aug 24 '18 at 06:15
  • If you need a unique id **column** then better do it the other way around: `CREATE TABLE test1 (D DATE, N NUMBER, id NUMBER AS (TO_CHAR(D, 'YYYYMMDD')||N) VIRTUAL, text VARCHAR2(1024))` – Wernfried Domscheit Aug 24 '18 at 06:20

2 Answers2

1

You could use Oracle's virtual column:

CREATE TABLE test1 (
    id     NUMBER(18,0),
    text   VARCHAR2(1024),
    computed  AS (TO_DATE(substr(id,1,6),'yyyymm'))
)
partition BY RANGE (computed) INTERVAL (numtodsinterval(1,'DAY'))
( PARTITION "P0" VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00'
                     ,'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') )
);
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • @WernfriedDomscheit In Oracle context - virtual is the best word. In general this functionality has at least 4 names: virtual [computed](https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017) [generated](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) automatic columns. – Lukasz Szozda Aug 24 '18 at 04:50
  • `VALUES LESS THAN (DATE '2016-01-01')` is even shorter. – Wernfried Domscheit Aug 24 '18 at 06:10
0

It seems to me that you want something like I show below. It is not clear WHY you want this, but that's a different issue.

CREATE TABLE test1 (
    id     NUMBER(18,0),
    text   VARCHAR2(1024)
)
partition BY RANGE (id)
interval(10000000000)
(partition p0 values less than( (2016010000000000)))
;

(NOTE: Edited after the OP modified the question)