1

I have two tables 

select col1 , col2 , col3, col4, ........, col20 from ftcm; --TABLE has 470708 ROWS

select val from cspm where product='MARK'; --TABLE has 1 ROW

i have to make col3 as null if col2=val.

have thought of joining as

  select 
    col1 , col2 , decode(col2,val,NULL,col3) col3 , col4, ........, col20
    from ftcm a left outer join ( select val from cspm where product='MARK') b
    on a.col2=b.val;

but it seems to be time taking Please advise if there is any other way to get it tuned in best way.

Yuck
  • 49,664
  • 13
  • 105
  • 135
shashwat
  • 61
  • 3
  • Are your tables properly indexed? – Barranka Jul 30 '13 at 17:35
  • yes Barranka Table ftcm on col1 Table cspm on product – shashwat Jul 30 '13 at 17:42
  • It would be hard to judge w/o `Explain plan` and knowledge of the tables/indexes...etc One tiny tip, I think, using `CASE` might give you better performance than `DECODE`. – Jafar Kofahi Jul 30 '13 at 18:16
  • 1
    Is it significantly slower than fetching just the half million rows from ftcm ? (I know it's not what you want but just to have an idea of what can be done) – Nicolas Jul 30 '13 at 18:32

1 Answers1

1

I have not tested this query but if you know that the record from cspm is returning only one value, then you can perhaps try the following query :-

select col1, col2, decode(col2,(select val from cspm where product='MARK'),NULL,col3) col3, col4 ... col20 from ftcm

Since you are doing an outer join, the above might produce an equivalent output.

Another option which you can explore is to use a parallel hint

select /*+ parallel(em,4) */ col1, col2, decode(col2,(select val from cspm where product='MARK'),NULL,col3) col3, col4 ... col20 from ftcm em

However, consult with your DBA before using parallel hint at the specified degree (4)

Max
  • 4,067
  • 1
  • 18
  • 29
  • +1 With scalar subquery caching this should be about as fast as possible. – Jon Heller Jul 31 '13 at 04:33
  • Thanks Max with below query – shashwat Jul 31 '13 at 14:45
  • Thanks Max with your query "select col1, col2, decode(col2,(select val from cspm where product='MARK'),NULL,col3) col3, col4 ... col20 from ftcm" Cost reduced from 6434 to 439 thnx I was just needing this query as joining was causing additional cost Also I observed that whe I tried to create same query before i didn't add Braces to "select val from cspm where product='MARK'" which caused a missing expression error hence doubted use of query inside Decode Thanks for enlightening – shashwat Jul 31 '13 at 15:50