Tuning SQL with External Tables

Not only are all these fancy functions and materialized views available to access Oracle data.  They can also be used to access non-Oracle data, via external tables.  An external table is a file that lives outside of the database, yet you can use DML to access the data in the table, as if it lives inside the database.   Mike Ault, tuning guru, describes their access below.

Using External Tables in Oracle9i

In Oracle we are used to using internal tables; in fact, that, for many years, was the only option available to Oracle users. In later versions, the utl_file utility provided access for read and write to external files but not directly from SQL; you had to use PL/SQL to move the data into internal tables before it could be easily accessed by Oracle users. External data files had to be loaded using SQL*Loader or UTL_FILE into internal tables before SQL access was allowed. In Oracle8, the concept of BFILEs was introduced. BFILEs allow access to external files such as bitmaps, jpegs, PDF, and other formats as a single data column in a table; however, once again, SQL access was not possible and the columns had to be read by specialized PL/SQL routines.

Now in Oracle9i, DBAs have been given the external table. Combining the best features of BFILEs, UTL_FILE, and SQL*Loader, external tables allow direct SQL access to data contained in external data files. Once the DBA defines the data map into the external data file, users can directly access the data through SQL. However, the true strength of external tables will be in simplifying and streamlining data loading. An external table only has to be defined once and can be reused many times against multiple versions of the external data file.

Many applications use data from external sources to feed tables in an Oracle database. In previous releases (prior to Oracle9i), this data feed would require you to:

1.      Collect data from an external source such as a mainframe or telephony switch.

2.      Move file to Oracle system using FTP or other file transfer protocols.

3.      Convert data to a recognizable format.

4.      Load into Oracle using:

• SQL*Loader routine

• 3GL routine

• UTL_FILE and PL/SQL

• Multiple SQL insert commands

5.      Manipulate data inside Oracle using SQL or PL/SQL.

6.      Parse data to multiple tables (if required) using SQL or PL/SQL.

The scripts and processes involved in this data import from external files where usually complex and prone to failures.

Oracle9i has added the capability to use external tables as sources for data in an Oracle database. The data will still reside in the external tables but will be accessible to users from inside the database.

Using External Tables

Using external tables with Oracle9i is actually a very easy process. In fact, if you are using SQL*Loader to perform Step 4, you have already performed the most difficult part of using external tables.

To use external data tables, they must be compatible for use with SQL*Loader. Oracle will support other loading routines; but until the APIs are written to use third-party routines, we are stuck with SQL*Loader. The new process flow for using external data in an Oracle database will be:

1.      Collect data from an external source such as mainframe.

2.      Move file to Oracle system.

3.      Convert data to recognizable format.

4.      Create external table call.

5.      Manipulate data inside Oracle.

6.      Parse data to multiple tables (if required).

With new features in Oracle9i we can simplify Steps 4, 5, and 6; that is all the steps that require access to the Oracle database.

As long as the external data file is named the same, is of the same format, and is placed in the same location each time, the external table call only has to be created once; there is no hard connection between the external file and the external table call other than the location and file name. Also, the definition of an external table can be modified without being dropped to point to new data locations on-the-fly. This ability to re-point the data file locations means that you will not have to re-grant permissions, recompile packages and procedures, or re-compile views because you had to drop and recreate the external table definition.

The general format for accomplishing this connection between an external data file and a table defined as ORGANIZATION EXTERNAL in the database is:

  CREATE TABLE [schema.]table_name

  [relational_properties]

  ORGANIZATION EXTERNAL external_table_clause;

Where

  Relational_properties are:

  (column datatype constraint list)

  [table constraints]

  [logging clauses]

    external_table_clause is:

    ([TYPE external_driver_type]

  external_data_properties)

  [REJECT LIMIT n|UNLIMITED]

  external_data_properties are:

  DEFAULT DIRECTORY directory

  [ACCESS PARAMETERS (opaque_format_spec)| USING

    CLOB subquery]

  LOCATION ([directory:]'location specifier')

The definitions for the various parts of the above clauses are:

TYPE. TYPE access_driver_typeindicates the access driver of the external table. The access driver is the API that interprets the external data for the database. If you do not specify TYPE, Oracle uses the default access driver, ORACLE_LOADER. Currently, ORACLE_LOADER is the only access driver available.

