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.

what are the maximum number of rows that can be constructed by inserting rows directly in value list? : Transact sql

698

---

What is localdb mssqllocaldb?

796

---

what is the difference between undefined value and null value? : Sql dba

772

---

What is restrict in sql?

730

---

Can unique keys be null?

654

---

---

Which command is used to delete a package?

764

---

what is normalization? : Sql dba

735

---

how many sql dml commands are supported by 'mysql'? : Sql dba

784

---

Could you please provide oca (oracle 10g) dumps for my certification ?

4769

---

Where the sql database files are stored?

685

---

What is a sql profiler?

762

---

What is a database? Explain

775

---

How many rows can sqlite handle?

773

---

What are the datatypes available in pl/sql ?

786

---

What is a file delimiter?

736

---