Bulk collect and for all in Oracle for performance tuning

Hello There,
Here I am describing a small example of bulk collect and for all clause.This will helpt the execution time of query executed

Before how it work

A quick glance at the following Code should make one point very clear: This is straightforward code; unfortunately, it takes a lot of time to run - it is "old-fashioned" code, so let's improve it using collections and bulk processing.

CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
  FOR x IN (SELECT * FROM all_objects)
  LOOP
    INSERT INTO t1
    (owner, object_name, subobject_name, object_id,
     data_object_id, object_type, created, last_ddl_time,
     timestamp, status, temporary, generated, secondary)
    VALUES
    (x.owner, x.object_name, x.subobject_name, x.object_id,
    x.data_object_id, x.object_type, x.created,
    x.last_ddl_time, x.timestamp, x.status, x.temporary,
    x.generated, x.secondary);
  END LOOP;
  COMMIT;
END test_proc;
/

CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1 = 2;

SQL> set timing on;
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.84
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.03
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.54

Very slow - do not use it in that way!

USING Bulk Collect: 

Converting to collections and bulk processing can increase the volume and complexity of your code. If you need a serious boost in performance, however, that increase is well-justified.

Collections, an evolution of PL/SQL tables that allows us to manipulate many variables at once, as a unit. Collections, coupled with two new features introduced with Oracle 8i, BULK_COLLECT and FORALL, can dramatically increase the performance of data manipulation code within PL/SQL.

CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM all_objects;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO t1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_proc;
/

SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.34
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.20
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.90

Eliminate CURSOR LOOP at all

You may eliminate the CURSOR Loop at all, the resulting Procedure is compacter and the performance is more or less the same.

CREATE OR REPLACE PROCEDURE test_proc
IS
TYPE TObjectTable IS TABLE OF ALL_OBJECTS%ROWTYPE;
ObjectTable$ TObjectTable;

BEGIN
   SELECT * BULK COLLECT INTO ObjectTable$
     FROM ALL_OBJECTS;

     FORALL x in ObjectTable$.First..ObjectTable$.Last
     INSERT INTO t1 VALUES ObjectTable$(x) ;
END;
/

SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.51
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.35
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.46

Thanks for reading the document. 

Calling a Sub Template from Main Template in Oracle XML Publisher

Hello there,

Please go through the document of how to use subtemplate

There are two entries that you must make to call a subtemplate from a main template.

To implement the subtemplate in a main template, you must make two entries in the main template:

First, import the subtemplate file to the main template. The import syntax tells the BI Publisher engine where to find the Sub Template in the catalog.

Second, enter a call command to render the contents of the subtemplate at the position desired.

Importing the Subtemplate to the Main Template

Enter the import command anywhere in the main template prior to the call template command.

If you do not require a locale, enter the following:

<?import:xdoxsl:///path to subtemplate.xsb?>

where

path to subtemplate.xsb is the path to the subtemplate .xsb object in the catalog.

For example:

<?import:xdoxsl:///Executive/HR_Reports/mySubtemplate.xsb?>

Note:

If the subtemplate resides in a personal folder under My Folders, the command to import the subtemplate is:

<?import:xdoxsl:///~username/path to subtemplate.xsb?>

where username is your user name.

For example, if user myuser uploads a subtemplate called Template1 to a folder called Subtemplates under My Folders, the correct import statement is:

<?import:xdoxsl:///~myuser/Subtemplates/Template1.xsb?>

Calling the Subtemplate to Render Its Contents

You can also enter a call command to render the contents of the subtemplate at the position that you desire.

To call the subtemplate to render its contents:

• In the position in the main template where you want the subtemplate to render, enter the call-template command, as follows:

  <?call-template:template_name?>
  

  where

  template_name is the name you assigned to the contents in the template declaration statement within the subtemplate file (that is, the <?template:template_name?>statement).

The following figure illustrates the entries required in a main template:

Description of the illustration GUID-3BF18A85-B294-41EA-9A89-838A0B6F6CD5-default.gif

Importing a Localized Subtemplate

To designate the locale of the imported subtemplate, append the locale to the import statement as shown here.

<?import:xdoxsl:///{path to subtemplate.xsb}?loc={locale_name}?>

where

path to subtemplate.xsb is the path to the subtemplate .xsb object in the catalog

and

locale_name is the language-territory combination which comprises the locale. The locale designation is optional.

For example:

<?import:xdoxsl:///Executive/HR_Reports/mySubtemplate.xsb?loc=en-US?>

Note that you can also use ${_XDOLOCALE} to import a localized subtemplate based on the runtime user locale. For example:

<?import:xdoxsl:///Executive/HR_Reports/mySubtemplate.xsb?loc=${_XDOLOCALE}?>

Example

In this example, your company address is a fixed string that is displayed in all your templates. Rather than reproduce the string in all the templates, you can place it in one subtemplate and reference it from all the others.

To place the string in a subtemplate and reference it:

1. In an RTF file enter the following template declaration:

   <?template:MyAddress?>
   My Company
   500 Main Street
   Any City, CA 98765
   <?end template?>
   

2. Create a Sub Template in the catalog in the following location: Customer Reports/Templates.
3. Upload this file to the Sub Template and save it as "Common Components" (BI Publisher assigns the object the .xsb extension).
4. In the main template, enter the following import statement in a form field or directly in the template:

   <?import:xdoxsl:///Customer Reports/Templates/Common Components.xsb?>
   

5. In the main template, in the location you want the address to appear, enter:
   <?call-template:MyAddress?>

At runtime the contents of the MyAddress subtemplate are fetched and rendered in the layout of the main template.

Thanks for reading.