
Actian Matrix Architecture and SQL
Course Length: 3 days
Delivery Methods:
Available as private class only
Course Overview
In this training class, students will learn the Actian Matrix Architecture and SQL starting at the most basic level and going to the most advanced level with many examples.
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
Class Materials
Each student will receive a comprehensive set of materials, including course notes and all the class examples.
Request a Private Class
- Private Class for your Team
- Online or On-location
- Customizable
- Expert Instructors