Oracle 8 Tips
by Burleson Consulting
The Data Warehouse Development Life Cycle
Parallelism And Oracle Data Warehousing
Oracle Parallel Query
We can now query the ALL_CUSTOMER view as if it were a single
database table, and Oracle parallel query will automatically
recognize the UNION ALL parameter, firing off simultaneous queries
against each of the three base tables. It is important to note that
the distributed database manager will direct each query to be
processed at the remote location, while the query manager waits
until each remote node has returned its result set. For example, the
following query will assemble the requested data from three table
segments in parallel, with each query optimized separately. The
result set from each sub-query is then merged by the query manager.
SEE CODE DEPOT FOR FULL SCRIPT
Instead of having a single query server manage the I/O against the
table, parallel query allows the Oracle query server to dedicate
many processors to simultaneously access data.
In order to be most effective, the table should be partitioned onto
separate disk devices, such that each process can do I/O against its
segment of the table without interfering with the other simultaneous
query processes. However, the client/server environment of the 1990s
relies on RAID or a logical volume manager (LVM), which scrambles
data files across disk packs in order to balance the I/O load.
Consequently, full utilization of parallel query involves striping a
table across numerous data files, each on a separate device.