DEFAULT DIRECTORY. Allows you to specify one or more default directory objects corresponding to directories on the file system where the external data sources may reside. Default directories can also be used by the access driver to store auxiliary files such as error logs. Multiple default directories are permitted to facilitate load balancing on multiple disk drives using parallel access streams.

ACCESS PARAMETERS. The optional ACCESS PARAMETERS clause lets you assign values to the parameters of the specific access driver for this external table:

• The opaque_format_speclets you list the parameters and their values. Use the Oracle Utilities manual to help you define the opaque format specification for your data. Essentially, it will be identical to an SQL*Loader control file for the same data

• The USING CLOB subquery lets you derive the parameters and their values through a subquery. The subquery cannot contain any set operators or an ORDER BY clause. It must return one row containing a single item of datatype CLOB.

• Whether you specify the parameters in an opaque_format_spec or derive them using a subquery, Oracle does not interpret anything in this clause. It is up to the access driver to interpret this information in the context of the external data.

LOCATION. The LOCATION clause lets you specify one external locator for each external data source. Usually, the location_identifier  is a file, but it need not be. Oracle does not interpret this clause. It is up to the access driver to interpret this information in the context of the external data.

REJECT LIMIT. The REJECT LIMIT clause lets you specify how many conversion errors can occur during a query of the external data before an Oracle error is returned and the query is aborted. The default value is 0. The value of UNLIMITED is also allowed.

You use the external_table_clauseto create an external table, which is a read-only table whose metadata is stored in the database but whose data is stored outside the database. External tables let you query data without first loading it into the database, among other capabilities.

Because external tables have no data in the database, you define them with a small subset of the clauses normally available when creating tables.

• Within the relational_propertiesclause, you can specify only column, datatype, and column_constraint. Further, the only constraints valid for an external table are NULL, NOT NULL, and CHECK constraints.

• Within the table_propertiesclause, you can specify only the parallel_clause and the enable-disable clause:

• The parallel_clauselets you parallelize subsequent queries on the external data.

• The enable-disable clause lets you either enable or disable a NULL, NOT NULL, or CHECK constraint. You can specify only ENABLE or DISABLE, and CONSTRAINT constraint_name. No other parts of this clause are permitted.

Restrictions on external tables include:

• No other clauses are permitted in the same CREATE TABLE statement if you specify the external_table_clause .

• An external table cannot be a temporary table.

• An external table cannot be indexed.

• An external table cannot be analyzed.

• No DML is allowed against an external table.

Example Creation of an External Table

We have a listing of all of the SQL scripts we use to manage Oracle databases. This listing has been generated on a Linux box using the ls–l>file.dat command and the resulting listing file cleaned up using system editors looks like the following (the actual file has over 400 entries):

'-rw-r--r--';1;oracle;dba;626;

  Apr 17 18:25;accept.sql;

'-rw-r--r--';1;oracle;dba;11103;

  Apr 17 18:25;access.sql;

'-rw-r--r--';1;oracle;dba;3295;

  Apr 18 01:19;act_size8.sql;

'-rw-r--r--';1;oracle;dba;918;

  Apr 17 18:25;active_cursors.sql;

'-rw-r--r--';1;oracle;dba;63;

  Aug 21 12:35;afiedt.buf;

'-rw-r--r--';1;oracle;dba;273;

  Apr 17 18:25;alter_resource.sql;

'-rw-r--r--';1;oracle;dba;5265;

  Apr 17 18:25;alter_views.sql;

'-rw-r--r--';1;oracle;dba;401;

  Apr 17 18:25;anal_tab.sql;

'-rw-r--r--';1;oracle;dba;374;

  Apr 17 18:25;analyze_all.sql;

'-rw-r--r--';1;oracle;dba;244;

  Apr 17 18:25;analz_sch.sql;

'-rw-r--r--';1;oracle;dba;989;

  Apr 17 19:25;auto_chn.sql;

'-rw-r--r--';1;oracle;dba;1861;

  Apr 17 18:25;auto_defrag.sql;

'-rw-r--r--';1;oracle;dba;167;

  Apr 17 18:25;awt.sql;

'-rw-r--r--';1;oracle;dba;481;

  Apr 18 01:20;backup.sql;

'-rw-r--r--';1;oracle;dba;405;

  Apr 18 01:20;block_usage.sql;

'-rw-r--r--';1;oracle;dba;960;

  Apr 18 01:21;blockers.sql;

'-rw-r--r--';1;oracle;dba;940;

  Apr 17 18:25;blockers2.sql;

