Performance tuning tips while Joining tables / views in Oracle

Performance tuning tips for Joins:

In OLTP databases, you may be facing lots of performance issues while joining more base tables or views. You can just remind below recommendations before writing complex queries using joins.

1) Controlling the Access Path and Join Order with Hints

You can influence the optimizer's choices by setting the optimizer approach and goal, and by gathering representative statistics for the query optimizer. Sometimes, the application designer, who has more information about a particular application's data than is available to the optimizer, can choose a more effective way to execute a SQL statement. You can use hints in SQL statements to instruct the optimizer about how the statement should be executed.

Hints, such as /*+FULL */ control access paths. For example:

SELECT /*+ FULL(e) */ e.ename
  FROM emp e WHERE e.empno = 12656;

When you are not using /* FULL */ hint, then your index as usual be used. As per requirement you can use this hint. See the explain plan, when you are not using /* FULL */ hint.

Let us discuss the /* PARALLEL */ hint:

Use of PARALLEL hint improves your query performance. You can use like below:

SQL> select /*+ parallel(B,4) parallel(A,4) */
A.name,avg(B.salary),max(B.salary)
from dept A,emp B
where A.deptno = B.deptno
group by A.name
order by max(B.salary),avg(B.salary) desc;

Note: PARALLEL hint is CPU bound. Test before deploy.

See the explain plan, When not using /* PARALLEL */ hint.

When you are using parallel hint, then you will more performance benefits. See the below expaln plan window:

Join order can have a significant effect on performance. The main objective of SQL tuning is to avoid performing unnecessary work to access rows that do not affect the result. This leads to three general rules:

Avoid a full-table scan if it is more efficient to get the required rows through an index.

Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows.

Choose the join order so as to join fewer rows to tables later in the join order.

The following example shows how to tune join order effectively:

SELECT info
FROM tx1 a, tx2 b, tx3 c
 WHERE a.acol BETWEEN 100 AND 200
   AND b.bcol BETWEEN 10000 AND 20000
   AND c.ccol BETWEEN 10000 AND 20000
   AND a.key1 = b.key1
   AND a.key2 = c.key2;


Choose the driving table and the driving index (if any).

The first three conditions in the previous example are filter conditions applying to only a single table each. The last two conditions are join conditions.

Filter conditions dominate the choice of driving table and index. In general, the driving table is the one containing the filter condition that eliminates the highest percentage of the table. Thus, because the range of 100 to 200 is narrow compared with the range of acol, but the ranges of 10000 and 20000 are relatively large, tx1 is the driving table, all else being equal.

With nested loop joins, the joins all happen through the join indexes, the indexes on the primary or foreign keys used to connect that table to an earlier table in the join tree. Rarely do you use the indexes on the non-join conditions, except for the driving table. Thus, after tx1 is chosen as the driving table, use the indexes on b.key1 and c.key2 to drive into tx2 and tx3, respectively.

Choose the best join order, driving to the best unused filters earliest.

The work of the following join can be reduced by first joining to the table with the best still-unused filter. Thus, if "bcol BETWEEN ..." is more restrictive (rejects a higher percentage of the rows seen) than "ccol BETWEEN ...", the last join can be made easier (with fewer rows) if tx2 is joined before tx3.

You can use the ORDERED or STAR hint to force the join order.

Use Caution When Joining Complex Views

Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data.

Do Not Recycle Views

Beware of writing a view for one purpose and then using it for other purposes to which it might be ill-suited. Querying from a view requires all tables from the view to be accessed for the data to be returned. Before reusing a view, determine whether all tables in the view need to be accessed to return the data. If not, then do not use the view. Instead, use the base table(s), or if necessary, define a new view. The goal is to refer to the minimum number of tables and views necessary to return the required data.

Use Caution When Performing Outer Joins to Views

In the case of an outer join to a multi-table view, the query optimizer (in Release 8.1.6 and later) can drive from an outer join column, if an equality predicate is defined on it.

An outer join within a view is problematic because the performance implications of the outer join are not visible.

Store Intermediate Results

Intermediate, or staging, tables are quite common in relational database systems, because they temporarily store some intermediate results. In many applications they are useful, but Oracle requires additional resources to create them. Always consider whether the benefit they could bring is more than the cost to create them. Avoid staging tables when the information is not reused multiple times.

Some additional considerations:

Storing intermediate results in staging tables could improve application performance. In general, whenever an intermediate result is usable by multiple following queries, it is worthwhile to store it in a staging table. The benefit of not retrieving data multiple times with a complex statement already at the second usage of the intermediate result is better than the cost to materialize it.

Long and complex queries are hard to understand and optimize. Staging tables can break a complicated SQL statement into several smaller statements, and then store the result of each step.

Consider using materialized views. These are precomputed tables comprising aggregated or joined data from fact and possibly dimension tables.

Restructuring the Indexes

Often, there is a beneficial impact on performance by restructuring indexes. This can involve the following:

• Remove nonselective indexes to speed the DML.
• Index performance-critical access paths.
• Consider reordering columns in existing concatenated indexes.
• Add columns to the index to improve selectivity.

Do not use indexes as a panacea. Application developers sometimes think that performance will improve if they create more indexes. If a single programmer creates an appropriate index, then this might indeed improve the application's performance. However, if 50 programmers each create an index, then application performance will probably be hampered.