[Solved] Oracle syntax - should we have to choose between the old and the new?

EverSQL Database Performance Knowledge Base

Oracle syntax - should we have to choose between the old and the new?

Database type:

I work on a code base in the region of about 1'000'000 lines of source, in a team of around eight developers. Our code is basically an application using an Oracle database, but the code has evolved over time (we have plenty of source code from the mid nineties in there!).

A dispute has arisen amongst the team over the syntax that we are using for querying the Oracle database. At the moment, the overwhelming majority of our queries use the "old" Oracle Syntax for joins, meaning we have code that looks like this...

Example of Inner Join

select customers.*
       , orders.date
       , orders.value 
from customers, orders
where customers.custid = orders.custid

Example of Outer Join

select customers.custid
       , contacts.ContactName
       , contacts.ContactTelNo 
from customers, contacts 
where customers.custid = contacts.custid(+)

As new developers have joined the team, we have noticed that some of them seem to prefer using SQL-92 queries, like this:

Example of Inner Join

select customers.*
       , orders.date
       , orders.value 
from customers inner join orders 
     on (customers.custid = orders.custid)

Example of Outer Join

select customers.custid
      , contacts.ContactName
      , contacts.ContactTelNo
from customers left join contacts 
      on (customers.custid = contacts.custid)

Group A say that everyone should be using the the "old" syntax - we have lots of code in this format, and we ought to value consistency. We don't have time to go all the way through the code now rewriting database queries, and it wouldn't pay us if we had. They also point out that "this is the way we've always done it, and we're comfortable with it..."

Group B however say that they agree that we don't have the time to go back and change existing queries, we really ought to be adopting the "new" syntax on code that we write from here on in. They say that developers only really look at a single query at a time, and that so long as developers know both syntax there is nothing to be gained from rigidly sticking to the old syntax, which might be deprecated at some point in the future.

Without declaring with which group my loyalties lie, I am interested in hearing the opinions of impartial observers - so let the games commence!

Martin.

Ps. I've made this a community wiki so as not to be seen as just blatantly chasing after question points...

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX customers_idx_custid ON customers (custid);
CREATE INDEX orders_idx_custid ON orders (custid);
The optimized query:
SELECT
        customers.*,
        orders.date,
        orders.value 
    FROM
        customers,
        orders 
    WHERE
        customers.custid = orders.custid

Related Articles



* original question posted on StackOverflow here.