All Forums General
karthik_2244 36 posts Joined 12/10
12 Mar 2012
Error 3149

Hi,

when we execute a query with many left joins in SQL assistant,

we are facing 3149 error code : TDWM filter violation for query request: For rule name '3HR_Alert'

Any suggestion on these error?

 

Thanks,

Karthik. N

ulrich 816 posts Joined 09/09
13 Mar 2012

 

From the documentation:

 

Explanation: This error occurs if a query request is

aborted by Teradata TDWM for a Filter rule violation.

Check currently active Teradata TDWM rules.

 

Remedy: Conform to the TDWM rules or contact your

TDWM Administrator.

 

You need to talk to your DBAs whats behind the rule to understand what you might need to change.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

karthik_2244 36 posts Joined 12/10
13 Mar 2012

Thnaks Ulrich,

one more doubt I have is ,

when I select the column used in the join in the select part explain plan is showing more time.

 ex:

SEL col1,col2,col3,col4 
FROM tableA a
JOIN tableB b
ON a.col1=b.col1
JOIN tableC c
ON a.col2=c.col2
AND so ON 

we have a query with some 15 left joins, explain plan is showing more time when I select the fileds used in the joins.

Is there any reason behind the explain plan taking more time when selecting those columns?

Thanks,

Karthik. N

ulrich 816 posts Joined 09/09
13 Mar 2012

Did the plan itself changed? Or only the estimates?

What did you select before? Select *?

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

karthik_2244 36 posts Joined 12/10
13 Mar 2012

There is not much change in explain plan. other than the new field added.

 

before  the query is like

SEL col8,col9,col10
FROM tableA a
JOIN tableB b
ON a.col1=b.col1
LEFT JOIN tableC c
ON a.col2=c.col2 

where  col8,col9,col10 are not part of join condition. 

after the query is like 

SEL col1,col2,col8,col9,col10
FROM tableA a
JOIN tableB b
ON a.col1=b.col1
LEFT JOIN tableC c
ON a.col2=c.col2 

 where col1 and col2 are part of join condition.

sencond select showing more time in explain plan.

ulrich 816 posts Joined 09/09
13 Mar 2012

"There is not much change in explain plan." So same steps and order in both?

Can it be related to bigger estimated spool files? 

Can you check the explain output.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

karthik_2244 36 posts Joined 12/10
13 Mar 2012

Yes I verified the explain plan, they are same. steps and order:

 both has 26 steps and in same order.

 

without the field used in join:

to be 148,374,004 rows (247,932,960,684 bytes).  The
     estimated time for this step is 6 minutes and 14 seconds.

be 111,280,503 rows (495,977,201,871 bytes).  The estimated time
     for this step is 21 minutes and 42 seconds.

 

with the field used in join:

 to be 148,374,004 rows (248,674,830,704 bytes).  The
      estimated time for this step is 6 minutes and 15 seconds.

  be 111,280,503 rows (497,868,970,422 bytes).  The estimated time
      for this step is 3 hours and 31 minutes.

Thanks,

Karthik N

ulrich 816 posts Joined 09/09
14 Mar 2012

no idea what might be the root caus for that.

Could be that optimizer internal thresholds are responcible for that. But thats only a guess. 

Maybe someone from Teradata can answer that?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

karthik_2244 36 posts Joined 12/10
14 Mar 2012

Thanks Ulrich.

These is point I noticed.

There was a distinct in the select clause.

Optimizer is taking time, when there is distinct in the select on the columns used in the join.

ulrich 816 posts Joined 09/09
14 Mar 2012

So the select you shared above was not fully what you did.

Adding columns to a distinct would clearly mean longer time to calculate... 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.