13.1 Tables

---

So what can be done with table objects?

13.1.1 Caching

This option is much like the keep and recycle buffer pools from a previous version of Oracle Database and applies to full table scans only. Caching will allow forcing of small static tables into MRU (most recently used) cache and large seldom used tables into LRU (least recently used) cache.

For my Accounts schema I could do something like that shown as follows. The GeneralLedger and StockMovement tables are large and I would like to force any full table scans on very large tables out of cache as soon as possible. Small static lookup tables in my Accounts schema are Type, Subtype, COA, Stock, and Category. I would want to force these lookup tables to remain in cache for as long as possible.

ALTER TABLE generalledger NOCACHE; ALTER TABLE stockmovement NOCACHE; ALTER TABLE type CACHE; ALTER TABLE subtype CACHE; ALTER TABLE coa CACHE; ALTER TABLE stock CACHE; ALTER TABLE category CACHE;

The Customer and Supplier tables are semi-static and are not cached because they are large and not generally full table scanned. Other static tables such as Period and PeriodSum are not cached because they are rarely used. Changes will be made to these pools with further discussion on caching tables and indexes in Chapter 15.

Tip 

There are numerous methods of caching objects in Oracle Database. It is not necessarily particularly useful, perhaps causing more problems than it resolves.

13.1.2 Logging

Logging can be switched off where redo log entries are minimized. Switching off logging for an object such as a table will help performance but is not recommended because recoverability can be seriously affected. Logging applies to indexes as well as tables. Indexes are recoverable without logging because they can be rebuilt. Rebuilding indexes is not a problem as long as applications can account for slow access speeds whilst no indexes are available and indexes are rebuilt, as a result of recovery.

13.1.3 Table Parallelism

A table can be set as being accessible in parallel or not in parallel for DML and SELECT activity. Parallel execution applies to database servers with more than one CPU. I will experiment using a rather geriatric dual 300 MHz PII CPU server with minimal RAM (128 Mb).

Some points to note about parallelism:

• Full Table Scan.   A query must contain a full table scan to be executed in parallel.

• Oracle Partitioning.   It is most appropriate to use parallelism with Oracle Partitioning or a powerful machine with plenty of RAM and at least two CPUs.

• CREATE TABLE AS SELECT.   Often believed to be executable in parallel. It is, but only from a remote database.

• Bitmaps.   Using bitmap indexes in an OLTP database is unwise. Parallel operations on tables using bitmap indexes in data warehouses are commonly used.

So what does use parallelism? Let's use a large table in the Accounts schema. First of all make sure that the table has no parallelism.

ALTER TABLE generalledger NOPARALLEL;

Now create a temporary table and compute statistics.

CREATE TABLE tmp1 AS SELECT * FROM generalledger; ANALYSE TABLE tmp1 COMPUTE STATISTICS;

Now count the rows with parallelism switched off. The time elapsed is less than 1 s.

SQL> SELECT COUNT(*) FROM (SELECT * FROM tmp1);        COUNT(*) -----------      752741     Elapsed: 00:00:00.07 

Here is the query plan.

Query                          Cost -----------------------------  ---- 1. SELECT STATEMENT on          495 2.  SORT AGGREGATE on 3.   TABLE ACCESS FULL on TMP1   495

Now set the temporary table to PARALLEL 2.

ALTER TABLE tmp1 PARALLEL 2;

Figure 13.1: Nonparallel Versus Parallel Processing

Count the rows again. Note how the time elapsed has more than quadrupled using parallelism. This same result was found in Chapter 8. Figure 13.1 shows a picture of CPU usage for both processors. Notice how the query executed in parallel is using both processors even though the elapsed time is slower. It is quite possible that my somewhat geriatric dual CPU machine is the problem but not necessarily.

SQL> SELECT COUNT(*) FROM (SELECT * FROM tmp1);        COUNT(*) -----------      752741     Elapsed: 00:00:03.04 

