RelationalDBDesignRelationalDBDesign





Backup Recovery   «Prev  Next»
Lesson 2 Export and import improvements
Objective Describe the Improvements in Export and Import.

Oracle Export and Import Components

In this lesson, we will explore all the changes made within the export and import utilities to improve on their earlier versions.
Within Export, the user now has the ability to specify a subset of rows from a set of tables, when performing the table mode of export. The query parameter is a string that contains a WHERE clause for a SQL SELECT statement, which is applied to all the tables listed within the TABLE parameter.

EXP PETSTORE/GREATPETS TABLES=PRODUCT_OBJ_TABLE QUERY
=\"WHERE PET_FLAG = 'Y'\"

For example, if user PETSTORE wants to export only those records from the PRODUCT_OBJ_TABLE where the PET_FLAG is 'Y,' the export command will be as follows. View the Code below.
SELECT * FROM PRODUCT_OBJ_TABLE 
WHERE PET_FLAG = 'Y';

The WHERE clause starts with "\ and ends with \". When executing this command, Export builds a SELECT statement similar to the following code. Click the View Code button to see this code.
The following are some restrictions to the QUERY parameter:
  1. This parameter cannot be specified for full, user, or transportable tablespace mode exports
  2. This parameter must be applicable to all specified tables
  3. This parameter cannot be specified within a direct path export (DIRECT=Y)
  4. This parameter cannot be specified for tables with inner nested tables


Export and import utilities in Oracle

When performing export and import, you can now specify the number of files that should be exported and the size of each file. As soon as the first file reaches its limit (specified within FILESIZE), the export utility closes the file and starts writing to the next file. The export utility continues writing until complete or the maximum value of FILESIZE is again reached. If sufficient export filenames to complete the export are not specified, Export will prompt you to provide additional filenames. Because of this enhancement, a user can avoid the problems encountered during export when the file size exceeds two gigabytes.
Within Oracle, the export utility enables you to export tables that contain LOBs and objects, even if a direct path is specified on the command line. On export, the LONG datatypes are fetched into sections. However, sufficient memory must be available to hold all the contents of each row, including the LONG data.
Optimization is the process of choosing the most efficient way to execute a SQL statement. This is an important step in processing any data manipulation language (DML) statement such as SELECT, INSERT, UPDATE, or DELETE.
A part of Oracle called the optimizer calculates the most efficient way to execute a SQL statement. The optimizer evaluates several factors to select among alternative access paths. It can use a
  1. cost-based approach or
  2. rule-based approach.
The cost-based approach relies on statistics. Hence, it is necessary to generate statistics for all tables, clusters, and all types of indexes accessed by SQL statements before using the cost-based approach. If the size and data distribution of tables change frequently, statistics must be generated regularly to ensure that the statistics accurately represent the data within the tables.
The generation of statistics is an extensive and resource consuming process. Oracle provides the ability to export and import pre-calculated optimizer statistics, instead of recomputing the statistics at the time of import.

Optimizer

Optimization is a process of choosing the most efficient way to execute a SQL statement. A part of Oracle called the optimizer calculates the most efficient way to execute a SQL statement. The optimizer evaluates several factors to select among alternative access paths. The next lesson describes implementing duplex or multiple archive logs.