A cool thing with EXCHANGE PARTITION (part 2)

May 19, 2016

1–2 minutes

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:

4 responses to “A cool thing with EXCHANGE PARTITION (part 2)”

Jonathan Lewis

May 20, 2016 at 5:09 pm

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.)

Reply
Sam

September 9, 2017 at 2:09 am

It would be useful if you included a link to your Part 1 “In the [previous post], ….”

Reply
1. Victor V
  
  September 25, 2018 at 6:51 pm
  
  +1
  
  Reply
Mikhail L

August 18, 2020 at 5:01 am

Here is the first part: https://www.youtube.com/watch?v=xeUr9K8NnBo

Reply