FAQ
Hi everyone,
I have an 11.2.0.3.0 RAC cluster. We have a table (in 12 schemas) that gets a decent amount of rows (depending on schema up to ~1m per day) and that we need to clean. Currently this is being done by a job that just deletes the rows in chunks. This of course, produces a lot of redo and causes extra load on the system. I'm looking into partitioning it and have some questions.

I have read that hash-partitioning is great for RAC; however, the purpose of this partitioning is to make removing old data easier. I also need to be sure that we never are in a situation where the "next" partition is not available. As such I was planning on using interval partitioning on the "time" column (number datatype in unix milliseconds).

About the table

1. The table has a sequence generated Primary key.

2. We want to cleanup up rows that are not current (<sysdate-n) on a column in the table

3. The table is heavily used.

As such, the Primary Key has to be a global index. My plan is to drop the partitions as they get old (except the anchor partition of course), but my concern is the impact of the "update indexes" work to keep the global PK usable. Is the "update indexes" done online while the table is in use? I can't shut down the application to do this work, so I'm wondering how big an impact "update indexes" might have or if there are better ways to do this?

I don't see a need for the sequence based primary key so I might suggest eliminating it if possible.

I'd love to get opinions and experience on this?

(Oh, and I wish I had a test system ... yep)

Thanks,

Jed

