OGB Appreciation Day: Efficiency with Partitions.

Partitioning is Very Useful - If ... you take the effort to Design for it properly.

What I like best about Partitioning is the "life cycle feature". You can remove large amounts of (old) data with a single statement in the blink of an eye, and with very little Redo incurred.

If you want, you can also Add or Move data  around in the blink of an eye as well.

Google for :
Alter table Drop Partition
Alter table Add Partition


Use Partitioning for Fast and Efficient Data Manipulation (notably Deletes)

The main "successful" use I've seen from partitioning is this: (Re)Moving large amounts of data in the blink of an eye, without incurring Undo+Redo for the total amount of data, and without locking.

Very little Redo (it is a DDL operation, not DML)
No Locking.
No Index Rebuilding (e.g. only local indexes).
Fast!
Efficient!


Your Mission, should you choose to accept it: 
Verify this for yourself!

Here is your homework.

If you are serious about trying / using this feature:
 - Create a partitioned table with at least 2 partitions.
 - Insert data into both partitions, at least 100K records (and commit)
 - generate statistics, verify the approx row-counts in each partition.
 - set Autotrace on
 - set timing on
 - Delete all the data in 1 partition with a "Delete from ... Where ..."
 - Note the time and the amount of Redo involved.
 - commit (just dont forget...)
 - Re-generate stats and verify the row-counts, your data is deleted ?

Now for the Partition-operation...
 - Re-create the same table, same partitions, with same data (commit!)
 - generate + verify stats...
 - Set autotrace + timing again
 - Drop a partition: Alter table ... drop partition ;
 - note: the implicit commit... this is a DDL operation.
 - Note the time and the amount of redo.
- Verify the data is gone.. (trust-no-1....)


QED.

Now how cool is that?
And so Simple...


Go ahead, do it, and et us know the differences in Time and Redo in the comments...

My (not quite conform) example script is here

And if You can Do this:
Congratulations! You are on your way to Master the use of Partitioning.



Now, there is a lot more to Partitioning, of course.

My opinion in Short, rather simplified statements.
 - You need to "design" partitioning from the start. A "bolt on" to an existing data-model will 99/100 fail.
 - Use (only) automatic interval partitioning, prevent yourself from having to pre-create partitions regularly (you will forget...)
 - Every SQL to Ever Access that table needs a partition-key in the where-clause.
 - Avoid global indexes if possible.

NB: I know the "maintain global indexes" was created to prevent+fix some of my pet-problems-with-partitions, but I am still skeptical about the usage in live (OLTP) systems.

NB2: Kuddos to Hermann (dot) Baer (at oracle dot com), a.ka. @sdjh2000 for constantly improving the capabilities of the partitioning option. Good Work on a Great Feature.



That's all for now folks.

With a Large Kuddos to Tim Hall (@oraclebase) for the Yearly OGB-appreciation day (link). Makes me blog at least once per year...

OGB-appreciation day was f.k.a. the OTN-Appreciation-Day,
re-branded as the ODC-Appreciation day,
re-branded as the OGB-Appreciation Day.
Who knows what next year will bring - Oracle Cloud Dev-Ops (OCD) Appreciation day?


And if you got this far, a word of warning:
Do Not Ever Make Fun of Oracle Buzzwords or Hashtags.
Humour and Oracle only work if it is Oracle-Approved humour.
(mandatory buzzword compliant content...)
Remember: We all work for Larry, Only some of us dont know it yet.

CU at some event, at some webinar or next year on this blog-event.

Hashtag : #ThanksOGB