Step 1: Create GLOBAL TEMPORARY TABLE T1
CREATE GLOBAL TEMPORARY TABLE T1
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2 (128 BYTE),
OBJECT_ID NUMBER,
TIMESTAMP VARCHAR2(19 BYTE)
)
ON COMMIT DELETE ROWS;
Elapsed: 00:00:00.03
Step 2: Create a permanent table T2
CREATE TABLE T2
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
OBJECT_ID NUMBER,
TIMESTAMP VARCHAR2(19 BYTE)
);
Elapsed: 00:00:00.02
Step 3: Create an index on T1.
Create index idx_test_t1 on t1(owner);
Index created.
Elapsed: 00:00:00.01
Step 4: Create an index on T2.
Create index idx_test_t2 on t2(owner);
Index created.
Elapsed: 00:00:00.02
Step 5: Insert records into T1 from a static table T3(Sample Table).
Approach 1:
insert into t1 select * from t3;
4610624 rows created.
Elapsed: 00:17:24.12
Step 6: Insert records into T2 from a static table T3(Sample Table).
Approach 1:
insert into t2 select * from t3;
4610624 rows created.
Elapsed: 00:18:30.82 ****
Approach 2: (Preferable)
INSERT /*+ APPEND +*/ into t2 select * from t3;
4610624 rows created.
Elapsed: 00:03:25.48 ****
Approach 3: (Preferable)
insert /*+ append parallel(t2, 10) nologging */ into t2 select * from t3;
4610624 rows created.
Elapsed: 00:02:03.54 ****
**** There is a significant improved performance after using append hint in static table
Step 7: Execute a select statement count number of records from T1
Approach 1:
select count(1) from t1;
COUNT(1)
----------
4610624
Elapsed: 00:00:18.10
select /*+ dynamic_sampling(t1 4 ) */ count(1) from t1;
COUNT(1)
----------
4610624
Elapsed: 00:00:03.58
Step 8: Execute a select statement count number of records from T2
Approach 1:
select count(1) from t2;
COUNT(1)
----------
4610624
Elapsed: 00:00:06.71
Approach 2:
select /*+ dynamic sampling(t2 4 ) */ count(1) from t2;
COUNT(1)
----------
4610624
Elapsed: 00:00:03.09
Step 8: Execute a select statement which will use index
Select owner, count(1) from t1 where owner in (‘SYS’,’SEARCH’,’PUBLIC’) group by owner order by 2 desc;
OWNER COUNT(1)
------------------------------ ----------
SYS 1494528
PUBLIC 1284992
SEARCH 444608
Elapsed: 00:00:02.20
Step 9: Execute a select statement which will use index
Select owner, count(1) from t2 where owner in (‘SYS’,’SEARCH’,’PUBLIC’) group by owner order by 2 desc;
OWNER COUNT(1)
------------------------------ ----------
SYS 1494528
PUBLIC 1284992
SEARCH 444608
Elapsed: 00:00:02.16
Restriction on Global temporary tables :
1. GTT cannot be partitioned.
2. We cannot create any Foreign key constraint on GTT
3. Parallel DML and parallel queries are not supported for temporary tables. Parallel hints
are ignored
4. GTT cannot contain columns of nested table.
5. Export and Import utilities can be used to transfer the table definitions, but no data
rows are processed.
