CODEWITHSUNDEEP
sqloracle-database
dang
dang

Reputation: 2412

Optimized query in join

I have a query:

Select a.VD, a.VEXT, c.evalu from val_tb  a, carin b, mapos c
where a.VA_record_id in 
(Select reD from val_tb  where valnum = 100008533 and VD=1887) 
and a.VD in(1890) 
and a.varnum=1
and b.sname = 'cat'
and a.VEXT = b.source_pos_id
and b.posd = c.posid;

Is it possible to convert it to a join query in Oracle?

Upvotes: 1

Views: 58

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Use ANSI join syntax and avoid the older Oracle join syntax.

SELECT a.vd,
       a.vext,
       c.evalu
FROM   val_tb a
       join val_tb d
         ON A.va_record_id = d.red
       join carin b
         ON A.vext = b.source_pos_id
       join mapos c
         ON b.posd = c.posid
WHERE  d.valnum = 100008533
       AND d.vd IN ( 1887, 1890 )
       AND a.varnum = 1
       AND b.sname = 'cat';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You are using joins, just using a bad syntax for it. You query should look like this:

Select a.VD, a.VEXT, c.evalu
from val_tb  a join
     carin b
     on a.VEXT = b.source_pos_id join
     mapos c
     on b.posd = c.posid
where a.VA_record_id in (Select reD from val_tb  where valnum = 100008533 and VD=1887) and
      a.VD in (1890) and
      a.varnum = 1 and
      b.sname = 'cat';

This should have the same execution plan.

Simple rule: Never use commas in the FROM clause; always use explicit JOIN syntax.

For performance, you want indexes. Be sure that all the columns in the ON clauses have indexes (which they do if they have primary keys). Then for the IN clause, you want an index on val_tb(valnum, vd, reD) and perhaps another index for the where clause.

Upvotes: 2

Related Questions