This shows you the differences between two versions of the page.
temporary-tables [2015/09/23 20:27] 127.0.0.1 external edit |
temporary-tables [2016/04/20 07:50] (current) dmtolpeko |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== Native and Managed Temporary Tables - PL/HQL ====== | + | ====== Native and Managed Temporary Tables ====== |
- | PL/HQL provides you with two options to work with temporary tables: native and managed. | + | HPL/SQL provides you with two options to work with temporary tables: native and managed. |
- | Use the [[configuration#plhqltemptables|plhql.temp.tables]] option to define how to handle temporary tables, the default value is **native**. | + | Use the [[configuration#hplsqltemptables|hplsql.temp.tables]] option to define how to handle temporary tables, the default value is **native**. |
===== Native Temporary Tables ===== | ===== Native Temporary Tables ===== | ||
- | When native temporary tables are used PL/HQL relies on the underlying database to manage temporary tables. | + | When native temporary tables are used HPL/SQL relies on the underlying database to manage temporary tables. |
- | PL/HQL converts DECLARE TEMPORARY TABLE statement to CREATE TEMPORARY TABLE in Hive. Note that Hive supports temporary tables since version 0.14 only. | + | HPL/SQL converts DECLARE TEMPORARY TABLE statement to CREATE TEMPORARY TABLE in Hive. Note that Hive supports temporary tables since version 0.14 only. |
===== Managed Temporary Tables ===== | ===== Managed Temporary Tables ===== | ||
- | When [[configuration#plhqltemptables|plhql.temp.tables]] is set to **managed**, PL/HQL creates a regular table in the database and automatically drops it at the end of the session. | + | When [[configuration#hplsqltemptables|hplsql.temp.tables]] is set to **managed**, HPL/SQL creates a regular table in the database and automatically drops it at the end of the session. |
- | Note that the schema name and location are defined by [[configuration#plhqltemptablesschema|plhql.temp.tables.schema]] and [[configuration#plhqltemptableslocation|plhql.temp.tables.location]] options, respectively. | + | Note that the schema name and location are defined by [[configuration#hplsqltemptablesschema|hplsql.temp.tables.schema]] and [[configuration#hplsqltemptableslocation|hplsql.temp.tables.location]] options, respectively. |
Also UUID is added to the table name to prevent name conflicts between multiple sessions. | Also UUID is added to the table name to prevent name conflicts between multiple sessions. | ||
- | For example, if you declare temporary table //temp1//, PL/HQL will actually create something like //temp1_3fc162e0590f4e17ae141385cc0e8447//. | + | For example, if you declare temporary table //temp1//, HPL/SQL will actually create something like //temp1_3fc162e0590f4e17ae141385cc0e8447//. |
**Example**: | **Example**: | ||
Line 26: | Line 26: | ||
<code language=sql> | <code language=sql> | ||
- | SET plhql.temp.tables = managed; | + | SET hplsql.temp.tables = managed; |
DECLARE TEMPORARY TABLE temp1 | DECLARE TEMPORARY TABLE temp1 |