- 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
- 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
John Nolan is the Director of Sales and Marketing for Coffing Data Warehousing. In his role with CoffingDW, he has orchestrated multiple strategic partnerships with major international technology and data warehousing companies. Through his leadership and sales, he has been instrumental in helping to bring the Nexus Query Chameleon to the largest data warehouses in the world. He is responsible for testing and key development of the Nexus across the globe.
John brings with him a wealth of sales and marketing experience from a diverse background including his time spent at Fidelity Investments where he became Series 7 and 63 licensed and TQL where he built and maintained a portfolio of clients across the US.
John is a graduate of Xavier University where he was captain of an Academic All-American swim team and obtained a BSBA in Marketing with a minor in Biology.
John is a native of Pittsburgh, PA but he currently resides in Cincinnati, OH with his lovely wife.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.