Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 30, 2006

There are more than one TABLE Type – Part 1

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 10:27 am

1- Temporary Tables

In addition to permanent(heap) tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data.

Temporary tables can be indexed, analyzed and you can also create triggers on temporary tables;


DROP TABLE aglbtemptbl PURGE;
CREATE GLOBAL TEMPORARY TABLE aglbtemptbl (
acolumn VARCHAR2(5),
anumber NUMBER,
adate DATE) ;

INSERT INTO aglbtemptbl VALUES (’0123′, 123, SYSDATE);

create index anindex_aglbtemptbl on aglbtemptbl (anumber) ;

analyze table aglbtemptbl compute statistics ;

set autot traceonly explain
SELECT adate FROM aglbtemptbl where anumber = 123 ;
set autot off

Execution Plan
———————————————————-
..
|   1 |  TABLE ACCESS BY INDEX ROWID| AGLBTEMPTBL         |     1 |    22 |1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ANINDEX_AGLBTEMPTBL |     1 |       |1   (0)| 00:00:01 |
..

So when it is more advantageous to use a permanent table or a global temporary table, lets try this example. Before the comparison we need some grants to be given by sys to the user we will be working with.


conn sys/passwd as sysdba

grant select on v_$statname to hr ;
grant select on v_$mystat to hr ;

conn hr/passwd

CREATE OR REPLACE VIEW redo_size AS
SELECT value
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.name = ‘redo size’;

DROP TABLE perm PURGE;
CREATE TABLE perm
( x CHAR(2000) DEFAULT ‘x’,
y CHAR(2000) DEFAULT ‘y’,
z CHAR(2000) DEFAULT ‘z’ );

DROP TABLE temp PURGE;
CREATE GLOBAL TEMPORARY TABLE temp
( x CHAR(2000) DEFAULT ‘x’,
y CHAR(2000) DEFAULT ‘y’,
z CHAR(2000) DEFAULT ‘z’ )
ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE PROCEDURE do_sql (p_sql IN VARCHAR2) AS
l_start_redo NUMBER;
l_redo       NUMBER;
BEGIN
SELECT value
INTO l_start_redo
FROM redo_size;

EXECUTE IMMEDIATE p_sql;

COMMIT;

SELECT value - l_start_redo
INTO l_redo
FROM redo_size;

DBMS_OUTPUT.PUT_LINE( TO_CHAR(l_redo, ‘9,999,999′) || ‘ bytes of redo generated for “‘||
SUBSTR( REPLACE (p_sql, CHR(10), ‘’), 1, 25) || ‘”…’);
END;
/

