A cool thing with EXCHANGE PARTITION (part 2)

Posted by

In the previous post, I showed that even though a partition was “removed” (ie, exchanged out) from a table, a query running against the table could still successfully run the completion.

However, of course, if once that partition is exchanged out, it is now a table in it’s own right…and is subject to the whims of what a DBA may wish to do with it.   If that table is dropped, or truncated, then as you might expect, our query is going to struggle to find that data ! Smile

Here’s an example of what happens when the query cannot successfully run:


  1. Connor,

    Things can get worse.

    The 8103 only happens if the query reads any of the blocks that have been modified by the space management code (the ones which you see written back to disc with “local write wait” waits) – so, depending on the access path to the object, you may find that a query will sometimes succeed and sometimes fail when exactly the same pattern of exchanges is going on. If your query references a single partition it may even start working – returning data – and then fail part way through because it gets to a point where ist has to re-access an overwritten space managment block.

    And it gets worse – because if the exchange partition happens in the moments between optimising the query and executing the query it seems that the session may decide to re-optimize the query before executing it and, for reasons I’ve never been able to model, change the execution plan from a high precision, “partition single” plan to a “partition all” plan – and crash with 8103 because the new plan decides to access a partition that it should not have been accessing at all. (I’ve only seen this in cases where the code accesses a single subpartition of a single partition – but decides to access all the subpartitions of the partition.)

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.