Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rupam.

Asked: February 16, 2017 - 10:11 am UTC

Last updated: February 18, 2017 - 4:25 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

I have composite partitioned FACT table that list-range partitioned and it currently houses 1756515660 rows.
In order to incorporate dynamic partitioning/sub-partitioning, we want to switch to list-list style of table.
Given the large size of the table, we do not want to transfer the data to a temp table , recreate the table with the new style and put the data back in.

What we are thinking to do is create another table with the list-list style of sub-partition and rename the original table
and create a view having the name of the original table(so select queries will not need to be updated) of straight union of the old table and the new table.

Can we still reap the benefits of partitioning and indexing with the view?





and Connor said...

"Given the large size of the table, we do not want to transfer the data to a temp table , recreate the table with the new style and put the data back in."

If this is a downtime issue, you could look at dbms_redefinition to do it with near zero downtime.

Just so I understand, I assume your intent is the "old" data will be queried via the existing partitioning scheme, and new data will be in the new scheme ?

If that's the case, then as long as you a UNION ALL (not a UNION) then you should be ok, because the optimizer is reasonably good at picking this out, and eliminating tables from consideration if it can.

Obviously you would want to test/benchmark your most critical queries to guard against regression.

Rating

  (1 rating)

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

Comments

Rupam Shah, February 17, 2017 - 5:39 am UTC

Thanks for the reply.
I do not think downtime is an issue but my colleagues are hesitating of touching the production fact table and move the data around.
Ideally, we would have preferred only one table with the new partitioning style. Since that does not look like an option, we are using the view technique.
Connor McDonald
February 18, 2017 - 4:25 am UTC

"my colleagues are hesitating of touching the production fact table and move the data around"

At some stage...that culture will come back and bite you
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.