Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 08 Jan 2004 @ 10:36:22 GMT


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


Subj:   Re: View Performance
 
From:   Victor Sokovin

Ganga,

  1. I tried it but no performance gains  


  2. I ran the queries before and after collecting statistics. I collected full statistics, but no performance gains  


  3. 20 Millions  



Thank you for the answers. I am trying to add them to the context you provided in the original posting and I realize I would like to know how large the output of the fast query is:

SELECT * FROM
OBC_MASTER.VW_RECONTACTS
WHERE OCID = 9 AND MID = 48 ;


In your first mail you said:

"OBC_MASTER.FA_OBC_MDN_MTD has 20 Million rows" and together with the new information that

select count(*)
from OBC_MASTER.FA_OBC_MDN_MTD
where ACTIVITY_CODE in (1,2) ;


returns about the same 20 million rows, we should assume that the WHERE clause AND ACT IN (1,2) in the slow query should not matter much for the optimizer.

In the original posting you also said:

  "THIS Query is very fast(run time 10 sec): SELECT * FROM OBC_MASTER.VW_RECONTACTS WHERE OCID = 9 AND MID = 48"  



My idea was to keep this set (as I said, it would be good to know its size) as a (physical) derived table but apparently that does not happen. This would suggest that the optimizer might be doing some in-memory derived table processing. From recent discussions here we know that this feature should be better turned off. Have you looked into this yet?


Regards,

Victor



     
  <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