what is global temporary tables and how use that tables in
pl/sql packages



what is global temporary tables and how use that tables in pl/sql packages..

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.

Is This Answer Correct ?    0 Yes 5 No

Post New Answer

More SQL PLSQL Interview Questions

If i can use sys.check_constraints to display my constraints from my database using sql server 2005, how can i display then if i am using sql server 2000????

0 Answers  


Is left join inner or outer?

0 Answers  


How to improve the performance of a pl/sq stored procedures or functions or triggers and packages ?

11 Answers   IBM, TCS, UHD,


What is meant by Join? What are the different types of Joins available? Explain.

5 Answers   Cap Gemini,


Show the cursor attributes of pl/sql.

0 Answers  






What is db journal file?

0 Answers  


Is there any problem if we use commit repeatedly after each dml statement in a plsq procedure ? (eg. there are 10 update stmt and using 10 commit stmt after each update stmt)

1 Answers  


how can we repair a mysql table? : Sql dba

0 Answers  


What is the difference between sql and mysql?

0 Answers  


How does cross join work?

0 Answers  


Difference between global and parameter variables?

0 Answers   TCS,


Can we create table in function?

0 Answers  


Categories