Answer / 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.

can a stored procedure call itself or recursive stored procedure? How much level sp nesting is possible? : Sql dba

0 Answers  

---

What is Histogram?

0 Answers   NIIT,

---

Which query operators in sql is used for pattern matching?

0 Answers  

---

Can you upgrade sql express to full sql?

0 Answers  

---

What is set serveroutput on?

0 Answers  

---

What is the different between Stored Procedure and Procedure?

7 Answers   Informatica, MIS,

---

1.when will you use week refcursor and when will you use strong ref cursor ? 2.what is the use of sql trace..how do you use it ? 3.can you please send all the sql plus commands...like set line 6000....

1 Answers   Satyam,

---

Why truncate is used in sql?

0 Answers  

---

wirte a query to filter improper date format follwing table? date 20-apr 22-may-2010 26-jun-2010 feb-2009 i want the output date 22-may-2010 26-jun-2010

6 Answers   Accenture,

---

what is difference between stored procedures and application procedures,stored function and application function?

1 Answers  

---

How is a process of pl/sql compiled?

0 Answers  

---

What are records give examples?

0 Answers  

---