Systems Engineering and RDBMS

Archive for January 23rd, 2008

Virtual Columns in Oracle 11g

Posted by decipherinfosys on January 23, 2008

Virtual columns is a new feature in Oracle 11g. This is something that is already available in SQL Server and DB2 LUW albeit with a different name. You can read more about computed columns (virtual columns) in one of our previous blog post here. In Oracle 10g and prior releases, the only way we can achieve computed column functionality is via a trigger. In the latest release, Oracle introduced virtual columns for which values are calculated at run time. Syntax is more like DB2 syntax. Let’s follow that up using an example:

CREATE TABLE TEST_COMPUTE
(
COL1 VARCHAR2(15),
COL2 GENERATED ALWAYS AS (REVERSE(COL1)) VIRTUAL
);

In the above table, we are creating a virtual column with ‘GENERATED ALWAYS’ syntax. Column value is generated at run time based on the value that gets into the COL1 column of the table. Let’s create some data to see this in practice:
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘DECIPHER’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘INFORMATION’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘SYSTEMS’);

Let us check the output now which shows col2 contains reverse value of col1.
SQL> SELECT * FROM TEST_COMPUTE;

COL1            COL2
 ------------    ------------
 DECIPHER          REHPICED
 INFORMATION       NOITAMROFNI
 SYSTEMS          SMETSYS

For creating virtual column(s), column expression or a function should be a deterministic function which means that for a given input, virtual column should always return the same result. If that is not the case, then we can not define virtual column for such expressions. Following is an example.

SQL> ALTER TABLE TEST_COMPUTE
ADD COL3 GENERATED ALWAYS AS (TRUNC(SYSDATE));

ERROR at line 2:
ORA-54002: only pure functions can be specified in a virtual column expression

Since we can create virtual columns only for deterministic values, we can also create index(es) on virtual columns just like regular columns. Index will be always function based index.

SQL> CREATE INDEX TEST_COMPUTE_IND_1 ON TEST_COMPUTE(COL2);

We can verify it using the following SQL.

SQL> SELECT a.index_name, a.index_Type, b.column_Expression
FROM user_indexes a
INNER JOIN user_ind_Expressions b
ON a.index_name = b.index_name
WHERE a.index_name = ‘TEST_COMPUTE_IND_1’;

Here is the output.

INDEX_NAME            INDEX_TYPE                        COLUMN_EXPRESSION
 --------------------  -------------------------         --------------------
 TEST_COMPUTE_IND_1    FUNCTION-BASED NORMAL             REVERSE("COL1")

One can even partition on this column. There are some restrictions as well for virtual columns:

1. We cannot explicitly enter the data in virtual columns. Attempt to enter value for a column in the insert statement will result into ORA-54013 error.

2. We cannot update entire row using SET ROW command with TABLE%ROWTYPE operator. Virtual columns will not be ignored when we update entire record and hence will result into an error. Following is an example with an error.

DECLARE
V_TESTREC TEST_COMPUTE%ROWTYPE;
BEGIN

SELECT *
INTO v_testrec
FROM TEST_COMPUTE
WHERE col1 = ‘DECIPHER’;

v_testrec.COL1 := ‘ORACLE’;

UPDATE TEST_COMPUTE
SET ROW = v_testrec
WHERE col1 = ‘DECIPHER’;

COMMIT;

END;

ORA-54017: UPDATE operation disallowed on virtual columns
ORA-06512: at line 12

This is yet another step in the right direction – supporting an application across three different RDBMS (Oracle, MS SQL Server, DB2 LUW) would be so much more easier with such common features.

Posted in Oracle | Leave a Comment »

Two new management features in Oracle 11g

Posted by decipherinfosys on January 23, 2008

When doing migration of the schema from one version to the other, there are different modus operandi that are adopted by different DBA’s/Database Developers. You can read more on two common approaches in one our previous posts over here.  In the event of a DBA/developer chosing to use the DDLs, say that you have added a new NOT NULL constraint column to an existing table in the new release and that column has a default value associated to it.  And suppose that that table has over 100 million records in it.  Prior to Oracle 11g, when you issue such an alter statement, it would need to add the column, update the existing records with the default value and mark the column as NOT NULL.  The time taken to do so would be huge given the size of the table and given the fact that a large amount of undo and redo will be generated.  This is no longer an issue in Oracle 11g.  In 11g, when you issue such a command, say:

Alter table BIG_TABLE add New_Column varchar2(10)  default ‘test’ not null;

This statement will not issue an update to the existing records in the table.  New records will have their values set to the default value of “test” and when a query comes along that selects an older record, Oracle then derives that value out of the data dictionary and presents it.  So, bottom line is that you will no longer incur the penalty of redo and undo generation when issuing such alter commands.  This should help greatly in the conversion/migration/upgrade projects where one uses the DDL approach over the ETLM approach.

Another neat feature for schema management that has been introduced in 11g is the DDL_LOCK_TIMEOUT option.  This is something that the development DBAs/DB Developers will really appreciate.  Prior to Oracle 11g, if say you are executing the same alter statement as was shown above and if someone is holding a lock on that table, then you will get the following error:

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

What this means is that you cannot get an exclusive lock on the table in order to complete the alter command.  The only option prior to Oracle 11g was to keep on trying or to find the sessions holding the locks and terminate them.  In 11g, one can use the ddl_lock_timeout session/system setting.  Example:

SQL> Alter session set ddl_lock_timeout = 30;

Now, when this particular session encounters a lock that prevents it from getting the exclusive lock, instead of timing out, it will try the DDL operation for 30 seconds (similar to “select for update wait N” feature).  This can also be issued at the system level:

SQL> Alter system set ddl_lock_timeout = 30;

Setting it at the system level would be a better choice so that all the sessions get the same setting.  You can of course, override it at the session level.

Posted in Oracle | Leave a Comment »

Read Only Tables in Oracle 11g

Posted by decipherinfosys on January 23, 2008

Prior to Oracle 11g, one could create read only tablespaces and not read only tables.  This is now possible in the new version of Oracle.  You can convert a table from read/write to read only and vice-versa.  Example:

SQL> create table testcase (col1 varchar2(10));
Table Created.

SQL>Alter table testcase read only;
Table altered.

The DBA_TABLES/USER_TABLES/ALL_TABLES views have a new column called READ_ONLY which can be used to see which tables are read only in the schema.  Likewise, you can turn it back to be a read/write table:

SQL> Alter table testcase read write;

Needless to state, once a table has been put into the read only mode, the update/delete/insert DML operations are not allowed on it.  The select for update statement is also not allowed.  The DDL statements like alter table are allowed though.  You can use this feature to implement security or you can also use this feature during the maintenance mode when you want to prevent changes to the data in a given table or set of tables or it can also be used to protect the data in the static/look-up/configuration tables in a given schema.

Posted in Oracle | Leave a Comment »