Re: [External] : Re: small bug related to deferred_segment_creation parameter

An alternative to "drop empty segment" would be to do a back-door invocation of

"alter table tbl_test2 truncate partition maxvalue drop all storage;"

This (at least for 19.11) discards the segments but keeps the partition. The equivalent for composite partitions would be messier, of course. "Empty" is a bit tricky, of course, since there's a difference between "empty never been used" and "empty because it's all deleted". For partition maintenance Oracle already has a mechanism for knowing when a partition generated by a split is going to be empty, so that's the one case where the truncate option could be injected.

Regards
Jonathan Lewis

On Fri, 23 Jul 2021 at 13:04, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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_at_t-online.de <
> ahmed.fikri_at_t-online.de> 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_at_oracle.com>
>
> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>
>
>
>
>
>
>
>
> 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: gerald.venzl_at_oracle.com | 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_at_gmail.com> 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. *
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
> On Thu, 22 Jul 2021 at 20:23, ahmed.fikri_at_t-online.de <
> ahmed.fikri_at_t-online.de> wrote:
>
> in the current project I'm working on, 12.1 is still in use. Maybe someone
> can test the script in 12.2 or 19.11.
> In the meantime, I'll try to remove some dust from my private 12.2 and 19
> instances and see if we have the same behavior.
>
>
>
> Best regards
>
> Ahmed
>
>
>
>
>
>
>
> -----Original-Nachricht-----
>
> Betreff: Re: small bug related to deferred_segment_creation parameter
>
> Datum: 2021-07-22T20:14:53+0200
>
> Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com>
>
> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>
>
>
>
>
>
>
>
> I'm not surprised that you didn't get a response in less than 3 hours -
> especially since you were talking about 11.2.0.4 and 12.1.0.2.
>
>
>
> 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.
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
> On Thu, 22 Jul 2021 at 12:53, ahmed.fikri_at_t-online.de <
> ahmed.fikri_at_t-online.de> wrote:
>
> Hi everyone,
>
>
> it seems that the deferred_segment_creation parameter does not work
> properly when splitting a table partition that contains a clob field.
>
> If the partition is empty and has no segment, splitting it creates two
> segments for the old and new partitions.
>
> I tested this using this block in 11.2.0.4 and 12.1.0.2 (also attached and
> run with a non dba user):
>
>
>
> DECLARE
> PROCEDURE ex(cmd VARCHAR2) IS BEGIN dbms_output.put_line(cmd); EXECUTE
> IMMEDIATE cmd; END;
> PROCEDURE ex_ignore_error(cmd VARCHAR2) IS BEGIN ex(cmd); EXCEPTION WHEN
> OTHERS THEN NULL; END;
> PROCEDURE split_part(tbl VARCHAR2,p_part_val VARCHAR2) IS BEGIN
> ex('alter table '||tbl||' split partition maxvalue at
> (to_date('''||p_part_val||''',''yyyymmdd''))
> into (partition part_'||p_part_val||' tablespace users, partition
> maxvalue tablespace users)') ;END;
> BEGIN
> ex('alter session set deferred_segment_creation = true');
> ex_ignore_error('drop table tbl_test purge');
> ex_ignore_error('drop table tbl_test2 purge');
> /* create range-partitioned table without clob field */
> ex('create table tbl_test(id number, part_key date) tablespace users
> partition by range(part_key) (partition maxvalue values less than
> (maxvalue))');
> /* create range-partitoned table having a clob field */
> ex('create table tbl_test2(id number, part_key date, clob_col clob)
> tablespace users partition by range(part_key) (partition maxvalue values
> less than (maxvalue))');
> /* the table is empty. Splitting the maxvalue partition for the table
> without clob */
> split_part('tbl_test','20200101');
> /* until here no segments is generated for tbl_test and tbl_test2 tables
> spltting the maxvalue partition for the table having clob field */
> split_part('tbl_test2','20200101');
> -- two segments are created for the table having clob field
>
> /* SELECT * FROM user_segments WHERE segment_name IN
> ('TBL_TEST','TBL_TEST2')
> TBL_TEST2 PART_20200101 TABLE PARTITION ASSM USERS
> TBL_TEST2 MAXVALUE TABLE PARTITION ASSM USERS
>
> */
>
>
> END;
>
>
>
> Is this already known? Any Idea?
> For me, this behavior is annoying and is considered a bug.
>
>
>
> Best regards
>
> Ahmed
>
>
>
> 
>
>

--
http://www.freelists.org/webpage/oracle-l