This forum is currently locked. You can't register or post questions at this time. (read more)

Partitioning / Subpartitioning

All posts relating to Oracle database administration.

Moderator: Tim...

dnunknown
Advisor
Posts: 292
Joined: Wed Aug 01, 2007 5:35 pm
Location: Boulder, CO USA

Partitioning / Subpartitioning

Postby dnunknown » Wed Apr 09, 2014 4:31 pm

Hey Tim,

Wanted your opinion on this ...


11.2.0.4 EE and we have license for partition option

Have table that is billions of rows that the WHERE clause is using;
COL1 (DATE) NOT NULL --- around 150 distinct TRUNC(COL1)

We have partitioned based on range for COL1 per day, but now getting a bit deeper we are also using;
COL2(NUMBER) NOT NULL ---- around 57 distinct values

I build an index on COL2 and not getting the performance needed, so I was thinking of placing a partition on it as well

So with the below most frequent queries should I subpartition COL2?

Code: Select all

SELECT <COLUMNS>
FROM TABLE
WHERE COL1 = <DATE>;

SELECT <COLUMNS>
FROM TABLE
WHERE COL1 = <DATE> AND
           COL2 = <NUMBER>;

SELECT <COLUMNS>
FROM TABLE
WHERE COL1 = <DATE> AND
            COL2 IN <NUMBER>;

SELECT <COLUMNS>
FROM TABLE
WHERE COL1 BETWEEN <DATE> AND <DATE> AND
            COL2 IN <NUMBER, NUMBER> ;

Am I going about this the wrong way, maybe additional/different indexes in place of subpartition?
What would a simple example look like code wise for subpartition method with the above info?

Thanks,
Steve

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Partitioning / Subpartitioning

Postby Tim... » Wed Apr 09, 2014 5:26 pm

Hi.

That is difficult to answer as because it depends on the relationship between the data in COL1 and COL2. For example:

Let's say 56 of the 57 distinct values of COL2 are all present in 1 partition based on COL1, the partitioning on COL1 and sub-partitioning on COL2 would achieve nothing. If on the other hand, the spread of COL2 is about even for all partitions based on COL1, then maybe it would help... Looking for number of distinct values in each column is not a reflection of the data distribution. Skewed data can make what seems like a sensible plan go to waste.

Remember, partitioning is only likely to help if the query results in partition pruning. If not pruning takes place, then partitioning may result in worse performance as dealing with the partitions involves more work than not having it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 6 guests

cron