Home Page Icon
Home Page
Table of Contents for
The Brilliance of Netezza
Close
The Brilliance of Netezza
by John Nolan, Tom Coffing
The Brilliance of Netezza
Cover
Title Page
The Tera-Tom Genius Series
Tera-Tom- Author of over 50 Books
The Best Query Tool Works on all Systems
Trademarks and Copyrights
About Tom Coffing
About John Nolan
Table of Contents
Chapter 1 – How Netezza Works
What is Parallel Processing?
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
The Brilliance of Netezza
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
The Netezza Architecture
The Host
Netezza Disks
There Are Three Options for Backup and Restore
The IBM Netezza Family
Chapter 2 – A Chip Off The Old Block
Netezza Stores Data in Tables
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
Netezza Uses a Hash Formula
The Hash Map Determines which SPU will own the Row
The Hash Formula, Hash Map and SPU
Placing rows on the SPU
Placing rows on the SPU Continued
A Review of the Hashing Process
Like Data Hashes to the Same SPU
Distribution Keys
Distribution Key in WHERE Clause – 1 SPU Retrieve
A Non-Unique Distribution Key
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?
Fully Qualifying an Object
Checking for Skew
Educate the Business on the Business by Sharing the Model
Load Your Models and have the SQL Built Automatically
Netezza is Massively Parallel
Chapter 4 – Deep Dive Inside a Netezza Extent and Row
Netezza Performance – Three Things to Keep in Mind
How Netezza Allocates Data Storage
Extents and Zone Maps
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
An Update of Multiple Rows
How to Undo an Update of Multiple Rows
An Update Undo in Action
A Delete Example
A Delete Example Query
How to Undo a Delete
How to Undo a Delete In Action
An Insert Example
An Insert Example Query
How to Undo an Insert
How to Undo an Insert in Action
What is the Purpose of the GROOM Command?
The Groom Command Refreshes Zone Maps
Groom Command Syntax
Checking Groom Progress
Automatic Database Statistics
Drop Table, Truncate and Delete Compared
Chapter 5 – How Joins Work Internally
Netezza Join Quiz
Netezza Join Quiz Answer
Redistribution
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
The Joining of Two Tables
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
Chapter 6 – CTAS and CBT
CTAS (Create Table AS)
Using the CTAS (Create Table AS) Table For Co-Location
CTAS Facts
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
Groom Command Syntax
Checking Groom Progress
How to Know if your CBT Table Needs to be GROOMED?
Chapter 7 - Temporary Tables
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 in Action
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)
What Are External Tables?
External Tables Data Loading Formats
External Table Log Files
External Table Syntax
Exporting Data Off of Netezza into an External Table
Importing Data Into Netezza Using an External Table
What is the Problem Here?
Chapter 8 - Materialized Views
A Materialized View
Good Information to know about Materialized Views
Syntax/Example to Create a Materialized View
Replacing 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 Statistics
Syntax to Collect Express Statistics
The Basics on Collecting Statistics
Collecting Full Statistics
Just-In-Time (JIT) 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
Chapter 10 – Using nzsql
Using nzsql
The nzsql Command Prompt
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
Nzsql Internal Slash Options
Nzsql For External Tables
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
Chapter 11 – Creating Tables
CREATE TABLE Syntax
Viewing the DDL
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
Netezza Data Types in More Detail
Netezza Data Type Extensions
Reserved Names Within A Table
How To Query and See Non-Active Rows
Column Attributes
Constraints
Constraints
Column Level Constraint Example
Defining Constraints at the Table Level
Utilizing Default Values for a Table
CTAS (Create Table AS)
CTAS Facts
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
Altering A Table
Altering a Table Examples
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?
Netezza Users
Altering a Netezza User
Reserved Words to find out about a User
Using Limit to bring back a Sample
The Super-User is Named Admin
Starts with a Linux User, a Database User and A Database
Creating and Managing A Database
Creating a User
CREATE USER Syntax
Forcing a Password Change for a User
Reserved Words to find out about a User
Altering a Netezza 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
Creating and Managing a Group
Two Types of Permissions – Object and Admin
Netezza SQL Maximums
Admin Permissions
Object Permissions
Granting Object Permissions
Granting Admin Permissions
Table Permission Examples
Querying Cross-Database
Creating and Managing Synonyms
Creating a Table With Comments
Inserting Rows In A Table
Chapter 13 – Systems Views
_v_aggregate
_v_database
_v_datatype
_v_function
_v_group
_v_groupusers
_v_operator
_v_procedure
_v_relation_column_def
_v_relation_keydata
_v_sequence
_v_session
_v_table
_v_table_dist_map
_v_user
_v_usergroups
_v_view
(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
Chapter 14 – Explains
EXPLAIN
EXPLAIN Terms
EXPLAIN Terms Continued
EXPLAIN Syntax
EXPLAIN Example
EXPLAIN Verbose Example
EXPLAIN Example For A Join
EXPLAIN Verbose Example For A Join
Good Advice - Join Tables by the Same Distribution Key
EXPLAIN Verbose - Join With Matching Distribution Keys
EXPLAIN DISTRIBUTION
EXPLAIN PLANTEXT Example
EXPLAIN PLANGRAPH Example
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Chapter 14 – Explains
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset