Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 31 Jan 2003 @ 00:25:39 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Query optimization: difference between inner join and derived table?
 
From:   R.A.

Hello,

In tuning performance of a query generated by MicroStrategy Desktop, I noticed something that puzzled me regarding the Teradata optimizer: two versions of a query, which appear be functionally equivalent, are having radically different response times. The query is as simple as it gets - an inner join like:

SELECT ...
FROM table1
INNER JOIN table2
ON table1.rid=table2.rid


Due to the size of the larger table (70 million rows). In one of many tests, I took the SQL above (generated by MicroStrategy) and re- wrote it manually as:

SELECT *
FROM table1
WHERE rid
IN (
SELECT rid
FROM table2
)


The query generated by the tool runs in about 1.5 hours, whereas the modified version with the derived table runs in 7 minutes, returning identical results. Regardless of issues like skewing, statistics or other similar considerations:

1. aren't the two queries above equivalent?

2. is there a reason why the optimizer seems to work so differently on each query?

Many thanks in advance.

R.A.



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016