SkillBuilders | Oracle 9i Administration | Lesson 13: Managing Tables

Instructor's Notes

(If applicable)

Lesson 13: Managing Tables
We will start this lesson with a brief overview of the eight different types of tables that Oracle offers. The remainder of the lesson will concentrate on the most common type of table – heap tables. Not only will you learn how to create heap tables, you will also learn the practical management skills you will need for day to day tasks. Skills such as:

managing concurrency with INITRANS and MAXTRANS
moving tables
collecting statistics for the cost based optimizer
increasing performance by turning off redo logging
compressing tables
simple techniques for measuring performance

First, let’s take a brief look at the different types of tables Oracle supports…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Oracle provides many different organizations for storing data. The most common type – and the default – is called a "heap" table. With a heap table, data is inserted wherever it fits within the blocks allocated for the table. There is no specific sequence to the rows in a heap table. Thus, rows will not necessarily come out in the same order as it was inserted. Due to time constraints, this lesson will focus on heap tables.
Here is a brief description of the other table types (refer to the appendix Other Table Types for details on these table types):

Index-Organized Table (IOT) – A table stored in a B-Tree index structure.
Temporary Table – Stores data for the life of a transaction or until a session ends.
Index Clustered Table – Multiple tables stored physically together based on a key.
Hash Clustered Table – Multiple tables stored physically together based on a hash function.
Nested Table – Child table within a parent table’s definition. Part of Oracle’s Object Relational extensions.
Object Table – Table created based on an object type.
External Table – Table data is stored outside the database in an operating system file.

Other Text:
(Examples or comments displayed on slide, if any).

This lesson covers heap tables
See Appendix C for Other Table Types

Instructor's Notes

(If applicable)

Use the CREATE TABLE statement with the ORGANIZATION HEAP parameter to create a heap table. Requirements are a table name and at least one column definition (column name and data type). Each parameter and clause shown in this example will be discussed in this lesson.
Object Name Rules
Oracle objects (e.g. tables, indexes, triggers) and object parts (e.g. columns, integrity constraints) all adhere to the same naming rules, described here:

Names can be from 1 to 30 characters in length. Exceptions include database names (limited to 8 characters) and database links (up to 128 characters).
Names cannot contain quotes.
Names are not case sensitive, but are stored in the dictionary in upper case.
Names must begin with an alphabetic character.
Valid characters are a-z, 0-9, and _, $, and #.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t
2 (c1 number )
3 organization heap
4 tablespace ts1
5 nologging
6 nocompress
7 nomonitoring
8 storage (buffer_pool default)
9 initrans 5
10 maxtrans 5
11 /

Table created.

Table Name – 1 to 30 characters
Heap is default organization
All parameters used here are discussed in this lesson

Instructor's Notes

(If applicable)

Names cannot be reserved words (e.g. SELECT).

The preceding rules can be violated by enclosing the object-name in double-quotes. For example, we can force a table name to contain mixed case and spaces, and a column name to be the reserved word “COLUMN”:
SQL> CREATE TABLE "My New Table" ("COLUMN" number);

Table created.

Recommendation
Do not violate standard naming conventions. By using the double quotes around an object name, the users will have to remember to access the object in the same way in their code, for example:
SQL> insert into "My New Table" values (1);

1 row created.

SQL> select * from "My New Table";

COLUMN
----------
1

Without quotes, you will encounter errors:
SQL> select * from my new table;
select * from my new table
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> select column from "My New Table";
select column from "My New Table"
*
ERROR at line 1:
ORA-00936: missing expression

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The CREATE TABLE syntax requires at least one column definition. The column definition must contain a column name (1 to 30 characters) and a datatype (see next section in this lesson). Optionally the column definition can contain the DEFAULT clause and a constraint. A constraint is a rule applied to the column. For example, a primary key constraint insure that the data in the column is unique and not null. You will learn all about constraints later in the Constraints lesson.
The DEFAULT clause provides a way to assign a default value to the column if a value is omitted from an INSERT statement or the DEFAULT keyword is used in the VALUES clause (as shown in the example above; note that the DEFAULT keyword in the VALUES clause is an Oracle9i feature). Most built-in SQL functions can be used. Here is an additional example:
SQL> create table t
2 (c1 number default 10,
3 c2 char(4) default to_char(sysdate, 'yyyy'));

Table created.
Refer to the CREATE TABLE section in the Oracle9i SQL Reference for complete details on the DEFAULT clause.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table test
2 (c1 date DEFAULT sysdate);

Table created.

SQL> insert into test
2 values(DEFAULT);

1 row created.

CREATE TABLE table-name
(col-name datatype [DEFAULT expr] [constraint]
[
,col-name datatype [DEFAULT expr] [constraint] . . .
] )

Instructor's Notes

(If applicable)

This section of the lesson provides a brief description and, in many cases, an example of many common datatypes. Complete descriptions of all datatypes can be found in the Oracle9i SQL Reference.

CHAR (n) – Used for fixed length character data. Maximum length is 2000 bytes. Default size is 1 byte.
VARCHAR2 (n) and VARCHAR (n) – Used for variable length character data. Maximum length is 4000 bytes. Although its current definition is the same as VARCHAR2, VARCHAR has been deprecated and should not be used.
NUMBER (p,s) – Variable length numeric data. Valid range for precision (p) is 1 to 38. Valid range for scale is -84 to 127.
CLOB – Character data. Maximum size is 4 gigabytes.
NCHAR (n) – Fixed length Unicode character data. Maximum size is 2000 bytes. Default size is 1 byte.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t2
2 (c1 number(10,2),
3 c2 varchar2(50),
4 c3 clob);

Table created.
SQL> insert into t2
2 values(12345678.99, 'x', 'x')
3 /

1 row created.

Instructor's Notes

(If applicable)

NVARCHAR2 (n) – Variable length Unicode character data. Maximum size is 4000 bytes. Default size is 1 byte.
NCLOB – Unicode character data. Maximum size is 4 gigabytes.
LONG – Variable length character data. Maximum size is 2 gigabytes. Many restrictions and performance issues apply to this datatype. This datatype has been deprecated as of Oracle9i. Use CLOB instead.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

DATE - Fixed length date and time. Stores day, month, year, century, hours, minutes, seconds. Uses 7 bytes of storage.
TIMESTAMP(p) – Includes date and time with fractional seconds. The precision is for the number of digits in the fractional second field and ranges in value from 0-9. Default is 6 digits of fractional seconds.
TIMESTAMP(p) WITH TIME ZONE – Timestamp plus a time zone setting either as an offset from UDT or as a regional value. UDT is Coordinated Universal Time, the new standard that replaces Greenwich Mean Time (GMT).
TIMESTAMP(p) WITH LOCAL TIME ZONE – Same as TIMESTAMP WITH TIMEZONE except that the date/time data is converted to the database time zone when inserted and converted to the client’s time zone when retrieved.
INTERVAL DAY(p) TO SECOND(p) – Period of time representing days, hours, minutes, and seconds. Uses 11 bytes. The precision is the number of digits in the day and second fields. Valid values range from 0-9 and the defaults are 2 for day and 6 for second.
INTERVAL YEAR(p) TO MONTH – Period of time representing years and months. Uses 5 bytes. The precision is the number of digits in the year field and ranges from 0-9 and defaults to 2.

Other Text:
(Examples or comments displayed on slide, if any).

Date
2002-11-01 12:00:00

Timestamp
03-NOV-02 03.00.00.000000 AM

Timestamp with Local Time Zone
03-NOV-02 03.00.00.000000 AM -07:00

Instructor's Notes

(If applicable)

The recommended binary datatypes are BLOB and BFILE (RAW and LONG RAW have been deprecated). Here is a brief description of the datatypes followed by a description of the code example shown here:

BLOB – Binary data. Maximum size is 4 gigabytes. For efficiency and management reasons, the BLOB data can be stored outside of the table segment – in a separate segment. Refer to the CREATE TABLE ENABLE | DISABLE] STORAGE IN ROW parameter (in the SQL Reference) for more information.
BFILE – Binary data. Maximum size is 4 gigabytes. Value is stored in an external file outside of the database.
RAW – Binary data. Maximum size is 2000 bytes. Value is stored with a table’s row.
LONG RAW – Binary data. Maximum size is 2 gigabytes. Many restrictions and performance issues apply to this datatype. This datatype has been deprecated as of Oracle9i. Use BLOB instead.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table daves_pictures
2 (id number primary key,
3 description varchar(200),
4 picture blob not null);

Table created.

SQL> create directory boat as 'c:\boat';

Directory created.

