Implicitly created when optimizer considers it usefulĮffect of Creation on Existing TransactionsĭROP PRIVATE TEMPORARY TABLE, or implicitly dropped at end of session ( PRESERVE DEFINITION) or transaction ( DROP DEFINITION) Until table is explicitly dropped, or end of session ( PRESERVE DEFINITION) or transaction ( DROP DEFINITION)ĬREATE GLOBAL TEMPORARY TABLE (supports AS SELECT)ĬREATE PRIVATE TEMPORARY TABLE (supports AS SELECT) Session that created table (in USER_PRIVATE_TEMP_TABLES view, which is based on a V$ view) Memory or tempfiles, but only for the duration of the session or transaction Table 10-1 Important Characteristics of Temporary Tables Characteristic Note that the database allocates storage space when a session first inserts data into a global temporary table, not at table creation. The tables differ in where they store data, how they are created and dropped, and in the duration and visibility of metadata. Unlike global and private temporary tables, DBMS_STATS cannot gather statistics for cursor-duration temporary tables. The ON COMMIT clause of CREATE PRIVATE TEMPORARY TABLE indicates whether the table is transaction-specific ( DROP DEFINITION) or session-specific ( PRESERVE DEFINITION).Ī cursor-duration temporary table is an implicitly created memory-only object that is associated with a cursor. The table is private because the definition is visible only to the session that created the table. Optimizer statistics for global temporary tables can be shared or session-specific.Ī private temporary table is an explicitly created object, defined by private memory-only metadata, that stores intermediate session-private data for a specific duration. The ON COMMIT clause of CREATE GLOBAL TEMPORARY TABLE indicates whether the table is transaction-specific ( DELETE ROWS) or session-specific ( PRESERVE ROWS). The table is global because the definition is visible to all sessions. The tables differ as follows:Ī global temporary table is an explicitly created persistent object that stores intermediate session-private data for a specific duration. In all types of temporary table, the data is only visible to the session that inserts it. Temporary tables are classified as global, private, or cursor-duration. You can access these statistics using data dictionary views. Number of distinct values (NDV) in a columnĪs shown in Figure 10-1, the database stores optimizer statistics for tables, columns, indexes, and the system in the data dictionary. Optimizer statistics include the following: The optimizer estimates the cost for the access, determines the cost for possible plans, and then picks the execution plan with the lowest cost. The optimizer uses statistics to get an estimate of the number of rows (and number of bytes) retrieved from a table, partition, or index. The optimizer cost model relies on statistics collected about the objects involved in a query, and the database and host where the query runs.
0 kommentar(er)
