The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior to Oracle 12c, all undo associated with DML against a GTT is written to the normal undo tablespace, which is itself protected by redo. As a result, using a GTT reduces the amount of redo generation, but does not eliminate it. Another why of describing this is, using a GTT removes direct redo generation, but not indirect redo generation cause by undo.
1. GTTs use space in your temporary tablespace. Regular tables use space in their assigned tablespace.
2. GTTs never have to be truncated, the data automatically goes away when your session ends (or you commit). Regular tables persist until you delete/truncate.
3. GTTs allow you to see only the data your session has put in. Regular tables allow you to see other people's data that they put in (Assuming you're not using FGAC).
4. Regular tables get fragmented/oversized over time because your report is 100 rows and the next guy's report is 10000000 rows which may lead to lots of empty blocks being scanned. GTTs are effectively truncated when you logout/commit.
5. Regular tables have stats calculated in order to help the optimizer (which may be good or bad). GTTs don't have stats calculated unless they are specifically set.
6. GTTs don't persist very well. This makes it harder for stateless or pooled connections to "build" with one connection and then "Query" with another connection.
7. You do understand that even a GTT will generate GOBS of redo when you use UPDATE and DELETE on it (generates MINIMAL with INSERT). You can pass a GTT to child function or procedure from parent call.
8. No REDO is generated for the GTT, however UNDO is generated for the GTT and REDO for the UNDO *must* be generated
9. Since INSERTs and SELECT generates minimal UNDO (and consequently minimal REDO), GTT will be best suited when most of your operations against GTT are either INSERTs and SELECTs.
10. Since UPDATEs and DELETEs generate the most amount of UNDO (and consequently most REDO to protect the UNDO), using GTT for these DML operations will almost generate the same amount of REDO as for a normal table.
11. An UPDATE against a GTT will generate 1/2 of REDO as it generated for normal table (since REDO for the UNDO is logged)
12. A DELETE against a GTT will generate same amount of REDO as it generated for normal table (since UNDO for DELETE is more) and the REDO for the UNDO is logged.
13. Although the data in a GTT is written to the temporary tablespace, the associated undo is still written to the normal undo tablespace, which is itself protected by redo, so using a GTT does not reduce undo and the redo associated with protecting the undo tablespace.
14. If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
15. Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session. Index stats is managed by Oracle it self.
16. Views can be created against temporary tables and combinations of temporary and permanent tables.
17. Temporary tables can have triggers associated with them.
18. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
You cannot use the following features with temporary tables:
How to create a GTT:
1. GTTs use space in your temporary tablespace. Regular tables use space in their assigned tablespace.
2. GTTs never have to be truncated, the data automatically goes away when your session ends (or you commit). Regular tables persist until you delete/truncate.
3. GTTs allow you to see only the data your session has put in. Regular tables allow you to see other people's data that they put in (Assuming you're not using FGAC).
4. Regular tables get fragmented/oversized over time because your report is 100 rows and the next guy's report is 10000000 rows which may lead to lots of empty blocks being scanned. GTTs are effectively truncated when you logout/commit.
5. Regular tables have stats calculated in order to help the optimizer (which may be good or bad). GTTs don't have stats calculated unless they are specifically set.
6. GTTs don't persist very well. This makes it harder for stateless or pooled connections to "build" with one connection and then "Query" with another connection.
7. You do understand that even a GTT will generate GOBS of redo when you use UPDATE and DELETE on it (generates MINIMAL with INSERT). You can pass a GTT to child function or procedure from parent call.
8. No REDO is generated for the GTT, however UNDO is generated for the GTT and REDO for the UNDO *must* be generated
9. Since INSERTs and SELECT generates minimal UNDO (and consequently minimal REDO), GTT will be best suited when most of your operations against GTT are either INSERTs and SELECTs.
10. Since UPDATEs and DELETEs generate the most amount of UNDO (and consequently most REDO to protect the UNDO), using GTT for these DML operations will almost generate the same amount of REDO as for a normal table.
11. An UPDATE against a GTT will generate 1/2 of REDO as it generated for normal table (since REDO for the UNDO is logged)
12. A DELETE against a GTT will generate same amount of REDO as it generated for normal table (since UNDO for DELETE is more) and the REDO for the UNDO is logged.
13. Although the data in a GTT is written to the temporary tablespace, the associated undo is still written to the normal undo tablespace, which is itself protected by redo, so using a GTT does not reduce undo and the redo associated with protecting the undo tablespace.
14. If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
15. Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session. Index stats is managed by Oracle it self.
16. Views can be created against temporary tables and combinations of temporary and permanent tables.
17. Temporary tables can have triggers associated with them.
18. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
You cannot use the following features with temporary tables:
- Synonyms, triggers and views on SESSION schema tables (including physical tables and temporary tables)
- Caching statements that reference SESSION schema tables and views
- Temporary tables cannot be specified in referential constraints and primary keys
- Temporary tables cannot be referenced in a triggered-SQL-statement
- Check constraints on columns
- Generated-column-spec
- Importing into temporary tables
How to create a GTT:
CREATE GLOBAL TEMPORARY TABLE gtt_table_name ( ..)
ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS | ON ROLLBACK DELETE ROWS;
This comment has been removed by a blog administrator.
ReplyDelete