An external table accesses data in external sources as if this data were in a table in the database.
The data can be in any format for which an access driver is provided. You can use SQL (serial or parallel), PL/SQL, and Java to query external tables.
iot internet of things training courses malaysia
Purpose of External Tables
External tables are useful when an Oracle database application must access non-relational data.
For example, a SQL-based application may need to access a text file whose records are in the following form:
Copy100,Steven,King,SKING,515.123.4567,17-JUN-03,AD_PRES,31944,150,90
101,Neena,Kochhar,NKOCHHAR,515.123.4568,21-SEP-05,AD_VP,17000,100,90
102,Lex,De Haan,LDEHAAN,515.123.4569,13-JAN-01,AD_VP,17000,100,90
You could create an external table, copy the text file to the location specified in the external table definition, and then use SQL to query the records in the text file. Similarly, you could use external tables to give read-only access to JSON documents or LOBs.
ibm websphere training courses malaysia
In data warehouse environments, external tables are valuable for performing extraction, transformation, and loading (ETL) tasks. For example, external tables enable you to pipeline the data loading phase with the transformation phase. This technique eliminates the need to stage data inside the database in preparation for further processing inside the database.
ibm lotus notes domino datastage training courses malaysia
Starting in Oracle Database 12c Release 2 (12.2), you can partition external tables on virtual or non-virtual columns. Thus, you can take advantage of performance improvements provided by partition pruning and partition-wise joins. For example, you could use partitioned external tables to analyze large volumes of non-relational data stored on Hadoop Distributed File System (HDFS) or a NoSQL database.
Leave a Reply