Course duration
- 3 days
Course Benefits
- Learn to gain a deeper knowledge and understanding of the Actian Matrix Architecture and SQL and how to write it.
Course Outline
- What is Columnar?
- What is Parallel Processing?
- The Basics of a Single Computer
- Data in Memory is fast as Lightning
- Parallel Processing Of Data
- A Table has Columns and Rows
- Rows are placed inside a Data Block
- Moving Data Blocks is Like Checking in Luggage
- Facts That Are Disturbing
- Why Columnar?
- Row Based Blocks vs. Columnar Based Blocks
- As Row-Based Tables Get Bigger, the Blocks Split
- Data Blocks Are Processed One at a Time per Unit
- Columnar Tables Store Each Column in Separate Blocks
- Visualize the Data - Rows vs. Columns
- The Architecture of Actian Matrix
- Matrix has Linear Scalability
- Distribution Styles
- Distribution Key Where the Data is Unique
- Another Way to Create a Table
- Distribution Key Where the Data is Non-Unique
- Even Distribution Key
- Matching Distribution Keys for Co-Location of Joins
- Big Table / Small Table Joins
- Fact and Dimension Table Distribution Key Designs
- Improving Performance by Defining a Sort Key
- Sort Keys Help Group By, Order by and Window Functions
- Each Block Comes With Metadata
- How Data Might Look On a Slice
- The ANALYZE Command Collects Statistics
- Matrix Automatically ANALYZES Some Create Statements
- What is a Vacuum?
- When is a Good Time to Vacuum?
- The VACUUM Command Grooms a Table
- The Matrix database catalog also needs periodic vacuuming and indexing
- Database Limits
- Creating a Database
- Creating a User
- Dropping a User
- Inserting Into a Table
- Renaming a Table or a Column
- Adding and Dropping a Column to a Table
- Best Practices for Table Design
- Converting Table Structures to Actian Matrix
- Converting Table Structures to Actian Matrix Finale
- Best Practices for Designing Tables
- Choose the Best Sort Key
- Each Block Comes With Metadata
- Creating a Sort Key
- Sort Keys Help Group By, Order by and Window Functions
- Choose a Great Distribution Key
- Distribution Key Where the Data is Unique
- Matching Distribution Keys for Co-Location of Joins
- Big Table / Small Table Joins
- Define Primary Key and Foreign Key Constraints
- Primary Key and Foreign Key Examples
- Use the Smallest Column Size When Creating Tables
- Use Date/Time Data Types for Date Columns
- Specify Redundant Predicates on the Sort Column
- Setting the statement_timeout to Abort Long Queries
- Systems Tables
- Actian Matrix System Tables
- Trouble Shooting Catalog Table pg_table_def
- Seeing the System Tables in your Nexus Tree
- Catalog Table pg_table_def
- Checking Tables for Skew (Poor Distribution)
- Checking All Statements That Used the Analyze Command
- Checking Tables for Skew (Poor Distribution)
- Checking for Details about the Last Copy Operation
- Checking When a Table Has Last Been Analyzed
- Checking For Column Information on a Table
- System tables for troubleshooting data loads
- Determining Whether a Query is writing to Disk
- Compression
- Compression Types
- Byte Dictionary Compression
- Delta Encoding
- Deflate Encoding - Lempel-Ziv-Oberhumer (LZO)
- Mostly Encoding
- Runlength encoding
- Text255 and Text32k Encodings
- Analyze Compression using xpx 'complyze'
- Analyze Results from xpx 'complyze'
- Copy
- Temporary Tables
- Create Table Syntax
- Basic Temporary Table Examples
- More Advanced Temporary Table Examples
- Advanced Temporary Table Examples
- Table Limits and CTAS
- Performing a Deep Copy
- Deep Copy Using the Original DDL
- Deep Copy Using A CTAS
- Deep Copy Using A Create Table LIKE
- Deep Copy by Creating a Temp Table and Truncating Original
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- Visualize This Derived Table
- Most Derived Tables Are Used To Join To Other Tables
- Multiple Ways to Alias the Columns in a Derived Table
- Our Join Example with a Different Column Aliasing Style
- Column Aliasing Can Default for Normal Columns
- CREATING a Derived Table using the WITH Command
- Our Join Example With the WITH Syntax
- WITH Statement That Uses a SELECT *
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- An Example of Two Derived Tables in a Single Query
- Connecting To Matrix via Nexus
- Explain
- Three Ways to Run an EXPLAIN
- EXPLAIN - Steps, Segments and Streams
- EXPLAIN Terms for Scans and Joins
- EXPLAIN Terms for Aggregation and Sorts
- EXPLAIN Terms for Set Operators and Miscellaneous Terms
- EXPLAIN Terms for Set Operators and Miscellaneous Terms
- EXPLAIN Example and the Cost
- EXPLAIN Example and the Rows
- EXPLAIN Example and the Width
- Simple EXPLAIN Example and the Costs
- EXPLAIN Join Example Using DS_BCAST_INNER
- EXPLAIN Join Example Using DS_DIST_NONE
- EXPLAIN Showing DS_DIST_NONE Visually
- EXPLAIN With a Warning
- EXPLAIN for Ordered Analytics Such as CSUM
- EXPLAIN for Scalar Aggregate Functions
- EXPLAIN for HashAggregate Functions
- EXPLAIN Using Limit, Merge and Sort
- EXPLAIN Using a WHERE Clause Filter
- EXPLAIN Using the Keyword Distinct
- EXPLAIN for Subqueries
- Basic SQL Functions
- Finding the Current Schema on the Leader Node
- Getting Things Setup in Your Search Path
- Five Details You Need To Know About the Search_Path
- Introduction
- SELECT * (All Columns) in a Table
- SELECT Specific Columns in a Table
- Commas in the Front or Back?
- Place your Commas in front for better Debugging Capabilities
- Sort the Data with the ORDER BY Keyword
- ORDER BY Defaults to Ascending
- Use the Name or the Number in your ORDER BY Statement
- Two Examples of ORDER BY using Different Techniques
- Changing the ORDER BY to Descending Order
- NULL Values sort First in Ascending Mode (Default)
- NULL Values sort Last in Descending Mode (DESC)
- Major Sort vs. Minor Sorts
- Multiple Sort Keys using Names vs. Numbers
- Sorts are Alphabetical, NOT Logical
- Using A CASE Statement to Sort Logically
- How to ALIAS a Column Name
- A Missing Comma can by Mistake become an Alias
- Comments using Double Dashes are Single Line Comments
- Comments for Multi-Lines
- Comments for Multi-Lines as Double Dashes Per Line
- A Great Technique for Comments to Look for SQL Errors
- The WHERE Clause
- Using Limit to bring back a Sample
- Using Limit with an Order by Statement
- The WHERE Clause limits Returning Rows
- Using a Column ALIAS throughout the SQL
- Double Quoted Aliases are for Reserved Words and Spaces
- Character Data needs Single Quotes in the WHERE Clause
- Character Data needs Single Quotes, but Numbers Don't
- NULL means UNKNOWN DATA so Equal (=) won't Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- NULL is UNKNOWN DATA so NOT Equal won't Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- Using Greater Than or Equal To (>=)
- AND in the WHERE Clause
- Troubleshooting AND
- OR in the WHERE Clause
- Troubleshooting Or
- Troubleshooting Character Data
- Using Different Columns in an AND Statement
- What is the Order of Precedence?
- Using Parentheses to change the Order of Precedence
- Using an IN List in place of OR
- The IN List is an Excellent Technique
- IN List vs. OR brings the same Results
- Using a NOT IN List
- A Technique for Handling Nulls with a NOT IN List
- Another Technique for Handling Nulls with a NOT IN List
- BETWEEN is Inclusive
- NOT BETWEEN is Also Inclusive
- LIKE command Underscore is Wildcard for one Character
- LIKE Command Works Differently on Char Vs Varchar
- The Ilike Command Is NOT Case Sensitive
- Troubleshooting LIKE Command on Character Data
- Introducing the TRIM Command
- Numbers are Right Justified and Character Data is Left
- An Example of Data with Left and Right Justification
- A Visual of CHARACTER Data vs. VARCHAR Data
- Use the TRIM command to remove spaces on CHAR Data
- Like and Your Escape Character of Choice
- Like and the Default Escape Character
- Similar To Operators
- Similar To Example with Lower Case Letters
- Similar To Example with Lower and Upper Case Letters
- Similar To Example with Multiple Occurrences
- Multiple Occurrences Must Be Consecutive
- Distinct Vs Group By AND TOP
- The Distinct Command
- Distinct vs. GROUP BY
- TOP Command
- TOP Command is brilliant when ORDER BY is used!
- What is the Difference between TOP and LIMIT?
- Aggregation
- The 3 Rules of Aggregation
- There are Five Aggregates
- Troubleshooting Aggregates
- GROUP BY when Aggregates and Normal Columns Mix
- GROUP BY delivers one row per Group
- GROUP BY Dept_No or GROUP BY 1 the same thing
- Limiting Rows and Improving Performance with WHERE
- WHERE Clause in Aggregation limits unneeded Calculations
- Keyword HAVING tests Aggregates after they are Totaled
- Keyword HAVING is like an Extra WHERE Clause for Totals
- Join Functions
- A Two-Table Join Using Traditional Syntax
- A two-table join using Non-ANSI Syntax with Table Alias
- You Can Fully Qualify All Columns
- A two-table join using ANSI Syntax
- Both Queries have the same Results and Performance
- LEFT OUTER JOIN
- LEFT OUTER JOIN Results
- Left Outer Joins Compatible with Oracle
- RIGHT OUTER JOIN
- RIGHT OUTER JOIN Example and Results
- Right Outer Joins Compatible with Oracle
- Right Outer Joins Compatible with Oracle
- FULL OUTER JOIN
- FULL OUTER JOIN Results
- Which Tables are the Left and which are the Right?
- INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional AND Clause
- OUTER JOIN with Additional AND Clause Results
- The DREADED Product Join
- The DREADED Product Join Results
- The Horrifying Cartesian product Join
- The ANSI Cartesian Join will ERROR
- The CROSS JOIN
- The CROSS JOIN Answer Set
- The Self Join
- The Self Join with ANSI Syntax
- How would you join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- Date Functions
- Current_Date
- TIMEOFDAY ()
- SYSDATE Returns a Timestamp with Microseconds
- GETDATE Returns a Timestamp without Microseconds
- Add or Subtract Days from a date
- The ADD_MONTHS Command Returns a Timestamp
- The ADD_MONTHS Command with Trunc Removes Time
- ADD_MONTHS Command to Add 1-Year or 5-Years
- Dateadd Function and Add_Months Function are Different
- The EXTRACT Command
- EXTRACT from DATES and TIME
- EXTRACT with DATE and TIME Literals
- EXTRACT of the Month on Aggregate Queries
- The Datediff command
- The Datediff Function on Column Data
- The Date_Part Function Using a Date
- The Date_Part Function Using a Time
- Date_Part Abbreviations
- The to_char command
- Conversion Functions
- Conversion Function Templates
- Conversion Function Templates Continued
- Formatting a Date
- A Summary of Math Operations on Dates
- Using a Math Operation to find your Age in Years
- Date Related Functions
- A Side Title example with Reserved Words as an Alias
- Implied Extract of Day, Month and Year
- DATE_PART Function
- DATE_PART Function using an ALIAS
- DATE_TRUNC Function
- DATE_TRUNC Function using TIME
- MONTHS_BETWEEN Function
- MONTHS_BETWEEN Function in Action
- ANSI TIME
- ANSI TIMESTAMP
- Matrix TIMESTAMP Function
- Matrix TO_TIMESTAMP Function
- Matrix NOW () Function
- Matrix TIMEOFDAY Function
- Matrix AGE Function
- Time Zones
- Setting Time Zones
- Using Time Zones
- Intervals for Date, Time and Timestamp
- Using Intervals
- Troubleshooting the Basics of a Simple Interval
- Interval Arithmetic Results
- A Date Interval Example
- A Time Interval Example
- A DATE Interval Example
- A Complex Time Interval Example using CAST
- A Complex Time Interval Example using CAST
- The OVERLAPS Command
- An OVERLAPS Example that Returns No Rows
- The OVERLAPS Command using TIME
- The OVERLAPS Command using a NULL Value
- OLAP Functions
- CSUM
- CSUM - The Sort Explained
- CSUM - Rows Unbounded Preceding Explained
- CSUM - Making Sense of the Data
- CSUM - Making Even More Sense of the Data
- CSUM - The Major and Minor Sort Key(s)
- Reset with a PARTITION BY Statement
- PARTITION BY only Resets a Single OLAP not ALL of them
- ANSI Moving Window is Current Row and Preceding n Rows
- How ANSI Moving SUM Handles the Sort
- Moving SUM every 3-rows Vs a Continuous Average
- Partition by Resets an ANSI OLAP
- Moving Average
- The Moving Window is Current Row and Preceding
- How Moving Average Handles the Sort
- Moving Average every 3-rows Vs a Continuous Average
- Partition by Resets an ANSI OLAP
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK () OVER and PARTITION BY
- RANK () OVER and LIMIT
- PERCENT_RANK () OVER
- PERCENT_RANK () OVER with 14 rows in Calculation
- PERCENT_RANK () OVER with 21 rows in Calculation
- COUNT OVER for a Sequential Number
- The MAX OVER Command
- MAX OVER with PARTITION BY Reset
- The MIN OVER Command
- The Row_Number Command
- Standard Deviation Functions Using STDDEV / OVER
- Standard Deviation Functions and STDDEV / OVER Syntax
- STDDEV / OVER Example
- VARIANCE / OVER Syntax
- Variance Functions Using VARIANCE / OVER
- Using VARIANCE with PARTITION BY Example
- Using FIRST_VALUE and LAST_VALUE
- Using FIRST_VALUE
- Using LAST_VALUE
- Using LAG and LEAD
- Using LEAD
- Using LEAD With and Offset of 2
- Using LAG
- Using LAG with an Offset of 2
- Temporary Tables
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- Visualize This Derived Table
- Most Derived Tables Are Used To Join To Other Tables
- Multiple Ways to Alias the Columns in a Derived Table
- Our Join Example with a Different Column Aliasing Style
- Column Aliasing Can Default for Normal Columns
- CREATING a Derived Table using the WITH Command
- Our Join Example With the WITH Syntax
- WITH
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- An Example of Two Derived Tables in a Single Query
- Create Table Syntax
- Basic Temporary Table Examples
- More Advanced Temporary Table Examples
- Advanced Temporary Table Examples
- Performing a Deep Copy
- Deep Copy Using the Original DDL
- Deep Copy Using A CTAS
- Deep Copy Using A Create Table LIKE
- Deep Copy by Creating a Temp Table and Truncating Original
- Sub-query Functions
- An IN List is much like a Subquery
- An IN List Never has Duplicates - Just like a Subquery
- An IN List Ignores Duplicates
- The Subquery
- The Three Steps of How a Basic Subquery Works
- These are Equivalent Queries
- The Final Answer Set from the Subquery
- Should you use a Subquery or a Join?
- The Basics of a Correlated Subquery
- The Top Query always runs first in a Correlated Subquery
- Correlated Subquery Example vs. a Join with a Derived Table
- How the Double Parameter Subquery Works
- More on how the Double Parameter Subquery Works
- How to handle a NOT IN with Potential NULL Values
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
- The Correlated NOT Exists Answer Set
- Substrings and Positioning Functions
- The TRIM Command trims both Leading and Trailing Spaces
- A Visual of the TRIM Command Using Concatenation
- Trim and Trailing is Case Sensitive
- How to TRIM Trailing Letters
- The SUBSTRING Command
- How SUBSTRING Works with NO ENDING POSITION
- Using SUBSTRING to move backwards
- How SUBSTRING Works with a Starting Position of -1
- How SUBSTRING Works with an Ending Position of 0
- The POSITION Command finds a Letters Position
- Using the SUBSTRING to Find the Second Word On
- Concatenation
- Concatenation and SUBSTRING
- Four Concatenations Together
- Troubleshooting Concatenation
- Declaring a Cursor
- Interrogating the Data
- The NULLIF Command
- The ISNULL, NVL and COALESCE Commands
- The ISNULL, NVL and COALESCE more examples
- The COALESCE Answer Set
- The Basics of CAST (Convert and Store)
- Some Great CAST (Convert and Store) Examples
- The Basics of the CASE Statements
- The Basics of the CASE Statement
- Valued Case Vs. A Searched Case
- When an ELSE is present in CASE Statement
- When an Alias is NOT used in a CASE Statement
- Combining Searched Case and Valued Case
- Nested Case
- Put a CASE in the ORDER BY
- View Functions
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Creating a View to Join Tables Together
- You Select From a View
- Basic Rules for Views
- An ORDER BY Example Inside of a View
- An ORDER BY Inside of a View that is Queried Differently
- Creating a View with Ordered Analytics
- Creating a View with the TOP Command
- Creating a View with the LIMIT Command
- Altering a Table
- Altering a Table after a View has been created
- A View that Errors after an ALTER
- Troubleshooting a View
- Updating Data in a Table through a View
- Set Operators Functions
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION Explained Logically
- EXCEPT Explained Logically
- EXCEPT Explained Logically
- Minus Explained Logically
- Minus Explained Logically
- Testing Your Knowledge
- Testing Your Knowledge
- An Equal Amount of Columns in both SELECT List
- Columns in the SELECT list should be from the same Domain
- The Top Query handles all Aliases
- The Bottom Query does the ORDER BY (a Number)
- Great Trick: Place your Set Operator in a Derived Table
- UNION Vs UNION ALL
- A Great Example of how EXCEPT works
- Statistical Aggregate Functions
- The Stats Table
- STDDEV
- Casting STDDEV_SAMP and SQRT (VAR_SAMP)
- The STDDEV_POP Function
- A STDDEV_POP Example
- The STDDEV_SAMP Function
- A STDDEV_SAMP Example
- The VAR_POP Function
- A VAR_POP Example
- The VAR_SAMP Function
- A VAR_SAMP Function
- Nexus
- Nexus is Now Available on the Microsoft Azure Cloud
- Nexus Queries Every Major System
- Setup of Nexus is as easy as pie
- Setup of Nexus is a Easy as 1, 2, 3
- Nexus Data Visualization
- Nexus Data Visualization
- Nexus Data Visualization Shows What Tables Can Be Joined
- Nexus is doing a Five-Table Join
- Nexus Generates the SQL Automatically
- Nexus Delivers the Report
- Cross-System Joins from Teradata, Oracle and SQL Server
- The Tab of the Super Join Builder
- The 9 Tabs of the Super Join Builder - Objects Tab 1
- Selecting Columns in the Objects Tab
- The 9 Tabs of the Super Join Builder - Columns Tab 2
- Removing Columns from the Report in the Columns Tab
- The 9 Tabs of the Super Join Builder - Sorting Tab 3
- The 9 Tabs of the Super Join Builder - Joins Tab 4
- The 9 Tabs of the Super Join Builder - Where Tab 5
- Using the WHERE Tab For Additional WHERE or AND
- The 9 Tabs of the Super Join Builder - SQL Tab 6
- The 9 Tabs of the Super Join Builder - Answer Set Tab 7
- The 9 Tabs of the Super Join Builder - Analytics Tab 9
- Analytics Tab
- Analytics Tab - OLAP Example
- Analytics Tab - OLAP Example of SQL Generated
- Analytics Tab - Grouping Sets Example
- Analytics Tab - Grouping Sets Answer Set
- Nexus Data Movement
- Moving a Single Table to a Different System
- The Single Table Data Movement Screen
- Moving an Entire Database to a Different System
- The Database Mover Screen
- The Database Mover Options Tab
- Converting DDL Table Structures
- Converting DDL Table Structures
- Converting DDL Table Structures
- Hound Dog Compression
- Hound Dog Compression on Teradata
Each student will receive a comprehensive set of materials, including course notes and all the class examples.
Instructor-led courses are offered via a live Web connection, at client sites throughout Europe, and at our Geneva Training Center.