Oracle Table Segment Attribute Clauses
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose 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.
Table Types Links
Cluster One or more tables in a single database block
Compressed Compressed heap table
External External files readable as tables
Global Temporary Built in the TEMP tablespace the tables are permanent but the data is temporary. Choose either on commit delete rows or on commit preserve rows
Heap Default Oracle table type
Index Organized Table and primary key index stored as a single queryable B*Tree index
JSON Merger between a table and an index
Object Also known as Nested Tables. These are essentially table like objects nested within a column-row of an otherwise relational table
Partitioned Partition/Subpartitioned by hash, list, or range
Private Temporary Built in memory both the data and the tables are temporary. Choose either on commit delete rows or on commit preserve rows
Sharded Not added to the Library at this time
Shared With 12cR2 application containers metadata and data, from an application root container can be shared, by point, with an application PDB
XML Optimized for efficient storage and retrieval of XML documents
Dependencies
ALL_ALL_TABLES CDB_ZONEMAPS SEG$
ALL_CLUSTERING_TABLES SCLST$ TAB$
ALL_TABLES DBA_ALL_TABLES USER_ALL_TABLES
ALL_ZONEMAPS DBA_CLUSTERING_TABLES USER_CLUSTERING_TABLES
CDB_ALL_TABLES DBA_TABLES USER_TABLES
CDB_CLUSTERING_TABLES DBA_ZONEMAPS USER_ZONEMAPS
CDB_TABLES    
Heap Table and Column Clauses Guide
Alter Table ALTER TABLE commands are used to modify table properties after the table has been created
Attribute Clustering Specify how to cluster data in close physical proximity based on column contents.
Cache Specify how the database should store blocks in the buffer cache.
Cluster Identifies the cluster mapped from a tablespace in which the table's segment will be stored.
Comment Tables and Columns
Commands write comments into the data dictionary.
Compression Tablespace, Basic, Advanced, Hybrid Columnar, and LOB Compression reduce storage space and can improve I/O performance.
Constraints Follow the links at page bottom for relational and object constraints.
Deferred Segment Creation The initial table segment is created upon INSERT of the first row.
Drop Table DROP TABLE commands are used to drop into the recyclebin or purge an existing table
Encryption Available encryption algorithms, for single columns or all columns, include 3DES168, AES128, AES192, and AES256.
Evaluation Edition Use this clause to specify the edition that is searched during name resolution of the editioned PL/SQL function—the evaluation edition
Flashback Archive 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.
Identity Columns Identity columns are columns, usually surrogate keys, that are autonumbered by an automatically generated sequence object.
ILM Policy Use this clause to define an Integrated Lifecycle Management policy for Automatic Data Optimization.
Indexing The indexing property determines whether table partitions and subpartitions are included in partial indexes on the table.
Invisible/Visible Column Specifies whether a column will be visible during a describe operation.
In-Memory Specifies a tables characteristics if used with In-Memory options.
Logging 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.
Null / Not Null Clause Allows columns to be specfied as nullable or not nullable.
Parallel 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 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.
Period Definition Use the PERIOD_DEFINITION clause to create a valid time dimension for table. This clause implements Temporal Validity support for table.
Physical Attributes Clauses 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.
Read Only Tables and partitions can be made read only to minimize data corruption and reduce backup size.
Result Cache Defines whether the table uses the DEFAULT or FORCE mode with respect to participation in the Result Cache independent of the Result Cache Hint.
Row Archival 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.
Row Dependencies Enable or disable row-level dependency tracking: Primary for parallel propagation in replication environments.
Row Movement Specifies whether the database can move a table row.
Sharing 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.
Storage Specify a table physical storage characteristics.
Supplemental Logging The clause lets you instruct the database to put additional data into the log stream to support log-based replication tools.
Table Lock Prevent and re-enable DDL on a table.
Tablespace Identifies the tablespace logical mapping of one or more datafiles in which the table's segment will be stored.
Tablespace Set Only valid for sharded tables.
Tiering 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.
Unusable Editions 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.
Virtual Column A non-stored column resulting from an explicit calculation possibly using a deterministic function.
System Privileges
ALTER ANY TABLE CREATE TABLE DROP ANY TABLE
CREATE ANY TABLE    
 