'-rw-r--r--';1;oracle;dba;1002;

  Apr 18 01:21;bound2.sql;

'-rw-r--r--';1;oracle;dba;1299;

  Apr 18 01:22;bound_ob.sql;

'-rw-r--r--';1;oracle;dba;1742;

  Apr 17 18:25;brown.sql;

To match this external file we create a CREATE TABLE command that matches up the columns in the internal representation with the external file using standard SQL*LOADER control file syntax (first, we must create the DIRECTORY object that points to the external file location):

CREATE DIRECTORY sql_dir as '/home/

oracle/sql_scripts';

For the directory to be used by more than just the creator, we have to grant READ and WRITE to other users, in this case, public:

GRANT READ ON DIRECTORY sql_dir TO public;

GRANT WRITE ON DIRECTORY sql_dir TO public;

CREATE TABLE sql_scripts (permissions

  VARCHAR2(20),

filetype NUMBER(3),owner VARCHAR2(20),

group_name VARCHAR2(20), size_in_bytes NUMBER,

date_edited DATE , script_name VARCHAR2(64))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY sql_dir

ACCESS PARAMETERS

(FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY "'"

(permissions, filetype,owner,group_name,size_in_bytes,

date_edited DATE(17) "Mon dd hh24:mi",

script_name))

LOCATION ('file.dat'))

/

Once the table is created, we grant SELECT permission so others can also use it:

GRANT SELECT ON sql_scripts TO public;

The syntax of the statement is not verified for accuracy concerning the existence of the specified directory or file, or that the format specification for the load is correct, until the table is actually executed. Exhibit 1 shows what it actually looked like during the creation.

To verify that the table is accessing the data correctly, you need to do more than just verify that a COUNT(*) works. You need to access each data item in each row. I suggest doing a COUNT of each column to verify that it is accessible. I then used multiple Linux copy (cp) commands to make three additional copies of the file.dat file, used the Linux concatenation (cat) command to combine them with the original to make the file four times larger, and then renamed the larger file using the Linux move (mv) command to the name of the original file. Without changing a thing inside Oracle, I was able to reselect from the new external table:

SQL> /

  COUNT(*)

----------

      1764

Elapsed: 00:00:00.37

Execution Plan

-----------------------------------------------

0   SELECT STATEMENT Optimizer=CHOOSE

1 0   SORT (AGGREGATE)

2 1     EXTERNAL TABLE ACCESS (FULL)

        OF 'SQL_SCRIPTS'

Hints and External Tables

External tables also support the use of hints to affect join and access methods. Of course, any hints involving features not able to be used by external tables, such as indexes, will be ignored. From a lecture by Richard Niemiec of TUSC:

SQL> l

  1  select /*+ use_hash(a) */ a.empno, b.job, a.job

  2  from   emp_external4 a, emp_external5 b

  3  where  a.empno = b.empno

  4  and    a.empno = 7900

  5* and    b.empno = 7900;

400 rows selected.

Elapsed: 00:00:02.65

Execution Plan

----------------------------------------------

0   SELECT STATEMENT Optimizer=CHOOSE

    (Cost=33 Card=81 Bytes=2754)

1 0   HASH JOIN  (Cost=33 Card=81 Bytes=2754)

2 1   EXTERNAL TABLE ACCESS (FULL) OF 'EMP_EXTERNAL4'

3 1   EXTERNAL TABLE ACCESS (FULL) OF 'EMP_EXTERNAL5'

Notice how we forced it to use a HASH join operation?

External Table Performance

To get an idea of the performance of these external tables, I kept quadrupling the size of the external table until I reached 1,806,336 rows. Exhibit 2 shows the results from the full table scans of the external table compared against a full table scan of an identical internal table created from a SELECT * FROM sql_tablescommand of the largest external table. The actual data is shown in Exhibit 3.

You must remember that external tables cannot be analyzed or indexed; thus, their performance will always be identical to that for a full table scan. In addition, if you have a syntax error in your SQL*LOADER command file section, it will not show itself until an attempt is made to access that row. In my initial specification, I had an error in the date conversion. I was able to select columns without the dates and do row counts and value counts; but as soon as I did anything that attempted to access the date_edited column, I would get an OCI error. This indicates that you should always verify that you can access all rows before turning over an external table for general use. Another thing to watch is that, by default, a log identical to an SQL*LOADER log will be generated and will be inserted into each time a user accesses the external table unless you specify that no log be generated.

