CODEWITHSUNDEEP
oracle-databaseperformancepartitioning
Mahsa ehsani
Mahsa ehsani

Reputation: 117

Query joined partitioned tables

I have 2 partitioned tables . I want to left joined them it means that i want all record of table 'A' that is in specific date , and data in table 'B' if there is any matched record (also in specific date). both tables are partitioned so i use partitioned column (time) in where clause , but in this query when there is not any matched record in table 'B' , the condition 'B.time' become null and the records of table 'A' dose not appear in result. if I do not use time condition for table 'B', the execution plan of it become partition range all and has bad performance . how can i edit the query?

select *   from A    left outer join B SSRESP
    on A.ID = B.A_ID  where 

    A.time >= to_date('1396/09/26 00:00:00',
                                 'yyyy/mm/dd hh24:mi:ss',
                                 'NLS_CALENDAR=persian')    
   and A.time <= to_date('1396/10/27 11:00:00',
                                 'yyyy/mm/dd hh24:mi:ss',
                                 'NLS_CALENDAR=persian')
          and B.time >=
       to_date('1396/09/26 00:00:00',
               'yyyy/mm/dd hh24:mi:ss',
               'NLS_CALENDAR=persian')    
      and B.time <=
       to_date('1396/10/27 11:00:00',
               'yyyy/mm/dd hh24:mi:ss',
               'NLS_CALENDAR=persian')

Upvotes: 0

Views: 362

Answers (1)

Radagast81
Radagast81

Reputation: 3006

There are 2 possibilities.

1) Move the condition to the on-clause:

select *   
  from A    
  left outer join B
    on A.ID = B.A_ID      
   and B.time >= to_date('1396/09/26 00:00:00',
                         'yyyy/mm/dd hh24:mi:ss',
                         'NLS_CALENDAR=persian')    
   and B.time <= to_date('1396/10/27 11:00:00',
                         'yyyy/mm/dd hh24:mi:ss',
                         'NLS_CALENDAR=persian')  
where A.time >= to_date('1396/09/26 00:00:00',
                        'yyyy/mm/dd hh24:mi:ss',
                        'NLS_CALENDAR=persian')    
  and A.time <= to_date('1396/10/27 11:00:00',
                        'yyyy/mm/dd hh24:mi:ss',
                        'NLS_CALENDAR=persian')

2) Filter the table before the left join:

select *   
  from A    
  left outer join 
    (SELECT * FROM B
      WHERE B.time >= to_date('1396/09/26 00:00:00',
                              'yyyy/mm/dd hh24:mi:ss',
                              'NLS_CALENDAR=persian')    
        and B.time <= to_date('1396/10/27 11:00:00',
                              'yyyy/mm/dd hh24:mi:ss',
                              'NLS_CALENDAR=persian')) AS B
    on A.ID = B.A_ID  
where A.time >= to_date('1396/09/26 00:00:00',
                        'yyyy/mm/dd hh24:mi:ss',
                        'NLS_CALENDAR=persian')    
  and A.time <= to_date('1396/10/27 11:00:00',
                        'yyyy/mm/dd hh24:mi:ss',
                        'NLS_CALENDAR=persian')

Upvotes: 1

Related Questions