Search Discussions

  • Tim Gorman at Jan 31, 2012 at 4:44 pm
    Jed,
    Why not get rid of the sequence-generated PK column and instead make
    another NUMBER column the PK, itself generated both from the needed
    timestamp appended to a sequence generated data value to ensure it's
    uniqueness? If you have that, then you can RANGE partition on that
    NUMBER value according to your data manipulation requirements and also
    have a LOCAL partitioned index so that you have no GLOBAL index issues.

    So, not sure what the scale/precision of the current PK column is, but
    let's assume NUMBER(12), just for the sake of example? With the
    following code, you can generate timestamp values synthesized with a
    sequence...

    SQL> select
    (to_number(to_char(systimestamp,'YYYYMMDDHH24MISSFF'))*1000000000)+timtest.nextval
    nbr from dual;

    NBR
    ----------------------------------
    20120131112513714848000000011


    Now, you can create your tables as follows...

    create table xyz
    (
    tstamp number(30) not null,
    / (...other column definitions...)/
    ) partition by range (tstamp)
    (partition p20120101 values less than (20120102000000000000)
    tablespace P201201_DATA,
    partition p20120102 values less than (20120103000000000000)
    tablespace P201201_DATA,
    / ...and so on.../
    partition p20141231 values less than (20150101000000000000)
    tablespace P201412_DATA,
    partition pmaxvalue values less than (maxvalue) tablespace
    PMAX_DATA
    );

    create unique index xyz_pk on xyz(tstamp) local
    (partition p20120101 tablespace P201201_INDX,
    partition p20120102 tablespace P201201_INDX,
    / ...and so on.../
    partition p20141231 tablespace P201412_INDX,
    partition pmaxvalue tablespace PMAX_INDX
    );

    alter table xyz add constraint xyz_pk primary key (tstamp);


    So now you have daily partitions, so if you wish to load or purge on a
    daily basis, a simple DROP PARTITION does the job with no UPDATE GLOBAL
    INDEXES issues (at least, not for a primary key). To get the timestamp,
    you'll need to truncate off the trailing digits. The example above
    shows monthly tablespaces to encourage tiered storage and READ ONLY
    tablespaces and optimization of backups, but that is another post for
    another day. Other details to be worked out, but that's the general idea...

    Hope this helps...

    --
    Tim Gorman
    consultant -> Evergreen Database Technologies, Inc.
    postal => PO Box 352151, Westminster CO 80035
    website => http://www.EvDBT.com/
    email => Tim@EvDBT.com
    mobile => +1-303-885-4526
    fax => +1-303-484-3608
    Lost Data? => http://www.ora600.be/ for info about DUDE...


    On 1/31/2012 8:04 AM, Walker, Jed S wrote:
    Hi everyone,
    I have an 11.2.0.3.0 RAC cluster. We have a table (in 12 schemas) that gets a decent amount of rows (depending on schema up to ~1m per day) and that we need to clean. Currently this is being done by a job that just deletes the rows in chunks. This of course, produces a lot of redo and causes extra load on the system. I'm looking into partitioning it and have some questions.

    I have read that hash-partitioning is great for RAC; however, the purpose of this partitioning is to make removing old data easier. I also need to be sure that we never are in a situation where the "next" partition is not available. As such I was planning on using interval partitioning on the "time" column (number datatype in unix milliseconds).

    About the table

    1. The table has a sequence generated Primary key.

    2. We want to cleanup up rows that are not current (<sysdate-n) on a column in the table

    3. The table is heavily used.

    As such, the Primary Key has to be a global index. My plan is to drop the partitions as they get old (except the anchor partition of course), but my concern is the impact of the "update indexes" work to keep the global PK usable. Is the "update indexes" done online while the table is in use? I can't shut down the application to do this work, so I'm wondering how big an impact "update indexes" might have or if there are better ways to do this?

    I don't see a need for the sequence based primary key so I might suggest eliminating it if possible.

    I'd love to get opinions and experience on this?

    (Oh, and I wish I had a test system ... yep)

    Thanks,

    Jed


    --
    http://www.freelists.org/webpage/oracle-l





    --
    http://www.freelists.org/webpage/oracle-l
  • Walker, Jed S at Jan 31, 2012 at 4:53 pm
    Tim,

    I have asked them about the PK and if it could be eliminated (preferable), but if that can't be done, then this is really a great idea. There is no trigger to set the PK value so their code must do it, though I guess via a trigger it could be modified to what we want.

    Thank you for a great suggestion. (I've thought of similar solutions for other problems, but it didn't occur to me on this one, I appreciate it!)


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Tim Gorman
    Sent: Tuesday, January 31, 2012 9:43 AM
    To: oracle-l@freelists.org
    Subject: Re: RAC partitioning question

    Jed,
    Why not get rid of the sequence-generated PK column and instead make another NUMBER column the PK, itself generated both from the needed timestamp appended to a sequence generated data value to ensure it's uniqueness? If you have that, then you can RANGE partition on that NUMBER value according to your data manipulation requirements and also have a LOCAL partitioned index so that you have no GLOBAL index issues.

    So, not sure what the scale/precision of the current PK column is, but let's assume NUMBER(12), just for the sake of example? With the following code, you can generate timestamp values synthesized with a sequence...

    SQL> select
    (to_number(to_char(systimestamp,'YYYYMMDDHH24MISSFF'))*1000000000)+timtest.nextval
    nbr from dual;

    NBR
    ----------------------------------
    20120131112513714848000000011


    Now, you can create your tables as follows...

    create table xyz
    (
    tstamp number(30) not null,
    / (...other column definitions...)/
    ) partition by range (tstamp)
    (partition p20120101 values less than (20120102000000000000)
    tablespace P201201_DATA,
    partition p20120102 values less than (20120103000000000000)
    tablespace P201201_DATA,
    / ...and so on.../
    partition p20141231 values less than (20150101000000000000)
    tablespace P201412_DATA,
    partition pmaxvalue values less than (maxvalue) tablespace
    PMAX_DATA
    );

    create unique index xyz_pk on xyz(tstamp) local
    (partition p20120101 tablespace P201201_INDX,
    partition p20120102 tablespace P201201_INDX,
    / ...and so on.../
    partition p20141231 tablespace P201412_INDX,
    partition pmaxvalue tablespace PMAX_INDX
    );

    alter table xyz add constraint xyz_pk primary key (tstamp);


    So now you have daily partitions, so if you wish to load or purge on a daily basis, a simple DROP PARTITION does the job with no UPDATE GLOBAL INDEXES issues (at least, not for a primary key). To get the timestamp, you'll need to truncate off the trailing digits. The example above shows monthly tablespaces to encourage tiered storage and READ ONLY tablespaces and optimization of backups, but that is another post for another day. Other details to be worked out, but that's the general idea...

    Hope this helps...

    --
    Tim Gorman
    consultant -> Evergreen Database Technologies, Inc.
    postal => PO Box 352151, Westminster CO 80035
    website => http://www.EvDBT.com/
    email => Tim@EvDBT.com
    mobile => +1-303-885-4526
    fax => +1-303-484-3608
    Lost Data? => http://www.ora600.be/ for info about DUDE...


    On 1/31/2012 8:04 AM, Walker, Jed S wrote:
    Hi everyone,
    I have an 11.2.0.3.0 RAC cluster. We have a table (in 12 schemas) that gets a decent amount of rows (depending on schema up to ~1m per day) and that we need to clean. Currently this is being done by a job that just deletes the rows in chunks. This of course, produces a lot of redo and causes extra load on the system. I'm looking into partitioning it and have some questions.

    I have read that hash-partitioning is great for RAC; however, the purpose of this partitioning is to make removing old data easier. I also need to be sure that we never are in a situation where the "next" partition is not available. As such I was planning on using interval partitioning on the "time" column (number datatype in unix milliseconds).

    About the table

    1. The table has a sequence generated Primary key.

    2. We want to cleanup up rows that are not current (<sysdate-n) on a column in the table

    3. The table is heavily used.

    As such, the Primary Key has to be a global index. My plan is to drop the partitions as they get old (except the anchor partition of course), but my concern is the impact of the "update indexes" work to keep the global PK usable. Is the "update indexes" done online while the table is in use? I can't shut down the application to do this work, so I'm wondering how big an impact "update indexes" might have or if there are better ways to do this?

    I don't see a need for the sequence based primary key so I might suggest eliminating it if possible.

    I'd love to get opinions and experience on this?

    (Oh, and I wish I had a test system ... yep)

    Thanks,

    Jed


    --
    http://www.freelists.org/webpage/oracle-l





    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Jan 31, 2012 at 6:24 pm
    Any reason not to just add the date/timestamp column into the PK and make
    it two columns? This would result in allowing the index to be local (the
    date col is the partition key col) without any modification to the current
    table definition.
    On Tue, Jan 31, 2012 at 8:42 AM, Tim Gorman wrote:

    Jed,
    Why not get rid of the sequence-generated PK column and instead make
    another NUMBER column the PK, itself generated both from the needed
    timestamp appended to a sequence generated data value to ensure it's
    uniqueness? If you have that, then you can RANGE partition on that
    NUMBER value according to your data manipulation requirements and also
    have a LOCAL partitioned index so that you have no GLOBAL index issues.
    --
    Regards,
    Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <http://bit.ly/v733dJ> |
    linkedin <http://linkd.in/gregrahn>


    --
    http://www.freelists.org/webpage/oracle-l
  • Walker, Jed S at Jan 31, 2012 at 6:00 pm
    Greg,

    I was just thinking about that and made some notes. My only thought (would need to test) is whether I could still have a unique index on just the old PK column without it having to be global. (I'll update on that)

    For example:
    Pk_id number
    Start_time number
    Pk on (start_time, pk_id)
    Unique index on (pk_id)


    Also, on Tim's point, I guess I could also make the old PK smaller since it could wrap now that is appended to the start_time_in_millis columns. The chance of rolling through, say 10000, sequence values within a second is almost nill.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Greg Rahn
    Sent: Tuesday, January 31, 2012 10:25 AM
    To: tim@evdbt.com
    Cc: oracle-l@freelists.org
    Subject: Re: RAC partitioning question

    Any reason not to just add the date/timestamp column into the PK and make it two columns? This would result in allowing the index to be local (the date col is the partition key col) without any modification to the current table definition.
    On Tue, Jan 31, 2012 at 8:42 AM, Tim Gorman wrote:

    Jed,
    Why not get rid of the sequence-generated PK column and instead make
    another NUMBER column the PK, itself generated both from the needed
    timestamp appended to a sequence generated data value to ensure it's
    uniqueness? If you have that, then you can RANGE partition on that
    NUMBER value according to your data manipulation requirements and also
    have a LOCAL partitioned index so that you have no GLOBAL index issues.
    --
    Regards,
    Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <http://bit.ly/v733dJ> | linkedin <http://linkd.in/gregrahn>


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Jan 31, 2012 at 6:18 pm
    The restriction on unique partitioned indexes is such:
    - Unique global partitioned indexes must always be prefixed with the
    partitioning columns.
    - Unique local indexes must have the partitioning key of the table as a
    subset of the unique key definition.

    http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_oltp.htm#VLDBG1369

    My point is that there really is no difference between 1 column (timestamp
    sequence) or 2 columns (timestamp, sequence) for a PK.
    On Tue, Jan 31, 2012 at 9:53 AM, Walker, Jed S wrote:

    Greg,

    I was just thinking about that and made some notes. My only thought (would
    need to test) is whether I could still have a unique index on just the old
    PK column without it having to be global. (I'll update on that)

    For example:
    Pk_id number
    Start_time number
    Pk on (start_time, pk_id)
    Unique index on (pk_id)


    Also, on Tim's point, I guess I could also make the old PK smaller since
    it could wrap now that is appended to the start_time_in_millis columns. The
    chance of rolling through, say 10000, sequence values within a second is
    almost nill.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Greg Rahn
    Sent: Tuesday, January 31, 2012 10:25 AM
    To: tim@evdbt.com
    Cc: oracle-l@freelists.org
    Subject: Re: RAC partitioning question

    Any reason not to just add the date/timestamp column into the PK and make
    it two columns? This would result in allowing the index to be local (the
    date col is the partition key col) without any modification to the current
    table definition.
    On Tue, Jan 31, 2012 at 8:42 AM, Tim Gorman wrote:

    Jed,
    Why not get rid of the sequence-generated PK column and instead make
    another NUMBER column the PK, itself generated both from the needed
    timestamp appended to a sequence generated data value to ensure it's
    uniqueness? If you have that, then you can RANGE partition on that
    NUMBER value according to your data manipulation requirements and also
    have a LOCAL partitioned index so that you have no GLOBAL index issues.
    --
    Regards,
    Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <
    http://bit.ly/v733dJ> | linkedin <http://linkd.in/gregrahn>


    --
    http://www.freelists.org/webpage/oracle-l


    --
    Regards,
    Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <http://bit.ly/v733dJ> |
    linkedin <http://linkd.in/gregrahn>


    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jan 31, 2012 at 6:34 pm
    In fact the two-column index may be significantly more space efficient.

    Given the average 12 rows per second (1M rows per day, 86,400 seconds per
    day (except for one day later on this year)) it would be sensible to
    compress on the first column of the two column index, storing (on average)
    one copy of each date value per block instead of about 12. The benefit is
    harder to assess if the 1M rows is actually 9 batches of 100,000 each plus
    one row per second, of course.

    You can't do any compression on the concatenated thing, and the 20 digit
    number imposed by the structure is pretty fixed in its storage, while you
    might choose to make the numeric bit of a two column key a cyclic sequence
    limited to (say) 20,000 - or some smaller, but very safe, limit on the
    number of rows arriving per second - keeping that part of the index to the
    smallest possible size.

    One problem - if the values are imposed from the front end - how do you
    guarantee to avoid timing differences between client machines, and how do
    you avoid sequence clashes ? The key values really ought to be generated
    (with a "returning" clause) by the database.

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Oracle Core (Apress 2011)
    http://www.apress.com/9781430239543


    ----- Original Message -----
    From: "Greg Rahn" <greg@structureddata.org>
    To: "Walker, Jed S" <Jed_Walker@cable.comcast.com>
    Cc: <tim@evdbt.com>; <oracle-l@freelists.org>
    Sent: Tuesday, January 31, 2012 6:16 PM
    Subject: Re: RAC partitioning question


    The restriction on unique partitioned indexes is such:
    - Unique global partitioned indexes must always be prefixed with the
    partitioning columns.
    - Unique local indexes must have the partitioning key of the table as a
    subset of the unique key definition.

    http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_oltp.htm#VLDBG1369

    My point is that there really is no difference between 1 column (timestamp
    sequence) or 2 columns (timestamp, sequence) for a PK.

    On Tue, Jan 31, 2012 at 9:53 AM, Walker, Jed S
    wrote:
    Greg,

    I was just thinking about that and made some notes. My only thought
    (would
    need to test) is whether I could still have a unique index on just the
    old
    PK column without it having to be global. (I'll update on that)

    For example:
    Pk_id number
    Start_time number
    Pk on (start_time, pk_id)
    Unique index on (pk_id)


    Also, on Tim's point, I guess I could also make the old PK smaller since
    it could wrap now that is appended to the start_time_in_millis columns.
    The
    chance of rolling through, say 10000, sequence values within a second is
    almost nill.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org

    On Behalf Of Greg Rahn
    Sent: Tuesday, January 31, 2012 10:25 AM
    To: tim@evdbt.com
    Cc: oracle-l@freelists.org
    Subject: Re: RAC partitioning question

    Any reason not to just add the date/timestamp column into the PK and make
    it two columns? This would result in allowing the index to be local (the
    date col is the partition key col) without any modification to the
    current
    table definition.
    On Tue, Jan 31, 2012 at 8:42 AM, Tim Gorman wrote:

    Jed,
    Why not get rid of the sequence-generated PK column and instead make
    another NUMBER column the PK, itself generated both from the needed
    timestamp appended to a sequence generated data value to ensure it's
    uniqueness? If you have that, then you can RANGE partition on that
    NUMBER value according to your data manipulation requirements and also
    have a LOCAL partitioned index so that you have no GLOBAL index issues.
    --
    Regards,
    Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <
    http://bit.ly/v733dJ> | linkedin <http://linkd.in/gregrahn>


    --
    http://www.freelists.org/webpage/oracle-l


    --
    Regards,
    Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter
    <http://bit.ly/v733dJ> |
    linkedin <http://linkd.in/gregrahn>


    --
    http://www.freelists.org/webpage/oracle-l




    -----
    No virus found in this message.
    Checked by AVG - www.avg.com
    Version: 2012.0.1901 / Virus Database: 2109/4777 - Release Date: 01/30/12


    --
    http://www.freelists.org/webpage/oracle-l
  • Tim Gorman at Jan 31, 2012 at 6:42 pm
    Agreed, and grateful for the generous application of common sense, Greg.

    The only thing I'd add is to get rid of the NUMBER column representing a
    date/time and use a DATE or TIMESTAMP datatype. Can't say how many
    times the road to hell has been paved with unnecessary datatype
    conversions. No upside and plenty of downside.


    On 1/31/2012 11:16 AM, Greg Rahn wrote:
    The restriction on unique partitioned indexes is such:
    - Unique global partitioned indexes must always be prefixed with the
    partitioning columns.
    - Unique local indexes must have the partitioning key of the table as a
    subset of the unique key definition.

    http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_oltp.htm#VLDBG1369

    My point is that there really is no difference between 1 column (timestamp
    sequence) or 2 columns (timestamp, sequence) for a PK.

    On Tue, Jan 31, 2012 at 9:53 AM, Walker, Jed S<Jed_Walker@cable.comcast.com
    wrote:
    Greg,

    I was just thinking about that and made some notes. My only thought (would
    need to test) is whether I could still have a unique index on just the old
    PK column without it having to be global. (I'll update on that)

    For example:
    Pk_id number
    Start_time number
    Pk on (start_time, pk_id)
    Unique index on (pk_id)


    Also, on Tim's point, I guess I could also make the old PK smaller since
    it could wrap now that is appended to the start_time_in_millis columns. The
    chance of rolling through, say 10000, sequence values within a second is
    almost nill.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Greg Rahn
    Sent: Tuesday, January 31, 2012 10:25 AM
    To: tim@evdbt.com
    Cc: oracle-l@freelists.org
    Subject: Re: RAC partitioning question

    Any reason not to just add the date/timestamp column into the PK and make
    it two columns? This would result in allowing the index to be local (the
    date col is the partition key col) without any modification to the current
    table definition.
    On Tue, Jan 31, 2012 at 8:42 AM, Tim Gormanwrote:
    Jed,
    Why not get rid of the sequence-generated PK column and instead make
    another NUMBER column the PK, itself generated both from the needed
    timestamp appended to a sequence generated data value to ensure it's
    uniqueness? If you have that, then you can RANGE partition on that
    NUMBER value according to your data manipulation requirements and also
    have a LOCAL partitioned index so that you have no GLOBAL index issues.
    --
    Regards,
    Greg Rahn | blog<http://bit.ly/u9N0i8> | twitter<
    http://bit.ly/v733dJ> | linkedin<http://linkd.in/gregrahn>


    --
    http://www.freelists.org/webpage/oracle-l

    --
    Tim Gorman
    consultant -> Evergreen Database Technologies, Inc.
    postal => PO Box 352151, Westminster CO 80035
    website => http://www.EvDBT.com/
    email => Tim@EvDBT.com
    mobile => +1-303-885-4526
    fax => +1-303-484-3608
    Lost Data? => http://www.ora600.be/ for info about DUDE...

    --
    http://www.freelists.org/webpage/oracle-l
  • Walker, Jed S at Jan 31, 2012 at 9:22 pm
    Thanks again everyone for the ideas. I tried to push for changing the column but the application uses that instead of timestamp, well, I won't get into it, bad design is bad design eh?

    I like the idea to make the two column index and compress the start_time_in_millis; however,

    This is a child table and I asked the developer if they really use the sequence based primary key column. He checked the code and said he can find nowhere where it is actually referenced, sheesh. That is good though, as it means we can probably remove the primary key (one less index) and maybe the column, and then I can partition on the other time column thus having all local indexes. That would be so nice.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Tim Gorman
    Sent: Tuesday, January 31, 2012 11:41 AM
    To: oracle-l@freelists.org
    Subject: Re: RAC partitioning question

    Agreed, and grateful for the generous application of common sense, Greg.

    The only thing I'd add is to get rid of the NUMBER column representing a date/time and use a DATE or TIMESTAMP datatype. Can't say how many times the road to hell has been paved with unnecessary datatype conversions. No upside and plenty of downside.


    On 1/31/2012 11:16 AM, Greg Rahn wrote:
    The restriction on unique partitioned indexes is such:
    - Unique global partitioned indexes must always be prefixed with the
    partitioning columns.
    - Unique local indexes must have the partitioning key of the table as a
    subset of the unique key definition.

    http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_oltp.htm#VL
    DBG1369

    My point is that there really is no difference between 1 column
    (timestamp
    sequence) or 2 columns (timestamp, sequence) for a PK.

    On Tue, Jan 31, 2012 at 9:53 AM, Walker, Jed
    S<Jed_Walker@cable.comcast.com
    wrote:
    Greg,

    I was just thinking about that and made some notes. My only thought
    (would need to test) is whether I could still have a unique index on
    just the old PK column without it having to be global. (I'll update
    on that)

    For example:
    Pk_id number
    Start_time number
    Pk on (start_time, pk_id)
    Unique index on (pk_id)


    Also, on Tim's point, I guess I could also make the old PK smaller
    since it could wrap now that is appended to the start_time_in_millis
    columns. The chance of rolling through, say 10000, sequence values
    within a second is almost nill.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org

    On Behalf Of Greg Rahn
    Sent: Tuesday, January 31, 2012 10:25 AM
    To: tim@evdbt.com
    Cc: oracle-l@freelists.org
    Subject: Re: RAC partitioning question

    Any reason not to just add the date/timestamp column into the PK and
    make it two columns? This would result in allowing the index to be
    local (the date col is the partition key col) without any
    modification to the current table definition.
    On Tue, Jan 31, 2012 at 8:42 AM, Tim Gormanwrote:
    Jed,
    Why not get rid of the sequence-generated PK column and instead make
    another NUMBER column the PK, itself generated both from the needed
    timestamp appended to a sequence generated data value to ensure it's
    uniqueness? If you have that, then you can RANGE partition on that
    NUMBER value according to your data manipulation requirements and
    also have a LOCAL partitioned index so that you have no GLOBAL index issues.
    --
    Regards,
    Greg Rahn | blog<http://bit.ly/u9N0i8> | twitter<
    http://bit.ly/v733dJ> | linkedin<http://linkd.in/gregrahn>


    --
    http://www.freelists.org/webpage/oracle-l

    --
    Tim Gorman
    consultant -> Evergreen Database Technologies, Inc.
    postal => PO Box 352151, Westminster CO 80035
    website => http://www.EvDBT.com/
    email => Tim@EvDBT.com
    mobile => +1-303-885-4526
    fax => +1-303-484-3608
    Lost Data? => http://www.ora600.be/ for info about DUDE...

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Marcin Przepiorowski at Feb 1, 2012 at 9:24 am

    On Tue, Jan 31, 2012 at 9:19 PM, Walker, Jed S wrote:
    Thanks again everyone for the ideas. I tried to push for changing the column but the application uses that instead of timestamp, well, I won't get into it, bad design is bad design eh?
    Hi,

    One more remark about global indexes and partition drop - yet it is
    possible online but you will "pay" for it. When Oracle is busy with
    dropping your partition and updating global index and application is
    doing inserts your index will growing faster during that activity.
    Next thing to remember is that Oracle is reading index block related
    to every dropped row in partition so dropping partition is not a cost
    less operation anymore.

    After partition drop you should add index maintenance task to your
    schedule - coalesce is fine for most of cases but if you are dropping
    lot of rows you can end up with index rebuild as well. I create simple
    test case here -
    http://oracleprof.blogspot.com/2011/03/rolling-partition-and-global-index.html
    . In real life I got a index which was 10 x bigger that is should
    after 1 year of dropping daily partitions.
  • Jonathan Lewis at Feb 1, 2012 at 11:46 am
    Marcin,

    I've had a look at the blog item, and don't think it's really making a
    point that's restricted to partitioning.

    Any time you have mechanisms that only ever add data at the right hand side
    of an index, and then have bulk deletes scattered randomly through the
    entire length of the index, the utilisation of the average leaf block is
    bound to drop slowly over a number of delete cycles. This will happen
    whether or not you're talking about partitioning.

    On the plus side, though, if you have already licensed the partitioning
    option, you could create the index as a hash-partitioned index, which would
    make it much easier to do index maintenance when you thought an index had
    got to a point where the older blocks had more free space than you liked,
    because you can rebuild each partition online separately. (Unfortunately
    you can't coalesce individual partitions of a hash-partitioned index -- or
    maybe you can, but the coalesce does something completely different in the
    context of hash partitioning).

    I wrote a script a couple of years ago to draw a "picture" of what the
    index usage looked like - it might be quite entertaining to repeat your
    demo and draw the index after each delete cycle.


    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Oracle Core (Apress 2011)
    http://www.apress.com/9781430239543


    ----- Original Message -----
    From: "Marcin Przepiorowski" <pioro1@gmail.com>
    To: <Jed_Walker@cable.comcast.com>
    Cc: <oracle-l@freelists.org>
    Sent: Wednesday, February 01, 2012 9:15 AM
    Subject: Re: RAC partitioning question


    On Tue, Jan 31, 2012 at 9:19 PM, Walker, Jed S
    wrote:
    Thanks again everyone for the ideas. I tried to push for changing the
    column but the application uses that instead of timestamp, well, I won't
    get into it, bad design is bad design eh?
    Hi,

    One more remark about global indexes and partition drop - yet it is
    possible online but you will "pay" for it. When Oracle is busy with
    dropping your partition and updating global index and application is
    doing inserts your index will growing faster during that activity.
    Next thing to remember is that Oracle is reading index block related
    to every dropped row in partition so dropping partition is not a cost
    less operation anymore.

    After partition drop you should add index maintenance task to your
    schedule - coalesce is fine for most of cases but if you are dropping
    lot of rows you can end up with index rebuild as well. I create simple
    test case here -
    http://oracleprof.blogspot.com/2011/03/rolling-partition-and-global-index.html
    . In real life I got a index which was 10 x bigger that is should
    after 1 year of dropping daily partitions.
  • Marcin Przepiorowski at Feb 1, 2012 at 1:30 pm

    On Wed, Feb 1, 2012 at 11:40 AM, Jonathan Lewis wrote:

    Marcin,

    I've had a look at the blog item, and don't think it's really making a
    point that's restricted to partitioning.

    Any time you have mechanisms that only ever add data at the right hand side
    of an index, and then have bulk deletes scattered randomly through the
    entire length of the index, the utilisation of the average leaf block is
    bound to drop slowly over a number of delete cycles. This will happen
    whether or not you're talking about partitioning.
    Hi Jonathan,

    Thanks for you comment.
    I realize that this is not restricted to partitioning only but I found
    out that during partition drop index is growing faster - even with
    same insert rate.
    I'm going to check it again and use you script as well.
  • Walker, Jed S at Feb 6, 2012 at 5:38 pm
    Ugh, they just told me that they actually do use the PK throughout the code I'd rather not mess with that, but.
    I think going back to Tim's idea might be the best way to go (similar to what Jonathan suggested too).

    Here is my table;
    desc rdvrgw_chic.scheduled_recording;
    Name Null? Type
    ----------------------------------------- -------- ---------- ------
    SCHEDULED_RECORDING_ID NOT NULL NUMBER PK
    STB_SETTING_ID NOT NULL NUMBER
    VIRTUAL_CHANNEL_NUMBER NUMBER
    ...
    START_TIME_IN_MILLIS NUMBER unix time
    ...

    select max(scheduled_recording_id), max(start_time_in_millis) from rdvrgw_chic.scheduled_recording
    SQL> /

    MAX(SCHEDULED_RECORDING_ID) MAX(START_TIME_IN_MILLIS)
    --------------------------- ---------------------------
    420556165 1329886140000

    So, recreate sequence with a max of 9999999999 and have it cycle (theoretically I could probably use much less precision).
    Then make PK ID = (start_time_in_millis*10000000000)+sequence.nextval

    This should produce a unique value for PKID and allow me to partition based on the PK column and have no global indexes.

    Thoughts?

    Jed



    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jonathan Lewis
    Sent: Wednesday, February 01, 2012 4:41 AM
    To: oracle-l@freelists.org
    Subject: Re: RAC partitioning question


    Marcin,

    I've had a look at the blog item, and don't think it's really making a point that's restricted to partitioning.

    Any time you have mechanisms that only ever add data at the right hand side of an index, and then have bulk deletes scattered randomly through the entire length of the index, the utilisation of the average leaf block is bound to drop slowly over a number of delete cycles. This will happen whether or not you're talking about partitioning.

    On the plus side, though, if you have already licensed the partitioning option, you could create the index as a hash-partitioned index, which would make it much easier to do index maintenance when you thought an index had got to a point where the older blocks had more free space than you liked, because you can rebuild each partition online separately. (Unfortunately you can't coalesce individual partitions of a hash-partitioned index -- or maybe you can, but the coalesce does something completely different in the context of hash partitioning).

    I wrote a script a couple of years ago to draw a "picture" of what the index usage looked like - it might be quite entertaining to repeat your demo and draw the index after each delete cycle.


    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Oracle Core (Apress 2011)
    http://www.apress.com/9781430239543


    ----- Original Message -----
    From: "Marcin Przepiorowski" <pioro1@gmail.com>
    To: <Jed_Walker@cable.comcast.com>
    Cc: <oracle-l@freelists.org>
    Sent: Wednesday, February 01, 2012 9:15 AM
    Subject: Re: RAC partitioning question


    On Tue, Jan 31, 2012 at 9:19 PM, Walker, Jed S
    wrote:
    Thanks again everyone for the ideas. I tried to push for changing the
    column but the application uses that instead of timestamp, well, I won't
    get into it, bad design is bad design eh?
    Hi,

    One more remark about global indexes and partition drop - yet it is
    possible online but you will "pay" for it. When Oracle is busy with
    dropping your partition and updating global index and application is
    doing inserts your index will growing faster during that activity.
    Next thing to remember is that Oracle is reading index block related
    to every dropped row in partition so dropping partition is not a cost
    less operation anymore.

    After partition drop you should add index maintenance task to your
    schedule - coalesce is fine for most of cases but if you are dropping
    lot of rows you can end up with index rebuild as well. I create simple
    test case here -
    http://oracleprof.blogspot.com/2011/03/rolling-partition-and-global-index.html
    . In real life I got a index which was 10 x bigger that is should
    after 1 year of dropping daily partitions.
  • Tim Gorman at Jan 31, 2012 at 6:17 pm
    I think that works better. Thanks Greg!
    On 1/31/2012 10:24 AM, Greg Rahn wrote:
    Any reason not to just add the date/timestamp column into the PK and make
    it two columns? This would result in allowing the index to be local (the
    date col is the partition key col) without any modification to the current
    table definition.
    On Tue, Jan 31, 2012 at 8:42 AM, Tim Gormanwrote:
    Jed,
    Why not get rid of the sequence-generated PK column and instead make
    another NUMBER column the PK, itself generated both from the needed
    timestamp appended to a sequence generated data value to ensure it's
    uniqueness? If you have that, then you can RANGE partition on that
    NUMBER value according to your data manipulation requirements and also
    have a LOCAL partitioned index so that you have no GLOBAL index issues.
    --
    Tim Gorman
    consultant -> Evergreen Database Technologies, Inc.
    postal => PO Box 352151, Westminster CO 80035
    website => http://www.EvDBT.com/
    email => Tim@EvDBT.com
    mobile => +1-303-885-4526
    fax => +1-303-484-3608
    Lost Data? => http://www.ora600.be/ for info about DUDE...

    --
    http://www.freelists.org/webpage/oracle-l
  • Yechiel Adar at Feb 1, 2012 at 11:45 am
    I beg to differ with your basic assumption:
    The primary key needs to be global.

    Use oracle 11 with range partition and use the sequence as the value to
    the range.

    Since you use a sequence the dates go up with the sequence.

    When it is time to delete just do:
    Select min(pk) from tables where date > sysdate - nn.

    Then calculate the partition this pk is in, and drop all the partitions
    below this.

    No global key, no changes to the current structure or programs.

    Yechiel Adar
    Mehish
    On 31/01/2012 17:04, Walker, Jed S wrote:
    As such, the Primary Key has to be a global index.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 31, '12 at 3:05p
activeFeb 6, '12 at 5:38p
posts15
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase