Pro Oracle Database 11g Administration (29 page)

A virtual column itself can’t be updated via the SET clause of an UPDATE statement. However, you can reference a virtual column in the WHERE clause of an UPDATE or DELETE statement.

You can optionally specify the data type of a virtual column. If you omit the data type, Oracle derives the data type from the expression you use to define the virtual column.

Several caveats are associated with virtual columns:

• You can only define a virtual column on a regular heap-organized table. You can’t define a virtual column on an index-organized table, an external table, a temporary table, object tables, or cluster tables.

• Virtual columns can’t reference other virtual columns.

• Virtual columns can only reference columns from the table in which the virtual column is defined.

• The output of a virtual column must be a scalar value (a single value, not a set of values).

To view the definition of a virtual column, use the DBMS_METADATA package to view the DDL

associated with the table. If you’re selecting from SQL*Plus, you need to set the LONG variable to a value large enough to show all data returned:

SQL> set long 10000;

SQL> select dbms_metadata.get_ddl('TABLE','INV') from dual;

Here is a partial snippet of the output:

CREATE TABLE "INV_MGMT"."INV"

( "INV_ID" NUMBER,

"INV_COUNT" NUMBER,

"INV_STATUS" VARCHAR2(11) GENERATED ALWAYS AS (CASE WHEN "INV_COUNT"<=50 THEN

'NEED MORE' WHEN ("INV_COUNT">50 AND "INV_COUNT"<=200) THEN 'GETTING LOW' WHEN "

INV_COUNT">200 THEN 'OKAY' END) VIRTUAL VISIBLE ...

Making Read-Only Tables

Starting with Oracle Database 11
g
, you can place individual tables in read-only mode. Doing so prevents any INSERT, UPDATE, or DELETE statements from running against a table. In versions prior to Oracle Database 11
g
, the only way to make a table read-only was to either place the entire database into readonly mode or place a tablespace in read-only mode (making all tables in the tablespace read-only).

140

CHAPTER 7 ■ TABLES AND CONSTRAINTS

There are several reasons why you may require the read-only feature at the table level:

• The data in the table is historical and should never be updated in normal circumstances.

• You’re performing some maintenance on the table and want to ensure that it doesn’t change while it’s being updated.

• You want to drop the table, but before you do, you want to place it in read-only mode to better determine if any users are attempting to update the table.

Use the ALTER TABLE statement to place a table in read-only mode:

SQL> alter table inv read only;

You can verify the status of a read-only table by issuing the following query: SQL> select table_name, read_only from user_tables where read_only='YES'; To modify a read-only table to read-write, issue the following SQL:

SQL> alter table inv read write;

■
Note
The read-only table feature requires that the database initialization COMPATIBLE parameter be set to 11.1.0 or higher.

Understanding Deferred Segment Creation

Starting with Oracle Database 11
g
Release 2, when you create a table, the creation of the associated segment is deferred until the first row is inserted into the table. This feature has some interesting implications. For example, if you have thousands of objects that you’re initially creating for an application (such as when you first install it), no space is consumed by any of the tables (or associated indexes) until data is inserted into the application tables. This means the initial DDL runs more quickly when you create a table, but the first INSERT statement runs slightly slower.

To illustrate the concept of deferred segments, first create a table: SQL> create table inv(inv_id number, inv_desc varchar2(30));

You can verify that the table has been created by inspecting USER_TABLES: select

table_name

,segment_created

from user_tables

where table_name='INV';

Here’s some sample output:

TABLE_NAME SEG

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

INV NO

Next, query USER_SEGMENTS to verify that a segment hasn’t yet been allocated for this table: 141

CHAPTER 7 ■ TABLES AND CONSTRAINTS

select

segment_name

,segment_type

,bytes

from user_segments

where segment_name='INV'

and segment_type='TABLE';

Here’s the corresponding output for this example:

no rows selected

Now, insert a row into a table:

SQL> insert into inv values(1,'BOOK');

Rerun the query, selecting from USER_SEGMENTS, and notice that a segment has been created: SEGMENT_NAME SEGMENT_TYPE BYTES

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

INV TABLE 65536

If you’re used to working with older versions of Oracle, the deferred-segment-creation feature can cause confusion. For example, if you have space-related monitoring reports that query DBA_SEGMENTS or DBA_EXTENTS, be aware that these views aren’t populated that for a table or any indexes associated with the table until the first row is inserted into the table.

■
Note
You can disable the deferred-segment-creation feature by setting the database-initialization parameter DEFERRED_SEGMENT_CREATION to FALSE. The default for this parameter is TRUE.

Allowing for Parallel SQL Execution

If you work with large tables, you may want to consider creating your tables as PARALLEL. This instructs Oracle to set the degree of parallelism to be used for any subsequent INSERT, UPDATE, DELETE, MERGE, and query statements. This example creates a table with a PARALLEL clause of 2: create table inv_apr_10

parallel 2

as select * from inv

where create_dtt >= '01-apr-10' and create_dtt < '01-may-10';

You can specify PARALLEL, NOPARALLEL, or PARALLEL N. If you don’t specify N, Oracle sets the degree of parallelism based on the PARALLEL_THREADS_PER_CPU initialization parameter.

142

CHAPTER 7 ■ TABLES AND CONSTRAINTS

■
Tip
Keep in mind that PARALLEL_THREADS_PER_CPU can vary considerably from a development environment to a production environment. Your development box may contain only 2 CPUS, whereas a production server may have 32 CPUs. Therefore, if you don’t specify the degree of parallelism, the behavior of parallel operations can vary widely depending on the environment.

Compressing Table Data

Oracle has had table compression for quite some time. Prior to Oracle Database 11
g
, the compression available was only suitable for data-warehouse environments, mainly because the table-level compression was CPU intensive and degraded the performance of DML statements. This type of compression usually wasn’t suitable for online transaction processing (OLTP) environments.

■
Note
OLTP table compression is a feature of the Oracle Advanced Compression option. The Oracle Advanced Compression option requires an additional license from Oracle.

Starting with Oracle Database 11
g
, you can do this:

create table inv(

inv_id number

,inv_name varchar2(64)

) compress for oltp;

The COMPRESS FOR OLTP clause enables compression for all DML operations (in prior versions of Oracle, the compress feature was limited to direct-path INSERT operations). The OLTP compression doesn’t immediately compress data as it’s inserted and updated in a table. Rather, the compression occurs in a batch mode when the block reaches a certain internally defined threshold. When the threshold is reached, all of the uncompressed rows are compressed at the same time. The threshold at which compression occurs is determined by an internal algorithm.

■
Note
Oracle also has a hybrid columnar compression feature that is available with the Oracle Exadata product.

See Oracle’s technology website (http://otn.oracle.com) for more details.

143

CHAPTER 7 ■ TABLES AND CONSTRAINTS

You can verify the compression for a table via the following SELECT statement: select

table_name

,compression

,compress_for

from user_tables where table_name='INV';

Here is some sample output:

TABLE_NAME COMPRESS COMPRESS_FOR

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

INV ENABLED OLTP

Avoiding Redo Creation

When you’re creating a table, you have the option of specifying the NOLOGGING clause. The NOLOGGING

feature can greatly reduce the amount of redo generation for certain types of operations. Sometimes, when you’re working with large amounts of data, it’s desirable for performance reasons to reduce the redo generation when you initially create and insert data into a table.

The downside to eliminating redo generation is that you can’t recover the data created via NOLOGGING

in the event a failure occurs after the data is loaded (and before you can back up the table). If you can tolerate some risk of data loss, then use NOLOGGING but back up the table soon after the data is loaded. If your data is critical, then don’t use NOLOGGING. If your data can be easily re-created, then NOLOGGING is desirable when you’re trying to improve performance of large data loads.

One perception is that NOLOGGING eliminates redo generation for the table for all DML operations.

That isn’t correct. The NOLOGGING feature never affects redo generation for normal DML statements (regular INSERT, UPDATE, and DELETE).

The NOLOGGING feature can significantly reduce redo generation for the following types of operations:

• SQL*Loader direct path load

• Direct path INSERT /*+ append */

• CREATE TABLE AS SELECT

• ALTER TABLE MOVE

• Creating or rebuilding an index

You need to be aware of some quirks (features) when using NOLOGGING. If your database is in FORCE

LOGGING mode, then redo is generated for all operations, regardless of whether you specify NOLOGGING.

When you’re loading a table, if the table has a referential foreign-key constraint defined, then redo is generated regardless of whether you specify NOLOGGING.

You can specify NOLOGGING at one of the following levels:

• Statement

• CREATE TABLE or ALTER TABLE

• CREATE TABLESPACE or ALTER TABLESPACE

I prefer to specify the NOLOGGING clause at the statement or table level. In these scenarios, it’s obvious to the DBA executing the statement or DDL that NOLOGGING is used. If you specify NOLOGGING at the tablespace level, then each DBA that creates objects within that tablespace must be aware of this 144

CHAPTER 7 ■ TABLES AND CONSTRAINTS

tablespace-level setting. In teams with multiple DBAs, it’s easy for one DBA to be unaware that another DBA has created a tablespace with NOLOGGING.

This example first creates a table with the NOLOGGING option:

create table inv(inv_id number)

tablespace users

nologging;

Next, do a direct-path insert via selecting from a table with historical data: SQL> insert /*+ append */ into inv select inv_id from inv_mar_2010; SQL> commit;

What happens if you have a media failure after you’ve populated a table in NOLOGGING mode (and before you’ve made a backup of the table)? After a restore and recovery operation, it will appear that the table has been restored:

SQL> desc inv

Name Null? Type

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

INV_ID NUMBER

However, try to execute a query that scans every block in the table:

SQL> select * from inv;

An error is thrown, indicating that there is logical corruption in the datafile: ORA-01578: ORACLE data block corrupted (file # 4, block # 11057)

ORA-01110: data file 4: '/ora02/dbfile/O11R2/users01.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

If you specify a logging clause at the statement level, it overrides any table or tablespace setting. If you specify a logging clause at the table level, it sets the default mode for any statements that don’t specify a logging clause, and overrides the logging setting at the tablespace. If you specify a logging clause at the tablespace level, it sets the default logging for any CREATE TABLE statements that don’t specify a logging clause.

You verify the logging mode of the database as follows:

SQL> select name, log_mode, force_logging from v$database;

The next statement verifies the logging mode of a tablespace:

SQL> select tablespace_name, logging from dba_tablespaces;

And this example verifies the logging mode of a table:

SQL> select owner, table_name, logging from dba_tables where logging = 'NO'; How do you tell whether Oracle logged redo for an operation? One way is to measure the amount of redo generated for an operation with logging enabled versus operating in NOLOGGING mode. If you have a development environment that you can test in, you can monitor how often the redo logs switch while the operation is taking place. Another simple test is to time how long the operation takes with and without logging. The operation performed in NOLOGGING mode should be faster (because a minimal amount of redo is being generated).

145

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Creating a Table from a Query

Sometimes it’s convenient to create a table based on the definition of an existing table. For example, say you want to create a quick backup of a table before you modify the table’s structure or data. Use the CREATE TABLE AS SELECT statement (sometimes referred to colloquially as CTAS) to achieve this. For example:

create table cwp_user_profile_101910

as select * from cwp_user_profile;

The previous statement creates an identical table complete with data. If you don’t want the data included, you just want the structure of the table replicated, then provide a WHERE clause that always evaluates to false (in this example, 1 will never equal 2):