FAQ
Dear List,

I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format "MONYYYY" . I need to partition the table based
on the year YYYY, that is, substr(report_cycle_cd, 4,4).

Substr function doesn't seem to be permitted in the partitioning syntax and
so am getting errors. Only TO_DATE function seems to be permitted. Since it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.

Appreciate any suggestions.

Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
INET: jbdonga_at_ucdavis.edu

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Khedr, Waleed at Mar 13, 2003 at 9:48 pm
    Easy to do if it was 'YYYYMON'

    Oracle 9i has list partitioning that may work for you.

    Regards,

    Waleed

    -----Original Message-----
    Sent: Thursday, March 13, 2003 4:14 PM
    To: Multiple recipients of list ORACLE-L
    ??????

    Dear List,

    I have a table of size approx 10gig, and I need to partition based on the
    YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
    data in the column of format "MONYYYY" . I need to partition the table based
    on the year YYYY, that is, substr(report_cycle_cd, 4,4).

    Substr function doesn't seem to be permitted in the partitioning syntax and
    so am getting errors. Only TO_DATE function seems to be permitted. Since it
    is not a date column, I would like to know if there is a way to RANGE
    partition the table, instead of HASH partitioning.

    Appreciate any suggestions.

    Thanks,
    -- Babu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Igor Neyman at Mar 13, 2003 at 10:18 pm
    No, I think you will have to add a column to store 'YYYY' separately in
    order to partition on it.

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, March 13, 2003 4:14 PM
    Dear List,

    I have a table of size approx 10gig, and I need to partition based on the
    YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
    data in the column of format "MONYYYY" . I need to partition the table based
    on the year YYYY, that is, substr(report_cycle_cd, 4,4).

    Substr function doesn't seem to be permitted in the partitioning syntax and
    so am getting errors. Only TO_DATE function seems to be permitted. Since it
    is not a date column, I would like to know if there is a way to RANGE
    partition the table, instead of HASH partitioning.

    Appreciate any suggestions.

    Thanks,
    -- Babu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • DENNIS WILLIAMS at Mar 13, 2003 at 10:19 pm
    Babu
    I don't think partitions are clearly documented anywhere. Here is some SQL
    that works so you can see how to use a date function. It partitions on two
    columns, but I wanted you to see something that works.

    add partition sum_fy_28

    values less than ('FY', to_date('02012003','mmddyyyy'))
    tablespace data_fy_28

    -----Original Message-----
    Sent: Thursday, March 13, 2003 3:14 PM
    To: Multiple recipients of list ORACLE-L
    ??????

    Dear List,

    I have a table of size approx 10gig, and I need to partition based on the
    YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
    data in the column of format "MONYYYY" . I need to partition the table based
    on the year YYYY, that is, substr(report_cycle_cd, 4,4).

    Substr function doesn't seem to be permitted in the partitioning syntax and
    so am getting errors. Only TO_DATE function seems to be permitted. Since it
    is not a date column, I would like to know if there is a way to RANGE
    partition the table, instead of HASH partitioning.

    Appreciate any suggestions.

    Thanks,
    -- Babu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Janardhana Babu Donga at Mar 13, 2003 at 10:49 pm
    Is list partitioning available in 8i? Iam on 8.1.7.4.

    Babu

    -----Original Message-----
    Sent: Thursday, March 13, 2003 1:49 PM
    To: Multiple recipients of list ORACLE-L
    ???

    Easy to do if it was 'YYYYMON'

    Oracle 9i has list partitioning that may work for you.

    Regards,

    Waleed

    -----Original Message-----
    Sent: Thursday, March 13, 2003 4:14 PM
    To: Multiple recipients of list ORACLE-L
    ??????

    Dear List,

    I have a table of size approx 10gig, and I need to partition based on the
    YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
    data in the column of format "MONYYYY" . I need to partition the table based
    on the year YYYY, that is, substr(report_cycle_cd, 4,4).

    Substr function doesn't seem to be permitted in the partitioning syntax and
    so am getting errors. Only TO_DATE function seems to be permitted. Since it
    is not a date column, I would like to know if there is a way to RANGE
    partition the table, instead of HASH partitioning.

    Appreciate any suggestions.

    Thanks,
    -- Babu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Khedr, Waleed at Mar 13, 2003 at 11:30 pm
    No

    -----Original Message-----
    Sent: Thursday, March 13, 2003 5:49 PM
    To: Multiple recipients of list ORACLE-L
    ???

    Is list partitioning available in 8i? Iam on 8.1.7.4.

    Babu

    -----Original Message-----
    Sent: Thursday, March 13, 2003 1:49 PM
    To: Multiple recipients of list ORACLE-L
    ???

    Easy to do if it was 'YYYYMON'

    Oracle 9i has list partitioning that may work for you.

    Regards,

    Waleed

    -----Original Message-----
    Sent: Thursday, March 13, 2003 4:14 PM
    To: Multiple recipients of list ORACLE-L
    ??????

    Dear List,

    I have a table of size approx 10gig, and I need to partition based on the
    YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
    data in the column of format "MONYYYY" . I need to partition the table based
    on the year YYYY, that is, substr(report_cycle_cd, 4,4).

    Substr function doesn't seem to be permitted in the partitioning syntax and
    so am getting errors. Only TO_DATE function seems to be permitted. Since it
    is not a date column, I would like to know if there is a way to RANGE
    partition the table, instead of HASH partitioning.

    Appreciate any suggestions.

    Thanks,
    -- Babu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Arup Nanda at Mar 14, 2003 at 4:33 am
    Babu,

    On a slightly different approach, is it possible to update the column to the
    format YYYYMON, from the present MONYYYY? If so, then there is hope. You
    could create the partitions like this

    PARTITIONING BY RANGE (REPORT_CYCLE_CD)

    (

    PARTITION P1998 VALUES LESS THAN ('1999%'),
    PARTITION P1999 VALUES LESS THAN ('2000%'),
    PARTITION P2000 VALUES LESS THAN ('2001%'),

    .........
    PARTITION PMAX VALUES LESS THAN (maxvalue)
    )

    Hope this helps.

    Arup Nanda

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, March 13, 2003 5:19 PM
    Babu
    I don't think partitions are clearly documented anywhere. Here is some SQL
    that works so you can see how to use a date function. It partitions on two
    columns, but I wanted you to see something that works.

    add partition sum_fy_28
    values less than ('FY', to_date('02012003','mmddyyyy'))
    tablespace data_fy_28

    -----Original Message-----
    Sent: Thursday, March 13, 2003 3:14 PM
    To: Multiple recipients of list ORACLE-L
    ??????


    Dear List,

    I have a table of size approx 10gig, and I need to partition based on the
    YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
    data in the column of format "MONYYYY" . I need to partition the table based
    on the year YYYY, that is, substr(report_cycle_cd, 4,4).

    Substr function doesn't seem to be permitted in the partitioning syntax and
    so am getting errors. Only TO_DATE function seems to be permitted. Since it
    is not a date column, I would like to know if there is a way to RANGE
    partition the table, instead of HASH partitioning.

    Appreciate any suggestions.

    Thanks,
    -- Babu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Janardhana Babu Donga at Mar 14, 2003 at 5:29 pm
    This seems to be a good idea. I will see if this is acceptable to my people.
    Earliar I suggested to change to date field, and was not acceptable for them
    as there seems plenty of code needs to be changed. I will see if this change
    is acceptable for them.

    One thing I could understand clearly from the LIST MEMBERS is that it is not
    at all possible to range partition without changing the column
    type/contents. I have two options now, one with what you suggested.

    Thanks for your help and thanks for all those who replied.

    Babu

    -----Original Message-----
    Sent: Thursday, March 13, 2003 8:34 PM
    To: Multiple recipients of list ORACLE-L
    ???

    Babu,

    On a slightly different approach, is it possible to update the column to the
    format YYYYMON, from the present MONYYYY? If so, then there is hope. You
    could create the partitions like this

    PARTITIONING BY RANGE (REPORT_CYCLE_CD)

    (

    PARTITION P1998 VALUES LESS THAN ('1999%'),
    PARTITION P1999 VALUES LESS THAN ('2000%'),
    PARTITION P2000 VALUES LESS THAN ('2001%'),

    .........
    PARTITION PMAX VALUES LESS THAN (maxvalue)
    )

    Hope this helps.

    Arup Nanda

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, March 13, 2003 5:19 PM
    Babu
    I don't think partitions are clearly documented anywhere. Here is some SQL
    that works so you can see how to use a date function. It partitions on two
    columns, but I wanted you to see something that works.
    add partition sum_fy_28
    values less than ('FY', to_date('02012003','mmddyyyy'))
    tablespace data_fy_28
    -----Original Message-----
    Sent: Thursday, March 13, 2003 3:14 PM
    To: Multiple recipients of list ORACLE-L
    ??????


    Dear List, >
    I have a table of size approx 10gig, and I need to partition based on the
    YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
    data in the column of format "MONYYYY" . I need to partition the table based
    on the year YYYY, that is, substr(report_cycle_cd, 4,4).

    Substr function doesn't seem to be permitted in the partitioning syntax and
    so am getting errors. Only TO_DATE function seems to be permitted. Since it
    is not a date column, I would like to know if there is a way to RANGE
    partition the table, instead of HASH partitioning. >
    Appreciate any suggestions. >
    Thanks,
    -- Babu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Janardhana Babu Donga
    INET: jbdonga_at_ucdavis.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 13, '03 at 9:14p
activeMar 14, '03 at 5:29p
posts8
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase