Comparison of Oracle temporary table usage

Comparison of Oracle temporary table usage temporary tables is a common means of complex SQL Performance optimization. Www.2cto.com 1) Global Temporary sentences can be divided into sessions and transactions. Session-level Temporary Table [syntax] Create Global Temporary Table Table_Name (the aggregation SQL statement) On Commit Preserve Rows; [features] after the Temporary Table data is automatically cleared, however, the structure and metadata of the temporary table are stored in the user's data dictionary. The table definition is visible to all sessions. DML locks are not required for temporary tables. Www.2cto.com can index temporary tables and create views based on temporary tables. The index on the temporary table is also temporary and only valid for the current session or transaction. Temporary tables can have triggers. You can use the export and import tools to import and export definitions of temporary tables, but cannot export data. [Applicable] When an SQL statement is associated with multiple tables and small tables. You can split a large table and store a small result set in a temporary table. Temporary data used multiple times during program execution, which is required throughout the program session. [Clearing Point] When a SESSION exits, data is automatically cleared (table structure retained) [insufficient] lob objects are not supported. This may be based on the consideration of the operation efficiency, however, temporary tables cannot be used when this function is required in practical applications. The primary/foreign key relationship is not supported. Example of www.2cto.com: create global temporary table my_temporary (birthdate DATE, enddate DATE, name CHAR (20) on commit preserve rows; transaction-level Temporary Table [syntax] Create Global Temporary Table Table_Name (the aggregation SQL statement) On Commit Delete Rows; [Clearing Point] automatically clears data after executing COMMIT (Table structure retained) other aspects are the same as session-level temporary tables and are not listed. 2) The with statement is not a temporary table, but is very similar to the use of a temporary table. [Syntax] WITH subquery_name AS (the aggregation SQL statement) SELECT (query naming subquery_name); [feature] it is attached to an SQL statement and only works for one SQL statement. You can generate several temporary tables at the same time. Extremely short and easy to manage. [Application] Global Temporary applies to all applications. It is only applicable to a single SQL scenario that facilitates the integration of constants or set calculation results, which can simplify SQL. [Clear point] immediately after the query is completed (data and table structure ). Example: WITH sum_sales AS (select/* + materialize */sum (quantity) all_sales from stores), number_stores AS (select/* + materialize */count (*) nbr_stores from stores), sales_by_store AS (select/* + materialize */store_name, sum (quantity) store_sales from store natural join sales) SELECT store_name FROM store, sum_sales, number_stores, sales_by_store where store_sales> (all_sales/nbr_stores) (Note: Use/* + materialize */to allow Oracle to generate temporary tables based on the cost-based (cost-based) optimization policy.)