Paritions
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Paritions

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    Paritions

    Hi all,

    I have existing table with 250 million records. I want to create partitions based on ERDAT & AEDAT.

    How can i create a paritions on this existing table?. Please Correct me if i am wrong with my syntax.


    ALTER TABLE VBAP_PART
    ADD PARTITION BY RANGE(ERDAT,AEDAT)
    (PARTITION DATE_1997 VALUES LESS THAN (TO_DATE('31-DEC-1997'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_1998 VALUES LESS THAN (TO_DATE('31-DEC-1998'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_1999 VALUES LESS THAN (TO_DATE('31-DEC-1999'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2000 VALUES LESS THAN (TO_DATE('31-DEC-2000'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2001 VALUES LESS THAN (TO_DATE('31-DEC-2001'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2002 VALUES LESS THAN (TO_DATE('31-DEC-2002'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2003 VALUES LESS THAN (TO_DATE('31-DEC-2003'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2004 VALUES LESS THAN (TO_DATE('31-DEC-2004'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2005 VALUES LESS THAN (TO_DATE('31-DEC-2005'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2006 VALUES LESS THAN (TO_DATE('31-DEC-2006'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2007 VALUES LESS THAN (TO_DATE('31-DEC-2007'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2008 VALUES LESS THAN (TO_DATE('31-DEC-2008'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2009 VALUES LESS THAN (TO_DATE('31-DEC-2009'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2010 VALUES LESS THAN (TO_DATE('31-DEC-2010'),'DD-MON-YYYY')) TABLESPACE DATA_STG,
    (PARTITION DATE_2010_LATER VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_STG)
    /

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Cool

    Quote Originally Posted by pranavgovind
    How can i create a paritions on this existing table?. Please Correct me if i am wrong with my syntax.
    You cannot create partitions on an existing non-partitioned table.

    Also correct the date format:
    Code:
    ...
    VALUES LESS THAN (TO_DATE('31-DEC-1997','DD-MON-YYYY')) 
    ...



    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    Thank you so much Lkbrwn. Your response is appreciated.

  4. #4
    Join Date
    Dec 2005
    Posts
    195
    All, I partitioned the VBAP_PART table with keys ERDAT & AEDAT.

    Now i have a two questions.

    Question 1
    ========
    I used to use the below statment for analyze. Can i use the same for partition table?.

    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'VBAP_PART', TABNAME => 'SALES',ESTIMATE_PERCENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);

    Question 2
    ========

    I need to use the below query most of the time.
    SELECT * FROM VBAP_PART WHERE XVEL='ISAC';

    The data are stored in all the partitions for this query. I guess, it has to go through all the paritions. Does it reduce the performance when we compared to non paritioned table?.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    1) fine - will do the whole thing
    2) no problem a full table scan, is still a full table scan. Is that column indexed and if so in what way

  6. #6
    Join Date
    Dec 2005
    Posts
    195
    Hi,

    It my general question. The table is paritioned table. When i run the query, based on my condition in where clause, the data are scattered in all the paritions. But we have proper indexes. Can we see good performance on this situation when we compared to non partitioned table.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    if you have to go through all partitions anyway - no benefit

  8. #8
    Join Date
    Dec 2005
    Posts
    195
    Thanks for the answer.

  9. #9
    Join Date
    Dec 2005
    Posts
    195
    Dear all, Can i analyze only specific partition on the table? Please let me know. Thanks.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    partname =>

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