Archives of the TeradataForum

Message Posted: Tue, 15 Jan 2002 @ 11:03:04 GMT

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

Jay,

The other suggestions that you've received on this are good. Here is a clarification of some of the points raised;

- you can only have 1 PI (UPI or NUPI) per table, so you may have tried adding a USI to one/more tables, but I doubt that it would help.

- if you can provide the explain output for the query and the table definitions that would be good

- what size are the tables in the query and what is the maxspool figure for the user running the query?

- I have seen the idea about adding "b.ban=c.ban" before. It doesn't make any logical sense, but I've seen it work. What was meant (i think) is to change your query to use the following

select count(distinct(a.ban))
from jq8274.bill_charge a, jq8274.bill_adjustment b, jq8274.tax c
where a.ban = b.ban
and a.ban = c.ban
and b.ban = c.ban

- this is quite possibly a data distribution problem, with a large spool file being built on a single amp. Try the following steps;

(1) for the userid that is going to run the problem query, set the PeakSpool figures to zero (macro dbc.clearpeakdisk should do this for you).

(2) run the query and let it abend with the 2646 spool space error

(3) run the following query to check the distribution of spool space

        Select sum(peakspool),100.00*(avg(peakspool)/max(peakspool)) (named spoolpe)
        From dbc.diskspace
        Where databasename = ;

- in the previous query, the 'spoolpe' column is the parallel efficiency of the 'peak spool' figures. Ideally you want that figure to be close to 100%, if it's a lot lower then you're problem is due to a skewed spool file(s).

- on the face of it I can't think why this sort of query should give you skewed spool files (assuming that's what it is). What's the distribution of the tables prior to running this query? If they're skewed then that may be the problem.

Let's see where that takes you.

Cheers,

Dave

Ward Analytics Ltd: Information in motion (www.ward-analytics.com)

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