Another little 12c improvement

September 21, 2018September 21, 2018 Connor McDonald 12c, dba

You’ve got a huge table right? Massive! Immense! And then something bad happens. You get asked to remove one of the columns from that table.

“No problem” you think. “I won’t run the ‘drop column’ command because that will visit every block and take forever!”

So you settle on the perfect tool for such a scenario – simply mark the column as unused so that it is no longer available to application code and the developers that write that code.

But there’s a catch that not many people know about. SET UNUSED is meant to be just a trivial database dictionary operation. We don’t touch the data, we only manipulate the column definition and thus an execution of SET UNUSED should be instantaneous. This is indeed the case most of the time, but as the example below shows – the way the column was added to the table, can have a bearing on what it costs to have that column removed.


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--
-- large-ish table to monitor the impact
--
SQL> create table t1
  2   as
  3   select d.*
  4   from dba_objects d,
  5   ( select 1 from dual connect by level <= 100);

Table created.

SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
   8713700

Elapsed: 00:00:01.92

--
-- Take an existing column to UNUSED ... instantaneous!
--
SQL> alter table t1 set unused column created;

Table altered.

Elapsed: 00:00:00.06


--
-- Add a new column, and then make it UNUSED ... instantaneous!
--
SQL> alter table t1 add new_col1 timestamp;

Table altered.

Elapsed: 00:00:00.01
SQL> alter table t1 set unused column new_col1;

Table altered.

Elapsed: 00:00:00.00


--
-- Add a new columns with a not null default, and then make it UNUSED ... careful!
--
SQL> alter table t1 add new_col2 timestamp default sysdate not null;

Table altered.

Elapsed: 00:00:00.00
SQL> alter table t1 set unused column new_col2;

Table altered.

Elapsed: 00:01:35.39

You will not get the same issue in 12c.


SQL> select banner from v$version;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t1  nologging
  2   as
  3   select d.*
  4   from dba_objects d,
  5   ( select 1 from dual connect by level <= 100);

Table created.

SQL>
SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
   7951500

Elapsed: 00:00:02.20
SQL> alter table t1 set unused column created;

Table altered.

Elapsed: 00:00:00.08
SQL>
SQL> alter table t1 add new_col1 timestamp;

Table altered.

Elapsed: 00:00:00.00
SQL> alter table t1 set unused column new_col1;

Table altered.

Elapsed: 00:00:00.00
SQL>
SQL> alter table t1 add new_col2 timestamp default sysdate not null;

Table altered.

Elapsed: 00:00:00.01
SQL> alter table t1 set unused column new_col2;

Table altered.

Elapsed: 00:00:00.01
SQL>

Partial indexing – get the dictionary definitions right

September 17, 2018September 21, 2018 Connor McDonald

Just a quick post to clear up some confusion that can be seen on the partial indexing capabilities in Oracle Database 12c and above. I was at an event in the Oracle Perth office last week, and an attendee mentioned that they thought the feature was not working or was not enabled on their database, and presented the following demonstration to prove their case:



SQL> create table t ( x int, y int )
  2  partition by range ( x )
  3  (
  4    partition p1 values less than ( 1000 ) indexing on,
  5    partition p2 values less than ( 2000 ) indexing off
  6  );

Table created.

SQL> insert into t
  2  select rownum, rownum from dual connect by level < 2000;

1999 rows created.

SQL> create index ix on t ( x ) local;

Index created.

SQL> select segment_name, partition_name
  2  from user_segments
  3  where segment_name = 'IX';

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
IX                             P1
IX                             P2

SQL> select partition_name, status
  2  from user_ind_partitions
  3  where index_name = 'IX';

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             USABLE
P2                             USABLE

At first glance, this looks counter-intuitive. I have explicitly specified that I do not want indexing on partition P2, yet after creating a local index, I still have 2 segments, one for each partition, and double-checking USER_IND_PARTITIONS tells me that they are both “fully-fledged” usable index partitions.

As per the documentation linked above, nominating the INDEXING ON / OFF at the partition level on the table definition is not the whole story. When you create the index, you need to inform the database that you wish a particular index to respect that intent. This is because you might want some indexes to be partial and others not to be.

