CREATE TABLE AS SELECT(CAST)(二)-Concept
一、Temporary Segments Concept
Oracle often requires temporary work space for intermediate stages of database processing. There are different kinds of temporary segments in the database.
Some of them are created explicitly by the users. The others are created and accessed for the user by the system.
There are SQL operations containing a sorting step which require temporary segments. However, segments used for sorting are not the only segments having SEGMENT_TYPE=TEMPORARY. Temporary segments can also exist for permanent segments creation.
Temporary segments for sorting are created in the default temporary tablespace of the user. This tablespace may be of type TEMPORARY or PERMANENT.
(1) A TEMPORARY tablespace (Locally Managed Tablespace) is recommended for sort operations.
(2) Temporary segments for permanent segments creation are created in the tablespace specified in the create statement or in the user’s default tablespace.
a. Temporary Tables
only exists during a transaction or session.
DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated.
b. Temporary LOBs
The goal of temporary LOBs is to develop an interface to support the creation and deletion of lobs that act like local variables.
c. Temporary Segments as work area for sorting
When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement execution.
The sort area is allocated in memory. If the sort operation needs additional memory (above the value specified by the SORT_AREA_SIZE parameter), the sorted rows are written to disk to free up the sort area so that it can be re-used for the remaining sort.
Oracle automatically allocates this disk space called a temporary segment.
The following statements may require the use of a temporary segment for sorting:
CREATE INDEX/SELECT ... ORDER BY/SELECT DISTINCT/SELECT ... GROUP BY/SELECT ... UNION/SELECT ... INTERSECT/SELECT ... MINUS/ANALYZE TABLE/Unindexed joins/Correlated subqueries
d. Temporary Segments for permanent segments creation
Besides sort operations, there are other SQL operations, which also require temporary segments:
CREATE PRIMARY/UNIQUE KEY CONSTRAINT
ALTER TABLE ... ENABLE PRIMARY/UNIQUE CONSTRAINT
CREATE TABLE STORAGE (MINEXTENTS>1)
CREATE TABLE AS SELECT
The CTAS creates a data segment in the target tablespace and marks this segment as temporary in dictionary.
On completion, the dictionary type is changed from temporary to table. In addition, if the SELECT performs a SORT operation, temporary space may be used as for a standard select.
CREATE PARTITION TABLE
ALTER TABLE ... SPLIT PARTITION
CREATE SNAPSHOT
CREATE INDEX
The CREATE INDEX statement, after sorting the index values, builds a temporary segment in the INDEX tablespace;
once the index is completely built, the segment type is changed to INDEX.
DROP TABLE
e. Temporary Tablespaces
二、Introduction to Direct-Path INSERT
1. Conventional insert operations:
Oracle reuses free space in the table, interleaving newly inserted data with existing data. During such operations, Oracle also maintains referential integrity constraints.
2. Direct-path INSERT operations:
Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.
a. During direct-path INSERT, you can disable the logging of redo and undo entries
b. CREATE TABLE ... AS SELECT statement, does not have any indexes defined on it and not null constraint; you must define them later.
Note:If the database or tablespace is in FORCE LOGGING mode, then direct path INSERT always logs, regardless of the logging or nologging setting, such as STANDBY database.
參考文獻:
1. Different Kinds of Temporary Segments [ID 181132.1]
2.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9252210/viewspace-660173/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- CREATE TABLE AS SELECT(CAST)(一)-ORA-1652AST
- Oracle Create Table as SelectOracle
- sqlserver不能直接create table as select ......SQLServer
- insert into select 與 create table as的用法和區別(轉)
- 【SQL】14 UNION 操作符、SELECT INTO 語句、INSERT INTO SELECT 語句、CREATE DATABASE 語句、CREATE TABLE 語句SQLDatabase
- Oracle Temp Table ConceptOracle
- create table 使用select查詢語句建立表的方法分享
- oracle 特殊SQL(TABLE( CAST( MULTISET()[zt]OracleSQLAST
- ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECTError
- create table進階學習(二)_全域性臨時表_global temporary table
- create table of mysql databaseMySqlDatabase
- Oracle table selectOracle
- create a partition table using a exsit table
- create table as select where 1=0會把null和not null屬性也帶上Null
- create table進階學習系列(十一)之cluster_續(二)
- create table if not exists Waiting for table metadata lockAI
- MySQL5.6 create table原理分析MySql
- create table進階學習(一)
- create table進階學習(三)
- create table進階學習(四)
- Key-preserved table concept in join view (Ask Tom)View
- 資料庫CONCEPT (二)資料庫
- Oracle 19c Concepts(02):Tables and Table ClustersOracle
- MySQL的create table as 與 like區別MySql
- VBA從Excel中生成Oracle create tableExcelOracle
- create table進階學習系列(八)
- create table進階學習系列(五)
- create table進階學習系列(七)
- create table進階學習系列(九)
- use azure data studio to create external table for oracleOracle
- create table之storage選項initial和其它
- T-SQL的CREATE TABLE語法(下)SQL
- T-SQL的CREATE TABLE語法(上)SQL
- oracle 19c 無法create table解決Oracle
- How to Quickly Create a Copy of a Table using Transact-SQLUISQL
- dual表與create table語句的關係
- NOARCHIVE模式CREATE TABLE不記錄日誌呀Hive模式
- create table進階學習系列(十一)之cluster