- 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
“If I have seen farther than others, it is because I was standing on the shoulders of giants.”
Isaac Newton
(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
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.