So by slightly changing my CREATE INDEX statement, I can get the desired outcome.


SQL> drop index ix;

Index dropped.

SQL> create index ix on t ( x ) local indexing partial;

Index created.

SQL> select segment_name, partition_name
  2  from user_segments
  3  where segment_name = 'IX';

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
IX                             P1

SQL> select partition_name, status
  2  from user_ind_partitions
  3  where index_name = 'IX';

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             USABLE
P2                             UNUSABLE

Connor and Chris at OpenWorld

September 14, 2018September 21, 2018 Connor McDonald asktom, conference, openworld

Chris and I will be at OpenWorld next month, so our session details are below, but you can also click on the links below to add entries to your calendar to make sure you don’t miss us. Don’t forget to use the official Schedule Builder to make sure you have booked your spot at our sessions!

If you can’t get to our sessions, you might catch us wandering the halls or at The Hub. Feel free to come and say Hello and talk tech!

See you at OpenWorld!

Calendar ICS files

Fast Lane to Database Success (.ics)

Developers – Don’t Be The Person That Discombobulates Your Database (.ics)

18 Things Developers Will Love About Database 18c (.ics)

Why Isn’t My Query Using An Index (.ics)

Session Details

The full stack developer….is BACK for 2018!

September 11, 2018September 21, 2018 Connor McDonald apex, conference, openworld

Last year, I flexed my technology muscles by building on the fine ground work of Lucas Jellema in using some Node, some REST, and some JSON to extract the full Oracle Openworld speaker catalogue, and then added some JSON parsing in the database, some SQL and slapped a nice helping on Application Express on top of it all, to end up with an Application Express rendition of the Oracle Openworld speaker catalogue.

So it is that time of year again, and whilst some of the bits and pieces have changed, I’ve managed to stumble my way through all of the difference pieces once again, and made some enhancements along the way to give you the new and improved 2018 version!

It’s waiting there on apex.oracle.com for you to explore.

https://tinyurl.com/oow18catalog

Enjoy!

Modifying tables without losing materialized views

September 10, 2018September 21, 2018 Connor McDonald materialized view

Whilst the ever increasing speed of storage and servers, and features likes In-memory are decreasing the need for large numbers of materialized views in databases, there are still plenty of use cases where they can be a useful performance or integrity tool.

But what if that materialized view takes minutes or hours to build? Then even if it is fast refresh enabled, than fast refresh is no of use if we have to rebuild the entire materialized view from scratch due to an structural change in the underlying table.

For example, let’s say I have a table and a simple fast refresh materialized view on that table as below:


SQL> create table t(x number(8) primary key);

Table created.

SQL> insert into t values(55);

1 row created.

SQL> create materialized view log on t with primary key, rowid;

Materialized view log created.

SQL> create materialized view mv
  2  build immediate
  3  refresh fast on demand as
  4  select * from t;

Materialized view created.

SQL>
SQL> select * from t;

         X
----------
        55

1 row selected.

SQL> select * from mv;

         X
----------
        55

1 row selected.

SQL> insert into t values (10);

1 row created.

