Partitioning a Table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Partitioning a Table

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi

    I am attempting to a table partition based on the month. My intension is to keep all the records based on the month irrespective of the year. For example, all the January records go into Jan_Partition, Feb records into Feb_Partition and so on..

    Currently the table I am attempting, containing one varchar2 field, which contains the data like 'MMDDYYYYXXXXXXX'.
    When I tryto partition this table I am getting ora-14019 error.

    I tried like this:

    CREATE TABLE CALLLOG3 (
    CALLLOG_ID NUMBER (18) NOT NULL,
    SESSION_ID VARCHAR2 (18) DEFAULT '0',
    CONSTRAINT PK_CALLLOG3
    PRIMARY KEY ( CALLLOG_ID )
    USING INDEX
    TABLESPACE INDEX_SSP1_TRANSACTION PCTFREE 10
    STORAGE ( INITIAL 65536 NEXT 81920 PCTINCREASE 0 ))
    TABLESPACE USERS_SSP1_TRANSACTION NOLOGGING
    PARTITION BY RANGE (SESSION_ID)
    (PARTITION calllog3_jan VALUES LESS THAN(substr('02212001',1,2))
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_dec VALUES LESS THAN (MAXVALUE)
    TABLESPACE USERS_SSP1_TRANSACTION)

    ERROR at line 11:
    ORA-14019: partition bound element must be one of: string, datetime or interval
    literal, number, or MAXVALUE

    Can anyone shed some light on this?


    Thanks In Advance.


    Nagesh

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    The only function you can use in range partitioning is TO_DATE(). Otherwise, you must use a literal value.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Why do you want to use "substr('02212001',1,2)" instead of simply using "02" ??

    Sanjay

  4. #4
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Becuase, fields rest of the value may contain another "02". And mainly I am concerned about the month wise partitions.

    Is it possible in Version 8.1.7/9i ?

    Thanks In Advance

    Nagesh

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Try this...

    Code:
    CREATE TABLE CALLLOG3 ( 
    CALLLOG_ID NUMBER (18) NOT NULL, 
    SESSION_ID VARCHAR2 (18) DEFAULT '0', 
    CONSTRAINT PK_CALLLOG3 
    PRIMARY KEY ( CALLLOG_ID ) 
    USING INDEX 
    TABLESPACE INDEX_SSP1_TRANSACTION PCTFREE 10 
    STORAGE ( INITIAL 65536 NEXT 81920 PCTINCREASE 0 )) 
    TABLESPACE USERS_SSP1_TRANSACTION NOLOGGING 
    PARTITION BY RANGE (SESSION_ID) 
    (PARTITION calllog3_jan VALUES LESS THAN ('020000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION, 
    PARTITION calllog3_dec VALUES LESS THAN (MAXVALUE) 
    TABLESPACE USERS_SSP1_TRANSACTION);
    Sanjay




    [Edited by SANJAY_G on 02-05-2002 at 01:44 AM]

  6. #6
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    What it does exactly? Is it something like partitioning on alphanumaric fields?

    Thanks In Advance.
    Nagesh

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    This will do exactly what you want. You can't use substr function while partitioning, you will have to provide a literal value. In your case all January records will be 01XXXX...
    i.e less than 02XXXX...

    Sanjay

  8. #8
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Sorry Sanjay,

    I tried. It does not work. Here are the results.

    PPMS5_SSP1@MASSBEJ2> select * from calllog3 partition(calllog3_dec);

    CALLLOG_ID SESSION_ID
    ---------- ------------------
    123 02012001123456
    124 02042002345678
    125 020000000000000000

    PPMS5_SSP1@MASSBEJ2> select * from calllog3 partition(calllog3_jan);

    no rows selected

    Please advise.
    Nagesh

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    How many characters you have in the session_id column?
    Is it varying or always having 14 characters. I am assuming that it has 14 chracters always.
    Try replacing maxvalue by '12000000000000' in callog3_dec partition definition.
    Similarly use 11...., 10.... for Nov, Oct and so on.. let me know the results.

    Sanjay

  10. #10
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Assuming SESSION_ID is 14 characters below is the code. If it is more than 14 then put additional 0s.
    Code:
    CREATE TABLE CALLLOG3 (
    CALLLOG_ID NUMBER (18) NOT NULL, 
    SESSION_ID VARCHAR2 (18) DEFAULT '0', 
    CONSTRAINT PK_CALLLOG3 
    PRIMARY KEY ( CALLLOG_ID ) 
    USING INDEX 
    TABLESPACE INDEX_SSP1_TRANSACTION PCTFREE 10 
    STORAGE ( INITIAL 65536 NEXT 81920 PCTINCREASE 0 )) 
    TABLESPACE USERS_SSP1_TRANSACTION NOLOGGING 
    PARTITION BY RANGE (SESSION_ID) 
    (PARTITION calllog3_jan VALUES LESS THAN ('02000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_feb VALUES LESS THAN ('03000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_mar VALUES LESS THAN ('04000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_apr VALUES LESS THAN ('05000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_may VALUES LESS THAN ('06000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_jun VALUES LESS THAN ('07000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_jul VALUES LESS THAN ('08000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_aug VALUES LESS THAN ('09000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_sep VALUES LESS THAN ('10000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_oct VALUES LESS THAN ('11000000000000') 
    TABLESPACE USERS_SSP1_TRANSACTION, 
    PARTITION calllog3_nov VALUES LESS THAN ('12000000000000')
    TABLESPACE USERS_SSP1_TRANSACTION,
    PARTITION calllog3_dec VALUES LESS THAN (MAXVALUE)
    TABLESPACE USERS_SSP1_TRANSACTION);
    Sanjay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width