Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

Kick it up a notch! 
                Faster Oracle queries using parallel features

Jan 25, 2000
Donald Burleson

2001 TechRepublic, Inc.

Oracle version 7.2 introduced some powerful new features to allow parallel processes to be used against the Oracle database. These features include parallel create table as select, parallel query, and parallel index building. Remember that you don't need to have parallel processors (SMP or MPP) in order to use and benefit from parallel processing. Even on the same processor, multiple processes can speed up queries. Oracle parallel query option can be used with any SQL SELECT statement, as long as the query performs a full-table scan on the target table.

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 that is 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 the 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?

While this "splitting" of a table according to a key value violates normalization, it can dramatically improve performance for individual queries. For large queries that may span many logical tables, the isolated tables can then easily be reassembled using Oracle's parallel query facility:


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.
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 and fire off simultaneous queries against each of the three base tables. It is important to note that the distributed database manager will direct that each query is 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 the three tables in parallel, with each query being separately optimized. The result set from each sub-query is then merged by the query manager:


select customer_name
from all_customer
where
total_purchases > 5000;



Data warehouses generally employ parallel technology to perform warehouse loading and query functions. These queries include:

  1. Parallel backup/recovery—Some parallel tools are capable of rates in excess of 40 BG/hour.
  2. Parallel query (SMP & MPP)—Multiple processes are used to retrieve table data.
  3. Parallel loading—Multiple processes are used to simultaneously load many tables.
  4. Parallel indexing—Multiple processes are used to create indexes.


For parallel query, the most powerful approach deals with the use of the SQL UNION verb in very large databases (VLDBs). In most large Oracle data warehouses, it is not uncommon to logically partition a single table into many smaller tables in order to improve query throughput. For example, a sales table that is ordered by date_of_sale may be partitioned into 1997_sales, 1998_sales, and 1999_sales tables. This approach is very common with data warehouse applications in which single logical tables might have millions of rows. While this "splitting" of a table according to a key value violates normalization, it can dramatically improve performance for individual queries. For large queries that may span many logical tables, the isolated tables can then be easily reassembled using Oracle's parallel query facility:


Create view all_sales as
select * from 1997_sales
UNION ALL
select * from 1998_sales
UNION ALL
select * from 1999_sales;




We can now query the all_sales view as if it were a single database table, and Oracle parallel query will automatically recognize the UNION ALL parameter. It will fire off simultaneous queries against each of the three base tables. For example, the following query will assemble the requested data from the three tables in parallel, with each query being separately optimized. The result set from each sub-query is then merged by the query manager:


select customer_name
from all_sales
where
sales_amount > 5000;




In future installments, we'll look at advanced Oracle8 parallel query features and share some of the tricks that experienced developers use to radically speed the execution time for their queries.


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.