Answer Posted / sreemanth reddy

hi
You can not specify a tablespace with global temporary
tables. GTT's are built in the TEMP tablespace.
Global temporary tables have three major benefits:

1. Non-interference between private sets of data.

2. Ease of getting rid of 'scratch' data. In a heap table
you either rollback, or delete it. But in a GTT, you can
truncate explicitly, without affecting anyone else (or allow
the implicit "truncate on commit / exit" effect to do
the same thing).

3. Decreased redo generation as, by definition, they are
non-logging.

However:

Mixing temporary tables (GTTs) with permanent tables usually
causes some grief to the CBO. It has no information
about the number of rows in the GTT, and therefore guesses
(badly).

Even if you analyze table .. or
dbms_stats.gather_table_stats() you don't get stats on the
temporary table.

Set the init parameter dynamic_sampling to at least 2 for
GTTs to be sampled at run-time.

Note: All DDL includes two implicit commits so any rows in a
GTT specified with ON COMMIT DELETE ROWS will empty the
table.

How does sql store data?

925

---

Can we call stored procedure in function?

965

---

Differences between Oracle 9i and 10g (Probably in terms of SQL and PL/SQL)?

3573

---

What is schema in sql example?

1186

---

Does oracle roll back the transaction on an error?

1022

---

What are its different types of dbms?

1010

---

How many triggers can be applied on a table?

936

---

What is tuple in sql?

1087

---

what happens if you no create privilege in a database? : Sql dba

1007

---

how to drop an existing view in mysql? : Sql dba

1063

---

Explain the components of sql?

1068

---

What are dml commands?

1026

---

Can we commit in trigger?

982

---

Can there be 2 primary keys in a table?

1002

---

what is rdbms? : Sql dba

1018

---