Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: 12c
12c FETCH PERCENT
A nice little feature in 12c is the FETCH FIRST n ROWS syntax, which is a simple shorthand to avoid using inline views and the like to get a subset of the rows from what would normally be a larger resultset. Here’s a simple…
Subtle variations in optimizer stats
Subtle variances in the way you work with statistics can have a significant impact on how your optimizer plans work out…so you need to take care. Let’s look at the following example SQL> create table T ( 2 x varchar2(20) , y varchar2(100)); Table…
Opatch quick tip
I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error: C:\oracle\stage\22581007>opatch apply Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2016, Oracle Corporation. All rights reserved. Oracle Home : C:\oracle\product\1210~1.2 Central Inventory : C:\Program Files\Oracle\Inventory from :…
TOP-N histograms on 12c
I had an interesting question on AskTom today, with a nicely provided test case !!!, so I thought I’d share it here with some additional commentary. The basic premise of the TOP-N histogram in 12c is that when the number of distinct values in…
How can I see my invisible columns
A cool new feature in 12c is the ability to make a column invisible. The concept has existed since 8i to handle things like “set unused” and function based indexes, but now it is available to developers directly. SQL> create table T ( c1…
Something new learned every day
One of the reasons I leapt at the chance to be on the AskTom team when we resurrected the site, was that it’s like free training. You get questions on topics you have not visited before, and you get new angles on things you…
DEFAULT SEQ.NEXTVAL in 12c
One of the cool things in 12c is that (finally after all these years) a sequence can be assigned as the default value for a column. Of course, some may say “big deal” – after all, it is trivial to create a simple trigger…
Multiple partitions
In 12c, one of the nice changes to come along is that partition maintenance operations can now be done on multiple partitions, for example ALTER TABLE t1 MERGE PARTITIONS p01, p02, p03, p04 INTO p0; ALTER TABLE SPLIT PARTITION p0 INTO (PARTITION p01 VALUES…
CLOBS, from 11g to 12c
If you are a regular user of LOB’s in the database, take care when you switch from 11g to 12c. Notice the subtle difference: In 11g, you are permitted to, and recommended to, use SECUREFILE lobs, but they are not the default SQL> select…
Longer strings in 12c
Most people are now aware that in 12c, a VARCHAR2 has been extended from a maximum size of 4000 to 32767 bytes. Similarly, there’s many a note/blog post etc about needing to set the following parameter to enable it: SQL> alter system set max_string_size…
Loading file data … easier than you think
So a request comes in from a colleague… ”Hi Connor. I’ve created this new table, and I need to populate it with some data. I’ve got it in Excel – can you help me load it” Now, at this point, my heart normally sinks,…
Navigating the world of Oracle database documentation
If you head on over to http://docs.oracle.com/en/database/ you’ll be both amazed and dismayed by the volume of documentation you can find about the database. If you’re a seasoned Oracle professional, then you probably dont think twice about finding PIVOT examples in Chapter 18 of…
