Query Rewrite and changes to joins

SQL Optimizer SQL Optimizer for Oracle
1

I was running SQL Optimzer (8.8.1) and noticed 2 things that were odd. Not incorrect, per se, but it made me want to find the reason.

  1. When optimizing a fairly standard query using ANSI Joins it converted those joins into the “Classic Oracle” joins. For example:

select con.contract_number, to.task_order_number

from contract con inner join task_order to on con.contract_seq = to.contract_seq;

This was changed to:

select con.contract_number, to.task_order_number

from contract con, task_order to

where con.contract_seq = to.contract_seq;

This was consistently done for all the queries I analyzed. I know there shouldn’t be any difference between the two but I thought the “Best Practice” was to use the ANSI Join syntax.

  1. In another query I analyzed the rewrite added a +0 to the right part of the join for any column that was a number (PK)

where con.contract_seq = to.contract_seq +0;

Thanks

Rick

2

I sent this without asking my question - Why is it adding the +0?

3

The are many options in SQL Optimizer - such as don’t use hints and stick to ANSI join syntax. Take a look at all the many options. As for the +0, there are times when forcing an index NOT to be sued can improve things - hence by making the column and expression rather than a simple column the index usage is suppressed.

4

Hi Richard,

To answer question 1, SQL Optimizer has options for how to rewrite joins that are accessible in Options -> Optimize SQL -> Optimizer -> Optimization. You can choose to (by default) rewrite using the same JOIN syntax or specify which JOIN syntax you want to use or both. In some cases, switching the JOIN type may result in a different explain plan and a potentially better performing SQL so that option is available if desired.

For question 2, adding the +0 will bypass an index that exists for that column and force the optimizer to evaluate a different access path which may be faster than using that index. Here is a link to a Knowledge Base article which explains this in more detail

support.quest.com/…/35726

Thanks!

Kevin