CODEWITHSUNDEEP
teradata
siva mallikarjun
siva mallikarjun

Reputation: 31

Executing SQL to get few rows from a huge table is throwing a error "No more spool space error"

 Select row_id from table_name
 Sample 20;

issuing no more spool space error...

Is there any query to get 20 arbitary rows from a table in less time ? Assume table is very huge one

Upvotes: 0

Views: 991

Answers (2)

siva mallikarjun
siva mallikarjun

Reputation: 31

Assuming

  • Table_T is table name
  • Table_v is view on that Table_T....

Following is the explain on query executed....

First we lock the table_T in view table_v for access

Next we will do an all Amp retrieve step from table in view table_v by way of an all rows scan with a condition of (table_T in view table_v.col2 is null) in to spool2(all_amps), which is built locally on all amps. The input table will not be cached in the memory, bit is eligible for synchronized scanning. The size of spool is estimated with high confidence to be 1 row (35 bytes). The estimated time for step is 2 minutes and 16 seconds

We do an all amps stat function step from spool2 by way of an all-rows scan in to spool5, which is redistributed by hash code to all amps. He result rows are put into spool1(group_amps), which is built locally on amps. The step is used to retrieve top 20 rows, then execute step4. The size is estimated with high confidence to be 1 row 41 bytes

We do an all-amps stat function step from spool2 (last use) by ways of an all row scan in to spool5 (last use) which is redistributed by hash code to all amps. The result rows are put in to spool1 (group_amps) which is built locally on the amps. This step is used to retrieve top 20 rows. The step is estimated with high confidence to be 1 row (41 bytes)

Finally we will send an END TRANSACTION to all amps involved

Content of spool1 are sent back to user

Upvotes: 0

ATS
ATS

Reputation: 184

This is pretty common depending on the primary index of the table. If you add a predicate using the primary index you should return results.

Just add a WHERE clause with the primary index in it to limit the results, and you should see results without a spool issue.

Upvotes: 0

Related Questions