ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Oracle defines multiple table types ... each one optimized for a specific use case. Use the guide, below to find information about table types with native support inside the database in addition to the default "heap" table.
Simply stated relational tables are the primary way permanently store data in a relational database.
The complete syntax for create table is so complex that there is no rational way to explore it on a single Library page so this page is showing only a small fraction of the available technology for persisting data in table structures within the database.
Information about use of the In-Memory clause is Clause is in the Library on a separate page too: See link at page bottom.
Specify to enable tracking for the table.
You can specify flashback_archive to designate a particular flashback data archive for this table.
The flashback data archive you specify much already exist.
Specify if you want logging of all tables, indexes, and partitions within the tablespace.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.
It is always prefereable to do FORCE LOGGING at the database level.
Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the
number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
Partitioning is the method of choice for improving performance of I/O and maintenance activities by breaking up large tables into multiple independently accessed segments.
Specifies those aspects of a table definition that affect the table's extents and blocks.
Oracle considers compression, in-memory,ilm part of the Physical Attributes but for purposes of clarity in the Library we treat them as separate entities.
Enables In-Database Archiving, which allows designation of table rows as active or archived. Queries can then only b performed on the active rows within the table.
The SHARING clause can be used with a table built in an Application Root to share the METADATA or DATA, by pointer, with the Application Root's PDB children.
Specify this clause to migrate data to tablespace when the condition specified in the AFTER clause is met or when the PL/SQL function specified in the ON clause returns TRUE.
If you specify READ ONLY, then tablespace is made read only after the data is migrated.
This clause lets you specify that the virtual column expression is unusable for evaluating queries in one or more editions.
The remaining editions form a range of editions in which it is safe for the optimizer to use the virtual column expression to evaluate queries.
A non-stored column resulting from an explicit calculation possibly using a deterministic function.
Dependencies
ALL_ALL_TABLES
CDB_TAB_COL_STATISTICS
DBA_TAB_PRIVS
ALL_COL_COMMENTS
CDB_TAB_COMMENTS
DBA_TAB_STATISTICS
ALL_PARTIAL_DROP_TABS
CDB_TAB_HISTOGRAMS
DBA_TAB_STATS_HISTORY
ALL_TABLES
CDB_TAB_MODIFICATIONS
DBA_UNUSED_COL_TABS
ALL_TAB_COLS
CDB_TAB_PRIVS
TAB$
ALL_TAB_COLUMNS
CDB_TAB_STATISTICS
USER_ALL_TABLES
ALL_TAB_COL_STATISTICS
CDB_TAB_STATS_HISTORY
USER_COL_COMMENTS
ALL_TAB_COMMENTS
CDB_UNUSED_COL_TABS
USER_PARTIAL_DROP_TABS
ALL_TAB_HISTOGRAMS
COL$
USER_TABLES
ALL_TAB_MODIFICATIONS
DBA_ALL_TABLES
USER_TAB_COLS
ALL_TAB_PRIVS
DBA_COL_COMMENTS
USER_TAB_COLUMNS
ALL_TAB_STATISTICS
DBA_PARTIAL_DROP_TABS
USER_TAB_COL_STATISTICS
ALL_TAB_STATS_HISTORY
DBA_TABLES
USER_TAB_COMMENTS
ALL_UNUSED_COL_TABS
DBA_TAB_COLS
USER_TAB_HISTOGRAMS
CDB_ALL_TABLES
DBA_TAB_COLUMNS
USER_TAB_MODIFICATIONS
CDB_COL_COMMENTS
DBA_TAB_COL_STATISTICS
USER_TAB_PRIVS
CDB_PARTIAL_DROP_TABS
DBA_TAB_COMMENTS
USER_TAB_STATISTICS
CDB_TABLES
DBA_TAB_HISTOGRAMS
USER_TAB_STATS_HISTORY
CDB_TAB_COLS
DBA_TAB_MODIFICATIONS
USER_UNUSED_COL_TABS
CDB_TAB_COLUMNS
Row Chaining and Migration
Row chaining occurs when a row can no longer fit into its original block. If the entire row can fit in a new block, the row is moved completely, leaving only a forwarding pointer - this is known as row migration.
If the row has grown so large that it may not fit in a single block then the row is split into two or more blocks - row chaining. When Oracle is forced to split a row into pieces, it often splits individual columns into one or more pieces too.
The more columns a table has, too, the greater the chance a row will not fit within a single block. All of these increase I/O and negatively impact performance.
Follow the ANALYZE link at page bottom and find the section titled "List Chained Rows"
Row Storage
The format of a row is: row header, column length - value; column_length - value; column_length - value; ....
The length of a column is stored as an attribute in the row.
If the column name is "LAST_NAME" and the column is defined as CHAR(20) it is be stored as :20:Morgan--------------:
If the column name is "LAST_NAME" and the column is defined as VARCHAR2(20) it is stored as :6:Morgan:
Oracle starts adding rows from the end of the block towards the block header. In this way, the block header can grow if required.
To see the actual block and row as stored by Oracle use the DBMS_ROWID built-in package.
If you want to optimize access and improve performance create the table with those columns in order of how often they will need to be accessed rather than in the order in which you might fill out a paper form.
SELECT column_id, column_name
FROM user_tab_cols
WHERE table_name = 'MULTI_COL'
ORDER BY column_id;
Create Table As the output of a Select statement
CREATE TABLE <table_name>
TABLESPACE <tablespace_name> AS
<select statement>;
-- CTAS can be used to create an empty table CREATE TABLE ctas1 AS
SELECT table_name, tablespace_name
FROM all_tables
WHERE 1=2;
SELECT * FROM ctas1;
-- CTAS can be used to clone an existing table with some or a filtered set of its data
CREATE TABLE ctas2 AS
SELECT table_name, tablespace_name
FROM all_tables
WHERE table_name LIKE 'U%';
SELECT * FROM ctas2;
-- CTAS can be used to create a table as the result of any valid SQL statement
CREATE TABLE ctas3 AS
SELECT CAST(t.table_name, VARCHAR2(30)) AS TABLE_NAME, CAST(i.index_name, VARCHAR2(30)) AS INDEX_NAME
FROM user_tables t, user_indexes i
WHERE t.table_name = i.table_name;
-- the value inserted into the column if the insert or update would leave the column value NULL. In 12c this can include a sequence generated surrogate key
<column_name> [<data_type>] [GENERATED ALWAYS] AS (column_expression)
VIRTUAL (inline_constraint)
Note: "GENERATED ALWAYS" and "VIRTUAL" are optional keywords. For an example of a virtual column based on a deterministic function see the Evaluate Edition Clause above.
ALTER TABLE vcol
ADD CONSTRAINT cc_vcol_total_comp
CHECK (total_comp < 50001);
INSERT into vcol
(salary, bonus)
VALUES
(100, 10);
INSERT into vcol
(salary, bonus)
VALUES
(200, 12);
COMMIT;
INSERT into vcol
(salary, bonus)
VALUES
(50000, 1);
SELECT * FROM vcol;
EXPLAIN PLAN FOR
SELECT * FROM vcol;
SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR
SELECT /*+ RESULT_CACHE */ * FROM vcol;
SELECT * FROM TABLE(dbms_xplan.display);
ALTER TABLE vcol
ADD CONSTRAINT pk_vcol
PRIMARY KEY (salary)
USING INDEX;
CREATE INDEX ix_virtual_column
ON vcol(total_comp);
SELECT index_name, index_type
FROM user_indexes
WHERE table_name = 'VCOL';
col column_name format a30
SELECT column_name, column_position, column_length
FROM user_ind_columns
WHERE table_name = 'VCOL';
BEGIN
FOR i IN 1 .. 20000 LOOP
BEGIN
INSERT INTO vcol
(salary, bonus)
VALUES
(i, TRUNC(MOD(i, 5)));
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
COMMIT;
END;
/
ALTER TABLE c##sh.j_purchase_order ADD uprice NUMBER AS
(JSON_VALUE(po_document, '$.UnitPrice'));
SQL> desc c##sh.j_purchase_order
Name Null? Type
--------------------------------- -------- ----------------------------
DOC_ID NOT NULL RAW(16)
DATE_LOADED TIMESTAMP(6) WITH TIME ZONE
PO_DOCUMENT CLOB
UPRICE NUMBER
SELECT doc_id, date_loaded, uprice
FROM c##sh.j_purchase_order;
CREATE OR REPLACE FUNCTION evalValue(rid IN NUMBER) RETURN PLS_INTEGER AUTHID DEFINER DETERMINISTIC IS
BEGIN
RETURN TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),rid,1));
END evalValue;
/
SELECT evalValue FROM dual;
CREATE TABLE eec (
rid NUMBER,
virtcol NUMBER AS (evalValue(rid))
EVALUATE USING CURRENT EDITION);
col evaluation_edition format a20
SELECT table_name, column_name, evaluation_edition
FROM user_tab_cols
WHERE table_name = 'EEC';
Use the CACHE clauses to indicate how Oracle Database should store blocks in the buffer cache. If you specify neither CACHE nor NOCACHE, then:
In a CREATE TABLE statement, NOCACHE is the default
In an ALTER TABLE statement, the existing value is not changed.
CACHE
For data that is accessed frequently, this clause indicates that the
blocks retrieved for this table are placed at the most recently used end
of the least recently used (LRU) list in the buffer cache when a full
table scan is performed. This attribute is useful for small lookup tables.
You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior.
NOCACHE
For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the default for LOB storage.
As a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB value either is not brought into the buffer cache or is brought into the buffer cache and placed at the least recently used end of the LRU list. The latter is the default behavior.
Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
Specify NOPARALLEL, the default, for serial execution.
<PARALLEL | NOPARALLEL>
For this to be optimally effective the table should be distributed among multiple datafiles.
-- manipulate archiving with the DBMS_ILM package
UPDATE trowarch
SET ora_archive_state = dbms_ilm.archivestatename('1')
WHERE testcol <= TO_DATE('01-FEB-2017')
AND rownum < 11;
COMMIT;
SELECT ora_archive_state, COUNT(*)
FROM trowarch
GROUP BY ora_archive_state;
This clause lets you specify whether table will use row-level dependency tracking.
With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row. This setting cannot be changed after table creation.
CREATE OR REPLACE FUNCTION evalValue(rid IN NUMBER) RETURN PLS_INTEGER AUTHID DEFINER DETERMINISTIC IS
BEGIN
RETURN TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),rid,1));
END evalValue;
/
SELECT evalValue FROM dual;
CREATE TABLE uec1 (
rid NUMBER,
virtcol NUMBER AS (evalValue(rid))
UNUSABLE BEFORE EDITION ora$base);
col unusable_before format a20
col unusable_beginning format a20
SELECT table_name, column_name, unusable_before, unusable_beginning
FROM user_tab_cols
WHERE table_name LIKE 'UEC%';
CREATE OR REPLACE FUNCTION evalValue(rid IN NUMBER) RETURN PLS_INTEGER AUTHID DEFINER DETERMINISTIC IS
BEGIN
RETURN TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),rid,1));
END evalValue;
/
SELECT evalValue FROM dual;
CREATE TABLE uec2 (
rid NUMBER,
virtcol NUMBER AS (evalValue(rid))
UNUSABLE BEGINNING WITH CURRENT EDITION);
col unusable_before format a20
col unusable_beginning format a20
SELECT table_name, column_name, unusable_before, unusable_beginning
FROM user_tab_cols
WHERE table_name LIKE 'UEC%';
ALTER TABLE Statements
MOVE
ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace_location>;
SELECT segment_type, tablespace_name, blocks
FROM dba_extents
WHERE segment_name = 'MGMT_JOB_STATE_CHANGES';
ALTER TABLE sysman.mgmt_job_state_changes MOVE TABLESPACE sysaux;
SELECT segment_type, tablespace_name, blocks
FROM dba_extents
WHERE segment_name = 'MGMT_JOB_STATE_CHANGES';
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
ALTER TABLE allo_test ALLOCATE EXTENT;
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
ALTER TABLE allo_test ALLOCATE EXTENT
(SIZE 1M INSTANCE 1);
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
Deallocate Unused Space
ALTER TABLE <table_name> DEALLOCATE UNUSED [KEEP <integer>];
ALTER TABLE allo_test DEALLOCATE UNUSED;
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
Shrink Space
Valid only for segments in tablespaces with automatic segment management. Row movement must be enabled. COMPACT defragments the segment space and compacts the table rows for subsequent release.
COMPACT does not readjust the high water mark and does not release the space immediately. CASCADE performs the same operations on all dependent objects.
ALTER TABLE <table_name> SHRINK SPACE [COMPACT] [CASCADE];
SELECT bid, count(*)
FROM (
SELECT dbms_rowid.rowid_block_number(rowid) BID
FROM shrink_test)
GROUP BY bid;
BEGIN
FOR i IN 1..40000
LOOP
INSERT INTO shrink_test (rid, testcol)
VALUES (i, 'ABCDEFGHIJKLMNOPQRST');
END LOOP;
COMMIT;
END;
/
SELECT COUNT(*)
FROM shrink_test;
SELECT bid, count(*)
FROM (
SELECT dbms_rowid.rowid_block_number(rowid) BID
FROM shrink_test)
GROUP BY bid;
DELETE FROM shrink_test WHERE mod(rid, 2) = 0;
COMMIT;
SELECT COUNT(*)
FROM shrink_test;
SELECT bid, count(*)
FROM (
SELECT dbms_rowid.rowid_block_number(rowid) BID
FROM shrink_test)
GROUP BY bid;
ALTER TABLE shrink_test SHRINK SPACE COMPACT CASCADE;
SELECT bid, COUNT(*)
FROM (
SELECT dbms_rowid.rowid_block_number(rowid) BID
FROM shrink_test)
GROUP BY bid;
Control the Number of Records per Block for bitmap indexes
This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.
It cannot be specify MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table or if the table is empty.
ALTER TABLE <table_name> MINIMIZE RECORDS_PER_BLOCK;
ALTER TABLE test MINIMIZE RECORDS_PER_BLOCK;
Release Control on the Number of Records Per Block
ALTER TABLE <table_name> NOMINIMIZE RECORDS_PER_BLOCK;
DROP TABLE work_note_header CASCADE CONSTRAINTS PURGE;
Miscellaneous
Maximum number of columns
Oracle documentation states that a table can contain as many as 1000 columns. No sane person should ever build one 1/10th that size and certainly never with more than 255 columns. The truth is that no table really can contain 1000 columns.
Oracle does a bit of sleight of hand behind the scenes to make it appear that a single table contains the large number of columns and you and your application will pay a horrendous price in performance.
Here is another reason to severely limit the number of columns in any table.
* Oracle stores columns in variable length format
* Each row is parsed in order to retrieve one or more columns
* Each subsequently parsed column introduces a cost of 20 CPU cycles whether or not it is used
Slow table reads
The init parameter db_file_multiblock_read_count is paramount
