Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Alessandro.

Asked: April 19, 2018 - 8:58 pm UTC

Last updated: April 20, 2018 - 3:58 am UTC

Version: 12

Viewed 1000+ times

You Asked

Hi Tom,
i have a question in partitioning by list of a table.
I have a set of tables which need to be hystoricized once a new record is inserted: then i have a STATUS column which flag an active status (AT) and a historic one (ST). To match this requirement i thought to partition by list my table enabling row movement among partitions.
In this scenario any time a logical record is changed (update) the system performs an update of the current active record for that key by changing the status from AT to ST (moving partition) and insert a new record in AT partition.

Hence a batch process shoult inquiry only the active rows, joining them with multiple other table, and this is the reason that drove me to partitioning apporach. I accept to pay more in insert process to have best performance in select in multi join query which involve active record.

It's better to partition or it's enough to define an index on each table on status column?

Thanks

and Connor said...

Only you can really answer that question.

Both are acceptable usages that depend on your business needs.

And don't forget, these are not mutually exclusive options. You can partition the table, and also have indexes on the partitioned table. Moreover, since you're on 12, you can have partial indexes, that is, you might want to index some elements of the historical data but *not* the active data (or vice versa).

The fact you've considered both options means you're already on the right path - ie, a little benchmarking and you'll have an answer.

From what you've given us in the question, it *sounds* to me like the partitioning approach is going to yield some good value for you.

Rating

  (1 rating)

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

Comments

Consider In-Database Archiving

Gh, April 20, 2018 - 7:00 am UTC

In-Database Archiving May ne relevant To this situation. Whether yes or not. I wouldn't go for such partitioning. Since have no ddl scripts I cant suggest a partitioning/indexing solution cause they are many and depends of ddl and the way you query.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database