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 |
Hi All, I am new to both this blog and technology. I was able to see a response for one of the questions on triggers as below. I would like to know why are we using " if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then" instead, can't we use " if sysdate = 'sunday' then". I can understand the use of "rtrim", but dont know y v r using to_char. I have seen this in many cases but did not get a convincible explaination. Please help me with this and do excuse if this question sounds silly. Thanks in advance...... create or replace trigger trg_sun before insert on <table name> begin if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then raise_application_error(-20345,'no transaction in sunday'); end if; end trg_sun;
what is difference between decode and case function?
how do u call in & out parameters for stored procedures?
10 Answers A1 Technology, TCS, Techicon,
Can we insert data into view?
What is the difference between cross join and natural join?
source destination distance chennai bangalore 500 bangalore chennai 500 hyd delhi 1000 delhi hyd 1000 bangalore hyd 800 Here chennai to bangalore and bangalore to chennai is same distance. and hyd and delhi also same criteria. Based on the distance i want to display only one row using sql query?
What is the difference between python and sql?
what is difference between procedure and function, procedure and trigger?
What is java sql connection?
What is the difference between having clause and where clause?
How do we use distinct statement? What is its use?
what is csv? : Sql dba