Adding a new column

Every time we need to add a column during business hours we may face some stress trying to do it, for example:

Session 1 – 10g:

SQL> create table users (id number, name varchar2(10));

Table created.

SQL> Insert into users values(1,'Rodrigo');

1 row created.

Session 2 – 10g:

SQL> alter table users add (phone number);

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

Session 1 – 11g:

SQL> create table users (id number, name varchar2(10));

Table created.

SQL> Insert into users values(1,'Rodrigo');

1 row created.

Session 2 – 11g:

SQL> alter table users add (phone number);

Table altered.

• Can you see the first improvement?, since the first session didn’t commit it is holding an exclusive lock that prevent us to do any ddl in the older releases, but now at least we can add a new column, to modify or drop we still have to wait the lock to be released .

DDL wait parameter

As we saw we just need to wait to modify and drop a column, and that is really frustrating when there are a lot of users doing DML on your table, so the solution for that is the parameter DDL_LOCK_TIMEOUT, when we use this parameter our commands wait the lock to be released and then execute it, that way we don’t need to be trying thousands of time, example:

Session 1 – 11g:

SQL> update users

2 set phone=11111111

3 where id=1;

1 row updated.

Session 2 – 11g:

SQL> alter table users modify (name varchar2(60));

alter table users modify (name varchar2(60))

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

We got the error now we try:

SQL> Alter session set ddl_lock_timeout=10;

Session altered.

SQL> alter table users modify (name varchar2(60));

Session hanging ….

Session 1 – 11g:

SQL> commit;

Commit complete. –lock released

Session 2 – 11g:

SQL> alter table users modify (name varchar2(60));

Table altered.

With this parameter our command hangs till the lock is released and then executes it.

If you want you can use the ALTER SYSTEM, and use a wait time for the whole instance.

New columns with default values – not null

In previous versions when we added a column with default value and not null, Oracle will add the column and update all the rows in the table with the default value, imagine doing that in a table with 50 million rows, well will take a long time and also will fill up your Undo tablespace.

Now Oracle 11g is more “intelligent” doing that, when we add a new column with default value it will not update the table for the existing records, just the new ones will have the value inserted, then when we select the data it will get the default value from the dictionary and show to us, very clever I would say.

Let’s do an example:

SQL> create table magtest(id number, name varchar2(10));

Table created.

SQL> insert into magtest values(1,'Rodrigo');

1 row created.

SQL> insert into magtest values(2,'Righetti');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table magtest add country varchar2(10) default 'BRA' not null;

Table altered.

SQL> insert into magtest(id,name) values (3,'Oracle');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from magtest;

ID NAME COUNTRY

---------- ---------- ----------

1 Rodrigo BRA

2 Righetti BRA

3 Oracle BRA

You can see that all the rows are with the default value, but only the 3^rd rows had it value inserted, now we are going to confirm that with a block dump:

Let’s check the block_id:

SQL> Select dbms_rowid.rowid_block_number(rowid) block, COUNT(1)

from magtest

GROUP BY dbms_rowid.rowid_block_number(rowid) ;

BLOCK COUNT(1)

---------- ----------

1188. 3

The File_id:

SQL> select file_id from dba_EXTENTS where segment_name='MAGTEST';

FILE_ID

----------

4

SQL> ALTER SYSTEM DUMP DATAFILE 4 BLOCK MIN 1188 BLOCK MAX 1188;

System altered.

tl: 14 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 02

col 1: [ 7] 52 6f 64 72 69 67 6f

tab 0, row 1, @0x1f7b

tl: 15 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 03

col 1: [ 8] 52 69 67 68 65 74 74 69

tab 0, row 2, @0x1f6a

tl: 17 fb: --H-FL-- lb: 0x2 cc: 3

col 0: [ 2] c1 04

col 1: [ 6] 4f 72 61 63 6c 65

col 2: [ 3] 42 52 41

As you can see only the last row has 3 columns stored, all the other 2 don’t, and that in my opinion is a great improvement in terms of table management.

Virtual Columns

There are cases we need to have a column that change dynamically based on updates in other columns, normally we would use a trigger for that, but now we can use the virtual column.

For example:

We need to have a column in our stock table that will separate our products based in the amount we have in stock.

create table stock

( id number,

product varchar2(30),

quantity number,

status varchar2(10)

generated always as

( case

when quantity <= 10 then 'LOW'

when quantity > 11 and quantity <= 100 then 'MEDIUM'

when quantity > 101 and quantity <= 1000 then 'HIGH'

else 'OVERMUCH'

end ) virtual

);

insert into STOCK (ID, PRODUCT, QUANTITY) values (1,'TOY1',5);

insert into STOCK (ID, PRODUCT, QUANTITY) values (2,'TOY2',25);

insert into STOCK (ID, PRODUCT, QUANTITY) values (3,'TOY3',150);

insert into STOCK (ID, PRODUCT, QUANTITY) values (4,'TOY4',1200);

COMMIT;

SQL> SELECT * FROM STOCK;

ID PRODUCT QUANTITY STATUS

---------- ------------------------------ ---------- --------

1 TOY1 5 LOW

2 TOY2 25 MEDIUM

3 TOY3 150 HIGH

4 TOY4 1200 OVERMUCH

SQL> UPDATE STOCK

2 SET QUANTITY = 50

3 WHERE ID=4;

1 row updated.

SQL> SELECT * FROM STOCK;

ID PRODUCT QUANTITY STATUS

---------- ------------------------------ ---------- --------

1 TOY1 5 LOW

2 TOY2 25 MEDIUM

3 TOY3 150 HIGH

4 TOY4 50 MEDIUM

For me seems the Virtual column, works quite well, and with some performance tests I’ve done I didn’t see any overhead to return this column, I would say this is a really good new feature.

Also you can add this column with the alter table statement:

alter table stock add (status varchar2(10)

generated always as

( case

when quantity <= 10000 then 'LOW'

when quantity > 10000 and quantity <= 100000 then 'MEDIUM'

when quantity > 100000 and quantity <= 1000000 then 'HIGH'

else 'OVERMUCH'

end ) virtual

);

Another good point is that we can create indexes on it, also we can partition based on the virtual column, what we can’t do is insert or update this column.

SQL> create index stock_status on stock(status);

Index created.

Elapsed: 00:00:00.09

SQL> set autotrace on

SQL> select * from stock where status='HIGH';

ID PRODUCT QUANTITY STATUS

---------- ------------------------------ ---------- ----------

3 TOY3 150 HIGH

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 3923203641

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 50 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| STOCK | 1 | 50 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | STOCK_STATUS | 1 | | 1 (0)| 00:00:01 |

The index type is:

SQL> select index_type from user_indexes where index_name = 'STOCK_STATUS';

INDEX_TYPE

---------------------------

FUNCTION-BASED NORMAL

SQL> select column_expression from user_ind_expressions where index_name = 'STOCK_STATUS';

COLUMN_EXPRESSION

--------------------------------------------------------------------------------

CASE WHEN "QUANTITY"<=10 THEN 'LOW' WHEN ("QUANTITY">11 AND "QUANTITY"<=100) TH

EN 'MEDIUM' WHEN ("QUANTITY">101 AND "QUANTITY"<=1000) THEN 'HIGH' ELSE 'OVERMUC

H' END

Invisible Indexes

Every new release Oracle brings new features to the optimizer, and invisible index is one of them.

What we can do is hide an index from the optimizer, and it will only be used if specified by a hint or an alter session command, this feature can be very helpful and also can be very painful if you forget you have made an index invisible.

Example:

SQL> create table obj as select * from dba_objects;

Table created.

SQL> create index obj_object_id_idx on obj(object_id);

Index created.

SQL> analyze table obj compute statistics;

Table analyzed.

SQL> set autotrace trace explain

SQL> select object_name from obj where object_id=57896;

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3628079472

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| OBJ | 1 | 28 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | OBJ_OBJECT_ID_IDX | 1 | | 1 (0)| 00:00:01 |

SQL> alter index OBJ_OBJECT_ID_IDX invisible;

Index altered.

SQL> select object_name from obj where object_id=57896;

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 730912574

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 282 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| OBJ | 1 | 28 | 282 (1)| 00:00:04 |

SQL> alter session set optimizer_use_invisible_indexes =true;

Session altered.

Elapsed: 00:00:00.00

SQL> select object_name from obj where object_id=57896;

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3628079472

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| OBJ | 1 | 28 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | OBJ_OBJECT_ID_IDX | 1 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

SQL> select visibility from user_indexes where index_name = 'OBJ_OBJECT_ID_IDX';

VISIBILIT

---------

INVISIBLE

You can also use the invisible index to make a safer clean up, what I mean is if you have an over indexed table you can monitor them and also you can hide them before you really drop it, since the invisible index it is kept up to date, all dml will take effect on the index, if you have hidden a good index you just need to make it visible again, without the pain of recreate it.

Read only tables

Now we have one more feature to control access on a table, not only for security purposes but also for maintenance reasons.

When we put a table in read only mode, all commands that intend to change the data will not work:

Example:

SQL> alter table obj read only;

Table altered.

SQL> delete from obj;

delete from obj

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "SQLMAG"."OBJ"

SQL> truncate table obj;

truncate table obj

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "SQLMAG"."OBJ"

SQL> create index obj_name_idx on obj(object_name);

Index created.

SQL> alter table obj move tablespace users;

Table altered.

As you can see there are some ddl you can perform and others you can not, as mentioned above, just the ones that will not change the data or the table structure can be executed, below is the list of commands that you can and can not execute:

Not allowed operations in read-only mode:

All DML operations on the table or any of its partitions

• TRUNCATE TABLE

• SELECT FOR UPDATE

• ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN

• ALTER TABLE SET COLUMN UNUSED

• ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION

• ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependents

• Online redefinition

• FLASHBACK TABLE

Allowed operations in read-only mode:

• SELECT

• CREATE/ALTER/DROP INDEX

• ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT

• ALTER TABLE for physical property changes

• ALTER TABLE DROP UNUSED COLUMNS

• ALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME/SPLIT (SUB)PARTITION

• ALTER TABLE MOVE

• ALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINK

• RENAME TABLE and ALTER TABLE RENAME TO

• DROP TABLE

• ALTER TABLE DEALLOCATE UNUSED

• ALTER TABLE ADD/DROP SUPPLEMENTAL LOG

Sequence in PL/SQL expressions

Normally when we want to get a value from a sequence using PL/SQL we use:

Select sequence_name.nextval into var1 from table;

Now we can use it direct on the pl/sql variable:

Var1 := sequence_name.nextval;

Example:

SQL> create sequence obj_seq

2 start with 1

3 increment by 1;

Sequence created.

SQL> set serverout on

declare

var1 number;

begin

for i in 1..10 loop

var1:= obj_seq.nextval;

dbms_output.put_line(var1);

end loop;

end;

SQL> /

1

2

3

4

5

6

7

8

9

10

Enhancements to the Regular Expressions and new function

Regular expressions were created on Oracle 10g and now they come with some improvements and with a new function, they are a very powerful tool to work with these data types:

CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, and VARCHAR2

Regular expressions are very common in Unix tools to work with character data types, they can be used to find, replace, look for white spaces, etc. any kind of character manipulation, and this kind of operation was brought to the database side.

A full article can be written about it, but let’s check at least a few examples:

The functions are:

REGEXP_LIKE , REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE and REGEXP_COUNT (new on 11g).

To understand it better you can relate them with the common functions LIKE, INSTR, SUBSTR and REPLACE, the difference is the regular expressions are much more powerful.

To use them we need to know the metacharacters, for example:

(.) – Used to find any character less a new line – e.g.: r.d – that means I want to find any pattern that has the letter r (any letter) o, like: rod, Rodrigo, production

(^) – Used to point the beginning of a line

($) – Used to point the end of a line

e.g.: ^r.d$ - it is going to look for - rod

(*) – Multiple characters similar to % using the function LIKE

The regular expressions also work with POSIX(Portable Operating System Interface), that means you can look for any kind of “type” of character like lower cases.

E.G.:

[[:lower:]]{8} - This example will look for 8 consecutives lower case characters.

There is a huge list of metacharacters to be used, all of them are available at the Oracle documentation, so just showing a few examples now:

Examples:

REGEXP_LIKE

create table toys

(id number,

descr varchar2(20));