SQL> insert into t values (20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

         X
----------
        10
        20
        55

3 rows selected.

SQL> select * from mv;

         X
----------
        55

1 row selected.

SQL>
SQL> exec dbms_mview.refresh('mv');

PL/SQL procedure successfully completed.

SQL> select * from mv;

         X
----------
        10
        20
        55

3 rows selected.

For the sake of this discussion, let’s assume the build of materialized takes hours. Naturally we want to avoid having to a rebuild (or do a complete refresh) of that materialized view. But then…the inevitable happens. We need to change the table T. In this case, the values for column X now exceed the limits of the definition.


SQL> insert into t values (123123123);
insert into t values (123123123)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

Obviously, this is a trivial fix for the table. We simply alter the column to make it larger (which does not require any downtime or reorganization of the data).


SQL> alter table t modify x number(10);

But all is not well…Having a materialized view means that either the materialized view log, or the materialized view itself may have replicas of that column, so they remain “broken”


SQL> insert into t values (123123123);
insert into t values (123123123)
            *
ERROR at line 1:
ORA-12096: error in materialized view log on "MCDONAC"."T"
ORA-01438: value larger than specified precision allowed for this column

Notice the subtle difference in the error message. It is not an error on table T, but an error on the materialized view log. You might have the urge to simply jump and run the alter commands on the materialized view log and the materialized view. And you might even find that this approach works. But please note – this approach is not supported, and thus we can’t guarantee that it will (a) work, or (b) not create problems later down the track when you attempt to refresh the view or perform other operations.

But if direct alteration is not support, how do we solve the problem without having to rebuild the entire materialized view from scratch?

The solution here is the option to preserve the materialized view as a standalone table. We can drop the definition of the materialized view but hold on to the table that supports it. Now that it is a standalone table, we can alter the column definition so that it matches our source table T. Notice that before I drop the definition, I perform a final refresh to make sure the materialized is totally up to date – so there is a little bit of coordination required here to make sure that you do not lose any changes that occur to table T during the process.


SQL> exec dbms_mview.refresh('mv');

PL/SQL procedure successfully completed.

SQL> drop materialized view mv preserve table;

Materialized view dropped.

SQL> drop materialized view log on t ;

Materialized view log dropped.

SQL>
SQL> alter table t modify x number(10);

Table altered.

SQL> alter table mv modify x number(10);

Table altered.

We still don’t have our materialized view back though. But we can recreate it without needing a full build cycle, using the PREBUILT table clause.


SQL> create materialized view log on t with PRIMARY KEY, rowid;

Materialized view log created.

SQL>
SQL> create materialized view mv
  2  on prebuilt table
  3  refresh fast  on demand as
  4  select * from t;

Materialized view created.

And we are done! An easy and supported means of altering the materialized view structure without a full rebuild of the data.

Complex materialized views? Try a table first

September 3, 2018September 21, 2018 Connor McDonald materialized view

Just a quick post today that arose from an AskTOM question a little while back. Over the years and across the evolution of various versions of the Oracle database, the amount of “power” you could pack into a CREATE TABLE statement as grown. For example, I can do a CREATE-TABLE-AS-SELECT, along with a partitioning clause, including an explicit CREATE INDEX command and add constraints all in one atomic operation.



SQL> create table t
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_pk primary key ( id1) using index
  7        ( create index t_pk on t ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  as
 15    select
 16      object_id id1,
 17      object_id id2,
 18      owner,
 19      object_name
 20  from dba_objects
 21  where object_id is not null;

Table created.

That’s pretty cool, but one of the most common times you will be writing DDL that includes a “select * from” as part of the DDL definition, is during the creation of materialized views, because the view text naturally will contain a select statement. Unfortunately, we are not quite as “generous” when it comes to accepting all of the various physical implementation options when it comes to DDL for materialized views. Trying to create a materialized view that matches our table T above is a struggle.


SQL> create materialized view t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  refresh complete on demand
 15  as
 16    select
 17      id1,
 18      id2,
 19      owner,
 20      object_name
 21  from t;
   constraint t_mv_pk primary key ( id1) using index
              *
ERROR at line 6:
ORA-00907: missing right parenthesis


SQL>
SQL> create materialized view t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  refresh complete on demand
 10  partition by range ( id2 )
 11  (
 12    partition p1 values less than ( 100000 ),
 13    partition p2 values less than ( 200000 )
 14  )
 15  as
 16    select
 17      id1,
 18      id2,
 19      owner,
 20      object_name
 21  from t;
   constraint t_mv_pk primary key ( id1) using index
              *
ERROR at line 6:
ORA-00907: missing right parenthesis

When you encounter this limitation, don’t forget that one of the nice things you can do with materialized view creation is pre-create the underlying table with all of its flexibility:



SQL> create table t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  as
 15    select
 16      id1,
 17      id2,
 18      owner,
 19      object_name
 20  from t;

Table created.

And then once that it done, you can use the PREBUILT TABLE clause to create your materialized view which will now satisfy all of the underlying physical structure elements you desired.



SQL> create materialized view t_mv
  2  on prebuilt table
  3  refresh complete on demand
  4  as select
  5      id1,
  6      id2,
  7      owner,
  8      object_name
  9  from t;

Materialized view created.

SQL>
SQL>

18c database creation on Windows

August 27, 2018September 21, 2018 Connor McDonald 18c, dba, installation, windows

Hopefully you’ve followed my very simple and easy guide to downloading the 18c database software for Windows. But of course, software on its own is not much use – we need a database! So let’s get cracking and create one. Using the Start menu like I’ve done below, or using the Windows panels, locate the Database Configuration assistant and start it.

After a few seconds the initial screen will ask what you want to do. Choose “Create Database”.

If you like you could just go with “Typical Configuration” and you’ll be done in just a couple of clicks, but I always prefer to opt for the “Advanced Configuration” for two reasons. Firstly, even if you accept all of the defaults, it gives you a better idea of what options are going to be installed, where the files will be stored etc. And secondly…well…we all like to think of ourselves as advanced don’t we Smile

For just research and exploration on your own Windows machine, you’ll probably want to opt for just a single instance database. RAC takes a bit more setup and complexity. For a faster install, choose one of the options that has the datafiles included. This way, the installer will just copy some existing files and seed them as your database, rather then building the entire instance from scratch.

Now choose a unique and memorable name for your database. I’ve reached into the depths of my imagination and come up with “db18” for my version 18 database. Go figure Smile . I’d also recommend you go with a container database configuration, because that is the strategic direction for Oracle going forward, so if you are going to have a database to skill up on, it makes sense for that database to be a container database.

To keep things simple, I’m just nominating 1 single location for all of my database files. It can be anywhere but a common convention is that wherever you house them, you’ll have a folder called “oradata” and then a folder for each database you create under that.

I’m skipping the fast recovery area and archiving at this stage. If I start to get serious with testing things like backup and recovery, then I would revisit this after database creation to enable at least archiving so that I can explore all the goodies that RMAN has to offer.

On a brand new installation, it is likely you will not have a pre-existing listener to choose from. (If you had a previous installation, or had run the Network Configuration Assistant already, then you would see a listener to use).

I’m creating one called LISTENER18. The default port is normally 1521, but I’ve opted for 1518 just to align it with the version I’m using.

I’m skipping Data Vault and Label Security, but hopefully you can now see why it’s cool to go with the “Advanced Configuration” – you get to see all the potential functionality areas of the database that you might want to explore.

Now you choose how much of your server/desktop/laptop you’re going to allow this database to grab. My machine has plenty of RAM, but it also has a stack of other database versions running on it to handle my AskTOM daily tasks. So I’ll keep this dude at around 6G.

Now we’ll flick across the other tabs on this screen to see if there anything of note. The default for processes seems to have gone up in this version (I think it used to be around 300 in 12c) but in any event, that’s more than enough for me on this machine.

I have simple rule for character sets – UTF all the way. Single byte charactersets are soooo last century. We live in a global village, so you should be able to handle characters from all over the world!

And for the last tab, I’m going to opt for the sample schemas, so that when my database is created I’m not just left with an empty database shell. I want some sample data there so I can jump straight in and start experimenting with this release.

If I’m a DBA, I might go for some EM management to see how that all works, but for now, I’m skipping that.

Being a sandbox for experimenting, I’m setting all the passwords to a common value. Naturally this will not what you’ll be doing for your production databases!

So now I’m ready to go. I always tick the “Generate Scripts” option because it lets me see what is actually occurring when the creation assistant is doing its job. Even so, the scripts are probably not what I would use to automate a database install, since the ‘dbca’ command has nice command line option nowadays, along with the ‘-silent’ option so you can create an entire database with just a single command.

I finally get a summary of what is about to occur, and we’re off! Database creation is go for launch!

The total time to create your database will depending on your hardware, in particular how fast your storage is. I’ve done a few creations now using both flash storage and conventional hard drives, and unsurprisingly the flash storage is faster. You’re probably looking at around 10 minutes to complete.

When your database creation is complete, you’ll get the standard summary screen and you are ready to go.

And here it is – the finished product! My 18c database on Windows is ready to go. By default, the underlying Windows service will have a Start status of “Automatic” which means your database will start every time Windows starts. If you are short on memory, or do not want the database started unless you explicitly want it to, you can set this to Manual via “services.msc”

You can watch the video version of this installation here

Enjoy your 18c Windows database !

Learning is not a spectator sport

Connor McDonald's Oracle blog