Contents
Can temp table create index?
You can only add a (non PK) index to a #temp table. If you simply reference #tableName in the stored procedure (without creating it), it will look into the caller’s scope.
Can we create index on GTT?
There is no problem with having an index on a GTT. You need to show us some examples of how it is failing. SQL> SQL> drop table GTT; drop table GTT * ERROR at line 1: ORA-14452: attempt to create, alter or drop an index on temporary table already in use SQL> truncate table GTT; Table truncated.
What is the use of GTT in Oracle?
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.
Can you create an index on a temp table?
Create Index on Temp Table. One of the most valuable assets of a temp table (#temp) is the ability to add either a clustered or non clustered index. Additionally, #temp tables allow for the auto-generated statistics to be created against them. This can help the optimizer when determining cardinality.
When to use global temporary table in dynamic SQL?
The same applies to the execution of a dynamic SQL statement. If you want to communicate a temporary data table between a script and a dynamic SQL statement, you have to use global temporary table with ##, and the same applies to a stored procedure and the invoking script. This is the semantic of global temporaty tables.
Do you start with clustered or nonclustered index in tempdb?
Well, sort of. TempDB isn’t happy, but then it never is. It’s like your liver, or Oscar the Grouch. When starting to index temp tables, I usually start with a clustered index, and potentially add nonclustered indexes later if performance isn’t where I want it to be.
How to create a temp table with a name?
Bottom line is that this is really a data conversion program. << It would be nice to use an ETL tool to clean up the data, but we can use a SELECT DISTINCT to filter the dups, then MERGE the new data into the target. You can also capture source rows that are already in the target.