Problems with Partitions
This attached document is in Word 97, with a size of 43KB.
The content is an article first published on www.dbazine.com in September 2003. The article discusses some of the issues that hit you when you start to use partitioned tables in a realistic way, instead of restricting yourself to the trivial examples so loved by books, presenters, and the manuals.
The article has a ‘stop press’ note at the end which says:
Shortly before sending this article in to DBAZine for publication, I installed an upgrade to 22.214.171.124, and found a note in the bug patch list pointing out that a bug that ‘may cause exchange on tables with unique constraints to be slow’ had been fixed. The fix was to remove the constraint check described in this article.
I have had an email from Roman Guoussev-Donskoi that in fact Oracle still does a uniqueness check, although the SQL has changed. He supplied the following example from one of his systems (the actual code you see will probably be very similar in structure, but may have some minor differences):
/*+ first_rows */ 1
"OMS_263MG"."TMP_NOPART" "A" ,
"A"."ORDER_SEQ_ID" = "B"."ORDER_SEQ_ID"
And "A"."ORDER_NODE_INDEX" = "B"."ORDER_NODE_INDEX"
"OMS_263MG"."OM_ORDER_INSTANCE",0,0,0 ,"B"."ORDER_SEQ_ID" ,"B" ."ORDER_SEQ_ID"
) between 1 and 3
"OMS_263MG"."OM_ORDER_INSTANCE",0,0,0 ,"B" ."ORDER_SEQ_ID" ,"B" ."ORDER_SEQ_ID"
) between 5 and 3 )
and rownum < 2;
The second clause with the “between 5 and 3” is a little odd – the example I ran had a rather more sensible looking predicate – but Roman was exchanging a single hash subpartition from a composite partitioned table. For unique indexes (as opposed to “primary key” indexes) you may also see some “not null” predicats being used.
Judging from the code, it seems that the intent is to ensure that there is no data in the incoming table that should be in a different partition in the table. (The original query used to check also that there were no rows in the partitioned table that ought to have been in the partition being exchanged).
Despite appearances to the contrary, the join to the partitioned table should never take place if the data is good – and will only take place once if the data is bad. The effect is that the typical execution path will be a full scan, or fast full scan of the index on the incoming table. However, as Roman points out, this could still be quite slow, and does not take us back to the speed of 8i exchange unless we are still prepared to have the primary key in a novalidate state.