You can create a table that exists solely for your session or whose data persists for the duration of your transaction. You can use temporary tables to support specialized rollups or specific application-processing requirements whose results will not persist beyond the session or even past a COMMIT statement.
To create a temporary table, use the CREATE GLOBAL TEMPORARY TABLE command. When you create a temporary table, you can specify whether it should last for the duration of your session (via the ON COMMIT PRESERVE ROWS clause) or whether its rows should be deleted when the transaction completes (via the ON COMMIT DELETE ROWS clause).
Unlike a permanent table, a temporary table does not automatically allocate space when it is created. Space will be dynamically allocated for the table as rows are inserted:
You can see the duration of your data in YEAR_ROLLUP by querying the DURATION column of USER_TABLES for this table. In this case, the value of DURATION is SYS$SESSION. If ON COMMIT DELETE ROWS had been specified instead, the DURATION value would be SYS$TRANSACTION.
Now that the YEAR_ROLLUP table exists, you can populate it, for instance, via an INSERT AS SELECT command with a complex query. You can then query the YEAR_ROLLUP table as part of a join with other tables.
Leave a Reply