SQL> declare
2 l_bfile bfile;
3 l_blob blob;
4 begin
5 insert into daves_pictures (id, picture)
6 values(s1.nextval, empty_blob)
7 returning picture into l_blob;
8 l_bfile := bfilename('BOAT', 'boat001.jpg');
9 dbms_lob.fileopen( l_bfile );
10 dbms_lob.loadfromfile( l_blob, l_bfile,
11 dbms_lob.getlength(l_bfile) );
12 dbms_lob.fileclose( l_bfile );
13 commit;
14 end;
15 /

PL/SQL procedure successfully completed.

Instructor's Notes

(If applicable)

The code example demonstrates one method of loading a binary file into the database (another method would be SQL*Loader). In this example I use a PL/SQL BFILE and the DBMS_LOB.LOADFROMFILE procedure. There is too much to cover beyond the scope of this book; refer to the Oracle docs for more information on loading data into binary datatypes.
See the supplied script BLOB_LOAD.SQL for a working copy of this code.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Oracle provides several datatypes for compatibility with other databases and/or ANSI. These include:

INTEGER – Converts to NUMBER(38) in Oracle.
SMALLINT – Converts to NUMBER(38) in Oracle.
DECIMAL – Equivalent to NUMBER(p, s).
FLOAT, REAL and DOUBLE PRECISION are equivalent to NUMBER.

Many other datatypes are available, including

ROWID – String representing a row address. Base 64 notation.
XMLType – Use for storing and querying XML documents.
HTTPUriTYPE – Use for storing URL’s. Oracle will invoke HTTP to access the web page.

Refer to the Oracle9i SQL Reference for more information on these and all Oracle datatypes.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select rowid from t;

ROWID
------------------
AAAHfxAAHAAAAGOAAA

Instructor's Notes

(If applicable)

Table Create Workshop
1. Use your username (e.g. DAVE) to create a table (called "T") that contains the following columns:

C1 NUMBER
C2 DATE
C3 TIMESTAMP
Place it in the TS1 tablespace created in the previous lesson. If this tablespace is not available, place the table in the USERS tablespace.

Insert a row into the new table. Use SYSDATE and SYSTIMESTAMP for the DATE and TIMESTAMP columns.
Select the row from the table.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The CREATE TABLE AS subquery statement (CTAS) creates a new table using the structure and data from an existing table. This is effectively a define using a model and copy operation in one step. Note that the new table has only those columns specified in the column_list. It contains all the rows returned by the subquery; if the subquery does not contain a WHERE clause, the new table contains all rows.
The new table only has the NOT NULL constraints defined in the source table. We will discuss this further later in the Constraints lesson.
The second example shown above demonstrates that columns in the new table can have different names than the source – simply add a column name list as shown. Datatype specification is not allowed; the datatype of the new column is always the same as the source column.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t1 (c1 , c2) as
2 select owner, table_name
3 from dba_tables
4 where owner='SCOTT';

SQL> desc t1
Name Null? Type
------------- -------- -------------
C1 NOT NULL VARCHAR2(30)
C2 NOT NULL VARCHAR2(30)

Only not null constraints are created
Uses datatype of source columns

SQL> create table t2
2 tablespace users as
3 select * from dba_users;

Table created.

SQL> select count(*) from t2;

COUNT(*)
----------
19

Instructor's Notes

(If applicable)

As the first example demonstrates, supplying a WHERE clause that is always FALSE will prevent the copying of data from the source table to the new table.
Note that the second example shown above uses the concatenation function in the subquery column list. Whenever you create a derived column such as this, you must supply a valid column name (use a column list in the CREATE TABLE as shown or a column alias in the subquery). Violating this rule produces the following error:
SQL> create table emp2 as
2 select ename || ' ' || job
3 from scott.emp;
select ename || ' ' || job
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table emp as
2 select * from scott.emp
3 where 1=0;

Table created.

1 create table emp2 (name_job) as
2 select ename || ' ' || job
3* from scott.emp
SQL> /

Table created.

Instructor's Notes

(If applicable)

