BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Oracle Partitioning and Oracle SQL Tuning

Oracle Tips by Burleson Consulting

In order to improve the manageability of large tables and indexes in Oracle8, Oracle introduced the concept of object partitioning. Once a table or index is defined as partitioned , Oracle SQL automatically detects the partition segments and determines the appropriate segment to access during a SQL query.

WARNING – You should always ensure that you are using the cost-based optimizer when using partitions because the rule-based optimizer will not use the performance enhancements of partition-wise joins and exclusive partition access.  Make sure that your optimizer_mode is set to first_rows or all_rows.

Partitioning and SQL table joining

The use of partitioned tables and indexes will also improve the speed of table join operations in a specials case of a join called a partition-wise join.

In a partition-wise join, Oracle divides the join into smaller joins that occur between each of the partitions on which the tables reside, completing the overall join in less time.

There are two types of partition-wise joins.  The first variation requires that both table reside in partitions, but Oracle also supports a partial partition-wise join, whereby only one of the table is partitioned.  Of course, the access of the partitions is transparent to the SQL, and the only way to see the partition access is to view the execution plan for the SQL.

In cases of partitioned full-table scans, parallelism can also be combined with partitioning. When parallelism is combined such that each partition has a parallel query process, the dividing of the table becomes faster an the overall execution time is faster.

Partitioning and SQL execution speed

When a partitioned index is analyzed with the analyze command, the Oracle data dictionary is populated with information about the ranges of values within each partition. This information is used by Oracle to include only those partitions that are relevant to the SQL query.

For example, suppose you have defined a partitioned index and an SQL statement requests an index range scan within a single index partition. Oracle will only access that index partition and can invoke an index fast-full scan against the partition, thereby improving the query speed because the entire index did not need to be accessed. Oracle calls this feature partition-aware optimization. At the heart of partition-aware optimization is the requirement that Oracle only access those partitions that are required to service the SQL query.

If you know the partition that contains your data you can explicitly reference it in your SQL query. While the CBO should be intelligent enough to recognize that a single partition is required, the partition clause make it very clear which partition is being referenced.

Select /*+ full */
   sum(sales)
from
   fact PARTITION ( march_2001_sales )
where
   sales_month = ‘2001-03’
;

Next, let’s examine how Oracle parallel query can be used with data warehouse SQL.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter