All-AMPs DISPATCHER RETRIEVE in Explain plan

Database
Enthusiast

All-AMPs DISPATCHER RETRIEVE in Explain plan

Hi,

 

One of our ETL script( to to 12 joins) running for more than half hrs. I have identifed its due to one of the business data table have cross join it. So I have removed the cross join and passed the business date in sub query and used the same in all WHERE Clause  and ON( Left outer join). Kind of passing patermeter values in SQL scripts refer Dnoeth post - http://developer.teradata.com/node/7579

 

After fine tuning the script in my explain plan am getting ALL-AMPs DISPATCHER RETRIEVE step. Could any please let me know ALL-AMPs DISPATCHER RETRIEVE is acceptable in explain plan or it will further impact the CPu utilization. Please help

 

 

 

 

  • performance
  • teradata explain

Accepted Solutions
Enthusiast

Re: All-AMPs DISPATCHER RETRIEVE in Explain plan

Thanks Dnoeth

1 ACCEPTED SOLUTION
3 REPLIES
Enthusiast

Re: All-AMPs DISPATCHER RETRIEVE in Explain plan

We do an all-AMPs DISPATCHER RETRIEVE step from Spool 2 by way of an all-rows scan and send the rows back to the Dispatcher.

12) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 2 by way of an all-rows scan and send the rows back to the Dispatcher.

13) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 2 by way of an all-rows scan and send the rows back to the Dispatcher.

14) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 2 by way of

Senior Apprentice

Re: All-AMPs DISPATCHER RETRIEVE in Explain plan

Dispatcher Retrieve Step is used to get the result of a Scalar Subquery in the WHERE-condition. The Subquery is replaced by the returned value (similar to using a parameter in Prepared SQL), you'll see a conditon using :%SSQnn in a following step.

There should be no more product join(s) now.

Enthusiast

Re: All-AMPs DISPATCHER RETRIEVE in Explain plan

Thanks Dnoeth