Partition index or Partition table and Index?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Partition index or Partition table and Index?

  1. #1
    Join Date
    Oct 2002
    Posts
    182

    Partition index or Partition table and Index?

    I have been reading all the documentation I can find on Partitioning tables and indexes.

    A composite partition would be best for the tables I have in mind.

    I was wondering if I should only partition the indexes, or should I partition the table and the indexes on said table?

    Obviously, if I only partition the indexes then I am at the mercy of the code matching the indexed columns.

    If I partition the table I was planning on also partitioning the indexes locally.

    Any other advantages/disadvantages to only partitioning indexes instead of the table and indexes?

    thanks.
    - Cookies

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can you describe the function of the table? Is it OLTP or DW, ow often is it loaded, how many rows, how often is data deleted or updated etc?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    The table itself is more like a DSS
    where data only gets inserted and never updated
    (for this specific table it never gets updated, just inserts and then maybe older than 2 years delete that data).

    The database runs more like OLTP than DW however. Certainly a combination.

    It is a historic table based on date intervals of 15 minutes.

    Table Key is:
    Organization
    Device_Id <-- represents a customer basically
    Interval (actually called END_DT)
    (then various other columns)

    Right now it is only around 1Mil records, but in the furture this
    would exponentially become huge since right now with only 60 devices,
    it is at 1Mil records since April 1, and we would expect eventually
    500 devices perhaps (or more) down the road.

    the queries generally are summing up the other fields by date

    so, and example part of a where clause in the code would be:
    PHP Code:
    mi.end_dt > :AND 
    mi.end_dt <= :6  
    GROUP BY mi
    .end_dt  
    ORDER BY mi
    .end_dt ASC 
    Last edited by Cookies; 08-20-2003 at 05:32 PM.
    - Cookies

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Some of the details you mention suggest that the table + index could be partitioned.

    For example, having data that is essentially read-only until it gets deleted at some future point suggests that you can arrange that once partitions have been completely loaded, the table partition can be ...

    * Physically reordered to suit your main query types
    * Compressed
    * Placed in read-only tablespaces
    * Merged together for a lower date granularity (you might partition by 15 minute intervals for "todays" data to make loading more robust, then merge to daily or monthly as the data ages to improve manageability and reduce the number of segments being "probed" by queries)
    * Easily dropped in the future
    * assigned to a different buffer cache pool -- you might want your most recent data set to KEEP, and the old data that is less often queried set to RECYCLE

    I guess that the partitioning key would be based on date, but you might like to be cautious about using a composite partitioning key. There are things that you can do with a single partitioning key that don't work so well with composite keys, and personally i would want to have identified very definite advantages to the composite partitioning before i dabbled in it.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Oct 2002
    Posts
    182
    thank you for your recommendations and verication of my ideas.

    I've attempted to read up on compressed indexes but I couldn't find any concrete evidence of WHEN you would use them OR IF they are better performance-wise than standard NOCOMPRESS option.

    If you have a link (other than the online docs which say little regarding compressed indexes) to more information I would appreciate it.

    Also, once all my partitions are set up, how do I set up future partitions going forward?

    Let's say I partition by week or month (or whatever). Do I need to manually create the new partitions for the next month etc?

    so on Sept 1st I would need to add?:
    PHP Code:
    ALTER TABLE tablename
          ADD PARTITION sep03 VALUES LESS THAN 
    '01-OCT-2003' )
          
    TABLESPACE tsx
    - Cookies

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    For the compression, I was referring to data segment compression. This is where the table or individual table partition is compressed, thus requiring much less i/o to read a given number of rows.

    You can compress a partition by running ...

    Code:
    Alter Table MY_TABLE Move Partition MY_PARTITION compress;
    There's much about this feature in the Oracle Data Warehousing guide, although as with all these new features it always pays to take a dip into metalink to check for any "issues" before commiting to using it. There is currently no ability to add columns to a compressed table, so wise monkeys will add a few spare columns to the end of their compressed tables just in case they need them in the future.


    You're right about the need to manually add the partitions, although of course you can add them far into the future if you like.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Oct 2002
    Posts
    182
    To follow up with this:

    I set up 3 different test environments with a different partition strategy for the specific table in each environment.

    I guess I am confused about the indexes on the partitioned table. With my tests, it seems that some queries take longer on one environment than anbother. However, there is no consistency between the 3 environments (ie: the 3rd environment, all queries on the table run much more efficiently).

    If I range partition by date, and the queries all have the date-column in their where-clauses, does that mean I definitely should use a local-partition index??

    Should all indexes that have that column be local-partitioned?
    - Cookies

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If I range partition by date, and the queries all have the date-column in their where-clauses, does that mean I definitely should use a local-partition index??
    Yes, sounds right -- the optimizer will partition prune, and you won't end up probing every partition of the indexes.

    Should all indexes that have that column be local-partitioned?
    Are you considering including the date column in every index, making them all composite? That wouldn't be strictly necessary (particularly with bitmap indexes), and might be an excessive index-maintenance load.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Oct 2002
    Posts
    182
    Thanks for the reply Slim.

    As an example, I have the meter_interval.end_dt partitioned by week
    in environment 1 and partitioned by month, then subpartitioned hashed by org_id in environment 2.

    These two queries from the application are confusing me.
    Environment 1: Query 1 runs slow, but Query 2 runs great.
    Environment 2: Query 1 runs great, but Query 2 runs slow.

    I know I am missing something here ...

    Query 1
    PHP Code:
    SELECT /* getFilteredForecast */   
      
    MIN(f.intvl_nbr) AS intvl_nbr,
      
    SUM(f.usage_nbr 4) * 0.0 AS usage_nbr,
      
    COUNT(*) AS sample_count
    FROM 
      ACTIVE_DEVICE_MV ad
    ,
      
    USAGE_FORECAST f
    WHERE 
      ad
    .org_id'4' AND
      
    ad.svc_type_cd 'E' AND
      
    EXISTS 
      
    (SELECT mi.end_dt 
       FROM METER_INTERVAL mi           
       WHERE mi
    .org_id ad.org_id AND
             
    mi.device_id ad.device_id AND
             
    mi.end_dt >= SYSDATE-3) AND
      
    f.org_id ad.org_id AND
      
    f.cust_id ad.cust_id AND
      
    f.day_type_cd 'WD'
    GROUP BY f.intvl_nbr 
    and Query 2
    PHP Code:
    SELECT 
      MIN
    (mi.end_dt) AS end_dt,
      
    SUM(mi.usage_nbr*4) AS usage_nbr,
      
    COUNT(*) AS sample_count
    FROM 
      ACTIVE_DEVICE_MV ad
    ,
      
    METER_INTERVAL mi
    WHERE 
      ad
    .org_id='ME' AND
      
    ad.svc_type_cd 'E'   AND
      
    mi.org_id ad.org_id   AND
      
    mi.device_id ad.device_id   AND
      
    mi.end_dt >= ad.created_dt   AND
      
    mi.end_dt >= SYSDATE-AND
      
    mi.end_dt <= SYSDATE
    GROUP BY mi
    .end_dt 
    - Cookies

  10. #10
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    can you post all 4 plans?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

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