We will now look at using external tables in parallel and using some of the new, advanced features of Oracle9i, the multi-table INSERT and the MERGE command with the external table as a source for our data.

If we are to use external tables to replace the data loads from other data sources, then we must have a means to load data quickly and easily into multiple internal tables. Additionally, we must be able to provide conditional INSERT and UPDATE based on existence of rows in existing tables and the actual column values in existing rows. By using the new multi-table INSERT and MERGE commands, these tasks become easier than ever before.

Using External Tables in Parallel

If you have a multiple-file external file, you can use parallel access to speed performance. For example, suppose we have our script list in three files and we want to utilize parallel processing to speed access to these lists. We would redefine the external table as follows:

SQL> CREATE TABLE sql_scripts (permissions

       VARCHAR2(20),

  2  filetype NUMBER(3),owner VARCHAR2(20),

  3  group_name varchar2(20), size_in_bytes number,

  4  date_edited date , script_name

       VARCHAR2(64))

  5  ORGANIZATION EXTERNAL

  6  (TYPE ORACLE_LOADER

  7  DEFAULT DIRECTORY sql_dir

  8  ACCESS PARAMETERS

  9  (FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY "'"

 10  (permissions, filetype,owner,group_name,size_in_bytes,

 11  date_edited DATE(17) "Mon dd hh24:mi",

 12  script_name))

 13  LOCATION

       ('file1.dat',sql_dir2:'file2.dat',

       sql_dir3: 'file3.dat'))

 14  PARALLEL 3

 15* REJECT LIMIT UNLIMITED

SQL>/

The major difference between the above command and the standard command we saw earlier is in the LOCATION line; there are three data files listed. In the data file specifications, notice that there are two additional directories specified: the additional directories would have to exist and, of course, the specified files would have to reside in them. If no additional directories are specified, then Oracle assumes that the files in the LOCATION qualifier reside in the same directory. Also notice the PARALLEL keyword. If no integer argument is specified with the PARALLEL keyword, the default degree of parallel will be used. In the example, a parallel degree of 3 will be used to read the data from the external tables into Oracle. You should also make sure that your system is properly set up for parallel query by verifying the settings of the min_parallel_serversand max_parallel_serversinitialization parameters. Other initialization parameters that you may want to check include db_file_multiblock_read_count, sort_area_size, as well as others dealing with how efficiently Oracle reads data to and from disk (because access to external tables will be by full-table scan only).

If the external data files are located on different disk assets or are spread to minimize contention, then performance increases will be realized from using parallel processes to access the external data files. However, if they contend for each other, a performance penalty may occur.

Using External Tables to Export Data

According to early documentation, external tables can also be used to export data out of Oracle. The following example using the EMP and DEPT tables in the SCOTT schema shows how all employees working in the MARKETING and ENGINEERING departments can be exported in parallel using the default degree of parallelism. Note: This example was listed in the Oracle9i Beta documentation but removed for the production release. The Oracle concepts manual still states that the external table can be used to perform parallel data export but no examples are given anywhere in the documentation that I can find. I have to assume the capability was yanked at the last minute but may be available in future releases.

#

# Export employees working in the departments

  'MARKETING' and

# 'ENGINEERING'

#

CREATE TABLE emp_ext

   (empno NUMBER, ename VARCHAR2(100), ...)

    ORGANIZATION EXTERNAL

   (

       TYPE ORACLE_INTERNAL

       DEFAULT DIRECTORY private2

       LOCATION(export_dir:'/emp_data/emp1.exp',

         export_dir:'/emp_data/emp2.exp')

...)

PARALLEL

AS SELECT e.*, d.dname

   FROM   emp e, dept d

   WHERE  e.deptno = d.deptno

   AND    d.dname IN ('MARKETING','ENGINEERING');

At this point, you will have an external table that contains all of the records corresponding to the MARKETING and ENGINEERING departments that can be used by other programs if desired. Once changes are made to the data, it can be re-imported into the external table specification and then moved easily from there back into its parent tables. Notice that the TYPE becomes ORACLE_INTERNAL rather than ORACLE_LOADER and that no conversion specification is required.

#

# Re-import the employees that are working in

  the 'ENGINEERING' department

#

CREATE TABLE emp_import

  PARALLEL

  AS SELECT *

     FROM   emp_ext

     WHERE  dname = 'ENGINEERING';