Segment Attributes
Segment Attribute clause example SQL> SELECT dbms_metadata.get_ddl('TABLE','AIRPLANES') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','AIRPLANES')
--------------------------------------------------------------------------------
CREATE TABLE "UWCLASS"."AIRPLANES" (
"PROGRAM_ID"     VARCHAR2(3),
"LINE_NUMBER"    NUMBER(10,0),
"CUSTOMER_ID"    VARCHAR2(4),
"ORDER_DATE"     DATE,
"DELIVERED_DATE" DATE,
CONSTRAINT "PK_AIRPLANES" PRIMARY KEY ("PROGRAM_ID", "LINE_NUMBER") USING INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "UWDATA" ENABLE)
SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UWDATA"
Physical Attributes Clause See Physical Attribute Clause Below
TABLESPACE TABLESPACE <tablespace_name>
conn uwclass/uwclass@pdbdev

SELECT tablespace_name, max_bytes, max_blocks
FROM user_ts_quotas;

CREATE TABLE td (
testcol NUMBER(5))
TABLESPACE uwdata;

CREATE TABLE tu (
testcol NUMBER(5))
TABLESPACE users;

SELECT table_name, tablespace_name
FROM user_tables
WHERE table_name IN ('TD', 'TU');

CREATE TABLE ctas
TABLESPACE uwdata AS
SELECT * FROM all_all_tables;
Tablespace Set TABLESPACE SET <tablespace_set_name>
This syntax statement is valid only if you are using Oracle Sharding
Logging Clause <LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING>
conn uwclass/uwclass@pdbdev

CREATE TABLE tlog (
testcol NUMBER(5))
LOGGING;

CREATE TABLE tnolog (
testcol NUMBER(5))
NOLOGGING;

CREATE TABLE fslikelog (
testcol NUMBER(5),
blobcol BLOB)
LOB (blobcol) STORE AS blobseg (TABLESPACE uwdata STORAGE (INITIAL 1M)
CHUNK 4096
NOCACHE FILESYSTEM_LIKE_LOGGING)
TABLESPACE uwdata;

SELECT table_name, logging
FROM dba_tables
WHERE table_name IN ('TLOG', 'TNOLOG', 'FSLIKELOG');

TABLE_NAME  LOG
----------- ---
TNOLOG      NO
TLOG        YES
FSLIKELOG   YES
 
Physical Attributes
Deferred Segment Creation

aka: Segment Creation On Demand
Deferred creation defers creating the segment until an insert statement is executed.

SEGMENT CREATION <IMMEDIATE | DEFERRED>
CREATE TABLE uwclass.now(
col1 NUMBER,
col2 VARCHAR2(20))
SEGMENT CREATION IMMEDIATE;

CREATE TABLE uwclass.later(
col1 NUMBER,
col2 VARCHAR2(20))
SEGMENT CREATION DEFERRED;

SELECT table_name, tablespace_name, segment_created
FROM user_tables
WHERE table_name IN ('LATER', 'NOW');

SELECT segment_name, tablespace_name
FROM user_segments
WHERE segment_name IN ('LATER', 'NOW');

INSERT INTO now (col1, col2) VALUES (1, 'A');
INSERT INTO later (col1, col2) VALUES (1, 'B');

SELECT segment_name, tablespace_name
FROM user_segments
WHERE segment_name IN ('LATER', 'NOW');
INITRANS Specifies the number of DML transaction entries for which space is initially reserved in the data block header. Space is reserved in the headers of all data blocks in the associated segment.

Oracle uses control information stored in the data block to indicates which rows in the block contain committed and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE and ALTER TABLE.

Under some circumstances, Oracle can have insufficient history information to determine whether a row has been updated by a "too recent" transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block.


INITRANS <1 | integer>
SELECT table_name, ini_trans
FROM user_tables;
MAXTRANS In earlier releases, the MAXTRANS parameter determined the maximum number of concurrent update transactions allowed for each data block in the segment. This parameter has been deprecated. Oracle now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

Existing objects for which a value of MAXTRANS has already been set retain that setting. However, if you attempt to change the value for MAXTRANS, Oracle ignores the new specification and substitutes the value 255 without returning an error.

MAXTRANS <255 | integer>
Deprecated and ignored by the database
PCTFREE Determines when a used block is removed from the list of available blocks. When a block is removed from the list ... no more data is written to it so that when records are updated there is room for the data in the block ... thus no chained rows.

