DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: table partitions in oracle 10g

  1. #1
    Join Date
    Dec 2007
    Posts
    82

    table partitions in oracle 10g

    I have a table in which i have a date column. I need to do a partition due to many (billions) rows in it. I need to do year (including future years) wise first and then months wise in each year. Could any please guide me how to do this with commands and materials..
    (least date is 01-01-1998 in the table).
    thanks
    /MR

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Have you even bothered to look at the syntax?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Dec 2007
    Posts
    82
    yes, i checked but could not to do so..

    SQL> CREATE TABLE EMP1
    2 (EMPNO NUMBER,
    3 HIREDATE DATA)
    4 TABLESPACE USERS
    5 PARTITION BY list(HIREDATE)
    6 (PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (1998,1999,2000) TABLESPACE TS01,
    7 PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (2001,2002,2003) TABLESPACE TS02,
    8 PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (2004,2005,2006) TABLESPACE TS03,
    9 PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (2007,2008,2009) TABLESPACE TS04,
    10 PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (2010,2011,2012) TABLESPACE TS05
    11 )
    12 ENABLE ROW MOVEMENT;
    (PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (1998,1999,2000) TABLESPACE TS01,
    *
    ERROR at line 6:
    ORA-00926: missing VALUES keyword


    actually i need to do subpartition level too accordingly monthly wise, it means each year will have 12 more subpartitions, do we have any method to avoid hardcode values in partition level to meet future needs??
    please advice. thanks so much...

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    1- Your syntax is wrong, that's why you are getting the error.

    2- On 10g we cannot do it, you have to go to 11g for list/list or list/range partition/subpartitioning either way what you are looking for is to have 12 partitions per year so... what you don't create them in a "range" partitioning strategy instead of this non-sense list you are pursuing?

    3- Yes, Oracle will create new partitions for you -read about "interval" partitioning.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2007
    Posts
    82
    thanks for reply. my oracle is Enterprise Edition Release 10.1.0.4.0
    I can choose any type of partitioning whichever is applicable for best performance as i am the one to do so.. or suggest me.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    You partition a table to help during either one or both of the items below...

    a) Improve query performance.
    b) Facilitate purge strategy

    First question is, are you planning to partition that table for one -or better the two - of the above reasons?

    If your answer is Yes, even not knowing your specific needs I would be inclined to do range partitioning by date(*), perhaps cutting one partition per month -- How may rows do you expect to store in each partition in such an scenario?

    On the top of my head I do not remember if Ora10g 10.1 includes interval partitioning, either way we escaped from 10.1 as soon as we where able to do it.

    (*) In my experience most of the time there is a "date" column that certainly helps on purge strategy and at least doesn't hurt query performance. If this is your case you might consider that column as your partition key.
    Last edited by PAVB; 07-29-2008 at 10:16 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2007
    Posts
    82
    yes, exactly for the above 2 you mentioned.
    Per months almost 10 million+ records (max at moment) and i want to do on timestamp(date?) column data type and will be partition key. i am little unclear about this concept as i am doing first time. thanks a lot for you clear description...

  8. #8
    Join Date
    Dec 2007
    Posts
    82
    Can anyone give me the SQL statement to achieve the one i explained??

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    What do you have so far?

    Here are two hints if you are planning to go for RANGE partitioning in a DATE datatype column:

    1- Each partition has to be defined in a line like...
    Code:
    (PARTITION JUL2008 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE YOUR_TABLESPACE)
    2- Since you are not in 11g you cannot rely on interval partitioning then do not forget to define a MAX VALUE partition.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Jun 2005
    Posts
    31
    Quote Originally Posted by mdvreddy
    thanks for reply. my oracle is Enterprise Edition Release 10.1.0.4.0
    I can choose any type of partitioning whichever is applicable for best performance as i am the one to do so.. or suggest me.
    Some time ago I summarized arguments for the partitioning-intervall

    Daily, Weekly or Monthly Partitions?
    http://www.mercury-consulting-ltd.co...artitions.html

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