This example illustrates how the external table feature can help to export (and import) a selective set of records.

Multi-Table Insert Using External Tables

Once the external data source is made into an external table that is accessible by SQL, the DBA will usually need to parse the data into one or more internal database tables for it to be in a truly useful form. In prior Oracle releases, after loading the data into a staging table, a PL/SQL procedure or SQL script with multiple SQL statements would have been required. In Oracle9i, the new multi-table insert capability will reduce the complexity of data parsing.

Using the new multi-table insert capability with Oracle9i and external tables, many lines of code in older versions can be compressed into a few statements with a resulting improvement in reliability and performance. For example, let's pull all of the scripts with tab in their names (assuming that all with tab in the name are for tables) into a table_scripts table and all of the scripts with ind in their names into a index_scripts table, the scripts left over will go into an other_scripts table.

First we need to create our empty tables. You cannot create them as a select off of the external table, so we will use the internal table we created as a template:

SQL> create table table_scripts as select * from

  2  sql_scripts_int where rownum<1;

Table created.

SQL> create table index_scripts as select * from

  2  sql_scripts_int where rownum<1;

Table created.

SQL> create table other_scripts as select * from

  2  sql_scripts_int where rownum<1;

Table created.

Now do the multi-table INSERT (see Exhibit 1).

SQL> INSERT FIRST

  2  WHEN script_name like '%tab%' THEN

  3  INTO table_scripts

  4  VALUES (PERMISSIONS, FILETYPE ,OWNER,

       GROUP_NAME, SIZE_IN_BYTES,

  5  DATE_EDITED, SCRIPT_NAME )

  6  WHEN script_name like '%ind%' THEN

  7  INTO index_scripts

  8  VALUES (PERMISSIONS, FILETYPE, OWNER,

       GROUP_NAME, SIZE_IN_BYTES,

  9  DATE_EDITED,SCRIPT_NAME)

 10  ELSE

 11  INTO other_scripts

 12  VALUES(PERMISSIONS, FILETYPE, OWNER,

       GROUP_NAME, SIZE_IN_BYTES,

 13  DATE_EDITED,SCRIPT_NAME)

 14  SELECT * FROM sql_scripts;

1806336 rows created.

Elapsed: 00:03:36.50

SQL> select count(*) from table_scripts;

COUNT(*)

----------131072

SQL> select count(*) from index_scripts;

COUNT(*)

----------20480

SQL> select count(*) from other_scripts;

COUNT(*)

----------1654784

Was this faster than doing the tables individually? Let's see. We will truncate the tables we just placed data into and reload them using standard INSERT commands (see Exhibit 2).

Them Using Standard INSERT Commands

SQL> truncate table table_scripts;

Table truncated.

SQL> truncate table index_scripts;

Table truncated.

SQL> truncate table other_scripts;

Table truncated.

SQL> INSERT

  2  INTO table_scripts (

  3  PERMISSIONS, FILETYPE ,OWNER,

  4  GROUP_NAME, SIZE_IN_BYTES, DATE_EDITED,

       SCRIPT_NAME)

  5  SELECT * FROM sql_scripts

  6  WHERE script_name like '%tab%';

131072 rows created.

Elapsed: 00:00:46.05

SQL> INSERT

  2  INTO index_scripts (

  3  PERMISSIONS, FILETYPE ,OWNER,

  4  GROUP_NAME, SIZE_IN_BYTES,

  5  DATE_EDITED, SCRIPT_NAME )

  6  SELECT * FROM sql_scripts

  7  WHERE script_name like '%ind%';

20480 rows created.

Elapsed: 00:00:27.21

SQL> INSERT

  2  INTO other_scripts (

  3  PERMISSIONS, FILETYPE ,OWNER,

  4  GROUP_NAME, SIZE_IN_BYTES,

  5  DATE_EDITED, SCRIPT_NAME )

  6  SELECT * FROM sql_scripts

  7  WHERE script_name not like '%tab%'

  8  AND script_name not like '%ind%';

1654784 rows created.

Elapsed: 00:03:45.64

Now compare the results. First we need to sum the times required for the multiple statements:

           46.05

           27.21

         3:45.64

      ----------

total    4:58.90

A total of 4:58.90 versus 3:36.50 for the multi-table insert. A 25 percent improvement in speed! Some complex examples have up to a reported 400 percent improvement in speed. I repeated the test several times with similar results.

Using the MERGE Command with External Tables

