what is global temporary tables and how use that tables in
pl/sql packages
- 1 Answers
- 9563 Views
- Cap Gemini, I also Faced
- E-Mail Answers
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.
| Is This Answer Correct ? | 0 Yes | 5 No |
Post New Answer View All Answers
Explain the steps needed to create the scheduled job?
What are the basic sql commands?
Mention what does the hierarchical profiler does?
How would you convert date into julian date format?
What are analytical functions in sql?
Does sql full backup truncate logs?
Is natural join and inner join same?
How do you remove duplicates without using distinct in sql?
Explain architecture of sql server notification services?
How many columns should be in an index?
What do you mean by table in sql?
Is it possible to link two groups inside a cross products after the cross products group has been created?
What is the difference between the repeatable read and serializable isolation levels? : Transact sql
What is autocommit sql?
Write a sql query to get the third highest salary of an employee from employee_table?
