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',