In many situations, data might have to be inserted into a table if it does not already exist; but if the key portions of data are already in the table, the changed data columns have to be updated instead. In earlier versions of Oracle, this would have been done with an INSERT and UPDATE command. Now we have a new Oracle9i feature called the MERGE command. The MERGE command allows for what is euphemistically known as an UPSERT. The MERGE command allows for the conditional INSERT or UPDATE of data based on the values in the data being manipulated. Because the INSERT and UPDATE are combined into a single command, the requirement for multiple scans in SQL or decision trees in PL/SQL is eliminated, thus allowing better performance and less complex code.

For example, we want to update all of the values for the DATE_EDITED column in our OTHER_SCRIPTS table based on the script name. If the script does not exist in OTHER_SCRIPTS, we want the entire new row inserted. Here is the UPDATE and INSERT required in SQL to do this operation without the MERGE command:

SQL> UPDATE other_scripts a

  2  SET    a.date_edited = (SELECT distinct

            b.date_edited FROM sql_scripts b

  3  WHERE  a.script_name = b.script_name);

492 rows updated.

Elapsed: 00:00:01.01

Execution Plan

-----------------------------------------------

0   UPDATE STATEMENT Optimizer=CHOOSE

1 0   UPDATE OF 'OTHER_SCRIPTS'

2 1     TABLE ACCESS (FULL) OF

          'OTHER_SCRIPTS'

3 1     SORT (UNIQUE)

4 3       EXTERNAL TABLE ACCESS (FULL)

            OF 'SQL_SCRIPTS'

And

SQL> INSERT INTO other_scripts a

  2  SELECT * FROM sql_scripts b

  3  WHERE  b.script_name not in (

  4  select b.script_name from other_scripts b);

28 rows created.

Elapsed: 00:00:00.01

Execution Plan

-----------------------------------------------

0   INSERT STATEMENT Optimizer=CHOOSE

1 0   FILTER

2 1     EXTERNAL TABLE ACCESS (FULL) OF

          'SQL_SCRIPTS'

3 1     TABLE ACCESS (FULL) OF

          'OTHER_SCRIPTS'

With the new MERGE command, this becomes like the code in Exhibit 3.

SQL> MERGE INTO other_scripts a

  2  USING (SELECT * FROM sql_scripts) b

  3  ON (a.script_name = b.script_name)

  4  WHEN MATCHED THEN

  5  UPDATE SET a.date_edited =

       b.date_edited

  6  WHEN NOT MATCHED THEN

  7  INSERT (a.permissions, a.filetype,

       a.owner, a.group_name,

  8  a.size_in_bytes,

       a.date_edited,a.script_name )

  9  VALUES (b.permissions, b.filetype,

       b.owner, b.group_name,

 10  b.size_in_bytes, b.date_edited,

       b.script_name );

12770 rows merged.

Elapsed: 00:00:05.01

Execution Plan

-------------------------------------------

0   MERGE STATEMENT Optimizer=CHOOSE

    (Cost=7874 Card=4018656 Bytes=598779744)

1 0   MERGE OF 'OTHER_SCRIPTS'

2 1     HASH JOIN  (OUTER)(Cost=26

            Card=211508 Bytes=32995248)

3 2       EXTERNAL TABLE ACCESS (FULL)

            OF'SQL_SCRIPTS' (Cost=16

            Card=8168 Bytes=857640)

4 2       TABLE ACCESS (FULL) OF

            'OTHER_SCRIPTS' (Cost=2

            Card=492 Bytes=25092)

Thus, we were able to perform both the UPDATE and the INSERT using a single command and a single transaction that performs both operations. Oddly, the performance seems to be worse using the MERGE command in Oracle 9.0.1.1 and there are certain oddities with the statistics reported.

Examples of the oddities in the reported statistics are the CARD (cardinality) reported against the external table SQL_SCRIPTS; the external table only contains 520 rows, not the 8168 reported by the cardinality. In addition, only 520 rows were subject to the combined INSERT and UPDATE operations, not the 12770 reported by the summary for the command. This seems to indicate that multiple, instead of single scans of the external table are being done, followed by multiple UPDATE of rows (because the final row count on the table was only 520). This has been reported to Oracle support and perhaps in future releases the expected better performance will materialize.

---

The above text is an excerpt from: Oracle SQL Tuning & CBO Internals ISBN 0-9745993-3-6 by Kimberly Floss