Contents
The Basics of a Single Computer
Netezza Parallel Processes Data
Netezza is Born to be Parallel
Starts with a Linux User, a Database User and A Database
Each SPU holds a Portion of Every Table
The Rows of a Table are Spread Across All SPUs
Compress Engine II – Adaptive Stream Compression
FPGA Card and Zone Maps – The Netezza Secret Weapon
How Data Might Look Like on a SPU
Question – How Many Blocks Move Into Memory?
Answer – How Many Blocks Move Into Memory?
Quiz – Master that Query With the Zone Map
Answer to Quiz – Master that Query With the Zone Map
Netezza has Linear Scalability
There Are Three Options for Backup and Restore
Chapter 2 – A Chip Off The Old Block
Each SPU is Assigned Specific Rows
Each SPU Organizes the Rows inside a Data Block (Extent)
SPUs Must Transfer Their Data Blocks to Memory
As Tables Get Bigger the SPU uses Multiple Extents
SPUs Process A Table One Block at a Time
The Slowest Processing is a Full Table Scan
The FPGA Card and the Zone Maps Eliminate Extents
The FPGA Card and the Zone Map Enlightenment
Netezza Systems Can Grow Forever
Chapter 3 – How Netezza Distributes the Data
Netezza Tables - Distribution Key or Random Distribution
Table CREATE Examples with 4 different Distribution Keys
The Hash Map Determines which SPU will own the Row
The Hash Formula, Hash Map and SPU
Placing rows on the SPU Continued
A Review of the Hashing Process
Like Data Hashes to the Same SPU
Distribution Key in WHERE Clause – 1 SPU Retrieve
Distribution Key in the WHERE Clause – 1 SPU Retrieve
A conceptual example of a Multi-Column Distribution Key
Distribution Key in the WHERE Clause – 1 SPU Retrieve
A conceptual example of a Table with Random Distribution
A Full Table Scan – or a Sequential Scan
What happens when you forget the Distribution Key?
Educate the Business on the Business by Sharing the Model
Load Your Models and have the SQL Built Automatically
Chapter 4 – Deep Dive Inside a Netezza Extent and Row
Netezza Performance – Three Things to Keep in Mind
How Netezza Allocates Data Storage
How Data Might Look in an Extent
Why Dates Are Generally Not Good Distribution Keys
When a Table is Created, a Table Header is Created
Every SPU has the Exact Same Tables
All Netezza Tables are spread across All SPUs
The Table Header and the Data Rows are Stored Separately
A SPU Stores Rows of a Table inside a Data Block (Extent)
To Read Rows, a SPU Moves the Data Block into Memory
A Full Table Scan Means All SPUs must Read All Rows
The “Achilles Heel”, or Slowest Process, is Block Transfer
Each Table has a Distribution Key
A Query Using the Distribution Key uses a Single SPU
As Rows are Added, Another Extent is Added
A Full Table Scan Means All SPUs Read All Blocks
Distribution Key Query uses One SPU
Using a CTAS to Improve Zone Map Selectivity
How A CTAS with ORDER BY Improves Queries
Each SPU Can Have Many Blocks for a Single Table
A Full Table Scan Means All SPUs Read All Blocks
Quiz – How Many Blocks Move into SPU Memory?
Answer – How Many Blocks Move into SPU Memory?
Netezza Rowid, CreateXid, DeleteXid
How to Undo an Update of Multiple Rows
How to Undo a Delete In Action
How to Undo an Insert in Action
What is the Purpose of the GROOM Command?
The Groom Command Refreshes Zone Maps
Drop Table, Truncate and Delete Compared
Chapter 5 – How Joins Work Internally
Duplication of the Smaller Table across All-SPUs
If the Join Condition is the Distribution Key no Movement
Matching Rows landed on SPU because of Distribution Keys
What if the Join Condition Columns are Not Distribution Keys
Quiz – Redistribute the Employees by their Dept_No
Quiz – Employees’ Dept_No landed on SPU with Matches
When Rows are on the same SPU they can be Joined
Quiz – Redistribute the Orders to the Proper SPU
Answer to Redistribute the Employees by their Dept_No Quiz
A Visual of the Join in Action
Netezza Moves Joining Rows to the Same SPU
Imagine Joining Two Random Distribution Tables
Both Tables are Redistributed to Join Rows on the Same SPU
How do you join if One Table is Big and One Table is Small?
Duplicate the Small Table on Every SPU (like a mirror)
What Could You Do If Two Tables Joined 1000 Times a Day?
Joining Two Tables with the same PK/FK Primary Index
A Join With No Redistribution or Duplication
Using the CTAS (Create Table AS) Table For Co-Location
Altering a CTAS Table to Rename It
FPGA Card and Zone Maps – The Netezza Secret Weapon
How A CTAS with ORDER BY Improves Queries
A CTAS Major Sort Benefits over the Minor Sort
A CBT (Cluster Based Table) Orders Data Without Precedence
A CBT (Cluster Based Table) in Theory
Creating a Cluster Based Table (CBT
Creating a Temp Table as a Cluster Based Table (CBT)
Comparing Extents That Are Sorted Vs. A CBT
Benefits Of A Cluster Based Table (CBT)
Altering a Cluster Based Table (CBT) back to a Normal Table
GROOM Command is Used to Physically Change the Table
After Creating a CBT, You Must GROOM The Table
What The GROOM Does for a Table
How to Know if your CBT Table Needs to be GROOMED?
There are Three Types of Temporary Tables
The Same Derived Query shown Three Different Ways
Most Derived Tables Are Used To Join To Other Tables
Our Join Example With A Different Column Aliasing Style
Our Join Example With The WITH Syntax
Syntax For Creating A Temporary Table
Creating and Populating a Temporary Table
A Temporary Table Can Be Used Again and Again
Alternative CREATE TEMPORARY TABLE Option
A CTAS Temp Table to Improve Zone Map Selectivity
Creating a Temp Table as a Cluster Based Table (CBT)
External Tables Data Loading Formats
Exporting Data Off of Netezza into an External Table
Importing Data Into Netezza Using an External Table
Chapter 8 - Materialized Views
Good Information to know about Materialized Views
Syntax/Example to Create a Materialized View
Zone Maps for Materialized Views
Materialized View Restrictions
Maintaining Materialized Views
Materialized View Best Practices
Chapter 9 – Collecting Statistics
The Basics on Collecting Statistics
Best Practices for Generating Statistics
Syntax to Collect Express Statistics
The Basics on Collecting Statistics
How Netezza Collects Statistics on Small Tables
How Netezza Collects Statistics on Medium Tables
How Netezza Collects Statistics on Large Tables
Generating Statistics using NzAdmin
You Cannot Generate Statistics Within a Begin-End Block
Exporting Variable and then Using nzsql
Connecting to another database through nzsql
Displaying SQL User Session Variables
Inserts, Updates and Deletes Show the Number of Rows
Running a SQL Query from the nzsql Command Line
Nzsql Options That Might Come In Handy
Why Would Anyone Use nzsql When They Can Use Nexus?
The Nexus Super Join Builder Shows Tables Visually
The Nexus Super Join Builder Builds the SQL Automatically
Netezza Tables - Distribution Key or Random Distribution
Table CREATE Examples with 4 different Distribution Keys
The Worst Mistake You Can Make For A Distribution Key
Good things to know about Table and Object Names
Netezza Data Types in More Detail
How To Query and See Non-Active Rows
Column Level Constraint Example
Defining Constraints at the Table Level
Utilizing Default Values for a Table
Using the CTAS (Create Table AS) Table For Co-Location
Altering a CTAS Table to Rename It
FPGA Card and Zone Maps – The Netezza Secret Weapon
How A CTAS with ORDER BY Improves Queries
A CTAS Major Sort Benefits over the Minor Sort
Drop Table, Truncate, and Delete Compared
Chapter 12 – Creating Databases and Users and Managing Them
Creating and Dropping a Netezza Database
How to Determine the Database you are in?
Reserved Words to find out about a User
Using Limit to bring back a Sample
Starts with a Linux User, a Database User and A Database
Creating and Managing A Database
Forcing a Password Change for a User
Reserved Words to find out about a User
How to Determine the Database you are in?
Fully Qualifying a Database, Schema, and Table
Options for Handling Invalid Schema Names
An Example of Setting enable_schema_dbo_check
Two Types of Permissions – Object and Admin
Creating and Managing Synonyms
Creating a Table With Comments
(System Administrators Only) _v_sys_index
(System Administrators Only) _v_sys_priv
(System Administrators Only) _v_sys_table
(System Administrators Only) _v_sys_user_priv
(System Administrators Only) _v_sys_view
EXPLAIN Verbose Example For A Join
Good Advice - Join Tables by the Same Distribution Key