It seems likely that “create” fixes in place the “deferred” mode at a different
point in the code than maintenance. That’s a complete guess since I don’t get
to see the code, but it is compatible with “create” correctly not creating
“deferred” segments in the cases where it might have to create an out of line
segment.
Possibly it would be easier to create a “drop empty segments” maintenance that
to insert the correction to deferred segments. Since at least logically Oracle
could know range partition key references refer to a single partition by name
instead of stats more easily, that might be better for the optimizer using
partition stats. And, of course, clearing up clutter in the dictionary as well
as the possibly designed large initial segment sizes. Gargantuan segments for
Monday through Friday and no segments at all for Saturday and Sunday is still a
common pattern, even in the days of globalization.
So this message is mostly to the Oracle lurkers: Consider a drop empty segments
between <low partition key reference> and <high partition key reference> as a
fix up if the maintenance code is too different from the create code to pattern
match the “bug except for restriction documentation” behavior.
Quite often such restrictions are because resources could not be prioritized
rather than it being arcane to implement without the restriction.
Since this behavior has been around, documented, for a long time, if you change
it a lot of folks will be asking for the “cleanup” code anyway.
Good luck.
Thanks Gerald and JL for pointing out it has “always” been this way, ending the
mystery.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Friday, July 23, 2021 4:38 AM
To: list, oracle
Subject: Re: [External] : Re: small bug related to deferred_segment_creation
parameter
I know that Gerald Venzi quoted the 21c reference manual, and going so far
forward to "retro-document" a detail can look like a cop-out; but in this case
you can find exactly the same text in the 11gR2 reference manual - search for
"Deferred segment creation" at the URL:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#CJABFJEA
Regards
Jonathan Lewis
On Fri, 23 Jul 2021 at 07:45, ahmed.fikri@xxxxxxxxxxx <ahmed.fikri@xxxxxxxxxxx>
wrote:
Hi all,
My impression is that every time Oracle detects a bug it doesn't seem easy to
resolve, add two lines of documentation that says this is a restriction.
I've seen some examples of this on this forum. We even have conflicting
documentation, one saying this is a limitation, the other saying it's a
feature, and so on.
But that's just my impression, and doesn't mean it is true.
Many thanks to Jonathan and Andy.
Best regards
Ahmed
-----Original-Nachricht-----
Betreff: Re: [External] : Re: small bug related to deferred_segment_creation
parameter
Datum: 2021-07-23T02:44:53+0200
Von: "Gerald Venzl" <gerald.venzl@xxxxxxxxxx>
An: "ahmed.fikri@xxxxxxxxxxx" <ahmed.fikri@xxxxxxxxxxx>
Hi all,
It is reproducible not only in 19.11 but will also be in 21c as this is not a
bug but a documented restriction (see alter_table_partitioning section)
<https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-TABLE.html>
:
* Deferred segment creation is not supported for partition maintenance
operations that create new segments on tables with LOB columns; segments will
always be created for the involved (sub)partitions.
Furthermore, you can see that this restriction holds true for all partition
maintenance operations and not only the SPLIT operation.
Nope this helps!
Thx,
---------------
Gerald Venzl | Distinguished Product Manager
Email: <mailto:gerald.venzl@xxxxxxxxxx> gerald.venzl@xxxxxxxxxx | Phone:
+1.650.633.0085
Oracle ST & Database Development
400 Oracle Parkway | Redwood Shores | 94065 | USA
On Jul 22, 2021, at 15:14, Jonathan Lewis <jlewisoracle@xxxxxxxxx> wrote:
It looks like you missed the point in my second paragraph:
However, it may be a little more interesting to point out that this example
does
the same thing in 19.11.0.0 - and still creates the segments even if you
include
the "segment creation deferred" option with each of the listed partitions in
the split.