1. Category

1) Object tables

2) Relational tables

Heap-organized table
Index-organized table
External table

A table is either permanent or temporary.

A permanent table definition and data persist across sessions. A temporary table definition persists in the same way, but the data exists only for the duration of a transaction or session.

2. Oracle data types

commonly used data types fall into the following categories:

Character Data Types
Numeric Data Types
Datetime Data Types
Rowid Data Types
Format Models and Data Types

Character Data Types

The VARCHAR2 data type stores variable-length character literals.

The CHAR stores fixed-length character strings.

The NCHAR and NVARCHAR2 data types store Unicode character data. Unicode is a universal encoded character set that can store information in any language using a single character set.

Numeric Data Types

The NUMBER data type stores fixed and floating-point numbers.You specify a fixed-point number in the form. NUMBER(p,s). For example, NUMBER(8,2) ,the database stores 100,000 as 100000.00.

Datetime Data Types

The DATE data type stores date and time. The database stores dates internally as numbers. The standard date format is DD-MON-YY, which displays dates in the form. 01-JAN-09. Oracle Database stores time in 24-hour format—HH:MI:SS.

The TIMESTAMP data type is an extension of the DATE data type. It stores fractional seconds in addition to the information stored in the DATE data type. Useful for storing precise time values, such as in applications that must track event order.

Rowid Data Types

Physical rowids store the addresses of rows in heap-organized tables, table clusters, and table and index partitions.
Logical rowids store the addresses of rows in index-organized tables.
Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed through a gateway. They are not standard Oracle Database rowids.

Use of ROWID

Rowids are the fastest means of accessing particular rows.

Rowids provide the ability to see how a table is organized.

Rowids are unique identifiers for rows in a given table.

Create tables with columns defined using the ROWID data type whose values can be updated and so on.

ROWID Pseudocolumn

Every table has a pseudocolumn named ROWID.

Behaves like a table column, but not actually stored in the table.

Can be selected, but not updated, inserted, or deleted.

Format Models and Data Types

TO_CHAR

TO_DATE

3. Integrity Constraints

Named rules that restrict the values for one or more columns in a table.

Stored in the data dictionary.

4. Object Tables

An Oracle object type is a user-defined type with a name, attributes, and methods.

An object type defines a logical structure, but does not create storage.

For example,

CREATE TYPE department_typ AS OBJECT

( d_name VARCHAR2(100),

d_address VARCHAR2(200) );

An object table is a special kind of table in which each row represents an object.

By default, every row object in an object table has an associated logical object identifier (OID) that uniquely identifies it in an object table. The OID column of an object table is a hidden column.

For example,

CREATE TABLE departments_obj_t OF department_typ;

INSERT INTO departments_obj_t

VALUES ('hr', '10 Main St, Sometown, CA');

5. Temporary Tables

Hold data that exists only for the duration of a transaction or session.

Data in a temporary table is private to the session.

Temporary Table Creation

when you create a temporary table in Oracle, you create a static table definition described in the data dictionary.

Indexes views and triggers can created on temporary tables.

Segment Allocation in Temporary Tables

Temporary segments are allocated when data is first inserted, before that, the table appears empty.

Temporary segments are deallocated at the end of the transaction.

6. External Tables

External Table Creation

Creating an external table means creating metadata in the data dictionary.

The metadata describes how the external table layer must present data to the database, nor how data is stored in the DB or in the external table.

A CREATE TABLE ... ORGANIZATION EXTERNAL statement has two parts:

The external table definition describes the column types. This definition is like a view that enables SQL to query external data without loading it into the database.
The second part of the statement maps the external data to the columns.

External table access driver

An access driver(runs in the database) is an API that interprets the external data for the database.

Oracle provides the ORACLE_LOADER (default) and ORACLE_DATAPUMP access drivers for external tables.

7. Table Storage

Table Organization

By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order.

The database usually stores columns in the order in which they were listed in the CREATE TABLE statement, but this order is not guaranteed. For example, if a table has a column of type LONG, then Oracle Database always stores this column last in the row

Row Storage

Each row of a table containing data for less than 256 columns is contained in one or more row pieces.

If possible, Oracle Database stores each row as one row piece, or stores the row using multiple row pieces

Rowids of Row Pieces

Every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece.

Storage of Null Values

A null is the absence of a value in a column.

Nulls are stored in the database if they fall between columns with data values. In these cases, they require 1 byte to store the length of the column (zero).

Trailing nulls in a row require no storage because a new row header signals that the remaining columns in the previous row are null.

8. Table Compression

Compression saves disk space, reduces memory use in the database buffer cache, and in some cases speeds query execution.

Be transparent to database applications.

Basic and OLTP Table Compression

Dictionary-based table compression.

Basic table compression, is intended for bulk load operations. You must use direct path loads, ALTER TABLE . . . MOVE operations, or online table redefinition to achieve basic compression.
OLTP table compression, is intended for OLTP applications and compresses data manipulated by any SQL operation.

Hybrid Columnar Compression

external table access.JPG

ITPub博客

NOTES on concepts ---Tables

最新文章