Using external tables in Oracle 19c is a powerful way to handle large datasets stored outside the database. This feature allows you to query and manage external data efficiently, treating it as if it were part of the database. In this article, we will explore the best practices for using external tables, focusing on their creation, management, and the strategies to optimize their performance.
Understanding External Tables (Ext Tables)
What are External Tables?
Ext tables enable you to access data in Ext sources as if it were in a table within the database. This functionality is particularly useful for integrating large datasets without requiring extensive data loading processes. By leveraging Ext tables, you can query external data directly, streamlining data management and improving efficiency.
Creating External Tables
External tables are created using the CREATE TABLE...ORGANIZATION EXTERNAL statement. Here is an example:
CREATE TABLE external_employees (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION ('external_employees.csv')
);
This example demonstrates how to create an Ext table using the ORACLE_LOADER access driver, specifying the default directory and file location for the Ext data.
Key Features of External Tables
Access Parameters
Access parameters modify the default behavior of the access driver for Ext tables. Each type of external table has its own access driver that provides specific access parameters. For instance, the ORACLE_LOADER driver allows you to define record delimiters, field delimiters, and other file-specific attributes to ensure data is read correctly.
Managing Data Files
Data files and output files for external tables must be located on the server. You need to create a directory object to specify the location for reading and writing files. For example:
CREATE DIRECTORY ext_tab_dir AS '/usr/apps/datafiles';
GRANT READ ON DIRECTORY ext_tab_dir TO scott;
This setup ensures that the Oracle database can access the necessary files and perform read/write operations as needed.
Using Different Access Drivers
Oracle provides several access drivers for Ext tables, including ORACLE_LOADER, ORACLE_DATAPUMP, and ORACLE_BIGDATA. Each driver is suited for different types of data sources and use cases:
- ORACLE_LOADER: Ideal for loading data from text files.
- ORACLE_DATAPUMP: Suitable for both loading and unloading data using binary dump files.
- ORACLE_BIGDATA: Enables access to data stored in object stores, treating it as if it were in Oracle tables.
Managing External Tables
Partitioning Ext Tables
As of Oracle Database 12c Release 2 (12.2.0.1), you can partition data contained in Ext tables, which allows for performance improvements similar to those provided by partitioning internal tables. This feature enables you to take advantage of partition pruning and other optimizations.
Example: Creating a Partitioned External Table
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
table_name =>'partitioned_table',
credential_name =>'OBJ_STORE_CRED',
format => json_object('delimiter' value ',', 'recorddelimiter' value 'newline'),
column_list => 'col1 number, col2 number, col3 number',
partitioning_clause => 'partition by range (col1)
(partition p1 values less than (1000) location
( ''https://objectstorage.url/file1.csv'')
,
partition p2 values less than (2000) location
( ''https://objectstorage.url/file2.csv'')
)'
);
END;
This procedure creates an external partitioned table, specifying the partitions based on the column values and their corresponding file locations.
Data Type Conversion
When working with Ext tables, data type conversion is crucial to ensure compatibility between the source data and the database table structure. Oracle handles conversions from character to numeric data, date to timestamp, and other necessary transformations to match the data types specified in the external table definition.
Example: Handling Conversion Errors
To avoid conversion errors, ensure the data types of columns in the Ext table match those in the data source. If mismatches occur, the access driver may reject the row, write to a bad file, or set the field value to NULL, depending on the access driver used.
Conclusion
Using external tables in Oracle 19c provides a robust method for managing large datasets stored outside the database. By understanding the creation process, access parameters, and best practices for managing Ext tables, you can efficiently integrate and query Ext data. This approach streamlines data management, enhances performance, and leverages the full capabilities of Oracle 19c for handling external datasets.
See more on Oracle’s website!
Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!