CTAS Workshop
1. Use the CREATE TABLE AS statement to create a new table based on the DBA_OBJECTS view. Place your new table in the USERS tablespace.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Object statistics are needed by the cost based optimizer (CBO) to calculate the best (lowest cost) access path for an SQL statement. For example, if CBO did not know how many blocks are used by a table, how can it decide if using an index is the fastest way to select rows from the table? If the table contains just a few blocks, than accessing the table via an index would simply slow the query down (it would be better to do a full table scan).
Statistics can be collected with the ANALYZE command or with the DBMS_STATS package. The ANALYZE command has been deprecated (and will eventually be desupported) and thus it is strongly recommended to use the DBMS_STATS package to generate statistics. This section of the lesson will focus on the DBMS_STATS package.
DBMS_STATS contains several procedures that gather statistics, each with a slightly different scope. For example, you can gather statistics on an specific table (GATHER_TABLE_STATISTICS) or all objects within a schema (GATHER_SCHEMA_STATISTICS).
DBMS_STATS also provides utility-type procedures such as EXPORT_TABLE_STATS that can be used to save / backup statistics before collecting new statistics (this could be helpful if the new statistics produce worse response time and you want to import (recover) the original statistics, or you want to compare differences). SET_TABLE_STATISTICS provides a way of directly updating statistics perhaps for simulating a production environment.

Other Text:
(Examples or comments displayed on slide, if any).

GATHER_TABLE_STATS, GATHER_SCHEMA_STATS etc.

Instructor's Notes

(If applicable)

This example demonstrates the use of the GATHER_SCHEMA_STATS procedure to collect statistics on all tables and all dependent indexes in the current schema.
Warning: Do not collect statistics on the SYS or SYSTEM schemas in Oracle9i. The system objects in these schemas (e.g. the data dictionary) are tuned for Rule Based Optimization.
Unlike the ANALYZE command, the default is to not collect statistics on all dependent indexes, so you must use CASCADE =>TRUE parameter (or collect statistics on indexes separately with GATHER_INDEX_STATS).
The METHOD_OPT parameter controls the creation of histograms. Histograms allow the optimizer to detect and choose the best access path for skewed distributions of data. Histograms are expensive, so only create them on indexed columns.
Warning: The default is to create histograms on every column. This is rarely useful and very expensive. Use one of the following METHOD_OPT options:

Notes continue on the next slide…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> exec dbms_stats.gather_schema_stats(user, -
> cascade=>true, -
> method_opt=>'for columns skewonly')

PL/SQL procedure successfully completed.

Instructor's Notes

(If applicable)

‘FOR COLUMNS SKEWONLY’ to let Oracle decide which column values are skewed enough to warrant histograms.
'FOR ALL INDEXED COLUMNS' clause, which will create histograms only on indexed columns.
'for columns x‘ – Where “x” is the name of a column in the table. Collect histograms on specific columns that you know contain skewed distribution of data (column “c10” in this example) .

Histograms are very important structures for accessing tables via columns that contain a skewed distribution of data. This is a tuning issue and outside the scope of this course. Refer to the following documents for more information:

SkillBuilders’ Oracle9i SQL Tuning for DBAs and Developers course
Oracle9i Supplied Packages and Types Reference
Oracle9i Database Performance Tuning Guide and Reference, chapter 3
http://asktom.oracle.com, search on DBMS_STATS, histograms, skewonly

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