SET SERVEROUTPUT ON FORMAT WRAPPED
BEGIN
do_sql( ‘INSERT INTO perm
SELECT 1,1,1
FROM all_objects
WHERE ROWNUM <= 500');

do_sql( 'INSERT INTO temp
SELECT 1,1,1
FROM all_objects
WHERE ROWNUM <= 500');

do_sql( 'UPDATE perm SET x = 2');
do_sql( 'UPDATE temp SET x = 2');

do_sql( 'DELETE FROM perm');
do_sql( 'DELETE FROM temp');
END;
/

3,283,452 bytes of redo generated for "INSERT INTO perm         "
66,488 bytes of redo generated for "INSERT INTO temp         "
2,249,824 bytes of redo generated for "UPDATE perm SET x = 2"
1,152,380 bytes of redo generated for "UPDATE temp SET x = 2"
3,215,780 bytes of redo generated for "DELETE FROM perm"
3,213,460 bytes of redo generated for "DELETE FROM temp"

PL/SQL procedure successfully completed.

As a result we may say for insert operations global temporary table usage decreases the redo generated dramatically, where as for delete operations there is a discardable difference. Before using a temporary table you may build up a similar test case and check the amounts of redo for your case.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used : Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions book by Mr.Thomas Kyte

2- Object Tables

Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database.

Object types are abstractions of the real-world entities—for example, purchase orders—that application programs deal with. An object type is a schema object with three kinds of components:

– A name, which serves to identify the object type uniquely within that schema
– Attributes, which model the structure and state of the real-world entity. Attributes are built-in types or other user-defined types.
– Methods, which are functions or procedures written in PL/SQL or Java and stored in the database, or written in a language such as C and

stored externally. Methods implement operations the application can perform on the real-world entity.


-- AN OBJECT TYPE EXAMPLE

DROP TABLE sustenance ;
DROP TYPE cake_t ;
DROP TYPE dessert_t ;
DROP TYPE food_t ;
CREATE TYPE food_t AS OBJECT (
-- Attributes
name VARCHAR2(100),
food_group  VARCHAR2 (100),
grown_in    VARCHAR2 (100),

-- Methods
MEMBER PROCEDURE who_are_you ,
MEMBER FUNCTION how_much RETURN INTEGER
);
/

set serveroutput on
DECLARE
-- Create a new object with a constructor
my_favorite_vegetable   food_t
:= food_t ('Brussel Sprouts',
'VEGETABLE',
'Farm,Greenhouse,Backyard');
BEGIN
-- Read an attribute value
DBMS_OUTPUT.put_line (
my_favorite_vegetable.name);

-- Modify an attribute value
my_favorite_vegetable.food_group :=
'SATISFACTION';

IF INSTR (
my_favorite_vegetable.grown_in,
'yard') > 0
THEN
NULL;
--    Pass an object as a parameter
--      order_seeds (my_favorite_vegetable);
END IF;

-- Invoke object type methods using dot notation
-- my_favorite_vegetable.who_are_you ();
END;
/

-- INHERITANCE EXAMPLE; CREATING A SIMPLE OBJECT TYPE HIERARCHY

DROP TYPE food_t ;
CREATE TYPE food_t AS OBJECT (
name VARCHAR2(100),
food_group  VARCHAR2 (100),
grown_in    VARCHAR2 (100))
NOT FINAL;
/
CREATE TYPE dessert_t UNDER food_t (
contains_chocolate    CHAR(1),
year_created          NUMBER(4))
NOT FINAL;
/
CREATE TYPE cake_t UNDER dessert_t (
diameter      NUMBER,
inscription   VARCHAR2(200));
/

-- POPULATE AN OBJECT TABLE

CREATE TABLE sustenance OF food_t;

DECLARE
my_favorite_vegetables food_t :=
food_t ('Brussel Sprouts', 'VEGETABLE', 'farm' );
BEGIN
INSERT INTO sustenance VALUES (my_favorite_vegetables);

INSERT INTO sustenance
VALUES (dessert_t ('Jello', 'PROTEIN', 'bowl', 'N', 1887 ) );

INSERT INTO sustenance
VALUES (cake_t (
'Marzepan Delight', 'CARBOHYDRATE', 'bakery',
'N', 1634, 8, 'Happy Birthday!' ) );
END;
/

select x.name, x.food_group, x.grown_in from sustenance x ;

-- OBJECT TABLES ALSO CAN BE INDEXED

create unique index ui_sustenance_name on sustenance(name);
analyze table sustenance compute statistics for all indexed columns;
set autotrace traceonly
select x.food_group from sustenance x where x.name = 'Jello' ;
set autotrace off

..
|   1 |  TABLE ACCESS BY INDEX ROWID| SUSTENANCE         |     1 |    64 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | UI_SUSTENANCE_NAME |     1 |       |     0   (0)| 00:00:01 |
..

Here is a sample application that provides an overview of how to create and use user-defined datatypes(Oracle Objects). An typical order management application is first developed with the relational model and then with the object-relational model; A Sample Application Using Object-Relational Features

After all these introductions what about the performance comparison with a heap table?

– insert comparison with runstats; object table ~%16 slower
Run1 ran in 568 hsecs
Run2 ran in 490 hsecs
15.92% RESPONCE TIME CHANGE

– storage amount from show_space ; object table ~2 times more

‘RELATIONAL_TABLE’
Total Blocks……………………….320
Total MBytes……………………….5

‘OBJECT_TABLE’
Total Blocks……………………….640
Total MBytes……………………….10

– primary key based read with runstats – nearly the same
Run1 ran in 1560 hsecs
Run2 ran in 1570 hsecs

a) redo details;


drop index ui_sustenance_name;
truncate table sustenance;

DROP TABLE perm_sustenance PURGe ;
CREATE TABLE perm_sustenance (
name VARCHAR2(100),
food_group VARCHAR2 (100),
grown_in VARCHAR2 (100));

SET SERVEROUTPUT ON FORMAT WRAPPED
BEGIN
do_sql( 'INSERT INTO perm_sustenance
SELECT ''Brussel Sprouts'', ''VEGETABLE'', ''farm''
FROM all_objects where rownum < 500');

do_sql( 'INSERT INTO sustenance
SELECT food_t (''Brussel Sprouts'', ''VEGETABLE'', ''farm'')
FROM all_objects where rownum < 500');

do_sql( 'DELETE FROM perm_sustenance');
do_sql( 'DELETE FROM sustenance');
END;
/

22,224 bytes of redo generated for "INSERT INTO perm_sustenan"
99,356 bytes of redo generated for "INSERT INTO sustenanceSEL"
134,268 bytes of redo generated for "DELETE FROM perm_sustenan"
256,760 bytes of redo generated for "DELETE FROM sustenance"

PL/SQL procedure successfully completed.

b) runstats details;


truncate table sustenance;
truncate table perm_sustenance;

set serveroutput on

declare

procedure step_insert_perm is
BEGIN
INSERT INTO perm_sustenance
SELECT 'Brussel Sprouts', 'VEGETABLE', 'farm'
FROM all_objects ;
end;

procedure step_insert_obj is
BEGIN
INSERT INTO sustenance
SELECT food_t ('Brussel Sprouts', 'VEGETABLE', 'farm')
FROM all_objects ;
end;

begin
runstats_pkg.rs_start;

step_insert_perm ;

runstats_pkg.rs_middle;

step_insert_obj ;

runstats_pkg.rs_stop(100000);
end;
/

Run1 ran in 100 hsecs
Run2 ran in 646 hsecs
run 1 ran in 15,48% of the time

Name                                  Run1        Run2        Diff
STAT...session pga memory        1,048,576      65,536    -983,040
STAT...undo change vector size      38,028   1,584,968   1,546,940
STAT...redo size                   553,424   4,704,652   4,151,228

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
101,767     194,786      93,019     52.25%

PL/SQL procedure successfully completed.

c) space usage details;


set serveroutput on

exec show_space(upper('sustenance')) ;

Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................               2
Full Blocks        .....................              91
Total Blocks............................             104
Total Bytes.............................         851,968
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           2,081
Last Used Block.........................               8

PL/SQL procedure successfully completed.

exec show_space(upper('perm_sustenance')) ;

Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               4
Full Blocks        .....................              59
Total Blocks............................              72
Total Bytes.............................         589,824
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,665
Last Used Block.........................               8

PL/SQL procedure successfully completed.

Before deciding on using object tables on OLTP systems for development with OO features provided you may want to test the performance cost for your application. Besides Oracle is relational, object tables map to relational tables behind :)

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
Oracle® Database Application Developer’s Guide – Object-Relational Features 10g Release 2 ( 10.2)
AskTom thread on “Oracle’s Object Relational Model”

1 Comment »

  1. […] There are more than one TABLE Type Series Like Partitioned Tables, Nested Tables, Temporary Tables, Object Tables, External Tables, Index […]

    Pingback by H.Tonguç YILMAZ Blog » Last warning, I moved my blog - Son uyarı, taşındım — January 3, 2007 @ 8:39 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: