| |
 |
|
Oracle Parallel Query
Oracle Tips by Burleson Consulting |
The Data Warehouse Development Life Cycle
Parallelism And Oracle Data Warehousing
Oracle Parallel Query
One of the most exciting performance features of Oracle version 7.3
and above is the ability to partition an SQL query into sub-queries
and dedicate separate processors to concurrently service each
sub-query. At this time, parallel query is only useful for queries
that perform full-table scans on long tables, but the performance
improvements can be dramatic.
Parallel queries are most useful in distributed databases where a
single logical table has been partitioned into smaller tables at
each remote node. For example, a customer table ordered by customer
name may be partitioned into a customer table at each remote
database, such that we have a PHOENIX_CUSTOMER, a
LOS_ANGELES_CUSTOMER, and so on. This approach is very common with
distributed databases where local autonomy of processing is
important. However, what about the needs of those in corporate
headquarters? How can they query all of these remote tables as a
single unit and treat the logical customer table as a single entity?
For large queries that may span many logical tables, the isolated
tables can be easily reassembled using Oracle’s parallel query
facility, as follows:
CREATE VIEW all_customer AS
SELECT * FROM PHOENIX_CUSTOMER@phoenix
UNION ALL
SELECT * FROM LOS_ANGELES_CUSTOMER@los_angeles
UNION ALL
SELECT * FROM ROCHESTER_CUSTOMER@rochester;
NOTE: the @ references refer to SQL*Net service names for the remote
hosts. For details on distributed Oracle communications, see Chapter
9, Distributed Oracle Data Warehouses.
|