This example shows a nifty routine for collecting statistics on all objects in the database – except those owned by SYS and SYSTEM (there are still tuned for rule based optimization, which does not use statistics). The basis for this example came from code I found on http://asktom.oracle.com.
You will find a working version of this code in:
STATS_COLLECT_DATABASE.SQL.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> begin
2 for x in ( select username
3 from dba_users
4 where username not in ( 'SYS', 'SYSTEM' ) )
5 loop
6 dbms_output.put_line('gathering stats for '||x.username);
7 dbms_stats.gather_SCHEMA_stats(x.username,
8 cascade=>true,
9 method_opt=>'for columns skewonly',
10 granularity=>'all',
11 estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
12 end loop;
13 end;
14 /
gathering stats for DBSNMP
gathering stats for CTXSYS
gathering stats for SCOTT

Instructor's Notes

(If applicable)

OPTIONS=>GATHER_STALE limits the collection of statistics to only those tables that have the MONITORING option turned on and have at least 10% of the total rows updated since the last collection. (The default OPTION is GATHER, which causes all objects in the schema to be affected, regardless of the current state of their statistics. )
I was unable to see measurable overhead generated by the MONITORING option. Tom Kyte (http://asktom.oracle.com) says “monitoring is very, very non-intrusive, very light weight -- turn it on.”
Also see the supplied script DBMS_STATS.SQL for sample code to turn monitoring on all tables in a schema and collect statistics using the GATHER STALE option.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter table t monitoring;

Table altered.

SQL> exec dbms_stats.gather_schema_stats(user, -
> cascade=>true, -
> method_opt=>'for columns skewonly', -
> options=>'gather stale')

PL/SQL procedure successfully completed.

Instructor's Notes

(If applicable)

DBMS_STATS has many additional features (which provide additional advantages over the deprecated ANALYZE command):

DBMS_STATS can be run in parallel for better performance. See the DEGREE argument for more information.
Existing statistics can be saved (backed up) in a user-defined table, so that they can be restored if desired – or transported to another database. This can be done when you are gathering statistics or explicitly with EXPORT_x_STATS.
Prevent active cursor invalidation with NO_INVALIDATE=>TRUE parameter. Changes to access path decisions will not take place until the cursor is flushed from the shared pool and the query is executed again.
Update the data dictionary (or a user statistics table) with the SET_TABLE_STATS to see effect. For example, you can change the number of rows and number of blocks to see the effect on the optimizer.

Refer to the Oracle9i Supplied PL/SQL Packages and Types Reference for complete documentation on DBMS_STATS.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

DBMS_STATS Workshop
1. Run the supplied scripts DDL.SQL and INSERT.SQL to create tables and indexes needed for this workshop.
2. Run the following command to delete all table and index statistics in your schema:

EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS(USER);

Run the following command:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(USER);

Did the command in question 2 create index statistics? Run this command to found out:

SELECT index_name
FROM user_indexes
WHERE last_analyzed IS NULL;

Workshop continues on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Run DBMS_STATS.GATHER_SCHEMA_STATS to collect stats for all of your tables and indexes. Limit histograms to just the columns that Oracle chooses.
Verify the creation of the statistics by running the following queries:

SELECT table_name
FROM user_tables
WHERE last_analyzed IS NULL;

SELECT index_name
FROM user_indexes
WHERE last_analyzed IS NULL;

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Oracle9i has made it a lot easier for DBAs to manage disk storage. Most parameters are taken care of with new features such as locally managed tablespaces, automatic segment space management (ASSM) and automatic undo management (AUM).
Recommendations on Table Storage Parameters
Do not code the following storage parameters: INITIAL, NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS. Use locally-managed tablespaces (LMT’s) instead. These parameters are deprecated by LMT’s. Coding these parameters does have an affect when the object is created – they are not ignored – and will override the LMT parameters specified when the tablespace was created. If you feel the need, review the section on locally managed tablespaces in the Tablespaces lesson.
Do not code the following parameters: FREELISTS, FREELIST GROUPS, PCTFREE, PCTUSED. These parameters are deprecated by Automatic Segment Storage Management (ASSM). If you feel the need, review the section on ASSM in the Tablespaces lesson.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The BUFFER_POOL parameter was discussed in the lesson Architecture, Part III. We will review it briefly here:
Additional buffer caches can be created with the initialization parameters DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. The purpose of using multiple buffer caches is to segregate data that is used differently into separate caches. For example, the object of the KEEP buffer pool is to retain objects in memory, thus avoiding expensive I/O operations. Use it for very frequently access tables:
SQL> CREATE TABLE pinit (c1 NUMBER)
2 STORAGE (BUFFER_POOL KEEP);

Table created.

The RECYCLE buffer pool is good for data that is not referenced frequently. The recycle pool will age blocks fast - blocks can be replaced as soon as they are no longer needed. Thus, objects that we do not want cached should be placed in the recycle pool.
SQL> CREATE TABLE dont_cache (c1 NUMBER)
2 STORAGE (BUFFER_POOL RECYCLE);

Table created.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

A transaction acquires rows locks when an UPDATE or DELETE statement is issued. In Oracle, a row lock is implemented by creating an entry in the block header. The entry identifies the transaction and the row that is locked. There are two parameters that control space allocated in the block for row locks:

INITRANS This parameter identifies the amount of space reserved within the data block header for concurrent transactions. This parameter is specified as the number of concurrent transactions. Each transaction requires 23 bytes in the data block header. Default value is 1 for tables and 2 for clustered tables or indexes.
MAXTRANS This parameter identifies the maximum number of transactions that can concurrently access a data block. The default value of MAXTRANS is operating system dependent. The maximum value cannot exceed 255.

If the number of concurrent transactions accessing a data block exceeds INITRANS, Oracle will dynamically allocate space from the free space available in the data block, if available, for the additional transactions (up to MAXTRANS). If there is no space available, or the MAXTRANS limit is reached, a transaction will wait until a blocking transaction ends (COMMIT or ROLLBACK will end a transaction). If MAXTRANS is set too low, transactions will wait until an existing transaction either commits or rolls back.
Continues…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t
2 (c1 number )
3 initrans 5
4 maxtrans 10
5 tablespace ts1;

Table created.
If MAXTRANS is too low, transactions will wait
MAXTRANS = INITRANS prevents dynamic growth

Instructor's Notes

(If applicable)

Setting MAXTRANS equal to INITRANS stops Oracle from dynamically allocating space from the data block for concurrent transactions.
Setting these parameters too low can cause transactions to wait – obviously an undesirable situation.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The CREATE TABLE NOLOGGING parameter controls redo log for direct path insert and load operations.
Direct path load operations can be done with the INSERT APPEND hint (as shown above) or SQL*Loader (see the DIRECT=TRUE parameter). Note that in my test (see supplied script LOGGING.SQL), almost 28 million bytes of redo were generated from the insert operation when logging was in effect, yet only approximately 60,000 bytes when logging was turned off.
In NOLOGGING mode, only minimal logging is in effect (new extents and dictionary changes are recorded only). Since the data is not logged, when recovery is attempted, the range of blocks marked as invalid show as logically corrupt and cannot be recovered. (Access to a recovered table produces this error: ORA-26040: Data block was loaded using the NOLOGGING option.) If the table data is critical, make sure to take a backup after a NOLOGGING operation.
Logging of a table is independent of the table indexes, i.e. CREATE INDEX has its own NOLOGGING parameter.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table nolog(c1 number) nologging;

Table created.

NAME VALUE
---------------- ----------
redo size 572

SQL> insert /*+ append */ into nolog
2 select * from temp;

236504 rows created.
. . .
NAME VALUE
---------------- ----------
redo size 60492

Redo bytes for session before INSERT
Direct path insert
After Redo bytes; only changes to dictionary are logged

Instructor's Notes

(If applicable)

Note that a table remains in NOLOGGING mode until you ALTER it. In this mode, all direct path inserts and direct path loads are not logged.
As shown above:

Query the LOGGING column of DBA_TABLES to determine the logging mode of a table.
Change the logging mode with the ALTER statement.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select logging from user_tables
2 where table_name = 'TEMP'
3 /

LOG
---
NO

SQL> alter table temp logging;

Table altered.

Instructor's Notes

(If applicable)

Mini-Workshop
1. Review the supplied script LOGGING.SQL. Take particular note of the following:

The query on V$SESSSTAT and V$SYSSTAT to display redo bytes used for the session.
SET TIMING ON – SQL*Plus command to display elapsed time for the direct path load operations.

Execute the script. How much faster was the NOLOGGING load? Here is the results of my test:

SQL> insert /*+ append */ into log
2 select * from temp;
220120 rows created.
Elapsed: 00:00:02.05

SQL> insert /*+ append */ into nolog
2 select * from temp;
220120 rows created.
Elapsed: 00:00:01.08

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Oracle9i Release 2 provides a table compression feature. Each block in a compressed table contains a symbol table so that repeated values exist only once. Significant space and I/O savings can be realized. Data warehouse environments – with low DML activity – can really benefit from this.
If you are interested in this feature, I recommend that you visit http://asktom.oracle.com and search on “oracle9i compression” for examples and performance tests.
Supplemental Notes
- These operations support compression:
  - Direct path INSERT (insert /*+ append */ …)
  - Create table as select (see example in this slide)
  - SQL*Loader direct path loads (use the DIRECT=TRUE option)
  - ALTER TABLE MOVE COMPRESS
- UPDATE and INSERT operations are not compressed. So, after some DML activity, you will want to recompress the table
- SQL> alter table t move compress;
- Table altered.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t as select * from dba_objects;
Table created.

SQL> select bytes, blocks from user_segments
2 where segment_name = 'T';

BYTES BLOCKS
---------- ----------
4194304 512

SQL> create table t2 COMPRESS as select * from dba_objects;

Table created.

SQL> select bytes, blocks from user_segments
2 where segment_name = 'T2';

BYTES BLOCKS
---------- ----------
2097152 256

50% less blocks

Instructor's Notes

(If applicable)

Table Compression Workshop
1. Run the supplied script TABLE_COMPRESSION_PREP.SQL. This script creates 2 tables, T1 and T2. Both tables are identical except that T2 was created with the COMPRESS option.
2. Query the data dictionary to view the difference in space used between tables T1 and T2.

SELECT bytes, blocks FROM user_segments
WHERE segment_name = 'T1';
SELECT bytes, blocks FROM user_segments
WHERE segment_name = 'T2';

Run the supplied script UTLXPLAN.SQL, then execute the following SQL*Plus command to turn on automatic tracing.

SET AUTOTRACE TRACEONLY;

Workshop Continues on next page….

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Execute the following queries and note the differences in I/O counts for the two queries. Specifically, take note of the difference in the values of "consistent gets" (logical I/O's) and "physical reads".

SELECT object_name FROM t1
WHERE owner = 'DAVE';

SELECT object_name FROM t2
WHERE owner = 'DAVE';

Execute the supplied script TABLE_COMPRESSION_RESET.SQL.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The ALTER TABLE MOVE statement provides several useful capabilities. You can move the table to the same or different tablespace to reorganize the table. The table is completely rewritten into a new segment, so any rows that were migrated are fixed (“un-migrated”). The high water mark will potentially be lowered - if there was space left available by DELETE operations.
While moving the table you can modify many of the attributes including storage, compression and logging.
Unless the table is an indexed organized table, this operation requires exclusive access to the table. If there is an active transaction, the following error is returned:
SQL> alter table t move compress tablespace users;
alter table t move compress tablespace users
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
Transactions started after the MOVE has started will wait until the MOVE completes. Best bet: take the tablespace that contains your table offline before moving the table.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter table t move compress tablespace users;

Table altered.
Unless “T” is IOT, needexclusive access

Instructor's Notes

(If applicable)

Supplemental Notes
Table reorganization previously required either the export/import utility or CREATE TABLE AS subquery statement. Both techniques involve several additional steps. A summary of the steps using export/import would be (see the demonstration of this technique on page 38):

Export the table. Refer to the Logical Backups – Import and Export lesson for more information on this utility.
Drop and recreate the table. All dependencies are lost or marked invalid.
Import the data.
Recreate indexes, grants, constraints. Recompile procedures and triggers.

CREATE TABLE AS can be quicker, especially if NOLOGGING is used. The process would be:

Use CREATE TABLE AS subquery to create a new table from the original table.
Drop original table. All dependencies are lost or marked invalid.
Rename new table to original table name.
Recreate constraints, grants, indexes. Recompile procedures and triggers.

The MOVE clause on the ALTER TABLE statement makes this whole process much easier!

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

One of the great features about ALTER TABLE MOVE is that it maintains all privileges and constraints that have been created on the table.
However, all dependent indexes are marked “unusable” after a MOVE operation. (Remember, the table segment is completely rewritten by ALTER TABLE MOVE, so all the ROWIDs change. This invalidates all ROWIDs stored in the index.) Therefore, as shown above, you will want to rebuild the indexes after a MOVE operation. Later, you will learn more about indexes in the Creating Indexes lesson.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter table t move;
Table altered.

SQL> select status from user_indexes where index_name='TI';

STATUS
--------
UNUSABLE

SQL> alter index ti rebuild;
Index altered.

Rebuild all indexes after MOVE

Instructor's Notes

(If applicable)

As demonstrated here, another technique for reorganization of a table is with the CREATE TABLE AS subquery statement. You can see that there’s quite a few additional steps.
Note the use of NOLOGGING on the CREATE TABLE AS subquery statement. This increase performance by suppressing redo logging for the insert phase of this statement.
As the example demonstrates, because the original table is actually dropped, you will need to manually add constraints, grants and indexes and recompile dependent procedures. You may also want to:

Backup the reorganized table. You will learn how to do this in the Introduction to RMAN lesson of this course.
Put the table in logging mode so that future direct path insert/load operations are logged (ALTER TABLE x LOGGING).

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Dropping columns allows unused space to be freed from the database and acts to keep an application “clean” so that tables contain only columns that are actually used. Dropping a column can be done immediately with this form of the statement:
SQL> alter table t drop column temporary;
Or, as the example in the slide shows, you can use SET UNUSED to mark the columns unavailable (this cannot be reversed) and actually drop them later. Due to the time and resource intensive nature of the drop column process, the SET UNUSED option is often a preferred way of dropping columns during periods of heavy usage.
Any indexes or constraints on the dropped column will automatically be dropped. Even if only one column of a multi-column index is specified, the index will still be dropped.
Dependent views and procedures are marked invalid and need to be recompiled. Additionally, statistics should be updated.
There are several restrictions related to DROP COLUMN. Refer to the Oracle9i SQL Reference for a complete description.

Other Text:
(Examples or comments displayed on slide, if any).

Mark for removal

SQL> alter table t set unused (secondary, generated);

Table altered.

Later, off-peak hours, drop columns
Recompile dependencies, gather new stats

SQL> alter table t drop unused columns;

SQL> alter procedure p1 compile;

SQL> exec dbms_stats.gather_table_stats(user, 't')

Instructor's Notes

(If applicable)

Tables can be renamed with the RENAME command as shown in the slide. However, this can create quite a mess! Dependent views, procedures and triggers become unusable because they now refer to an invalid table name. All require a code change. However, Oracle does automatically update constraints, indexes, and grants to the new table name.
Supplemental Notes
- The RENAME command can also be used to rename views, sequences, or private synonyms.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> rename t to temp;

Table renamed.

SQL> select text from user_views where view_name = 'TV';

TEXT
--------------------------------------------------------
select "C1" from t

SQL> alter procedure tp compile;

Warning: Procedure altered with compilation errors.

3/24 PL/SQL: ORA-00942: table or view does not exist

Views and procedures are not valid

Instructor's Notes

(If applicable)

Oracle9i introduced the column rename feature. Constraints and indexes dependent on the column will be updated. However, views, procedures and triggers will be marked invalid and will need a code change because they refer to the original column name!

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter table t rename column c1 to c10;

Table altered.

SQL> select status from user_objects
2> where object_name = 'TV';

STATUS
-------
INVALID

Constraints and indexes also updated
Views and procedures are not valid

Instructor's Notes

(If applicable)

Well, you have learned quite a lot about the Oracle heap table:

The heap organization is the most common and default organization for Oracle tables. There is no specific order to the rows in the heap table. Several other table organizations are available; refer to Appendix C for more information.
Common datatypes include VARCHAR2 and NUMBER. Many other datatypes are available including DATE, TIMESTAMP, XMLTYPE and more.
You know that it is critical for the performance of your database to keep object statistics current with DBMS_STATS.
Specify the “correct” values for INITRANS and MAXTRANS. “Correct” would be large enough to avoid blocking without wasting too much space in the block.
The CREATE TABLE statement supports many useful options such as compression, NOLOGGING and monitoring for DML activity.
Use the ALTER TABLE statement to change the table definition.
Use the DROP TABLE statement to drop a table.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

However, though you have learned quite a bit, there is quite a bit more to know about managing tables:

Creating constraints to insure the integrity of your data. For example, primary keys and unique constraints to prevent duplicates and foreign key constraints to insure child tables contain only valid references to the parent.
Indexing tables to increase the performance of DML operations. You’ll learn about indexes later in the Creating Indexes lesson.
Very large tables sometimes become so large you cannot manage them in a single segment. For example, when an ALTER TABLE MOVE operation takes all day long! Partitioning tables is the “divide and conquer” technique. Refer to Appendix B for an introduction to Oracle partitioning.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Constraints
Insuring the integrity of your data
See lesson on Constraints
Indexing tables
See the Creating Indexes lesson
Partitioned tables
Divide Very Large tables into separate segments
See Appendix B
Other table types
IOTs, Temporary Tables, Clusters, etc
See Appendix C
Loading
See the Loading with SQL*LOADER lesson
Importing
See the Logical Backups lesson
Backup and Recovery
See the Introduction to RMAN lesson

Instructor's Notes

(If applicable)

There are several more table organizations available. Choosing the best organization for your application can be critical to performance. Refer to the appendix called Other Table Types for a fundamental look at the other table organizations.
And, of course, we need to get data into the table. While the CREATE TABLE AS subquery and the INSERT statement will do nicely in some cases, you need to have a practical understanding of the load utility, SQL*Loader. We will learn about that in the Loading with SQL*Loader lesson later in the course.

Other Text:
(Examples or comments displayed on slide, if any).