Tables on which there are no updates should have PCTFREE set to 0. The default value of 10 leaves 90% of each block empty.


PCTFREE <10 | integer>
SELECT table_name, pct_free
FROM user_all_tables;
PCTUSED Determines when a used block is re-added to the list of available blocks. When deletes take place and the room available in a block falls below this value ... the block is made available for new inserts to take place.

Tables on which there are no updates should have PCTUSED set to 99. The default value is 40% which means that blocks are available for insertion when they are less than 40% full.


PCTUSED <40 | integer>
SELECT table_name, pct_used
FROM user_all_tables;
 
Storage Clause
BUFFER POOL Defines a default buffer pool (cache) for a schema object.

STORAGE (buffer pool <KEEP | RECYCLE | DEFAULT>)
CREATE TABLE buffer_test (
testcol  VARCHAR2(20))
STORAGE (buffer_pool DEFAULT);

SELECT table_name, buffer_pool
FROM user_tables;

ALTER TABLE buffer_test STORAGE (buffer_pool RECYCLE);

SELECT table_name, buffer_pool
FROM user_tables;

ALTER TABLE buffer_test STORAGE (buffer_pool KEEP);

SELECT table_name, buffer_pool
FROM user_tables;


-- for additional information click on the Buffer Pools link at page bottom
CELL_FLASH_CACHE

This is an Exadata specific syntax
STORAGE (cell_flash_cache <KEEP | NONE | DEFAULT>)
SELECT table_name, cell_flash_cache
FROM dba_tables;

CREATE TABLE  cfctab (
testcol NUMBER(5))
STORAGE (cell_flash_cache KEEP);
ENCRYPT -- valid algorithms are 3DES168, AES128, AES192, and AES256

