Netezza Temporary Table Space -

In IBM PureData System for Analytics, each disk is roughly equally divided into 3 partitions:1. The first partition holds the primary copy of one dataslice2. The second partition holds a mirror copy of a dataslice from a different disk.3. The temp partition is used to store data that is generated during query execution. Temporary tables, intermediary results, etc.

• When we use temporary tables, it is this third partition, the temp space, that we risk filling up.• It is important to only create the minimum number of temporary tables needed at a time. It is also important to only make the temporary tables as large as needed.• Temporary tables are local to a session rather than a specific query. This means that the table may be used repeatedly within a user session. That is the major difference between Temporary tables (multiple use) and derived tables (single use). A Temporary table is materialized in swap space. However, it is not discarded until the session ends or when the user manually drops it.

Storage layers

When explicitly creating large temporary tables used in JOINs it is recommended to GENERATE STATISTICS on the temporary table.



Meet Ananth Tirumanur. Hi there 👋

I work on projects in data science, big data, data engineering, data modeling, software engineering, and system design.

Connect with me:

My Resources:

Languages and Tools:

AWS, Bash, Docker, Elasticsearch, Git, Grafana, Hadoop, Hive, EMR, Glue, Athena, Lambda, Step Functions, Airflow/MWAA, DynamoDB, Kafka, Kubernetes, Linux, MariaDB, MySQL, Pandas, PostgreSQL, Python, Redis, Scala, SQLite