Thanks for the question, Pablo.

Asked: November 25, 2016 - 12:57 pm UTC

Last updated: November 28, 2016 - 2:49 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Tom,
I have a big table with 6 partitions(hash), the table have 270 Million records, i try to add 2 partitions(i want to have 8 partitions for use "power of 2"), after prepare temp space and undo space, then i run process to add to partitions:

The current table without new partitions:
Partition Records
P1 32431370
P2 32530030
P3 64649390
P4 64323130
P5 32068740
P6 31965810

ALTER TABLE "BENE"."MOVIMIENTOS_CREDITO" ADD PARTITION "P7" TABLESPACE "PARTICION_DATOS" UPDATE INDEXES PARALLEL 8;
ALTER TABLE "BENE"."MOVIMIENTOS_CREDITO" ADD PARTITION "P8" TABLESPACE "PARTICION_DATOS" UPDATE INDEXES PARALLEL 8;

This process consume a lot of time (more than 9 hours)....do you recommend some tips to lower the time?? (for instance: without update indexes and after rebuild those? or another tip..also for hash partitions to a big tables

The indexes are global.

Thanks a lot.-

and Connor said...

When you "add" a partition to a hash partitioned table, you are in reality, splitting one of the existing partitions. For example, here's a table with 5 partitions

SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) ptn,
  2         count(*)
  3  from T
  4  group by DBMS_ROWID.ROWID_OBJECT(rowid);

       PTN   COUNT(*)
---------- ----------
     72174      12342
     72172      25209
     72171      24955
     72173      24890
     72170      12603

5 rows selected.

You can see the imbalance. One I add a partition, one of them gets split

SQL> alter table T add partition;

Table altered.

SQL> select dbms_rowid.ROWID_OBJECT(rowid) ptn,
  2         count(*)
  3  from T
  4  group by dbms_rowid.ROWID_OBJECT(rowid);

       PTN   COUNT(*)
---------- ----------
     73515      25209
     73516      24890
     73517      12342
     73519      12381
     73518      12574
     73513      12603

This is a good thing, because we didnt need to reshuffle the entire table, but still, that's still a lot of rows to move.

Performance options:

1) drop indexes first, but that means an outage
2) use dbms_redefinition. The overall process will probably take longer, but the application remains available during this time.

The other question to ask is - what benefit are you getting from hash partitioing your table ?

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Same question to Connor

A reader, November 26, 2016 - 6:49 am UTC

Hi Connor
Indeed what benefit is?

November 27, 2016 - 7:40 am UTC

hash partitioning for tables is typically about manageability, that is, if a table has no obvious means of partitioning, and its size is problematic (eg backup/move/etc) then smaller size chunks might be better.

Before ASSM, it was also a contention mitigation strategy, but ASSM seems to handle that comfortably nowadays.

For indexes its a different story. A hash partitioned index *can* be a very useful tool to spread out activity over a hot leaf block.

Looks like more than one partition was moved?

Andrew Sayer, November 26, 2016 - 3:50 pm UTC

Connor, from your demo the DBMS_ROWID.ROWID_OBJECT outputs are all new - not just the ones from the split. Does that mean all the other rows in the table have also moved around and therefore needed their index entries updated too?

November 27, 2016 - 7:44 am UTC

My mistake - cut and paste fail from a set of presentation slides. Here's a top to bottom

SQL> create table t  ( x, y )
  2  partition by hash ( x )
  3  partitions 5
  4  as select rownum, rownum from dba_objects;

Table created.

SQL>
SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) ptn,count(*)
  2  from T
  3  group by DBMS_ROWID.ROWID_OBJECT(rowid)
  4  order by 1;

       PTN   COUNT(*)
---------- ----------
    180560      12539
    180561      24799
    180562      25066
    180563      24740
    180564      12269

5 rows selected.

SQL>
SQL> alter table t add partition p99;

Table altered.

SQL>
SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) ptn,count(*)
  2  from T
  3  group by DBMS_ROWID.ROWID_OBJECT(rowid)
  4  order by 1;

       PTN   COUNT(*)
---------- ----------
    180560      12539
    180562      25066
    180563      24740
    180564      12269
    180565      12504
    180566      12295

6 rows selected.

SQL>

More details

A reader, November 27, 2016 - 10:53 am UTC

Hi Connor and tkx for answering.
So then the hash partitioning is not really for performance purposes. While hash subpartitioning is in cases to promote wise pruning and bf pruning is that right?
Could detail yoyr answer for "it-depends" ones?

November 28, 2016 - 2:49 am UTC

If two *related* tables were equally hash partitioned, then you could get some benefit on partition-wise joins, but in such an instance you would also probably be considering whether reference partitioning is a more appropriate strategy.

But in the general sense of pruning, hash partitions rarely provide dramatic benefit because a predicate of:

where hash_key_column = ....

either has

1) lots of distinct values for the column, at which point, a lookup on the hash column is most probably going to be index lookup anyway, or

2) has very few distinct values..at which point, list partitining is perhaps the better option.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Same question to Connor

Looks like more than one partition was moved?

More details

More to Explore

DBMS_ROWID

Lot of time to add partitions to a Big Table

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Same question to Connor

Looks like more than one partition was moved?

More details

More to Explore

DBMS_ROWID