Partition index or Partition table and Index?

[Cookies]

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.

[slimdave]

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?

[Cookies]

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:

mi.end_dt > :5 AND 
mi.end_dt <= :6  
GROUP BY mi.end_dt  
ORDER BY mi.end_dt ASC 

[slimdave]

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.

[Cookies]

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?:

ALTER TABLE tablename
      ADD PARTITION sep03 VALUES LESS THAN ( '01-OCT-2003' )
      TABLESPACE tsx; 

[slimdave]

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 ...

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.

[Cookies]

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?

[slimdave]

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.

[Cookies]

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

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

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-3 AND
  mi.end_dt <= SYSDATE
GROUP BY mi.end_dt 

[TomazZ]

can you post all 4 plans?