![]() For example, are you selecting a couple of code/description pairs for which everyone knows the codes by heart? Getting rid of a couple of varchar(100) description columns can make a huge difference in spool usage over 100 million rows.Įliminate unneeded rows: To reduce the count of rows you’re working with, think about how the result set will be used. Reduce the number of rows and columns you’re working withĮliminate unneeded columns: Your first step is to carefully look through your select list and make sure you need all columns you’re retrieving. One limitation in many Teradata environments is that SQL power users often don’t have DDL rights or rights to procedural logic, so the above mentioned good practices don’t include building intermediate tables or processes. Generally, if the transaction tables you’re working with have ten to about a hundred million rows, with good SQL practices you can accomplish most of what you need to do within your assigned terabyte. A typical enterprise-level Teradata environment assigns one to 1.5 terabytes to power users. At the end of the post you’ll find volatile DDL that you can use to get the queries to run.Ĭontrary to how you feel when you get a spool space error, your DBA has likely assigned you plenty. For the second two tactics I’ll show working code. It is limited, in part, to keep your potentially runaway query from using up too much space and clogging up the system.Īfter briefly setting the stage, this post presents the top three tactics I use to avoid or overcome spool space errors. So, for example, if your query needs to build an intermediate table behind the scenes to sort or otherwise process before it hands over your result set, that happens in spool space. Roughly speaking, Teradata “spool” is the space DBAs assign to each user account as work space for queries. If you are a SQL developer or data analyst working with Teradata, it is likely you’ve gotten this error message: “Select Failed. Also see the related post More on “Select Failed.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |