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

Request Pricing