USING '<encryption_algorithm>' [IDENTIFIED BY <password>] ['<integrity_algorithm>' [[NO] SALT]
Follow the Transparent Data Encryption link at page bottom
FLASH_CACHE Allows an override of the automatic buffer cache policy and specification of how specific schema objects are cached in flash memory

STORAGE (flash_cache <KEEP | NONE | DEFAULT>)
CREATE TABLE fcache(
testcol VARCHAR2(20))
STORAGE (FLASH_CACHE DEFAULT);
FREELISTS The number of lists maintained on a table that can be used to identify a block available for insert. Set this to 1 on all tables except those receiving very large numbers of simultaneous inserts. When a process requests a free list, it uses a 'hashing' function to select which free list based on the process id. Using a prime number with such mechanisms usually reduces the number of collisions that occur if the input is randomly distributed. Therefore, if you need more than one free list make the number of lists a prime number (for example 1, 2, 3, 5, 7, 11, 13, 17, 19, 23, .... for optimal performance).

Oracle ignores a setting of FREELISTS if the tablespace in which the object resides is in automatic segment-space management mode.


FREELISTS <1 | integer>
SELECT table_name, freelists
FROM user_all_tables;
FREELIST GROUPS The number of groups of free lists for the database object you are creating. The database uses the instance number of Oracle Real Application Cluster instances to map each instance to one free list group.

This parameter is ignored for objects created in locally managed tablespaces with segment space management specified as AUTO.


FREELIST GROUPS <1 | integer>
SELECT table_name, freelist_groups
FROM user_all_tables;
INITIAL In a tablespace that is specified as EXTENT MANAGEMENT LOCAL. The database uses the value of INITIAL in conjunction with the extent size for the tablespace to determine the initial amount of space to reserve for the object. For example, in a uniform locally managed tablespace with 5M extents, if you specify an INITIAL value of 1M, then the database must allocate one 5M extent, because that is the uniform size of extents for the tablespace. If the extent size of the tablespace is smaller than the value of INITIAL, then the initial amount of space allocated will in fact be more than one extent.

INITIAL <integer> <K | M | G | T | P | E>
SELECT tablespace_name, initial_extent
FROM user_tablespaces

SELECT table_name, tablespace_name, initial_extent
FROM user_tables;
NEXT Not meaningful for objects created in a tablespace that is specified as EXTENT MANAGEMENT LOCAL because the database automatically manages extents.

NEXT <integer> <K | M | G | T | P | E>
SELECT tablespace_name, next_extent
FROM user_tablespaces

SELECT table_name, tablespace_name, next_extent
FROM user_all_tables;
MAXEXTENTS The maximum number of extents that can be allocated to the segment.

MAXEXTENTS <integer>
SELECT table_name, max_extents
FROM user_all_tables;
MINEXTENTS The total number of extents to be allocated when the segment is created. This allows for a large allocation of space at creation time, even if contiguous space is not available.

In a tablespace that is specified as EXTENT MANAGEMENT LOCAL, MINEXTENTS is used only to compute the initial amount of space that is allocated. The initial amount of space that is allocated and is equal to INITIAL * MINEXTENTS. Thereafter it is set to 1 for these tablespaces (as seen in the DBA_SEGMENTS view).


MINEXTENTS <integer>
SELECT table_name, min_extents
FROM user_all_tables;
MAXSIZE Defines the maximum size of a table in bytes.

MAXSIZE <integer <K | M | G | T | P | E> | UNLIMITED>
CREATE TABLE msize(
testcol VARCHAR2(20))
STORAGE(MAXSIZE 10G);
OPTIMAL STORAGE (optimal <size_clause | NULL>)
The OPTIMAL keyword is relevant only to rollback segments. It specifies an optimal size in bytes for a rollback segment.

Oracle tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle deallocates as many extents as possible without reducing the total size of the rollback segment below the OPTIMAL value.

The value of OPTIMAL cannot be less than the space initially allocated by the MINEXTENTS, INITIAL, NEXT, and PCTINCREASE parameters. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size.
PCTINCREASE The default is zero and it should be left that way. Using alternate values back in Oracle 8.0 and before was always a bad idea but unfortunately back then that didn't stop a lot of people from setting other values.

PCTFREE <0 | integer>
SELECT table_name, pct_increase
FROM user_tables;
 
Table Options
Attribute Clustering CLUSTERING BY <linear | interleaved> ORDER <clustering_columns>;
CREATE TABLE sales_ac (sale_id NUMBER(10), customer_id NUMBER(10))
CLUSTERING BY LINEAR ORDER (customer_id);

col last_load_clst format a9
col last_datamove_clst format a13

SELECT *
FROM dba_clustering_tables;

OWNER    TABLE_NAME  CLUSTERING_ ON_ ON_ VAL WIT LAST_LOAD LAST_DATAMOVE
-------- ----------- ----------- --- --- --- --- --------- -------------
UWCLASS  SALES_AC    LINEAR      YES YES YES NO
Attribute Clustering with Clustering Join CLUSTERING <schema_name>.<table_name> [JOIN <schema_name>.<table_name> ON (<equijoin_condition>)[,...])
BY <linear | interleaved> ORDER <clustering_columns>;
CREATE TABLE sales_header (sale_id NUMBER(10), sale_date date);

ALTER TABLE sales_header
ADD CONSTRAINT pk_sales_header
PRIMARY KEY (sale_id);

CREATE TABLE sales_detail (sale_id NUMBER(10), customer_id NUMBER(10))
CLUSTERING
sales_header JOIN uwclass.sales_header ON (sales_detail.sale_id = sales_header.sale_id)
BY LINEAR ORDER (customer_id);

SELECT *
FROM dba_clustering_tables;

OWNER    TABLE_NAME    CLUSTERING_ ON_ ON_ VAL WIT LAST_LOAD LAST_DATAMOVE
-------- ------------- ----------- --- --- --- --- --------- -------------
UWCLASS  SALES_DETAIL  LINEAR      YES YES YES NO

col tab1_owner  format a10
col tab1_name   format a12
col tab1_column format a11
col tab2_owner  format a10
col tab2_name   format a12
col tab2_column format a11

SELECT *
FROM dba_clustering_joins;

OWNER   TABLE_NAME   TAB1_OWNER TAB1_NAME    TAB1_COLUMN TAB2_OWNER TAB2_NAME    TAB2_COLUMN
------- ------------ ---------- ------------ ----------- ---------- ------------ -----------
UWCLASS SALES_DETAIL UWCLASS    SALES_DETAIL SALE_ID     UWCLASS    SALES_HEADER SALE_ID

col detail_owner format a10
col detail_name format a12
col detail_column format a12

SELECT *
FROM dba_clustering_keys;

OWNER   TABLE_NAME   DETAIL_OWN DETAIL_NAME  DETAIL_COLUM POSITION GROUPID
------- ------------ ---------- ------------ ------------ -------- --------
UWCLASS SALES_DETAIL UWCLASS    SALES_DETAIL CUSTOMER_ID         1       0
Attribute Clustering with Clustering When CLUSTERING BY <linear | interleaved> ORDER <clustering_columns>
[<YES | NO> ON LOAD] [<YES | NO> ON DATA MOVEMENT];
CREATE TABLE sales_when (sale_id NUMBER(10), customer_id NUMBER(10))
CLUSTERING BY LINEAR ORDER (customer_id)
YES ON LOAD YES ON DATA MOVEMENT
;

SELECT *
FROM dba_clustering_tables;

OWNER    TABLE_NAME    CLUSTERING_ ON_ ON_ VAL WIT LAST_LOAD LAST_DATAMOVE
-------- ------------- ----------- --- --- --- --- --------- -------------
UWCLASS  SALES_WHEN    LINEAR      YES YES YES NO

SELECT *
FROM dba_clustering_keys;

OWNER   TABLE_NAME   DETAIL_OWN DETAIL_NAME  DETAIL_COLUM POSITION GROUPID
------- ------------ ---------- ------------ ------------ -------- --------
UWCLASS SALES_WHEN   UWCLASS    SALES_WHEN   CUSTOMER_ID         1       0
Attribute Clustering with Zonemap Clause CLUSTERING BY <linear | interleaved> ORDER <clustering_columns>
[<WITH | WITHOUT> MATERIALIZED ZONEMAP]
CREATE TABLE sales_ac (sale_id NUMBER(10), customer_id NUMBER(10));

INSERT INTO sales_ac
SELECT ROWNUM, MOD(ROWNUM,1000)
FROM dual
CONNECT BY LEVEL <= 100000;

CREATE INDEX sales_ac_cix ON sales_ac (customer_id);

exec dbms_stats.gather_table_stats(USER, 'SALES_AC');

SET AUTOTRACE ON

SELECT /*+ INDEX(sales_ac sales_ac_cix) */ COUNT(DISTINCT sale_id)
FROM sales_ac
WHERE customer_id = 50;

ALTER TABLE sales_ac
ADD CLUSTERING BY LINEAR ORDER (customer_id)
WITHOUT MATERIALIZED ZONEMAP;


ALTER TABLE sales_ac MOVE;

ALTER INDEX sales_ac_cix REBUILD;

exec dbms_stats.gather_table_stats(USER, 'SALES_AC');

SELECT /*+ INDEX(sales_ac sales_ac_cix) */ COUNT(DISTINCT sale_id)
FROM sales_ac
WHERE customer_id = 50;

SET AUTOTRACE OFF
======================================================================================================
CREATE TABLE sales_ac (sale_id NUMBER(10), customer_id NUMBER(10));

Table created.

INSERT INTO sales_ac
SELECT ROWNUM, MOD(ROWNUM,1000)
FROM dual
CONNECT BY LEVEL <= 100000;

100000 rows created.

SQL> CREATE INDEX sales_ac_cix ON sales_ac (customer_id);
Index created.

exec dbms_stats.gather_table_stats(USER, 'SALES_AC');
PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE ON

SELECT /*+ INDEX(sales_ac sales_ac_cix) */ COUNT(DISTINCT sale_id)
FROM sales_ac
WHERE customer_id = 50;

COUNT(DISTINCTSALE_ID)
----------------------
100

Execution Plan
----------------------------------------------------------
Plan hash value: 1568061387

-----------------------------------------------------------------------------------------------------
| Id | Operation                              | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                       |              |    1 |   13 |      102 (1)| 00:00:01 |
|  1 |  SORT AGGREGATE                        |              |    1 |   13 |             |          |
|  2 |   VIEW                                 | VW_DAG_0     |  100 | 1300 |      102 (1)| 00:00:01 |
|  3 |    HASH GROUP BY                       |              |  100 |  900 |      102 (1)| 00:00:01 |
|  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| SALES_AC     |  100 |  900 |      101 (0)| 00:00:01 |
|* 5 |      INDEX RANGE SCAN                  | SALES_AC_CIX |  100 |      |        1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CUSTOMER_ID"=50)

Statistics
----------------------------------------------------------
9 recursive calls
6 db block gets
104 consistent gets
0 physical reads
1012 redo size
556 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> /

COUNT(DISTINCTSALE_ID)
----------------------
100

Execution Plan
----------------------------------------------------------
Plan hash value: 1568061387

-----------------------------------------------------------------------------------------------------
| Id | Operation                              | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                       |              |    1 |    13 |     102 (1)| 00:00:01 |
|  1 |  SORT AGGREGATE                        |              |    1 |    13 |            | |
|  2 |   VIEW                                 | VW_DAG_0     |  100 |  1300 |     102 (1)| 00:00:01 |
|  3 |    HASH GROUP BY                       |              |  100 |   900 |     102 (1)| 00:00:01 |
|  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| SALES_AC     |  100 |   900 |     101 (0)| 00:00:01 |
|* 5 |      INDEX RANGE SCAN                  | SALES_AC_CIX |  100 |       |       1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CUSTOMER_ID"=50)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        102 consistent gets
          0 physical reads
          0 redo size
        556 bytes sent via SQL*Net to client
        607 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> ALTER TABLE sales_ac
     ADD CLUSTERING BY LINEAR ORDER (customer_id)
     WITHOUT MATERIALIZED ZONEMAP;
Table altered.

SELECT *
FROM dba_zonemaps;

SQL> ALTER TABLE sales_ac MOVE;
Table altered.

SQL> ALTER INDEX sales_ac_cix REBUILD;
Index altered.

exec dbms_stats.gather_table_stats(USER, 'SALES_AC');
PL/SQL procedure successfully completed.

SQL> SELECT /*+ INDEX(sales_ac sales_ac_cix) */ COUNT(DISTINCT sale_id)
FROM sales_ac
WHERE customer_id = 50; 2 3

COUNT(DISTINCTSALE_ID)
----------------------
100

Execution Plan
----------------------------------------------------------
Plan hash value: 1568061387

-----------------------------------------------------------------------------------------------------
| Id | Operation                              | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                       |              |    1 |    13 |      3 (34)| 00:00:01 |
|  1 |  SORT AGGREGATE                        |              |    1 |    13 |            |          |
|  2 |   VIEW                                 | VW_DAG_0     |  100 |  1300 |      3 (34)| 00:00:01 |
|  3 |    HASH GROUP BY                       |              |  100 |   900 |      3 (34)| 00:00:01 |
|  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| SALES_AC     |  100 |   900 |      2  (0)| 00:00:01 |
|* 5 |      INDEX RANGE SCAN                  | SALES_AC_CIX |  100 |       |      1  (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CUSTOMER_ID"=50)

Statistics
----------------------------------------------------------
          5 recursive calls
          0 db block gets
          7 consistent gets
          0 physical reads
          0 redo size
        556 bytes sent via SQL*Net to client
        607 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> /

COUNT(DISTINCTSALE_ID)
----------------------
100

Execution Plan
----------------------------------------------------------
Plan hash value: 1568061387

----------------------------------------------------------------------------------------------------
| Id | Operation                             | Name         | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                      |              |    1 |    13 |      3 (34)| 00:00:01 |
|  1 |  SORT AGGREGATE                       |              |    1 |    13 |            |          |
|  2 |   VIEW                                | VW_DAG_0     |  100 |  1300 |      3 (34)| 00:00:01 |
|  3 |    HASH GROUP BY                      |              |  100 |   900 |      3 (34)| 00:00:01 |
|  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| SALES_AC     |  100 |   900 |      2  (0)| 00:00:01 |
|* 5 |     INDEX RANGE SCAN                  | SALES_AC_CIX |  100 |       |      1  (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CUSTOMER_ID"=50)

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        556 bytes sent via SQL*Net to client
        607 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
Evaluation Edition Clause -- to use this clause the schema owner must be editions enabled and the function used to specify the virtual column editioned.

EVALUATE USING <CURRENT EDITION | EDITION <edition_name> | NULL EDITION>;
conn uwclass/uwclass@pdbdev

show edition

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';
LOGGING Specify whether the creation of the table and of any indexes required because of constraints, partition, or LOB storage characteristics will be logged in the redo log file (LOGGING) or not (NOLOGGING). The logging attribute of the table is independent of that of its indexes.

This attribute also specifies whether subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged (LOGGING) or not logged (NOLOGGING).


<LOGGING | NOLOGGING>
CREATE TABLE logging_test (
testcol  VARCHAR2(20))
LOGGING;

SELECT table_name, logging
FROM user_all_tables;

ALTER TABLE logging_test NOLOGGING;

SELECT table_name, logging
FROM user_all_tables;
TABLESPACE LOGGING TABLESPACE <tablespace_name> <LOGGING | NOLOGGING>;
CREATE TABLE force_logging (
rid   NUMBER,
lname VARCHAR2(30))
TABLESPACE uwdata LOGGING;
PARALLEL 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.
CREATE TABLE parallel_test (
testcol VARCHAR2(20))
PARALLEL 2;

SELECT table_name, degree
FROM user_tables;

set autotrace traceonly explain

SELECT * FROM parallel_test;

set autotrace off

ALTER TABLE parallel_test PARALLEL 4;

SELECT table_name, degree
FROM user_tables;

set autotrace traceonly explain

SELECT * FROM parallel_test;

CREATE TABLE parallel_test (
testcol VARCHAR2(20))
PARALLEL;

SELECT table_name, degree
FROM user_tables;
ROW MOVEMENT The row_movement_clause specifies whether the database can move a table row. It is possible for a row to move, for example, during table compression or an update operation on partitioned data. The default is to disable row movement.

<ENABLE | DISABLE> ROW MOVEMENT
CREATE TABLE rowmove_test (
testcol  VARCHAR2(20))
ENABLE ROW MOVEMENT;

SELECT table_name, row_movement
FROM user_tables;

ALTER TABLE rowmove_test DISABLE ROW MOVEMENT;

SELECT table_name, row_movement
FROM user_tables;
 
Segment Attribute ALTER TABLE Statements
MOVE TABLESPACE The name of the tablespace where the table will be built. The table may exist in one or more the the datafiles mapped to the tablespace.

ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace_location>;
CREATE TABLE tbsp_test (
testcol VARCHAR2(20))
TABLESPACE users;

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY 1;

ALTER TABLE tbsp_test MOVE TABLESPACE uwdata;

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY 1;
Force Extent Allocation ALTER TABLE <table_name> ALLOCATE EXTENT;
ALLOCATE EXTENT [(
{ SIZE size_clause | DATAFILE 'filename' | INSTANCE integer }
[ SIZE size_clause | DATAFILE 'filename' | INSTANCE integer ]...)];
CREATE TABLE allo_test (
testcol VARCHAR2(20));

col segment_name format a30

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];
CREATE TABLE shrink_test (
rid      NUMBER(5),
testcol  VARCHAR2(20))
ENABLE ROW MOVEMENT;

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;
ALTER TABLE test NOMINIMIZE RECORDS_PER_BLOCK;
 
Table Related Queries
How much space is a table taking in the tablespace? SELECT SUBSTR(s.segment_name,1,20) TABLE_NAME,
SUBSTR(s.tablespace_name,1,20) TABLESPACE_NAME,
ROUND(DECODE(s.extents, 1, s.initial_extent,
(s.initial_extent + (s.extents-1) * s.next_extent))/1024000,2) ALLOCATED_MB,
ROUND((t.num_rows * t.avg_row_len / 1024000),2) REQUIRED_MB
FROM dba_segments s, dba_tables t
WHERE s.owner = t.owner
AND s.segment_name = t.table_name
ORDER BY s.segment_name;
Another space usage query SELECT owner, table_name, NVL(num_rows*avg_row_len,0)/1024000 MB
FROM dba_tables
ORDER BY owner, table_name;

Related Topics
Analyze
Buffer Pools
Cache Reads
Clusters
Constraints
Data Types & Subtypes
DBMS_REDEFINITION
DBMS_RESULT_CACHE
DBMS_ROWID
DBMS_SHARED_POOL
DBMS_STATS
DDL Statements
External Tables
Flashback Archive
Global Temporary Tables
Identity Columns
Indexes
In-Memory Tables
IOT (Index Organized Tables)
LOBs
Nested Tables
Object Privileges
Object Table Constraints
Object Tables
ORA_ROWSCN
Partitioned Tables
Pseudocolumns
Recycle Bin
Result Cache
Supplemental Logging
Table Flashback
Temporal Validity
Transparent Data Encryption
Triggers
Truncate
XMLType Tables

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved