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 is the usage of the distinct keyword?

1277

---

Explain the difference in execution of triggers and stored procedures?

1065

---

What is the difference between an inner and outer join?

1061

---

how to include character strings in sql statements? : Sql dba

1141

---

What is the use of primary key?

1049

---

What is mutating trigger?

1073

---

How do you respond to dementia behavior?

1015

---

What is difference between stored procedure and trigger?

1252

---

how to decrement dates by 1 in mysql? : Sql dba

1077

---

Why do we need databases?

1093

---

Explain the uses of database trigger.

1178

---

Is sql better than excel?

1226

---

Can we use pl sql in mysql?

1063

---

explain the options of myisamchk to improve the performance of a table. : Sql dba

1074

---

What are the types of views in sql?

1142

---