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
clause starts with
"\ and ends with \"
. When executing this command, Export builds a
statement similar to the following code. Click the View Code button to see this code.
The following are some restrictions to the
- This parameter cannot be specified for full, user, or transportable tablespace mode exports
- This parameter must be applicable to all specified tables
- This parameter cannot be specified within a direct path export (
- This parameter cannot be specified for tables with inner nested tables
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
), 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
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
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
- cost-based approach or
- 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.
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.