Database Reference
In-Depth Information
With parallel DDL or parallel DML plus external tables, we have a parallel direct path load that is implemented
via a simple
CREATE TABLE AS SELECT
or
INSERT /*+ APPEND */
. No more scripting, no more splitting of files, and no
more coordinating the N number of scripts that would be running. In short, this combination provides pure ease of
use, without a loss of performance.
Let's take a look at a simple example of this in action. We'll see shortly how to create an external table. (We'll look
at data loading with external tables in much more detail in the next chapter.) For now, we'll use a real table to load
another table from, much like many people do with staging tables in their data warehouse. The technique, in short, is
as follows:
1.
Use some extract, transform, load (ETL) tool to create input files.
2.
Load these input files into staging tables.
3.
Load a new table using queries against these staging tables.
We'll use the same
BIG_TABLE
from earlier, which is parallel-enabled and contains 10 million records. We're going
to join this to a second table,
USER_INFO
, which contains
OWNER
-related information from the
ALL_USERS
dictionary
view. The goal is to denormalize this information into a flat structure.
To start, we'll create the
USER_INFO
table, enable it for parallel operations, and then gather statistics on it:
EODA@ORA12CR1> create table user_info as select * from all_users;
Table created.
EODA@ORA12CR1> alter table user_info parallel;
Table altered.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'USER_INFO' );
PL/SQL procedure successfully completed.
Now, we would like to parallel direct path load a new table with this information. The query we'll use is simply:
create table new_table
parallel
as
select a.*, b.user_id, b.created user_created
from big_table a, user_info b
where a.owner = b.username;
The plan for that particular
CREATE TABLE AS SELECT
statement looked like this in Oracle 12
c
:
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | NEW_TABLE | | Q1,00 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 00:00:01 | Q1,00 | PCWP | |
|* 5 | HASH JOIN | | 00:00:01 | Q1,00 | PCWP | |
| 6 | TABLE ACCESS FULL | USER_INFO | 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL | BIG_TABLE | 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------