Here is the query plan. The cost for the parallel executed query is about half that of the serially executed query. My database server machine is probably just too old.

Query                                      Cost -----------------------------------------  ---- 1. SELECT STATEMENT on                      248 2.  SORT AGGREGATE on 3.   SORT AGGREGATE on PARALLEL_TO_SERIAL 4.    TABLE ACCESS FULL on TMP1          PARALLEL_COMBINED_WITH_PARENT       248

It is sensible to conclude that parallel processing works best with Oracle Partitioning and very large tables. My Accounts schema tables, at two million rows, are simply not large enough to show any effect and my hardware platform is inadequate. Oracle Partitioning will be covered in detail in Chapter 17.

13.1.4 Storing LOBs Separately

LOB objects such as images are generally large. Storing a large chunk of space into a table row block is very likely to result in large numbers of rows being occupied by multiple blocks. Row chaining and row migration are inefficient. Oracle Database allows LOB objects to be stored in a tablespace physically separate to the tablespace in which the table row is stored.

I have two LOB objects in the Stock table in my Accounts schema. The first object is called Stock.DESCRIPTION and is a text CLOB column. The second is called Stock.IMAGE and is a BLOB column containing an image. How do I change my Stock table to store the contents of these columns into a specialized objects tablespace? One method of moving the image column to a specialized objects table-space called OBJECTS is as shown. Since I have not yet stored any images in the table there is no problem dropping and recreating the column.

ALTER TABLE stock DROP COLUMN image; ALTER TABLE stock ADD (image BLOB) LOB (image)       STORE AS image (TABLESPACE objects);

There are various special LOB parameters. These parameters can sometimes affect performance adversely.

• STORAGE IN ROW.   Set to DISABLE and stores only a pointer in the row. It is much more efficient to store only the pointer to a contiguous binary object in a table.

• CHUNK.   LOB manipulation disk space reserved chunk.

• PCTVERSION.   Multiple versions storage of the same LOB object.

• RETENTION.   How many old versions can be stored.

• FREEPOOLS.   LOB freelists for high concurrency access.

The most important aspect about LOB objects with respect to tuning is to store them out of line. Out of line implies separated from other data in the row. The most efficient method of storing multimedia type objects is to use a BFILE pointer. A BFILE pointer contains the full path and name of an object file. The object file is located somewhere on disk, outside of the database. BFILE pointers are the most efficient LOB-type object storage for both relational and object databases.

13.1.5 Dropping Columns

Dropping columns from very large tables can cause a problem. Simply dropping a column using the DROP COLUMN clause from a very large table will very likely cause an exclusive table lock. During the process of dropping a column both the column definition is removed from the metadata, and data values are physically removed from data blocks. In very high availability systems dropping a column from a table can cause serious performance problems.

There are two options for column removal from a table in addition to the DROP COLUMN option.

• DROP COLUMN.   Removes the column both physically and from metadata.

  ALTER TABLE table DROP { COLUMN column | ( column [,     column ¼ ] ) };

• SET UNUSED COLUMN.   Removes access to the column to avoid interrupting service. Use this option for high usage, very large tables in highly available databases. Perhaps even small lookup tables could cause a problem when a column is dropped from them. It all depends on how much the table is accessed concurrently.

  ALTER TABLE table SET UNUSED    { COLUMN column | ( column [, column ¼ ] ) };

• DROP UNUSED COLUMNS.   Destroys metadata and physical column values for any unused columns. Use this option in low activity times to remove the column entirely, after having used the SET UNUSED COLUMN option previously. Use the DROP UNUSED option to reclaim physical space.

  ALTER TABLE table DROP { UNUSED COLUMNS | COLUMNS     CONTINUE };

13.1.6 Deallocating Unused Space

Unused space above the high water mark can be cleared for reuse. Deallocation can help to make a table smaller, particularly if extensive deletion has occurred.

ALTER TABLE table DEALLOCATE UNUSED [KEEP integer     [ K | M ]];

---