Oracle Scratchpad

July 9, 2019

Assumptions

Filed under: Oracle,Philosophy — Jonathan Lewis @ 11:47 am BST Jul 9,2019

Over the last few days I’ve been tweeting little extracts from Practical Oracle 8i, and one of the tweets contained the following quote:

This lead to the question:

Good question! The whole undo/redo infrastructure in Oracle is probably the most astounding technological achievement in the entire code base – so would you test it to see that it was working properly and if you could break it ? Probably not – although if you were about to recreate your undo tablespace with a 32KB block size you might test to see if the change would produce any surprise side-effects); or you might wonder if anything funny could happen to the redo generation  if you created all your varchar2() columns as 4000 bytes “just in case”. or possibly you’d check for undo or redo anomalies if you were told to create a table with more than 255 columns.

I don’t know quite what I was trying to imply (20 years ago) when I wrote the quoted sentence. Possibly I was trying to avoid saying “new features”, because it’s not just the new features you need to test. I was probably trying to suggest the flavour of “exotic”, “high-tech”, “exciting” – which basically comes down to the things where you think you might be (h/t Martin Widlake) a “thought leader” or ground-breaker.  If very few people have used some feature of Oracle you might be the first person to use that feature in a specific fashion – so if there’s a surprise (or bug) waiting to be found you’ll be the first to find it and you don’t want to find it in production.

Anything in Oracle might have an odd boundary condition, and life (or the project life-cycle) is too short to test everything – but almost any time you feel you may be going beyond “common usage”, it’s worth thinking about what might go wrong.

As a closing item of entertainment – here’s a little demonstration (last run on 19.2):


rem
rem     Script:         assumptions.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2019
rem     Purpose:
rem
rem     Last tested
rem             19.2.0.0        (LiveSQL)
rem             18.3.0.0
rem
rem     Notes:
rem     Add the predicate "where rownum <= 1600" to test on LiveSSQL
rem

create table t1
as
select * from all_objects
/

create table pt1(
        OWNER, OBJECT_NAME, SUBOBJECT_NAME,
        OBJECT_ID constraint pt1_pk primary key using index local,
        DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
        TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE,
        ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED,
        SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID
)
partition by hash (object_id) (
        partition p1,
        partition p2,
        partition p3,
        partition p4
)
as
select * from all_objects
/

create table iot1 (
        OWNER, OBJECT_NAME, SUBOBJECT_NAME,
        OBJECT_ID constraint iot1_pk primary key,
        DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
        TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE,
        ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED,
        SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID
)
organization index
as
select * from all_objects
/

create table ptiot1 (
        OWNER, OBJECT_NAME, SUBOBJECT_NAME,
        OBJECT_ID constraint ptiot1_pk primary key,
        DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
        TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE,
        ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED,
        SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID
)
organization index
partition by hash (object_id) (
        partition p1,
        partition p2,
        partition p3,
        partition p4
)
as
select * from all_objects
/

alter table t1 move online;

alter table pt1 move partition p1 online;

alter table iot1 move online;

alter table ptiot1 move partition p1 online;

It's a simple test – copying data from view all_objects I've created:

  • A simple heap table
  • A hash partitioned heap table – with locally partitioned primary key index
  • A simple index-organized table
  • A hash partitioned indesc organized table

Then I’ve issued an online move command for each table. I often lose track of which enhancements to features appeared in which version of Oracle, but I think the following is correct:

  • alter table t1 move online – the online option became possible in 12.2
  • alter table pt1 move partition online – the online option because possible in 12.1
  • alter table iot1 move online – the online option became possible in Oracle 8i (and gets a mention in Practical Oracle 8i)
  • alter table ptiot1 move partition online – any guesses ?)

In the absence of my bait-and-switch lead-up to the final question I think you could be forgiven for assuming that you would be able to move a partition of a partitioned index-organized table online – but even in 19.2 you’ll end up with error message: ORA-14808: table does not support ONLINE MOVE PARTITION.

In a vacuum it’s okay to make the mistake – on the other hand if someone suggested changing a partitioned table in your production system into a partitioned IOT it ought to be one of the first things you’d check (on a small model). Sadly I have been in design meetings where weeks of effort have been spent on producing a detailed design that can’t possibly work because no-one checked to see if some critical detail (like online move of IOT partitions) was actually possible – and that’s the background for the statement:

“If you’re going to depend on a technological feature of Oracle, you need to make sure that you have tried to break it, in half a dozen ways, before you use it in production.”

There are many technological features of Oracle that you can assume (safely) have been tested by many other people – when you get to the edge of the know universe your watchword should be: Here be Dragons.

 

3 Comments »

  1. Jonathan,

    The line that separates chartered territory from the wilderness is, unfortunately, not always that distinct and obvious. While I somewhat expected to get burned when adapting, perhaps, not so widely used features like Flashback Archive and Data Redaction, it was shocking to discover database corruption after issuing “alter move” against a table containing user defined types – even more so, because it used to work correctly in the previous Oracle release.

    Comment by Nenad Noveljic (@NenadNoveljic) — July 9, 2019 @ 12:46 pm BST Jul 9,2019 | Reply

    • Nenad,

      That’s just another example of why I made the point about finding an anomaly with UNDO and another affecting REDO, and why I produced the “odd man out” of online move. Your “uncharted territory” is user-defined types – the fact that it’s “alter table move” that exposes a problem is irrelevant; the fact that it’s user-defined types and “nobody” uses them is the threat. Even if all I had to do was code for “fetch into … bulk collect … limit” in PL/SQL with a user-defined type in the select statement I’d do some very aggressive testing before trusting the internals to be doing the right thing. (e.g. is 255 or 256 a dangerous array size; does anything change if the UDT is the first / last selected value).

      One of the most annoying things about doing consulting and trouble-shooting is that you find a workaround to a problem that seems totally reasonable, completely unbreakable, and prefectly valid – only to discover two versions later it was only working by accident (and, maybe, three versions later that it’s documented as an exception to a general rule).

      Comment by Jonathan Lewis — July 9, 2019 @ 1:01 pm BST Jul 9,2019 | Reply

  2. In the very early days of 10g (when it was still 10i), I headed up a group in Development with the responsibility of building a test bed of every Oracle feature we could think of, to see if it still worked as expected with RAC. Needless to say, many things failed that test! :)

    Comment by Pete Sharman — July 9, 2019 @ 10:33 pm BST Jul 9,2019 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

You are commenting using your Google 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.

Powered by WordPress.com.