1

I have queries with a LOT of these situations

Sel TB1.C1
TB2.C2,
TB3.C4
Tb5.C5
where < Join conditions involving all tables TB1 through TB4 . 
Most are inner some are LOJ > 
where TB2.C2 NOT In ( List ) 
   OR TB3.C5 <> 'string' 
   OR Tb5.C8 NOT IN ( another long list )

Is there a better way to rewrite the filter conditions

NOT IN ( List ) ,   Col <> and IN LIST Optimization

some of the cols are selected some are not which are there in the sel Vs the filter condition.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1874594
  • 2,277
  • 1
  • 25
  • 49
  • Have you considered put your lists into VOLATILE tables and using NOT EXISTS clauses? This would allow the optimizer to use an EXCLUSION MERGE JOIN and may perform better. – Rob Paller Oct 08 '15 at 13:24
  • I am aware of Volatile table for IN LISTS. The problem with VT's is that they entail an additional step and many 3rd party BI tools are not VT friendly . Coming to NOT exists where the "where clause column" is part of select - how do I get this done. `WHERE NOT EXISTS ( sel '1' from TB3 3 where and 3.tb3='string')` is how I think it should look . Problem here is I am calling TB3 a big FAT table twice. Would that be performance saavy compared to using it once ? UNLESS I am missing something in implementing NOT exists and you can PL help me here – user1874594 Oct 08 '15 at 17:10
  • Have you tried CASE_N partitioning of tables? In case the data permits such partitioning without impacting other jobs, this can result in a performance boost at select time with minimal storage and insertion overheads – xenodevil Aug 22 '16 at 11:00

0 Answers0