Database, SQL and PL/SQL

On Oracle Database 12c, Part 1

Our technologist improves default values, handles bigger datatypes, and FETCHes for the first time.

By Tom Kyte Oracle Employee ACE

September/October 2013

Usually I take three or four user-submitted questions from the past two months and present those questions and answers here in each Ask Tom column. In the next four columns, however, I will take a look at some key Oracle Database 12c features. These features are all part of the “12 Things About Oracle Database 12c” presentation I gave at Oracle OpenWorld 2012 in San Francisco. (You can find the slides for that presentation on asktom.oracle.com on the Files tab). The first three Oracle Database 12c features I’ll take a look at are

  • Improved defaults

  • Bigger datatypes

  • Top-n queries

Improved Defaults

The ability to create a default column value has existed in SQL for a while. The functionality has been somewhat limited, however, with various restrictions. For example, you were restricted from using a SEQUENCE object to supply a default value. Additionally, if a default value was to be inserted into or updated in a table, you had to either use the DEFAULT keyword in the SQL statement or leave the column out of the INSERT statement entirely. Furthermore, adding a new column that permits NULL values with a default value was an offline operation. In Oracle Database 12c, however, these restrictions and functionality limitations have been removed.

Removed Restriction: Generating a Default Value from a SEQUENCE. In Oracle Database 12c, you can now use the .NEXTVAL attribute of a sequence to create a default column value. For example, this code

SQL> create sequence s;
Sequence created.
SQL> create table t
  2  ( x int
  3      default s.nextval
  4          primary key,
  5    y varchar2(30)
  6  );
Table created.
SQL> insert into t (x,y)
  2  values ( default, 'hello' );
1 row created.
SQL> insert into t (y)
  2  values ( 'world' );
1 row created.
SQL> select * from t;
         X  Y
  ————————  ————————
         1  hello
         2  world

demonstrates that you can create a default column value for the primary key from the sequence value—without using a trigger, as you would have in the past. So in Oracle Database 12c, DEFAULT S.NEXTVAL in the CREATE TABLE statement will replace the following procedural code:

SQL> create trigger t
  2  before insert on t
  3  for each row
  4  begin
  5    if (:new.x is null)
  6    then
  7       :new.x := s.nextval;
  8    end if;
  9  end;
 10  /
Trigger created.

In addition to using a reference to a sequence to create a default column value, you can alternatively use an IDENTITY type, which will generate a sequence and associate that sequence with the table. For example, this CREATE TABLE statement

SQL> create table t
  2  ( x int
  3      generated as identity
  4          primary key,
  5    y varchar2(30)
  6  )
  7  /
Table created.

will result in the same data’s being loaded into table T without your having to explicitly create a sequence (as you did in the CREATE TABLE statement that explicitly called DEFAULT S.NEXTVAL). You can see this sequence if you look at the schema:

SQL> select object_name, object_type
  2    from user_objects
  3  /
OBJECT_NAME              OBJECT_TYPE
——————————————————       —————————————
T                        TABLE
ISEQ$$_90241             SEQUENCE
SYS_C0010233             INDEX

But note that if you drop the table and purge it from the recycle bin, the sequence will be removed as well:

SQL> drop table t purge;
Table dropped.
SQL> select object_name, object_type
  2    from user_objects
  3  /
no rows selected

Because identity is using a sequence under the covers, you can also control all the settings of the underlying sequence. For example, this CREATE TABLE statement

SQL> create table t
  2  ( x int
  3      generated by default
  4          as identity
  5          ( start with 42
  6            increment by 1000 )
  7          primary key,
  8    y varchar2(30)
  9  )
 10  /
Table created.

shows that you can control the START WITH and INCREMENT BY values. Additionally, by using the GENERATED BY DEFAULT statement instead of just GENERATED, you can override the default identity value. Here I demonstrate this by inserting the value 1 and then two more rows, enabling identity to generate the default values:

SQL> insert into t (x,y)
  2  values ( 1, 'override' );
1 row created.
SQL> insert into t (x,y)
  2  values ( default, 'hello' );
1 row created.
SQL> insert into t (y)
  2  values ( 'world' );
1 row created.
SQL> select * from t;
         X  Y
——————————  ———————————
         1  override
        42  hello
      1042  world

Improved Functionality: Create a Default Value for a NULL Column. In Oracle Database 12c, you can now create a default column value not only when you use the DEFAULT keyword or leave the column entirely out of the INSERT statement but also when you set the column value explicitly to NULL.

In the past, if a column used a default value, you either had to use the DEFAULT keyword in the INSERT/UPDATE statement or leave the column entirely out of the INSERT/UPDATE statement. That meant that in order to use a default value at certain times and not others, you needed at least two INSERT/UPDATE statements with complicated if/then/else constructs. For example, if column X had a default value and you sometimes wanted to insert an overriding value and sometimes not, you would need code resembling the following:

if (x is_to_be_defaulted)
then
   insert into t (x, … )
   values ( DEFAULT, … );
else
   insert into t (x, … )
   values ( :x, … );
end if;

Now, that might be OK if you sometimes had to create a default value for one column, but what if you have two or three or more columns? Think of how many combinations of INSERTs or UPDATEs you would need with complex if/then/else blocks to support that. In Oracle Database 12c, you can now create a default column value when you explicitly put a NULL value into that column. Here’s an example:

SQL> create table t
  2  ( x number
  3      generated as identity
  4          primary key,
  5    y varchar2(30),
  6    z number default ON NULL 42
  7  )
  8  /
Table created.

By using z number default ON NULL 42, I’ve specified that column Z will receive the default value not only when I explicitly set it to DEFAULT or leave it out of the INSERT statement but also when I explicitly insert NULL into it, as in

SQL> insert into t (y)
  2  values ( 'just y' );
1 row created.
SQL> insert into t (y,z)
  2  values ( 'y with z set to null',
null );
1 row created.
SQL> insert into t (y,z)
  2  values ( 'y and z', 100 );
1 row created.
SQL> select * from t;
   X  Y                            Z
————  ——————————————————————     ————
   1  just y                      42
   2  y with z set to null        42
   3  y and z                    100

As you can see, the Z column value is created with the default value 42 in both cases now. Also, the declaration for Z had the effect of defining it as NOT NULL, even though I did not explicitly state that:

SQL> select column_name, nullable
  2    from user_tab_columns
  3   where table_name = 'T'
  4   order by column_id
  5  /
COLUMN_NAME  N
———————————  —
X            N
Y            Y
Z            N

More Online Operations: Better Column Addition. In Oracle Database 11g you were able to perform a fast add of a column to a table if it had a default value and was defined as NOT NULL. (Arup Nanda has written about this.) However, if you attempted to add a column with a default value and that column permitted null values, the ADD COLUMN operation could take a significant amount of time, generate a large amount of undo and redo, and lock the entire table for the duration of the operation. In Oracle Database 12c, that time, volume, and locking are no longer part of the process.

To demonstrate this, I copy ALL_OBJECTS into a table and measure its space—in blocks and bytes—using the show_space utility, posted on asktom.oracle.com:

SQL> create table t
  2  as
  3  select *
  4    from all_objects;
Table created.
SQL> exec show_space('T')
…
Full Blocks        ....        1,437
Total Blocks...........        1,536
Total Bytes............   12,582,912
Total MBytes...........           12
…
PL/SQL procedure successfully completed.

Now I add a column to table T, and this column will have a large default value. Because the column I’m adding is a CHAR(2000), it will always consume the full 2,000 bytes, given that the CHAR type is always blank-padded and fixed-width. Table T has more than 87,000 records, so adding a column would typically take a significant amount of time, but as you can see, the addition is practically instantaneous in Oracle Database 12c:

SQL> set timing on
SQL> alter table t
add (data char(2000) default 'x');
Table altered.
Elapsed: 00:00:00.07

I perform the identical operation in Oracle Database 11g and observe the following timing:

SQL> set timing on
SQL> alter table t
add (data char(2000) default 'x');
Table altered.
Elapsed: 00:00:28.59

Clearly, that’s a significant difference in runtimes. Plus, when I look at the size of the table with the additional column in Oracle Database 12c

SQL> exec show_space('T')
…
Full Blocks        ....        1,437
Total Blocks...........        1,536
Total Bytes............   12,582,912
Total MBytes...........           12
…
PL/SQL procedure successfully completed.

I see that the table did not grow at all. However, running the same test in Oracle Database 11
g shows that the table will grow from about 9 MB to 192 MB. Additionally, in Oracle Database 11
g, almost every row in the table was a migrated row, because the row grew by orders of magnitude. That table probably would be due for a reorganization in the prior releases but not in Oracle Database 12c.

Bigger Datatypes

Oracle8 Database provided a big increase in the size of VARCHAR types—from 255 bytes (in Oracle7) to 4,000 bytes. Now the Oracle Database 12c release increases the size from 4,000 bytes to 32 K, bringing the SQL VARCHAR2, NVARCHAR2, and RAW datatypes in line with their PL/SQL counterparts.

By default, this new capability is not enabled and would have to be enabled by the DBA’s setting the new MAX_STRING_SIZE init.ora parameter to EXTENDED. Once that’s done, you’ll be able to issue statements such as

SQL> create table t ( x varchar(32767) );
Table created.

and then use string functions such as RPAD, LPAD, and TRIM :

SQL> insert into
t values ( rpad('*',32000,'*') );
1 row created.
SQL> select length(x) from t;
 LENGTH(X)
——————————————
     32000

In the past, RPAD and other string built-in functions would have been able to return only 4,000 bytes, but now they can return up to 32 K for a VARCHAR2 return type.

Under the covers, Oracle Database 12c is using a large object (LOB) to store these larger strings and raw types. If the inserted string is up to 4,000 bytes, the database will store the data in the table database block just as it does with a legacy VARCHAR2 type; if the string exceeds 4,000 bytes, however, the database will transparently store it out of line in a LOB segment and index.

Top-N Queries and Pagination

Out of the many thousands of questions on Ask Tom (asktom.oracle.com), a couple of the most popular are, “How do I get rows N through M of a result set” (how to paginate through a result set) and “How do I get the first N records of a result set.” In fact, I’ve written more than one article in Oracle Magazine over the years to address these questions (“On Top-n and Pagination Queries” and “On ROWNUM and Limiting Results”). These articles demonstrated how to accomplish these feats, but the methods demonstrated are cumbersome, nonintuitive, and not necessarily portable.

Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set or, alternatively, the first N records after skipping over a set of records, so you can easily paginate through a result set. The diagram in Figure 1 shows the syntax for the row limiting clause.

o53asktom-f1

Figure 1: Row limiting clause syntax

The row limiting clause is simply added to the end of any SQL SELECT statement to fetch a specific set of records—there is no need for multiple layers of inline views and WHERE clauses that have to be carefully positioned, as there was with ROWNUM and ROW_NUMBER().

For example, if I have a table T

SQL> create table t
  2  as
  3  select * from all_objects;
Table created.
SQL> create index t_idx
on t(owner,object_name);
Index created.

and I want to retrieve the first five rows after sorting by OWNER and OBJECT_NAME, I only need to add a FETCH FIRST N ROWS to the SQL query, as shown in Listing 1.

Code Listing 1: Simple SELECT query with FETCH FIRST

SQL> select owner, object_name, object_id
  2    from t
  3   order by owner, object_name
  4   FETCH FIRST 5 ROWS ONLY;
…
——————————————————————————————————————————————————————————————————————————————
| Id |Operation                     | Name|Rows  |Bytes |Cost (%CPU)|Time    |
——————————————————————————————————————————————————————————————————————————————
|   0|SELECT STATEMENT              |     |    5 | 1450 |    7   (0)|00:00:01|
|*  1| VIEW                         |     |    5 | 1450 |    7   (0)|00:00:01|
|*  2|  WINDOW NOSORT STOPKEY       |     |    5 |  180 |    7   (0)|00:00:01|
|   3|   TABLE ACCESS BY INDEX ROWID|T    |87310 | 3069K|    7   (0)|00:00:01|
|   4|    INDEX FULL SCAN           |T_IDX|    5 |      |    3   (0)|00:00:01|
——————————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————————————————————————————————————————————————————
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)

As you can tell by the predicate information in Listing 1, the row limiting clause is using ROW_NUMBER() transparently under the covers, rewriting the query to use analytics. The row limiting clause, in short, is making it much easier to do something you would have done manually in the past.

To paginate through a result set—to get N rows at a time from a specific page in the result set—I add the OFFSET clause. In Listing 2, I skip the first five rows and get the next five rows from a result set.

Code Listing 2: Simple SELECT query with OFFSET FETCH

SQL> select owner, object_name, object_id
  2    from t
  3   order by owner, object_name
  4  OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
…
—————————————————————————————————————————————————————————————————————————————
| Id |Operation                     |Name |Rows |Bytes |Cost (%CPU)|Time    |
—————————————————————————————————————————————————————————————————————————————
|   0|SELECT STATEMENT              |     |    5| 1450 |    7   (0)|00:00:01|
|*  1| VIEW                         |     |    5| 1450 |    7   (0)|00:00:01|
|*  2|  WINDOW NOSORT STOPKEY       |     |    5|  180 |    7   (0)|00:00:01|
|   3|   TABLE ACCESS BY INDEX ROWID|T    |87310| 3069K|    7   (0)|00:00:01|
|   4|    INDEX FULL SCAN           |T_IDX|    5|      |    3   (0)|00:00:01|
—————————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE  WHEN (5>=0)
       THEN 5 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumber">5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=CASE  WHEN
       (5>=0) THEN 5 ELSE 0 END +5)

As you can see in Listing 2, the database, under the covers, is rewriting the query to use inline views and analytics once again—automating something that was previously nonintuitive and complex.

Note that in real life, you would use bind variables instead of hard-coded literals, so instead of using the number 5 as I did, you would have bound in the number 5.

Next Steps

 ASK Tom
Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.

 FOLLOW Tom on Twitter

 READ more Tom

 DOWNLOAD Oracle Database 12c

FOLLOW Oracle Database
 on Twitter
 on Facebook

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.