Global Temporary Tables

Introduction

Applications often use some form of temporary data store for processes that are required only for that session / transaction. Post that transaction / Session that data is no more required and need not be stored in the Data Base. Oracle Provides Global Temporary Tables (GTT) that can be used for this purpose.

Global temporary tables are types of database tables which can privately store data a session or transaction. The data will be flushed automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.

The Table data is session Specific, the table is available in all sessions like normal tables.

CREATE GLOBAL TEMPORARY TABLE <Table-name>
(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;



The Above syntax will be used to create the Global Temporary Tables.

Two types of GTT available based on the Options provided while creating the Temp table..

ON COMMIT DELETE ROWS

            Its transaction Specific temp table, the data in the table will get deleted when Commit/ Roll back statement is issued.

ON COMMIT PRESERVE ROWS

         It s session Specific temp table, the Data in the table will be available throughout the session until the data is deleted in the same session.  It both cases the data will get flushed when the session is closed normally / abnormally.

The default create statement is the equivalent of adding the clause ON COMMIT DELETE ROWS

Example

Create Table with default option (on Commit Delete Rows)

SQL>  CREATE GLOBAL TEMPORARY TABLE TEMP_EXAMPLE

          (   NEW_NO     NUMBER

          ) ON COMMIT  DELETE ROWS;

Table created

 

SQL> INSERT INTO TEMP_EXAMPLE VALUES (10);

1 row inserted

SQL> INSERT INTO TEMP_EXAMPLE VALUES (11);

1 row inserted

SQL> SELECT COUNT(1) FROM TEMP_EXAMPLE;

  COUNT(1)

----------

         2

SQL> COMMIT;

Commit complete

SQL> SELECT COUNT(1) FROM TEMP_EXAMPLE;

  COUNT(1)

----------

         0

-- Create table with on Commit preserve Rows

SQL> CREATE GLOBAL TEMPORARY TABLE TEMP_EXAMPLE_1

              (   NEW_NO     NUMBER

              ) ON COMMIT  PRESERVE ROWS;

Table created

SQL>

SQL> INSERT INTO TEMP_EXAMPLE_1 VALUES (10);

1 row inserted

SQL> INSERT INTO TEMP_EXAMPLE_1 VALUES (15);

1 row inserted

SQL> SELECT COUNT(1) FROM TEMP_EXAMPLE_1;

  COUNT(1)

----------

         2

SQL> COMMIT;

Commit complete

SQL> SELECT COUNT(1) FROM TEMP_EXAMPLE_1;

  COUNT(1)

----------

         2

Features

Ø  Temporary tables cannot be created without “Global”   keyword.

Ø  GTT data is private to a session. Although there is a single table definition, each session uses a     GTT as if it was privately owned.

Ø  Truncating data in a temp table will not affect other users sessions.

Ø  Depending on the table definition, data in a GTT will either be removed or retained after a commit. However it is always removed when the session terminates even if the session ends abnormally.

Ø  Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.

Ø  In Oracle 11g , the temp tablespace can be used to create the temp tables.

Ø  Views can be created against temporary tables and combinations of temporary and permanent tables.

Ø  Foreign key constraints are not applicable for Temporary tables

Ø  Temporary tables can have triggers associated with them.

Ø  Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Ø  Putting data in a temporary table is more efficient than placing this data in a permanent table. This is primarily due to less redo activity when a session is applying DML to temporary tables. DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Oracle writes data for temporary tables into temporary segments and thus doesn’t require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it’s not entirely eliminated because Oracle must log the changes made to these rollback segments. To summarize – “log generation should be approximately half of the log generation (or less) for permanent tables.”

Ø  Temporary tables cannot be partitioned.

Ø  If GTT has been defined as ON COMMIT DELETE ROWS, the GATHER_TABLE_STATS call will result in rows being deleted. This is because the GATHER_TABLE_STATS issues an implicit commit.

Ø  If GTT has been defined as ON COMMIT PRESERVE ROWS, the GATHER_TABLE_STATS will not delete rows in the table.

Table Fragmentation

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses. Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.HWM (High Water Mark) is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.DDL statement always resets the HWM.
How to find table fragmentation?

SQL> select count(*) from big1;
1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;
300000 rows deleted.

SQL> commit;
Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;
342226 rows updated.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');
PL/SQL procedure successfully completed.

Table size (with fragmentation)

SQL> select table_name,round((blocks*8),2)'kb' "size" from user_tables where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb
Actual data in table:

SQL> select table_name,round((num_rows*avg_row_len/1024),2)'kb' "size" from user_tables where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb
Note: 72952 - 30604 = 42348 Kb is wasted space in table.

The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.
How to reset HWM / remove fragemenation?
For that we need to reorganize the fragmented table.We have four options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

Option: 1 “alter table ... move + rebuild indexes”
SQL> alter table BIG1 move;
Table altered.

SQL> select status,index_name from user_indexes where table_name = 'BIG1';

STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE BIGIDX

SQL> alter index bigidx rebuild;
Index altered.

SQL> select status,index_name from user_indexeswhere table_name = 'BIG1';
STATUS INDEX_NAME
-------- ------------------------------
VALID BIGIDX

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');
PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)'kb' "size" from user_tables where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 38224kb
SQL> select table_name,round((num_rows*avg_row_len/1024),2)'kb' "size" from user_tables where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------BIG1 30727.37kb

