PLSQL_Oracle External table concept and usage (CASE) (using external tables to directly query data files to partition database tablespaces), plsql_oracle Database

PLSQL_Oracle External table concept and usage (CASE) (using external tables to directly query data files to partition database tablespaces), plsql_oracle Database

2014-08-25 BaoXinjian

I. Summary

ORACLE External tables are used to access Text files (Text files) other than databases or ORACLE exclusive format files.

Therefore, when an External table is created, storage structures such as segments, partitions, and data blocks are not generated. Only table-related definitions are placed in the data dictionary.

External tables, as the name suggests, are stored outside the database.

Data can be obtained from ORACLE exclusive format files only during access. External tables are only for query and cannot be modified (INSERT, UPDATE, and DELETE operations ).

You cannot create an index on an external table. Because creating an index means that a corresponding index record exists. However, external tables are not stored in the database.

Therefore, an index cannot be created externally. If an index is hard to be created, the system will prompt an error message "the operation is not supported on the external organization table.

 

1. External table features

• Located in the file system (must be in the database server, rather than other network paths), separated by a certain format, such as @ # $, text files or other types of files can be used as external tables.
• You can use SQL statements to access external tables without loading the data in the external tables into the database.
• External data tables are read-only. Therefore, you cannot perform DML operations on External tables or create indexes.
• The ANALYZE statement does not support collecting statistics from external tables. You should use the DMBS_STATS package to collect statistics from external tables.
• Queries operations and connections. You can also perform operations in parallel.
• The data is in an external organization of the database and is an operating system file.
• The identifier of the operating system file in the database is mapped through a logical directory.

2. External table restrictions

• You can only SELECT tables, but cannot perform DML operations such as DELETE, UPDATE, and INSERT.
• Because external tables need to create directories on the ORACLE Database "server", OS files must be placed in these directories. That is, these files can only be stored on the database server. If the data file is not on the server, the External table cannot be used.
• An index cannot be created on an external table. However, you can create a view.
• External tables do not support LOB objects. If you want to use the LOB type, you cannot use an external table.

3. Advantages of External tables

If we want to talk about the advantages of External tables, we generally compare them with SQLLDR. Many syntaxes of External tables are similar to those of SQLLDR control files, next, let's talk about my understanding and some comparisons in "El 9i & 10g programming art ".

• SQLLDR needs to load data into the database before querying related records. If it is only for querying some records, external tables are indeed more advantageous than SQLLDR, which is convenient and does not occupy database storage space. Especially for large data volumes, the SGSN statement was previously processed by a mobile integrated analysis project (hundreds of GB of data, if all data is loaded into the database, it is a waste of space and time) this is the case. The External table virtual import process is extremely fast.
• When the flat file changes, the data in the External table changes. This avoids insert, update, delete, and other operations. It is quite advantageous for external tables with large records. I only need to use Shell commands to do things that will take a high cost for the database.
• External tables can use complex WHERE conditions to selectively load data. Although SQLLDR has a WHEN clause to select the row to be loaded, you can only use the AND expression AND the expression for Equality comparison. In the WHEN clause, the interval (greater than or less than) cannot be used ), no or is null.
• Ability to MERGE (MERGE) data. You can take an operating system file that fills up data and update existing database records.
• Efficient code search. An External table can be joined to another database table as part of the loading process.
• INSERT makes it easier to INSERT multiple tables. From Oracle9i, you can use an INSERT statement to INSERT one or more tables by using complex WHEN conditions. Although SQLLDR can be loaded into multiple tables, the corresponding syntax is quite complex.

 

Ii. Case-create an external table and perform addition, deletion, modification, and query operations

Case: Create an External table, import test data, and test

1. Create a directory

CREATE DIRECTORY extertable as '/home/oracle/extertable';

 

 

2. Create a test data file and Test

 

3. Create an External table

CREATE TABLE exter_test_table(   ID      NUMBER  (5),   NAME    VARCHAR (12),   SEX     VARCHAR (8),   AGE     NUMBER  (3),   GRADE   NUMBER  (5))ORGANIZATION EXTERNAL   (      TYPE oracle_loader      DEFAULT DIRECTORY extertable      ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' )      LOCATION ('student.data' )   );ALTER TABLE exter_test_table REJECT LIMIT UNLIMITED;

 

 

4. After creating a table, you can directly query the information in the data file.

 

 

5. External tables can only be selected and cannot be deleted or updated. To perform DML operations, you can only

 

6. log files are generated in the data file directory to record data import and export.

 

7. query the external tables in the database. dba_external_locations/dba_external_tables

 

 

Iii. Case-delete an External table

The SQL syntax for deleting External tables is the same as that for normal tables, but the difference is that the corresponding directory object may also be deleted.

When the External table is not used, you need to delete the External table or its corresponding directory object in time.

However, there are some restrictions when deleting the content. These restrictions mainly apply to management rather than technical restrictions.

That is to say, the Oracle database system does not impose any restrictions on it.

However, if the database administrator does not comply with these restrictions, some problems may occur.

To delete an External table, delete the directory object. Sometimes a directory object may contain multiple external tables.

In this case, you must confirm that all external tables are not used and have deleted them before you can delete the directory objects.

When creating an external table, the operating system checks whether the corresponding directory object has been created.

However, when deleting an object, the system does not determine whether all external tables associated with this directory object have been deleted.

If the directory object is deleted, but an External table exists.

When querying this external table, the system will prompt the error message "the object does not exist. Therefore, when deleting a directory object, the database system lacks a check. When deleting the directory object, only the database administrator needs to manually check whether the directory object has other external tables.

 

********************Author: Bao Xin********************

 

Reference: Xiaoxiang hidden God http://www.cnblogs.com/kerrycode/p/3894260.html

What is an external table in Oracle? You

External Table, which has field and data type constraints and can be queried like a common database Table, but the data in the Table is not stored in the database, it is in a common external file associated with the database. When you query the External Table, Oracle will parse the file and return Qualified Data, just as the data is stored in the database Table.

Joint query of external DataTable and Oracle database tables

Build a DataTable into an Oracle External table, and then perform the same joint query as a common table.