Table Partition Design
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Table Partition Design

  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Table Partition Design

    We have two groups of users Group A and Group B.

    Group A of users queries data by insert_date = sysdate. Table A partitioned by INSERT_DATE.
    Group B has queries that use REPORT_DATE = sysdate -1. TABLE B partitioned by REPORT_DATE it contains all the columns of table A except that is partitioned by REPORT_DATE. Basically we have same data stored twice once partitioned by insert_date and report_date. Our users have hundreds of queries that run against either table A or Table B.

    Note that the tables are partitioned by DAY each day gets 1 million records.
    Data in each table goes back to 4 years to 2006. so those tables contain huge amounts of data.

    Group A goes after TABLE A

    INSERT_DATE date
    DWBUID number
    CURRENT_BALANCE number
    FLAG_ACTIVE char(1)
    ....More Columns up to (150 columns).......................
    combination of INSERT_DATE and DWBUID makes the data unique

    Group B uses TABLE B

    REPORT_DATE date
    DWBUID number
    CURRENT_BALANCE number
    FLAG_ACTIVE char(1)
    .....More Columns up to (150 columns).......
    combination of REPORT_DATE and DWBUID makes the data unique


    We would like to be able to build ONE partitioned table which allows users to write queries to retrieve data by either insert_date or report_date while getting same type of performance as they would have gotten if they query either Table A or Table B.

    We were thinking that table C should be the new table that contains both Insert_date and Report_date along with DWBUID.
    The question is what kind of partitioning should we use to achieve our goal where users do not get performance degradation.

    both users groups should be able to use TABLE C

    INSERT_DATE date
    REPORT_DATE date
    DWBUID number
    CURRENT_BALANCE number
    FLAG_ACTIVE char(1)
    ....More Columns up to (150 columns).......................
    combination of INSERT_DATE REPORT_DATE and DWBUID makes the data unique

    Not sure we can use a composite partition by (INSERT_DATE, REPORT_DATE)
    and hash subpartition by DWBUID.

    Any suggestions will be appreciated.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Build just one partitioned table.

    If business specs are asking to either archive or purge data, a good idea would be to have a partitioning strategy that helps during such archive and/or purge process.

    If business specs are asking to keep data forever then partitioning strategy should help your most critical queries.

    Revise indexing strategy - queries shouldn't rely only in partitioning strategy but in indexing strategy.

    Why are you mentioning hash partitioning? are yo seeing contention during insert?
    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 2010
    Posts
    2
    We tried creating a table partitioned by composite range
    PARTITION BY RANGE(insert_date, report_date, dwbuid)

    The thinking was that by having both dates part of range partition will allow users to have queries that hit either insert_date or report_date to get same performance.
    it turns out that queries that reference insert_date are much faster than the ones
    that hit report_date.

    Queries that hit report_date do a full index scan. (slow) 2 min
    queries that hit insert_date hit the correct partition ranges. (fast) 10 sec
    Need a way to have both dates have the same weight regardless of the order of which they are enumerated in the partitioning statement.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Test2day View Post
    We tried creating a table partitioned by composite range
    PARTITION BY RANGE(insert_date, report_date, dwbuid)

    The thinking was that by having both dates part of range partition will allow users to have queries that hit either insert_date or report_date to get same performance.
    it turns out that queries that reference insert_date are much faster than the ones that hit report_date.

    Queries that hit report_date do a full index scan. (slow) 2 min
    queries that hit insert_date hit the correct partition ranges. (fast) 10 sec
    Need a way to have both dates have the same weight regardless of the order of which they are enumerated in the partitioning statement.
    Partitioning strategy is wrong.
    Partition by range on (insert_date) then create indexes on insert_date, report_date and dwbuid.
    Is this a FACT table?
    do you have a DIMension describing dates? In the affirmative case, is your DIM_DATE table pointing to a surrogate date on FACT?
    do you have a DIMension describing dwbuid?
    If you got four"YES" that means star-schema is properly designed then consider defining insert_date, report_date and dwbuid as bitmap indexes; user queries should mention DIM tables.
    Last edited by PAVB; 12-13-2010 at 10:14 AM. Reason: typo
    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.

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