Option: 2 “Create table as select”

SQL> create table big2 as select * from big1;
Table created.

SQL> drop table big1 purge;
Table dropped.

SQL> rename big2 to big1;
Table renamed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');
PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)'kb' "size" from user_tables where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)'kb' "size" from user_tables where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 68986.97kb

SQL> select status from user_indexes where table_name = 'BIG1';
no rows selected

SQL> --Note we need to create all indexes.

Option: 3 "export / truncate / import"

SQL> select table_name, round((blocks*8),2)'kb' "size" from user_tables where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)'kb' "size" from user_tables where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb

SQL> select status from user_indexes where table_name = 'BIG1';
STATUS
--------
VALID
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production
With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1
Export: Release 10.1.0.5.0 - Production on Sat Jul 28 16:30:44 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path
.... . exporting table BIG1 468904 rows exported
Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - ProductionWith the Partitioning, OLAP and Data Mining options

SQL> truncate table big1;
Table truncated.

SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production
With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\big1.dmp ignore=y
Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

importing SCOTT's objects into SCOTT
. . importing table "BIG1" 468904 rows imported
Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - ProductionWith the Partitioning, OLAP and Data Mining options

SQL> select table_name, round((blocks*8),2)'kb' "size" from user_tables where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)'kb' "size"from user_tableswhere table_name = 'BIG1';
TABLE_NAME size
----------------------------- ------------------------------------------
BIG1 42535.54kb

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');
PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)'kb' "size" from user_tables where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)'kb' "size"from user_tableswhere table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42542.27kb

SQL> select status from user_indexes where table_name = 'BIG1';
STATUS
--------
VALID

SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1', dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.

Option: 4 "dbms_redefinition"

SQL> create table TABLE1 ( no number, name varchar2(20) default 'NONE', ddate date default SYSDATE);
Table created.

SQL> alter table table1 add constraint pk_no primary key(no);
Table altered.

SQL> begin
for x in 1..100000 loop
insert into table1 ( no , name, ddate)
values ( x , default, default);
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_table1
after insert on table1
begin
null;
end;
/
Trigger created.

SQL> select count(*) from table1;
COUNT(*)
----------
100000

SQL> delete table1 where rownum <= 50000;
50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');
PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)'kb' "size" from user_tables where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)'kb' "size" from user_tables where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 822.69kb

SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg

SQL> --First check table is condidate for redefinition.SQL>SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT', 'TABLE1',sys.dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.

SQL> --After verifying that the table can be redefined online, you manually create an empty interim table (in the same schema as the table to be redefined)
SQL>
SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;
Table created.
SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT','TABLE1','TABLE2');
PL/SQL procedure successfully completed.

SQL> --This procedure keeps the interim table synchronized with the original table.
SQL>

SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT','TABLE1','TABLE2');
PL/SQL procedure successfully completed.

SQL> --Create PRIMARY KEY on interim table(TABLE2)
SQL> alter table TABLE2add constraint pk_no1 primary key (no);
Table altered.

SQL> create trigger tri_table2 after insert on table2
begin
null;
end;
/
Trigger created.

SQL> --Disable foreign key on original table if exists before finish this process.
SQL>
SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT','TABLE1','TABLE2');
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');
PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)'kb' "size" from user_tables where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)'kb' "size" from user_tables where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 841.4kb

SQL> select status,constraint_name from user_constraints where table_name = 'TABLE1';
STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1

SQL> select status ,trigger_name from user_triggers where table_name = 'TABLE1';
STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_TABLE2

SQL> drop table TABLE2 PURGE;
Table dropped.

Oracle SGA Regions

The SGA (System Global Area) is Oracle's structural memory area that facilitates the transfer of data and information between clients and the Oracle database. Long gone are the days when only four main tunable components existed. If you are using Oracle9i or above, expect to deal with the following memory regions:
Default buffer cache – This is the default memory cache that stores data blocks when they are read from the database. If the DBA does not specifically place objects in another data cache (which will be covered next), then any data requested by clients from the database will be placed into this cache. This memory area is controlled by the db_block_buffers parameter in Oracle8i and below, and db_cache_size in Oracle9i and above.

Keep buffer cache - Beginning with Oracle8, a DBA can assign objects to a special cache that will retain those object’s requested blocks in RAM for as long as the database is up. The keep cache's main function is to hold frequently referenced lookup tables that should always be kept in memory for quick access. The buffer_pool_keep parameter controls the size of this cache in Oracle8, while the db_keep_cache_size parameter handles the cache in Oracle9i and above. The keep pool is a sub-pool of the default buffer cache.

Recycle buffer cache - Imagine the opposite of the keep cache, and you have the recycle cache. When large table scans occur, the data filling a memory cache is unlikely to be needed again, and should be quickly discarded from RAM. By placing this data into the recycle cache, it will neither occupy valuable memory space nor prevent blocks that are needed from being placed in a buffer. However, should it be requested again, the discarded data is quickly available. The buffer_pool_recycle parameter controls the size of this cache in Oracle8 and below, while the db_recycle_cache_size parameter handles the cache in Oracle9i and above.

Specific block size caches - Beginning in Oracle9i, a DBA can create tablespaces whose blocksize differs from the overall database blocksize. When data is read into the SGA from these tablespaces, their data has to be placed into memory regions that can accommodate their special blocksize. Oracle9i and above has memory settings for 2K, 4K, 8K, 16K, and 32K caches. The configuration parameter names are in the pattern of db_nk_cache_size .

Shared pool - This familiar area holds object structures and code definitions, as well as other metadata. Setting the proper amount of memory in the shared pool assists a great deal in improving overall performance with respect to code execution and object references. The shared_pool_size parameter controls this memory region.

Large pool – Starting in Oracle8, a DBA can configure an optional, specialized memory region called the large pool, that holds items for shared server operations, backup and restore tasks, and other miscellaneous things. The large_pool_size parameter controls this memory region. The large pool is also used for sorting when the multi-threaded server (MTS) is implemented.

Java pool – This area handles the memory for Java methods, class definitions, etc. The java_pool_size parameter controls the amount of memory for this area.

Redo log buffer - This area buffers modifications that are made to the database before they are physically written to the redo log files. The log_buffer configuration parameter controls this memory area.

Note that Oracle also maintains a "fixed" area in the SGA that contains a number of atomic variables, pointers, and other miscellaneous structures that reference areas of the SGA.

Oracle Partitioning

Oracle Partitioning allows large tables to be broken into smaller pieces that improve manageability, availability, and scalability. With partitioning you can make your database faster the larger it becomes.

Oracle support the following partitioning methods:

Range partitioning - data is mapped to partitions based on a range of column values (usually a date column)
Hash partitioning - data is mapped to partitions based on a hashing algorithm, evenly distributing data between the partitions.
List partitioning - data is mapped to partitions based on a list of discrete values.
Range-Hash partitioning - data is partitioned by range, then hashed to sub-partitions.
Range-List partitioning - data is partitioned by range, then to sub-partitions based on a value list.

Partitioning is only available with Oracle Enterprise Edition as a cost option (you need to buy licenses before you can use it).
Start the Oracle installer and check if "Oracle Partitioning" is installed. If it is, you can just start using it.
If not, you will get error ORA-00439: feature not enabled: Partitioning. If you get this error, upgrade to Enterprise Edition and/or install the partitioning option.

Materialized View Fast Refreshes are Slow

A materialized view that is verified to be fast refresh should update relatively fast. But, what happens when there are few changes to the master table, no network issues, no aggregation in the snapshot query and the refresh still runs slow?

Check out
http://www.orafaq.com/node/1897