SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

ManualsBrandsHP ManualsServerHP Integrity NonStop H-Series

HP NonStop SQL/MX

Release 3.2.1 Query

Guide

Abstract

This guide describes how to understand query execution plans and write optimal

queries for an HP NonStop™ SQL/MX database. It is intended for database

administrators and application developers who use NonStop SQL/MX to query an

SQL/MX database and who have a particular interest in issues related to query

performance.

Product Version

NonStop SQL/MX Releases 3.2.1

Supported Release Version Updates (RVUs)

This publication supports J06.15 and all subsequent J-series RVUs and H06.26 and all

subsequent H-series RVUs, until otherwise indicated by its replacement publications.

Part Number Published

663851-003 February 2013

TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM

Summary of content (256 pages)

PAGE 1
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM HP NonStop SQL/MX Release 3.2.1 Query Guide Abstract This guide describes how to understand query execution plans and write optimal queries for an HP NonStop™ SQL/MX database. It is intended for database administrators and application developers who use NonStop SQL/MX to query an SQL/MX database and who have a particular interest in issues related to query performance. Product Version NonStop SQL/MX Releases 3.2.
PAGE 2
TP663851.fm Page 2 Wednesday, January 30, 2013 5:37 PM Document History Part Number Product Version Published 640323-001 NonStop SQL/MX Release 3.0 February 2011 663851-001 NonStop SQL/MX Release 3.1 October 2011 663851-002 NonStop SQL/MX Release 3.2 August 2012 663851-003 NonStop SQL/MX Release 3.2.
PAGE 3
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM Legal Notices  Copyright 2013 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license.
PAGE 4
TP663851.
PAGE 5
TP663851.fm Page i Wednesday, January 30, 2013 5:37 PM HP NonStop SQL/MX Release 3.2.1 Query Guide Index Examples Figures Tables Legal Notices What’s New in This Manual ix Manual Information ix New and Changed Information ix Changes to the H06.25/J06.14 manual: ix Changes to the SQL/MX 3.1 manual: x Changes to the H06.22/J06.11 manual: x Changes to the H06.20/J06.09 manual: x Changes to the H06.19/J06.08 Manual x Changes to the H06.16/J06.05 Manual xi Changes to the H06.
PAGE 6
TP663851.fm Page ii Wednesday, January 30, 2013 5:37 PM Contents 2. Accessing SQL/MX Data 2.
PAGE 7
TP663851.fm Page iii Wednesday, January 30, 2013 5:37 PM Contents 5. Forcing Execution Plans Getting Help for Visual Query Planner 4-14 Graphically Displaying Execution Plans 4-14 Reviewing Run-Time Statistics 4-21 Simple Query Example 4-22 Using Measure 4-22 5.
PAGE 8
TP663851.fm Page iv Wednesday, January 30, 2013 5:37 PM Contents 7. SQL/MX Operators 7.
PAGE 9
TP663851.fm Page v Wednesday, January 30, 2013 5:37 PM Contents 8.
PAGE 10
TP663851.
PAGE 11
TP663851.fm Page vii Wednesday, January 30, 2013 5:37 PM Contents Figure 8-2. Figure 8-3. Figure 8-4. Figure 8-5. Figure 8-6. Figure 8-7. Figure 8-8.
PAGE 12
TP663851.fm Page viii Wednesday, January 30, 2013 5:37 PM Contents HP NonStop SQL/MX Release 3.2.
PAGE 13
TP663851.fm Page ix Wednesday, January 30, 2013 5:37 PM What’s New in This Manual Manual Information Abstract This guide describes how to understand query execution plans and write optimal queries for an HP NonStop™ SQL/MX database. It is intended for database administrators and application developers who use NonStop SQL/MX to query an SQL/MX database and who have a particular interest in issues related to query performance. Product Version NonStop SQL/MX Releases 3.2.
PAGE 14
TP663851.fm Page x Wednesday, January 30, 2013 5:37 PM What’s New in This Manual Changes to the H06.25/J06.14 manual: Changes to the H06.25/J06.14 manual:            Added a new section, Improving Performance for the Left Join on page 1-19. Updated the section, Forcing an Access Path on page 2-11. Added a note to the section, Influencing the Optimizer to Use MDAM on page 2-16. Added a note on the page 5-1. Added a section Considerations for CONTROL QUERY SHAPE on page 5-18.
PAGE 15
TP663851.fm Page xi Wednesday, January 30, 2013 5:37 PM What’s New in This Manual Changes to the H06.20/J06.09 manual: Changes to the H06.20/J06.09 manual:    Updated the DISPLAY_EXPLAIN function with EXPLAIN statement on pages 1-8, 1-9, 1-11, 1-17, 1-18, 4-1, 4-2, 4-5, 4-6, 4-8, 5-2, 5-8, 8-17, 8-18, and 8-22. Updated 7, SQL/MX Operators to add new tokens to the existing operators and to provide an example for each operator.
PAGE 16
TP663851.fm Page xii Wednesday, January 30, 2013 5:37 PM What’s New in This Manual Changes to the H06.05 Manual Section New or Changed Information Section 3, Keeping Statistics Current Added information about HIST_SCRATCH_VOL. Section 4, Reviewing Query Execution Plans Clarified information about buffer size settings for PARTITION_ACCESS operators. Section 7, SQL/MX Operators Removed examples and corrected token information. HP NonStop SQL/MX Release 3.2.
PAGE 17
TP663851.fm Page xiii Wednesday, January 30, 2013 5:37 PM About This Manual This guide describes the use and formulation of queries, how to understand query execution plans, and how to affect the performance of NonStop SQL/MX databases.
PAGE 18
TP663851.fm Page xiv Wednesday, January 30, 2013 5:37 PM About This Manual Related Documentation Related Documentation This manual is part of the HP NonStop SQL/MX library of manuals. The following table describes the list of manuals: Introductory Guides SQL/MX Comparison Guide for SQL/MP Users Describes SQL differences between NonStop SQL/MP and NonStop SQL/MX. SQL/MX Quick Start Describes basic techniques for using SQL in the SQL/MX conversational interface (MXCI).
PAGE 19
TP663851.fm Page xv Wednesday, January 30, 2013 5:37 PM About This Manual Related Documentation Migration Guides NonStop NS-Series Database Migration Guide Describes how to migrate NonStop SQL/MX, NonStop SQL/MP, and Enscribe databases and applications to HP Integrity NonStop NS-series systems. Data Management Guides SQL/MX Data Mining Guide Describes the SQL/MX data structures and operations to carry out the knowledge-discovery process.
PAGE 20
TP663851.fm Page xvi Wednesday, January 30, 2013 5:37 PM About This Manual Notation Conventions Messages, and Glossary online help from the HP Software Depot at http://www.software.hp.com. For more information about downloading the online help, see the SQL/MX Release 3.2 Installation and Upgrade Guide.
PAGE 21
TP663851.fm Page xvii Wednesday, January 30, 2013 5:37 PM About This Manual General Syntax Notation italic computer type. Italic computer type letters within text indicate C and Open System Services (OSS) variable items that you supply. Items not enclosed in brackets are required. For example: pathname [ ] Brackets. Brackets enclose optional syntax items. For example: TERM [\system-name.
PAGE 22
TP663851.fm Page xviii Wednesday, January 30, 2013 5:37 PM About This Manual Change Bar Notation Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must enter as shown. For example: "[" repetition-constant-list "]" Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma.
PAGE 23
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM 1 Compiling and Executing a Query Use the information in this section to understand how your queries are compiled and executed.   How the Compiler Works on page 1-2 How the Executor Processes the Plan on page 1-21 Overview At the highest level, compiling and executing an SQL query with NonStop SQL/MX consists of two basic steps. 1. The SQL/MX compiler processes the query and produces a query execution plan.
PAGE 24
TP663851.fm Page 2 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query  How the Compiler Works Modifying the database (metadata) by adding columns to a table, splitting or merging partitions, and creating and dropping indexes For more information, see Improving Query Performance on page 1-6. How the Compiler Works To compile a query, the SQL/MX compiler needs information about the tables listed in the query and the query environment.
PAGE 25
TP663851.fm Page 3 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Parsing, Binding, and Normalizing Parsing, Binding, and Normalizing The initial steps in the compile process—parsing, binding, and normalizing—prepare the query for the optimizer. Before query optimization begins, the query tree that is produced by the parser is bound with information from the metadata. Certain query processing instructions in addition to default values needed for optimizing the query are processed.
PAGE 26
TP663851.fm Page 4 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Parsing, Binding, and Normalizing optimizer remembers not to pursue that solution further. Alternative solutions found by the optimizer go into the search space, where they are stored in search memory.  Principle of Optimality In this strategy, the optimizer starts at the top of the normalized query tree and breaks the optimization tasks into smaller tasks (or subtasks).
PAGE 27
TP663851.fm Page 5 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query  Query Plan Caching How the optimizer finds the solution. Implementation rules transform a logical expression into a semantically equivalent expression whose root node is a physical operator. Recursive application of implementation rules result in a query execution plan that consists only of physical operators.
PAGE 28
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance This feature improves the performance of the compiler when the plan can be produced from cache rather than through a full compilation. The queries that can be considered for plan caching include simple TP-style inserts, updates, deletes, selects, and joins. Two queries are considered equivalent for the purposes of caching if their canonical forms are the same.
PAGE 29
TP663851.fm Page 7 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query    Improving Query Performance The computation of the transitive closure of “=” predicates and rewrite of predicate factors based on it Canonical reorder of the tables in the query tree Constant folding. Constant folding is a compiler optimization technique where an expression, consisting only of constants, is evaluated at compile time.
PAGE 30
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query  Improving Query Performance WHERE predicate match: SELECT a FROM t WHERE col = :hvar  Target and source INSERT match: INSERT INTO t(col) VALUES (:hvar)  UPDATE match: UPDATE t SET col = :hvar ...     Set different default values. Default values can affect both compile and run time.
PAGE 31
TP663851.fm Page 9 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance You can also use the EXPLAIN statement OPTIONS 'f' command to display operators in the query. Check the OPT column for letter o entries, as the following table shows, to determine whether OLT optimization is enabled. LC -2 1 . RC -. . . OP -3 2 1 OPERATOR --------root partition_access insert OPT ----o o o DESCRIPTION -----------r CARD ------1.00E+0 1.00E+0 1.00E+0 T Note.
PAGE 32
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance Programming Manual for C and COBOL, SQL/MX Programming Manual for Java, and the SQL/MX Release 3.2 Management Manual have tips and guidelines to help identify potential performance problem areas. Using OR Operators in Predicates For a narrow subset of queries with OR operations, NonStop SQL/MX uses a feature called OR optimization.
PAGE 33
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query  Improving Query Performance The search condition is restricted to simple OR predicates: L_SUPPKEY = 2407 OR L_PARTKEY > 34567 Note. For execution plans that use OR optimization, the optimizer considers index-only access in addition to index-key lookup through a nested join. Index-only access, however, can be used only if the index contains all the columns referenced in the query.
PAGE 34
TP663851.fm Page 12 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance For the DDL to these examples, see the LINEITEM table in Example 1-1 on page 1-13. The primary key is L_ORDERKEY + L_LINENUMBER, and there are indexes on L_PARTKEY (LX1) and L_SUPPKEY (LX2).
PAGE 35
TP663851.fm Page 13 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance Example 1-1.
PAGE 36
TP663851.fm Page 14 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance Order of Evaluation of Predicates The SQL/MX compiler can select any order for evaluating the predicates based on the factors such as cost, cardinality, join type, access path, and so on. As a result, SQL/MX might not be able to retain the order in which the predicates appear in a query while executing them. SQL/MX might return different results depending on the order of evaluation.
PAGE 37
TP663851.fm Page 15 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance >>INSERT INTO STAFF_UC VALUES (_ucs2'E1', N'Alice'); --- 1 row(s) inserted. >>INSERT INTO WORKS_UC VALUES (_ucs2'E1', _ucs2'P3'); --- 1 row(s) inserted. >>INSERT INTO PROJ_UC VALUES (_ucs2'P3', _ucs2'Tampa'); --- 1 row(s) inserted. >>INSERT INTO PROJ_UC VALUES (_ucs2 X'4E7A', _ucs2 X'4E2D56FD53174EAC'); --- 1 row(s) inserted.
PAGE 38
TP663851.fm Page 16 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance FROM STAFF_UC WHERE STAFF_UC.EMPNUM IN (SELECT WORKS_UC.EMPNUM FROM WORKS_UC WHERE WORKS_UC.PNUM IN (SELECT PROJ_UC.PNUM FROM PROJ_UC WHERE ( PROJ_UC.PNUM < _UCS2 X'00FF' AND TRANSLATE (PROJ_UC.CITY USING UCS2TOISO88591) = 'Tampa' ) OR PROJ_UC.CITY = _UCS2 X'5929 6D25' ) ); --- SQL command prepared.
PAGE 39
TP663851.fm Page 17 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance table. This feature enhances query performance by reducing compilation and execution time. Note. The constraint based query pruning feature is supported only on systems running J06.08 and later J-series RVUs and H06.19 and later H-series RVUs. During query compilation, NonStop SQL/MX receives the inputs from the constraints that are defined on a table.
PAGE 40
TP663851.fm Page 18 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance Example 1-2. EXPLAIN statement OPTIONS ‘f’ Output for Query Using CHECK_CONSTRAINT_PRUNING >>showddl t1; CREATE TABLE CAT.SCH.T1 ( COL1 , COL2 INT DEFAULT NULL INT DEFAULT NULL ) LOCATION \DMR11.$DATA04.ZSDLPGGW.VBNG7V00 NAME DMR11_DATA04_ZSDLPGGW_VBNG7V00 ; ALTER TABLE CAT.SCH.T1 ADD CONSTRAINT CAT.SCH.CHK1 CHECK (CAT.SCH.T1.COL1 > 100) DROPPABLE ; --- SQL operation complete.
PAGE 41
TP663851.fm Page 19 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance Improving Performance for the Left Join In SQL/MX, the predicates on the outer table of the left join are pushed down to the outer table. Starting with SQL/MX release 3.2.
PAGE 42
TP663851.fm Page 20 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query Improving Query Performance But, DP2 might still perform the full scan if:   t1.c1 = 10 or t1.c1 = 20 t1.c1 IN (10,20) -- equals a) However, if MDAM is forced by CONTROL QUERY TABLE * MDAM 'ON', MDAM is forced on both T1 and T2. Based on correct statistics, the compiler may automatically select MDAM.
PAGE 43
TP663851.
PAGE 44
TP663851.fm Page 22 Wednesday, January 30, 2013 5:37 PM Compiling and Executing a Query How the Executor Processes the Plan The task model makes it easy to perform all internal operations asynchronously so that a single server thread can have multiple I/Os outstanding. This model also provides parallelism for both shared memory and distributed memory architectures. In-memory queues are used for communication, and exchange operators are used for distributed memory.
PAGE 45
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM 2 Accessing SQL/MX Data Access methods provide different degrees of efficiency in accessing data contained in key-sequenced tables and indexes. Use the information in this section to understand the various access methods used by NonStop SQL/MX:   Access Methods on page 2-1 MultiDimensional Access Method (MDAM) on page 2-11 Access Methods This subsection describes the access methods used by the SQL/MX compiler.
PAGE 46
TP663851.fm Page 2 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data Index-Only Access TABLE employee empnum first_name last_name deptnum jobcode salary 93 ROOT Base Table PARTITION_ACCESS FILE_SCAN_UNIQUE SELECT * From employee WHERE empnum = 93; QUERY PLAN VST021.vsd Storage-Key Approximate Cost The cost of retrieving information through a storage key depends on how many blocks of data you must access.
PAGE 47
TP663851.fm Page 3 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data Index-Only Access Index-only access is not used if any of these conditions are true:   The columns required by the query are not all included in the index. The query performs an update. Even if the index contains the column, the base table column (and any other index containing the column) also needs to be updated. The next figure shows a query that contains an indexed item (deptnum) in the WHERE clause.
PAGE 48
TP663851.fm Page 4 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data Alternate Index Access Index-Only Approximate Cost The approximate cost for index-only access is comparable to storage-key access. It might be better because typically index tables are smaller than base tables. Alternate Index Access In alternate index access, a join is made between the index and the base table (an index-base table join). Access is not made through the clustering index.
PAGE 49
TP663851.fm Page 5 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data Full Table Scan Alternate Index Access Approximate Cost Because alternate index access relies on a join between an index and a base table, the cost associated with alternate index access can be high and is chosen only when the cost of a full table scan is even higher. Full Table Scan In a full table scan, SQL reads the entire base table from beginning value to end value in storage-key order.
PAGE 50
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data Full Table Scan Begin Value SELECT * FROM employee; ROOT PARTITION_ACCESS FILE_SCAN Query Plan End Value TABLE employee VST024.vsd Full Table Scan Approximate Cost A full table scan can be significantly higher in cost than the other methods. Minimizing Full Table Scans Use the INTERACTIVE_ACCESS CONTROL QUERY DEFAULT when you want to minimize the number of expensive full table scans.
PAGE 51
TP663851.fm Page 7 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data   Full Table Scan Among the set of all plans considered, the optimizer chooses the set of plans with the minimum number of full table or index scans. Among the set of plans with a minimum number of full table or index scans, the optimizer chooses the plan with the lowest estimated cost.
PAGE 52
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data Full Table Scan This plan does not have any full table or index scans because both index T3_b and T1_c are used for key lookup for the predicates T3.b=5 and T1.c=T3.c, respectively. In comparison, this hash join plan has one index used for lookup and one index fully scanned: HJ / \ T3_b T1_c The only index used for lookup in the previous hash join plan is T3_b.
PAGE 53
TP663851.fm Page 9 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data Understanding Unexpected Access Paths Another plan with no full table or index scans would be: NJ / \ T4_c NJ / T1_c \ T1 Understanding Unexpected Access Paths Sometimes the optimizer does not choose the preferred or expected access path.
PAGE 54
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data Understanding Unexpected Access Paths original position. The cursor or set update will once again encounter the row and increment it by 10 percent. Selecting an index for an UPDATE query could result in a query plan that does not terminate when executed. Consider this query: UPDATE INVNTRY SET RETAIL_PRICE = RETAIL_PRICE * 1.1; The query requests that the price of all items in the INVNTRY table be increased by 10 percent.
PAGE 55
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data  MultiDimensional Access Method (MDAM) All columns to be updated have an equality predicate in the WHERE clause. For example: UPDATE INVNTRY SET RETAIL_PRICE = RETAIL_PRICE * 1.
PAGE 56
TP663851.fm Page 12 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data   Specifying MDAM Obtain good performance in situations where previously a full table scan was needed. To add rows to a table without suffering performance penalties. Specifying MDAM MDAM is enabled by default. Enable means that the SQL/MX compiler can choose to use MDAM if it will provide a better quality plan.
PAGE 57
TP663851.fm Page 13 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data  Comparing MDAM With Single Subset Access With CONTROL TABLE, you can control MDAM at the current process level or for a specific table or index. The * (asterisk) option specifies all tables. If you specify CONTROL TABLE * MDAM ‘OFF‘, MDAM is disabled for all tables and indexes. If you specify CONTROL TABLE * MDAM ‘ON‘, only MDAM access will be tried for all indexes and tables. Note.
PAGE 58
TP663851.fm Page 14 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data How MDAM Processes Queries As shown in the next figure, the scan of the single subset access starts with the begin value and finishes at the end value when the last row is read. To reduce the cost of reading N blocks, you could break the table up into a series of smaller ranges with a high potential for hits.
PAGE 59
TP663851.fm Page 15 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data   How MDAM Processes Queries Does not read the same row twice Maintains sort order Intervening Range Predicates An intervening range predicate occurs when another key column predicate follows the first predicate: A > 5 AND B = 2 MDAM processes range predicates by stepping through the existing values for the column on which the range has been specified. Data outside the bounds is not read from disk or handled in any way.
PAGE 60
TP663851.
PAGE 61
TP663851.fm Page 17 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data  Controlling the Number of Key Columns Used by MDAM Order your key columns by the columns that you access most frequently. If you frequently perform a query that uses department number, include the DEPTNUM column in your key-column index.
PAGE 62
TP663851.fm Page 18 Wednesday, January 30, 2013 5:37 PM Accessing SQL/MX Data MDAM’s Use of DENSE and SPARSE Algorithms followed by the executor. For example, if the optimizer chooses a DENSE algorithm and the executor finds the DENSE algorithm is inefficient for accessing the column, the executor adapts by switching to a SPARSE algorithm when it discovers that many values are missing. The executor chooses the appropriate algorithm for the density or sparseness of data.
PAGE 63
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM 3 Keeping Statistics Current When you update statistics, information about a table is updated in the histogram tables so that the information more accurately represents the current content and structure of the database.
PAGE 64
TP663851.fm Page 2 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Updating Histogram Statistics For the syntax of the UPDATE STATISTICS statement and for more information about histogram tables, see the SQL/MX Reference Manual. Note. The compiler uses default statistics if no updated statistics exist for the table and column. When the compiler uses default statistics, the execution plan provided might not be the optimal plan.
PAGE 65
TP663851.fm Page 3 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Updating Histogram Statistics Table 3-2 compares histogram statistics table information. Table 3-2. Histogram Statistics Tables Statistics SQL/MX Objects SQL/MP Objects Registration Registered in the same catalog.schema as the table. Registered in the catalog of the primary partition of the table. Location Located in the same catalog.schema as the table. Located in the same \node.$vol.subvol as the catalog.
PAGE 66
TP663851.fm Page 4 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Updating Histogram Statistics In Case 2, the index (E) is defined as nonunique, so the KEY is added to the end of the INDEX, and the index is INDEX+KEY: Case 2 KEY: (A, B, C) => (A, B, C), (A, B) INDEX: (E) nonunique => (E, A, B, C), (E, A, B), (E, A) RESULT: (A, B, C), (A, B), (E, A, B, C), (E, A, B), (E, A) In Case 3, because the index (E) is defined as a unique index, the KEY is not added to the end of the INDEX.
PAGE 67
TP663851.fm Page 5 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Sampling and UPDATE STATISTICS Analyzing the Possible Impact of Updating Statistics Depending on the size of the table, updating statistics can take longer than you would like. Consider updating statistics during the hours when peak performance is not required. If you want to preserve the existing query execution plan, be aware that updating statistics might cause the optimizer to choose a different plan.
PAGE 68
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Sampling and UPDATE STATISTICS created in the default volume specified by the _DEFAULTS define. For SQL/MP tables, a single partition is created on the volume specified in the HIST_SCRATCH_VOL default attribute. If the default attribute is not used, the temporary table is created in the same volume as the primary partition of the table.
PAGE 69
TP663851.fm Page 7 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Performance Issues and Accuracy in Sampling From SQL/MX 2.3.2 onwards, it is also possible to push the sampling operation down into DP2. As a result, the number of read and discarded records reduces, thereby improving the performance of the command. This technique is effective for low sampling percentages. Use the ALLOW_DP2_ROW_SAMPLING default attribute to control the sampling. The attribute values are SYSTEM, ON, and OFF.
PAGE 70
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Collecting Statistics for Multiple Columns These sampling options perform a full table scan prior to selecting the sample set: SAMPLE RANDOM x PERCENT SAMPLE PERIODIC x ROWS EVERY y ROWS These sampling options do not perform a full table scan to determine the sample set.
PAGE 71
TP663851.fm Page 9 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Testing the Results for SQL/MX Tables 4. Determine the effect of the UPDATE STATISTICS statement and optionally back out the generated histogram if necessary: a. In SQLCI, back up current histogram tables, if any: SQLCI> DUP histogrm, myogrm; SQLCI> DUP histints, myints; b. In MXCI, issue the UPDATE STATISTICS command for required column groups. c. In MXCI, recompile the query. d.
PAGE 72
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM Keeping Statistics Current Testing the Results for SQL/MX Tables d. Use EXPLAIN to review the cost information for your query. e. Use DISPLAY STATISTICS to determine which plan is better. f. If necessary, use the UPDATE STATISTICS CLEAR option to remove histograms for unwanted column groups. g.
PAGE 73
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM 4 Reviewing Query Execution Plans Use the information in this section to display and understand how your query is optimized. At a later time, you might use this information to make decisions about forcing execution plans, described in Section 5, Forcing Execution Plans.
PAGE 74
TP663851.fm Page 2 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Using the EXPLAIN Statement Shortcut 1. In MXCI, prepare the query: PREPARE FINDSAL FROM SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE SALARY > 40000.00; 2.
PAGE 75
TP663851.fm Page 3 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans The Optimizer and Executor For further information about creating ODBC data sources, see the ODBC Driver Manual for Windows. 3. In the top pane of the Visual Query Planner window, enter your query. 4. Select Explain > Get Explain Plan to display your query execution plan. The operator tree for the query execution plan appears in the lower left pane of the Visual Query Planner window.
PAGE 76
TP663851.fm Page 4 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Description of the EXPLAIN Function Results Column Name Data Type Description PLAN_ID LARGEINT Unique system-generated plan ID automatically assigned by SQL; generated at compile time. SEQ_NUM INT Sequence number of the current node in the operator tree; indicates the sequence in which the operator tree is generated. OPERATOR CHAR(30) Current node type.
PAGE 77
TP663851.fm Page 5 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Displaying Selected Columns of the Execution Plan IDLETIME An estimate of the number of seconds to wait for an event to happen. The estimate includes the amount of time to open a table or start an ESP process. PROBES The number of times the operator will be executed. Usually, this value is 1, but can be greater when you have, for example, an inner scan of a nested-loop join.
PAGE 78
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Extracting EXPLAIN Output From Embedded SQL Programs Four nodes (operators) appear in the plan: FILE_SCAN, PARTITION_ACCESS, SORT, and ROOT. Operators are listed under Section 7, SQL/MX Operators. This query requires a full table scan (FILE_SCAN), because the query selected all employees, and required a SORT, because employee names are ordered by salary. Four rows have been selected.
PAGE 79
TP663851.fm Page 7 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Using the EXPLAIN Statement to Review the Execution Plan LEFT_CHILD_SEQ_NUM RIGHT_CHILD_SEQ_NUM TNAME CARDINALITY OPERATOR_COST TOTAL_COST DETAIL_COST DESCRIPTION ? EXPL_NAME__ 211977924124011276 1 FILE_SCAN ? ? SAMDBCAT.PERSNL.EMPLOYEE 2.0000000E+000 2.0646531E-002 2.0646531E-002 CPU_TIME: 0.000287 IO_TIME: 0.020647 MSG_TIME: 0 IDLETIME: 0 PROBES: 1 scan_type: file_scan SAMDBCAT.PERSNL.
PAGE 80
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Using the EXPLAIN Statement to Review the Execution Plan samdbcat.persnl.employee where salary > 40000.00 and jobcode=450; return select_list: indexcol(SAMDBCAT.PERSNL.EMPLOYEE.LAST_NAME), indexcol(SAMDBCAT.PERSNL.EMPLOYEE.FIRST_NAME), indexcol(SAMDBCAT.PERSNL.EMPLOYEE.SALARY) --- SQL operation complete.
PAGE 81
TP663851.fm Page 9 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Optimization Tips Column Name EXPLAIN statement Output TOTAL_COST 2.0646531-002 DETAIL_COST CPU_TIME: 0.000287 IO_TIME: 0.020647 MSG_TIME: 0 IDLETIME: 0 PROBES: 1 DESCRIPTION scan_type: file_scan SAMDBCAT.PERSNL.EMPLOYEE scan_direction: forward key_type: simple lock_mode: not specified access_mode: not specified columns_retrieved: 6 fast_scan: used fast_replydata_move: used key_columns: indexcol(SAMDBCAT.PERSNL.
PAGE 82
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Optimization Tips For OLTP queries that use OLT optimization, the settings are not relevant because SQL determines the buffer size depending on the row size. For non-OLT optimized queries that return or insert multiple rows, the buffer size is set to the default value to maximize the number of rows returned. If the actual number of rows is smaller than the default value, only the actual bytes are shipped.
PAGE 83
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Optimization Tips 2 The compiler uses a combination of heuristics and limited search space enumeration. In general, this level produces good quality plans, although it might miss a globally optimal plan. The compilation time is significantly shorter in comparison with the higher optimization levels (3 and 5). 3 (default) This default optimization level is recommended for general use.
PAGE 84
TP663851.fm Page 12 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Optimization Tips compound statement, so the strategy of placing a single SELECT statement between a BEGIN and END statement to push down the SELECT to the DAM process does not work. The first statement contained in the compound statement:   Cannot be an INSERT statement with a VALUES clause. If the statement is a SELECT or an UPDATE statement, it cannot have rowset host variables in the WHERE clause.
PAGE 85
TP663851.fm Page 13 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Verifying DAM Access Use the OFF setting to force NonStop SQL/MX to bring up all ESPs on the local system only.
PAGE 86
TP663851.fm Page 14 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Using the Visual Query Planner used. Operators appearing below the PARTITION_ACCESS operator are executing in DAM. Using the Visual Query Planner Visual Query Planner is a Microsoft Windows NT GUI application that enables you to extract and display query execution plans generated by the SQL/MX optimizer for DML statements.
PAGE 87
TP663851.fm Page 15 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Graphically Displaying Execution Plans 2. Select Explain > Connect to ODBC to connect to your ODBC data source. Note. MXCS must be running on the server before you can connect Visual Query Planner to MXCS. For more information about connecting to MXCS, see the SQL/MX Connectivity Services Manual.
PAGE 88
TP663851.fm Page 16 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Graphically Displaying Execution Plans Figure 4-2. Visual Query Planner vst501.vsd 5. Select File > Save to save your query execution plan. The default name for the session appears in the Save dialog box. If you choose to provide a different name for the query execution plan, the new name appears on the title bar.
PAGE 89
TP663851.fm Page 17 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Graphically Displaying Execution Plans Cut text within the edit window Copy text within the edit window Paste text within the edit window Connect to an ODBC source Disconnect from the ODBC data source Execute the query Open help These tasks are also available from the menus.
PAGE 90
TP663851.fm Page 18 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Graphically Displaying Execution Plans The Operator Properties dialog box, shown next, provides three tabs:    Node details Cost details Description VST504.vsd Notice the pushpin and ? icon in the upper left corner of the dialog box. You can pin the dialog box open by selecting the pushpin so that you can select on another operator without having to reopen the Properties dialog box.
PAGE 91
TP663851.fm Page 19 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans        Graphically Displaying Execution Plans Node Seq. Num. is 1, which indicates that the node was first in sequence during optimization. Left Child Seq. Num. is 0, which indicates that this is not a join. Right Child Seq. Num. is 0, which indicates that this is not a join. If both the left and right child sequence numbers contain values, the node is a join. Table Name provides the catalog.schema.
PAGE 92
TP663851.fm Page 20 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Graphically Displaying Execution Plans Reviewing the Cost Details The items listed in the Cost Details tab are the same as those items described in the DETAIL_COST column of the EXPLAIN function results. For a detailed description of each of these items, see Description of the EXPLAIN Function Results on page 4-3. VST505.vsd HP NonStop SQL/MX Release 3.2.
PAGE 93
TP663851.fm Page 21 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Reviewing Run-Time Statistics Additional Table Information The Description tab provides additional table information, including key columns, scan type, scan direction, and so on. vst506.vsd The token descriptions for each operator are described in Section 7, SQL/MX Operators. Reviewing Run-Time Statistics NonStop SQL/MX provides statistics for an executed query.
PAGE 94
TP663851.fm Page 22 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Simple Query Example Simple Query Example This query selects all rows and columns from the EMPLOYEE table: >>prepare q1 from +>select * from employee; --- SQL command prepared. >>execute Q1; The query returns this result: EMPNUM ------ FIRST_NAME ----------1 ROGER . 568 JESSICA LAST_NAME ---------GREEN . CRINER DEPTNUM ------9000 . 3500 JOBCODE SALARY -------- -----100 175500.00 . . 300 39500.
PAGE 95
TP663851.
PAGE 96
TP663851.fm Page 24 Wednesday, January 30, 2013 5:37 PM Reviewing Query Execution Plans Using Measure transactions is performing poorly, you can execute each transaction separately, measure it, and compare performance among the group of transactions.
PAGE 97
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM 5 Forcing Execution Plans Use the information in this section to make decisions about forcing query execution plans.
PAGE 98
TP663851.fm Page 2 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans   Checklist for Forcing Plans Testing purposes. You might want to try different execution scenarios than those provided by the optimizer. The optimizer might not have found the optimal plan. This situation could occur because of lack of recent statistics and calibration, data skew, or aggressive pruning. In these situations, forcing a plan gives you the power to control the plan shape.
PAGE 99
TP663851.fm Page 3 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Reviewing the Optimized Plan Reviewing the Optimized Plan The next example shows the EXPLAIN output for the optimized sample query. While this output simply shows the operators and sequence numbers, you will also want to select the costing columns to review the estimated costs of each operation. >>SET SCHEMA samdbcat.persnl; >>PREPARE s1 FROM SELECT employee.last_name, employee.first_name, >+dept.manager, employee.
PAGE 100
TP663851.fm Page 4 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Reviewing the Optimized Plan Now, view the output in a more visual tree format that shows the parent and child relationships. The sequence numbers and table names are also shown in Figure 5-1. Figure 5-1.
PAGE 101
TP663851.fm Page 5 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Translating the Operator Tree to Text Format vst601.vsd Translating the Operator Tree to Text Format You must translate the operator tree into a text format. The text format used to represent the tree to the CONTROL QUERY SHAPE statement is written in a LISP-like format. (LISP stands for list processor, a high-level programming language.
PAGE 102
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Using Visual Query Planner to Get the Shape forward, blocks_per_access 1, mdam off)),partition_access( scan(path 'SAMDBCAT.PERSNL.JOB', forward, blocks_per_access 1 , mdam off))),partition_access(scan(path 'SAMDBCAT.PERSNL.DEPT', forward, blocks_per_access 1 , mdam off))); --- SQL operation complete. Use the additional command, SET SHOWSHAPE, to display the execution plans in effect.
PAGE 103
TP663851.fm Page 7 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Manually Writing the Shape vst602.vsd You can make changes to the shape and then force a new shape by selecting the Get Shape icon (the middle icon in the upper left corner). In addition, from the Explain menu, you must execute Get Explain Plan to see the revised plan. For more information about the SHOWSHAPE and SET SHOWSHAPE commands, see the SQL/MX Reference Manual.
PAGE 104
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Writing the Forced Shape Statement Using the sample query, you can translate the operator tree into this text format: ROOT(HYBRID_HASH_JOIN (HYBRID_HASH_JOIN (PARTITION_ACCESS (FILE_SCAN_UNIQUE), PARTITION_ACCESS(FILE_SCAN)), PARTITION_ACCESS (FILE_SCAN_UNIQUE))) To refine a CONTROL QUERY SHAPE statement, several conditions apply to the text format:      Leave off the ROOT node. Leave off EXPR nodes.
PAGE 105
TP663851.fm Page 9 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Shaping Portions of an Operator Tree Use the CUT, ANYTHING, or OFF option to turn off the shape: CONTROL QUERY SHAPE OFF; Shaping Portions of an Operator Tree You can use the ANYTHING option to partially shape an operator tree. Use this option when you want a certain operation only and do not care how the rest of the plan is optimized.
PAGE 106
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Forcing Shapes on Views logical JOIN specification, with SCAN(EMPLOYEE) as the second argument of the join. The optimizer is free to choose nested, merge, or hash join as the implementation. When you force a plan by using the physical operator SHORTCUT_GROUPBY, the SHORTCUT_SCALAR_AGGR operator appears in the EXPLAIN output. If the optimizer cannot produce a plan with SHORTCUT_SCALAR_AGGR, no plan is returned.
PAGE 107
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Forcing Group By Operations to the Data Access Manager more control. Note that, while CONTROL TABLE enables you to force a single operation, CONTROL QUERY SHAPE requires that you force the entire tree structure. Forcing Group By Operations to the Data Access Manager The SQL/MX compiler has two methods for performing grouping and aggregation. Note.
PAGE 108
TP663851.fm Page 12 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Forcing Group By Operations to the Data Access Manager Consider this query against an EMPLOYEE table that contains 50,000 rows (employees) and uses 18 partitions: SELECT COUNT(*) FROM EMPLOYEE; When the compiler presents a query plan like the plan shown in Figure 5-2, the SCAN operator passes messages in blocks to the EXCHANGE node. The EXCHANGE node passes the messages to the GROUP BY operator. HP NonStop SQL/MX Release 3.
PAGE 109
TP663851.fm Page 13 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Forcing Group By Operations to the Data Access Manager To reduce this message traffic, move the GROUP BY operator down to DAM, as shown in Figure 5-3. Figure 5-3. GROUP BY Operator at the DAM Level Executor Exchange DAM Groupby Scan VST060.vsd The message traffic is reduced as follows. The SCAN operation still scans all 50,000 rows. However, the group by operation yields one result for each table partition (18).
PAGE 110
TP663851.fm Page 14 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Forcing Parallel Plans Special Case for Sorted Group By Operations For single partition sorted group by operations, only one GROUP BY operator is required. Sort group by operations can be performed only if the input to the group by is already ordered on the group by columns. In this case, the compiler can perform an index scan or file scan for the group, and the additional cost of sorting is avoided.
PAGE 111
TP663851.fm Page 15 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Forcing Parallel Plans This shape forces a partial grouping in DAM with a consolidator grouping in the ESP or master executor, as shown in Figure 5-5. CONTROL QUERY SHAPE GROUPBY( SPLIT_TOP_PA( GROUPBY(SCAN)) ); Figure 5-5. Query Tree for the Forced Plan root Master Executor hash_partial_groupby_root split_top partition_access hash_partial_groupby_leaf file_scan DAM fragment VST614.
PAGE 112
TP663851.fm Page 16 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Forcing Parallel Plans use a CUT for the children, or use an ESP_EXCHANGE above the children for any necessary repartitioning.
PAGE 113
TP663851.fm Page 17 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Forcing Parallel Plans Figure 5-7. Type2 Hash Join root esp_exchange hybrid_hash_join exchange esp_exchange scan 'DEPT' exchange scan 'EMP' VST651.vsd For more information about Type2 joins, see Parallelism on page 8-1.
PAGE 114
TP663851.fm Page 18 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Considerations for CONTROL QUERY SHAPE This statement enables the optimizer to add exchange nodes: CONTROL QUERY SHAPE IMPLICIT EXCHANGE HYBRID_HASH_JOIN ( SCAN('DEPT'), SCAN('EMP'), TYPE2); For syntax and more information, see the SQL/MX Reference Manual.
PAGE 115
TP663851.fm Page 19 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Considerations for CONTROL QUERY SHAPE and (sc.SCHEMA_NAME = :"hv_param2" or trim(sc.SCHEMA_NAME) LIKE :"hv_param3" ESCAPE '\') and (ob.OBJECT_NAME = :"hv_param4" or trim(ob.OBJECT_NAME) LIKE :"hv_param5" ESCAPE '\') and sc.SCHEMA_UID = ob.SCHEMA_UID and ob.OBJECT_UID = tc.TABLE_UID and tc.CONSTRAINT_TYPE = 'P' and ob.OBJECT_UID = co.OBJECT_UID and tc.CONSTRAINT_UID = ky.CONSTRAINT_UID and ky.COLUMN_NUMBER = co.
PAGE 116
TP663851.fm Page 20 Wednesday, January 30, 2013 5:37 PM Forcing Execution Plans Considerations for CONTROL QUERY SHAPE HP NonStop SQL/MX Release 3.2.
PAGE 117
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM 6 Query Plan Caching Use the information in this section to understand query plan caching:     Types of Cacheable Queries on page 6-2 Choosing an Appropriate Size for the Query Cache on page 6-6 Query Plan Caching Statistics on page 6-6 SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes on page 6-7 Overview Query Plan Caching is a feature of the SQL/MX compiler that provides the ability to cache the plans of certain queries.
PAGE 118
TP663851.fm Page 2 Wednesday, January 30, 2013 5:37 PM Query Plan Caching Types of Cacheable Queries settings that was in effect when this query was previously compiled, results in a cache hit, assuming that all other criteria for a cache hit are met.
PAGE 119
TP663851.fm Page 3 Wednesday, January 30, 2013 5:37 PM Query Plan Caching Examples of Cacheable Expressions to update or return at most one row if table T has K as its primary key column.
PAGE 120
TP663851.
PAGE 121
TP663851.
PAGE 122
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM Query Plan Caching   Choosing an Appropriate Size for the Query Cache Data Definition Language (DDL) statements are not cacheable. Data Control Language (DCL) statements are not cacheable. Choosing an Appropriate Size for the Query Cache To adequately choose an appropriate size for the query cache, examine your applications.
PAGE 123
TP663851.fm Page 7 Wednesday, January 30, 2013 5:37 PM Query Plan Caching SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes This subsection provides additional information about the query plan caching externalized attributes.
PAGE 124
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM Query Plan Caching SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes the new entry is not added to the cache, and no resident entries can be displaced. Because the query plan cache feature is transparent, no error messages are issued. If QUERY_CACHE_MAX_VICTIMS is later set to a nonzero value, replacement resumes as usual. The number of entries that the cache can hold depends on the size of the cache and the size of the cached plans.
PAGE 125
TP663851.fm Page 9 Wednesday, January 30, 2013 5:37 PM Query Plan Caching  QUERYCACHE Function QUERY_CACHE_STATEMENT_PINNING System-defined default setting: OFF Allowable values: ON, OFF, CLEAR This attribute controls whether queries are entered into the cache as pinned or unpinned. You might have important, compile-time critical queries that you want to ensure are in the cache when needed.
PAGE 126
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM Query Plan Caching QUERYCACHE Function Column Name Data Type Description NUM_RETRIES INT UNSIGNED Number of successful compiles that initially fail with caching on (caused by a defect in mxcmp) and that succeed with caching off. NUM_CACHEABLE_PARSING INT UNSIGNED Total number of SQL statements that mxcmp has processed after parsing and before binding the query that satisfy the conditions for caching.
PAGE 127
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM Query Plan Caching QUERYCACHEENTRIES Function QUERYCACHEENTRIES Function The query plan cache automatically collects statistics on each entry of the cache. When invoked, the QUERYCACHEENTRIES table-valued stored function collects and returns these statistics in a table with one row for each entry of the cache. The statistics are reinitialized when an mxcmp session is started. Each mxcmp session maintains an independent set of statistics.
PAGE 128
TP663851.fm Page 12 Wednesday, January 30, 2013 5:37 PM Query Plan Caching QUERYCACHEENTRIES Function Column Name Data Type Description PARAM_TYPES CHAR(1024) Comma-separated list of the types of constants that were changed into parameters. Blank if none. PLAN_LENGTH INT UNSIGNED Size in bytes of the compiled plan associated with this query. IS_PINNED CHAR(6) Indicates whether the entry is pinned. Can be ON or OFF.
PAGE 129
TP663851.fm Page 13 Wednesday, January 30, 2013 5:37 PM Query Plan Caching Querying the Query Plan Caching Virtual Tables Querying the Query Plan Caching Virtual Tables You can query and display certain columns or all columns of the query plan caching virtual tables. You specify the virtual table in a SELECT statement preceded by the keyword table and surrounded by parenthesis. In addition, a pair of parentheses must follow the table name. Information is returned in machine-readable format.
PAGE 130
TP663851.
PAGE 131
TP663851.fm Page 15 Wednesday, January 30, 2013 5:37 PM Query Plan Caching Reviewing the Query Plan Caching Statistics With the DISPLAY_QC and DISPLAY_QC_ENTRIES Reviewing the Query Plan Caching Statistics With the DISPLAY_QC and DISPLAY_QC_ENTRIES Commands The DISPLAY_QC and DISPLAY_QC_ENTRIES commands provide a quick look at the most commonly accessed columns of the query plan caching statistics. The commands are entered at the MXCI prompt with no parameters.
PAGE 132
TP663851.
PAGE 133
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM 7 SQL/MX Operators Use the information in this section to understand the DESCRIPTION column when you use the EXPLAIN function and when you view query execution plans with the Visual Query Planner. Operators are frequently called nodes or node types throughout the SQL/MX documentation set. For information about using the EXPLAIN function and Visual Query Planner, see Section 4, Reviewing Query Execution Plans.
PAGE 134
TP663851.
PAGE 135
TP663851.
PAGE 136
TP663851.
PAGE 137
TP663851.fm Page 5 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators Operators Operators BLOCKED_UNION Operator The BLOCKED_UNION operator always executes the left child element first and then the right. The execution of the right child is always blocked until that of the left child completes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment.
PAGE 138
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators CALL Operator table_b(col1, col2) on update restrict on delete restrict DROPPABLE; CREATE TRIGGER table_a_Owner_Count AFTER INSERT ON table_a REFERENCING NEW AS newrow FOR EACH ROW UPDATE table_b SET owner_count = ( SELECT count(*) FROM table_a WHERE (table_b.col1,table_b.col2)= (table_a.col1,table_a.col2) AND table_a.col3 = 1 ) WHERE (table_b.col1,table_b.col2)= (newrow.col1,newrow.
PAGE 139
TP663851.fm Page 7 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators CALL Operator Token Followed by ... Data Type parameter_modes A sequence of characters that specifies SQL parameter modes for the procedure. I is used for an IN parameter, O for an OUT parameter, and N for an INOUT parameter. Characters are separated by a single space. The value none is returned if the procedure has no SQL parameters.
PAGE 140
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators CURSOR_DELETE Operator routine_name ........... routine_label .......... sql_access_mode ........ external_name .......... external_path .......... external_file .......... signature .............. language ............... runtime_options ........ runtime_option_delimite max_result_sets ........ input_values ........... CAT.SCH.U300POPULATEA \DMR11.$DATA04.ZSDJG12X.WF32X300 MODIFIES SQL DATA populateA .
PAGE 141
TP663851.fm Page 9 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators CURSOR_UPDATE Operator index_begin_key Expression of the begin key predicates on index. expr(text) part_key_predicate Predicate expression specified on partitioning key. Displayed only if partitioning key differs from clustering key. expr(text) check_constraint Check constraints in the delete table.
PAGE 142
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators CURSOR_UPDATE Operator The CURSOR_UPDATE operator has no child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment.
PAGE 143
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ESP_ACCESS Operator lock_mode .............. not specified, defaulted to cursor access_mode ............ not specified, defaulted to committed columns_retrieved ...... 4 new_rec_expr ........... (OWNER_COUNT assign count(1 begin_key ................ (COL1 = COL1) and (COL2 = lock read )) COL2) ESP_ACCESS Operator The ESP_ACCESS operator is a form of ESP_EXCHANGE operator.
PAGE 144
TP663851.fm Page 12 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ESP_ACCESS Operator Token Followed by ... Data Type max_card_est Cardinality estimate for the operator. integer fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor.
PAGE 145
TP663851.fm Page 13 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ESP_EXCHANGE Operator ESP_EXCHANGE Operator Exchange Group An ESP_EXCHANGE operator describes a portion of an execution plan that redistributes the input data stream. This operator represents an interface between ESPs, between the master executor and one or more ESPs, or between an ESP process and a DAM process. For more information about exchange operators, see Section 8, Parallelism. The ESP_EXCHANGE operator has one child node.
PAGE 146
TP663851.fm Page 14 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ESP_EXCHANGE Operator Token Followed by ... Data Type merged_order Expression describing sort keys used to control the interaction between the parent process (usually the master) and ESPs when the result is ordered. expr(text) bottom_partition_input_values Internal values that identify the part of the data the ESP will work on. text partitioning_expression Expression used to partition data.
PAGE 147
TP663851.fm Page 15 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ESP_EXCHANGE Operator The following is an example of the ESP_EXCHANGE operator: control query shape off; drop table t1; -- Assuming that the node has a data-volume $data04. Otherwise use any other data-volume.
PAGE 148
TP663851.fm Page 16 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators EXE_LONG_RUNNING Operator EXE_LONG_RUNNING Operator An EXE_LONG_RUNNING operator describes a portion of a DELETE execution plan that can involve a large set of rows. An ESP_EXCHANGE operator is used to coordinate and execute the delete operation in each partition. Each partition-specific DELETE statement is sub-divided into multiple DELETE statements, each of which is executed in an independent transaction.
PAGE 149
TP663851.fm Page 17 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators EXPLAIN Operator EXPLAIN Operator Stored Function Group The EXPLAIN operator executes a stored function. The operator for an EXPLAIN operator is always EXPLAIN. EXPLAIN has no child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
PAGE 150
TP663851.fm Page 18 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators EXPLAIN_CMD Operator EXPLAIN_CMD Operator The EXPLAIN_CMD operator is generated when the EXPLAIN statement is used and it has no children. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments.
PAGE 151
TP663851.fm Page 19 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators FILE_SCAN Operator Token Followed by ... Data Type olt_opt_lean Indicates for short, simple operations whether a further optimization that reduces the physical size of the plan is used. Its value is used or not used. text scan_type FILE_SCAN followed by table name.
PAGE 152
TP663851.fm Page 20 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators FILE_SCAN_UNIQUE Operator AND p_size = 15 AND p_type like '%BRASS' AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE' AND ps_supplycost = (SELECT MIN(ps_supplycost) FROM partsupp ps1,supplier s1, nation n1,region r1 WHERE p_partkey = ps1.ps_partkey AND s1.s_suppkey = ps1.ps_suppkey AND s1.s_nationkey = n1.n_nationkey AND n1.n_regionkey = r1.r_regionkey AND r1.
PAGE 153
TP663851.fm Page 21 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators FILE_SCAN_UNIQUE Operator The FILE_SCAN_UNIQUE operator has no child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. Integer parent_frag The fragment_id for the parent of the current fragment.
PAGE 154
TP663851.fm Page 22 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators FILE_SCAN_UNIQUE Operator Token Followed by ... Data Type part_key_predicate Predicate expression specified on partitioning key. It is displayed only if partitioning key differs from clustering key. expr(text) fast_replydata_move Indicates whether an optimization for returning data from DAM is used. The value used is returned if this optimization is used.
PAGE 155
TP663851.fm Page 23 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators FirstN Operator FirstN Operator The FirstN operator describes a portion of execution plan that selects only first few rows of output. This operator has only one child node. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
PAGE 156
TP663851.fm Page 24 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators HASH_GROUPBY Operator The HASH_GROUPBY operator has one child node. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment.
PAGE 157
TP663851.fm Page 25 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators HASH_PARTIAL_GROUPBY_LEAF Operator (sum(DETCAT.DETSCH.LX3.L_EXTENDEDPRICE) > cast((100 * 100) AS NUMERIC(18,2) SIGNED)) Note. The query mentioned in the example provides the HASH_GROUPBY operator in Windows NT. To get this operator in the NonStop operating system, use some more CQS.
PAGE 158
TP663851.fm Page 26 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators HASH_PARTIAL_GROUPBY_ROOT Operator fragment_type .......... dp2 grouping_columns ......... DETCAT.DETSCH.T016PT1.D HASH_PARTIAL_GROUPBY_ROOT Operator Groupby Group The HASH_PARTIAL_GROUPBY_ROOT operator works together as a pair with the HASH_PARTIAL_GROUPBY_LEAF operator. The HASH_PARTIAL_GROUPBY_ROOT operator finalizes the group by at the ESP level. See HASH_PARTIAL_GROUPBY_LEAF Operator on page 7-25.
PAGE 159
TP663851.fm Page 27 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators HYBRID_HASH_ANTI_SEMI_JOIN Operator HYBRID_HASH_ANTI_SEMI_JOIN Operator Join Group The HYBRID_HASH_ANTI_SEMI_JOIN operator returns rows from the outer table where no match occurs. Also see HYBRID_HASH_JOIN Operator on page 7-28 and HYBRID_HASH_SEMI_JOIN Operator on page 7-29. The HYBRID_HASH_ANTI_SEMI_JOIN has two child nodes. The description field for this operator contains the following: Token Followed by ...
PAGE 160
TP663851.fm Page 28 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators HYBRID_HASH_JOIN Operator join_type .............. inner anti-semi join_method ............ hash hash_join_predicate .... (DETCAT.DETSCH.PARTSUPP.PS_SUPPKEY = DETCAT.DETSCH.SUPPLIER.S_SUPPKEY) HYBRID_HASH_JOIN Operator Join Group The HYBRID_HASH_JOIN operator joins the data from two child tables.
PAGE 161
TP663851.fm Page 29 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators HYBRID_HASH_SEMI_JOIN Operator AND ps_supplycost = (SELECT MIN(ps_supplycost) FROM partsupp, supplier, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE') ORDER BY s_acctbal desc, n_name, s_name, p_partkey; DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... join_type .............. join_method ...
PAGE 162
TP663851.fm Page 30 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators INDEX_SCAN Operator The following is an example of the HYBRID_HASH_SEMI_JOIN operator: prepare TestQuery31 from select * from TAB1 where TAB1.col1 in (select col2 from TAB2 where TAB2.col1 < 10); DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... join_type .............. join_method ............ hash_join_predicate .... 0 (none) master inner semi hash (DETCAT.DETSCH.TAB1.COL1 = DETCAT.DETSCH.
PAGE 163
TP663851.fm Page 31 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators INDEX_SCAN Operator Token Followed by ... Data Type scan_direction Direction in which table is scanned: forward or reverse. text lock_mode The lock mode specified: shared, exclusive, not specified (defaulted to lock cursor), or unknown. text access_mode The access specified: read uncommitted, skip conflict, read committed, stable, serializable, mx serializable, not specified (defaulted to read committed), or unknown.
PAGE 164
TP663851.fm Page 32 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators INDEX_SCAN_UNIQUE Operator fast_scan .............. used fast_replydata_move .... used key_columns ............ DETCAT.DETSCH.CX1.C_NATIONKEY, DETCAT.DETSCH.CX1.C_CUSTKEY executor_predicates .... (DETCAT.DETSCH.CX1.C_CUSTKEY > 1000) and(DETCAT.DETSCH.CX1.C_CUSTKEY < 1010) and (DETCAT.DETSCH.CX1.C_CUSTKEY = DETCAT.DETSCH.CX1.C_CUSTKEY) begin_key .............. (DETCAT.DETSCH.CX1.C_NATIONKEY = ), (DETCAT.DETSCH.CX1.
PAGE 165
TP663851.fm Page 33 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators INDEX_SCAN_UNIQUE Operator access_mode The access specified: read uncommitted, skip conflict, read committed, stable, serializable, mx serializable, not specified (defaulted to read committed), or unknown. text executor_predicates Any predicate expression that is not a key predicate evaluated by the executor in DAM. expr(text) part_key_predicate Predicate expression specified on partitioning key.
PAGE 166
TP663851.fm Page 34 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators INSERT Operator INSERT Operator INSERT Group The INSERT operator describes that part of an execution plan that inserts a new row into a table. The operator for an INSERT operator is always INSERT. The INSERT operator has no child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment.
PAGE 167
TP663851.fm Page 35 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators INSERT_VSBB Operator cursor access_mode ............ not specified, defaulted to read committed columns_retrieved ...... 8 new_rec_expr ........... (C_CUSTKEY assign DETCAT.DETSCH.CUSTOMER.C_CUSTKEY), (C_NAME assign DETCAT.DETSCH.CUSTOMER.C_NAME), (C_ADDRESS assign DETCAT.DETSCH.CUSTOMER.C_ADDRESS), (C_NATIONKEY assign DETCAT.DETSCH.CUSTOMER.C_NATIONKEY), (C_PHONE assign DETCAT.DETSCH.CUSTOMER.C_PHONE), (C_ACCTBAL assign DETCAT.
PAGE 168
TP663851.fm Page 36 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators LEFT_HYBRID_HASH_JOIN Operator new_rec_expr Computation of the row to be inserted. expr(text) part_key_predicate Predicate expression specified on partitioning key. It is displayed only if partitioning key differs from clustering key. expr(text) check_constraint Check constraints in the insert table.
PAGE 169
TP663851.fm Page 37 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators LEFT_MERGE_JOIN Operator The LEFT_HYBRID_HASH_JOIN operator has two child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment.
PAGE 170
TP663851.fm Page 38 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators LEFT_MERGE_JOIN Operator returned, and the data from the right table is set to null. See MERGE_JOIN Operator on page 7-43. The LEFT_MERGE_JOIN operator has two child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
PAGE 171
TP663851.fm Page 39 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators LEFT_NESTED_JOIN Operator LEFT_NESTED_JOIN Operator Join Group The LEFT_NESTED_JOIN operator describes a portion of an execution plan that involves a nested join. The LEFT_NESTED_JOIN sends each outer (left) row to the inner (right) child. The right child finds all the matches for a row and returns all the matches.
PAGE 172
TP663851.fm Page 40 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators LEFT_ORDERED_HASH_JOIN Operator LEFT_ORDERED_HASH_JOIN Operator Join Group The LEFT_ORDERED_HASH_JOIN operator returns an unmatched outer row even when it does not find a match in the inner table. Null values are supplied for the missing inner rows. The LEFT_ORDERED_HASH_JOIN operator differs from the LEFT_HYBRID_HASH_JOIN in that it preserves the order of the outer table and does not overflow to disk.
PAGE 173
TP663851.fm Page 41 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators MATERIALIZE Operator DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... join_type .............. join_method ............ hash_join_predicate .... 0 (none) master left hash (DETCAT.DETSCH.CUSTOMER.C_NATIONKEY = DETCAT.DETSCH.NATION.
PAGE 174
TP663851.fm Page 42 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators MERGE_ANTI_SEMI_JOIN Operator The following is an example of the MATERIALIZE operator: control query default materialize 'on'; control query shape sort_groupby(nested_join(sort(partition_access( scan(path 'TAB1', forward, mdam off))),materialize( partition_access(scan(path 'TAB2', forward, mdam off))))); prepare TestQuery30 from select TAB1.col1, TAB1.col2, sum(TAB2.col2), count(*) from TAB1 , TAB2 where TAB1.col1 = TAB2.
PAGE 175
TP663851.fm Page 43 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators MERGE_JOIN Operator fragment_type Master, ESP, or DP2. text merge_join_predicate Expression of the join predicate. expr(text) join_type Inner, left, natural, inner semi, or inner anti-semijoin. text join_method Name of join method: merge text parallel_join_type Type1 or Type2, depending on parallel join algorithm. text selection_predicate Expression of the HAVING clause.
PAGE 176
TP663851.fm Page 44 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators MERGE_JOIN Operator The MERGE_JOIN operator has two child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment.
PAGE 177
TP663851.fm Page 45 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators MERGE_SEMI_JOIN Operator MERGE_SEMI_JOIN Operator Join Group The MERGE_SEMI_JOIN operator returns one row for the first match it finds in the inner table. Conversely, MERGE_JOIN returns a row for all matches in the inner table. See MERGE_JOIN Operator on page 7-43. The MERGE_SEMI_JOIN operator has two child nodes. The description field for this operator contains the following: Token Followed by...
PAGE 178
TP663851.fm Page 46 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators MERGE_UNION Operator fragment_id ............ parent_frag ............ fragment_type .......... join_type .............. join_method ............ merge_join_predicate ... 0 (none) master inner semi merge (DETCAT.DETSCH.ORDERS.O_ORDERKEY = DETCAT.DETSCH.LINEITEM.L_ORDERKEY) MERGE_UNION Operator MERGE_UNION Group The MERGE_UNION operator describes that part of an execution plan that merges rows from two child nodes.
PAGE 179
TP663851.fm Page 47 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators MultiUnion Operator prepare xx from select * from table_a union select * from table_c; DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master union_type .............. merge MultiUnion Operator N-ary Group The MultiUnion operator provides performance enhancement for queries that have a large number of table unions. It denotes a single relational union operator with multiple children.
PAGE 180
TP663851.fm Page 48 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators NESTED_JOIN Operator The NESTED_ANTI_SEMI_JOIN operator has two child nodes. The description field for this operator contains the following:. Token Followed by... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment.
PAGE 181
TP663851.fm Page 49 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators NESTED_JOIN Operator goes to a scan operation. Normally, the inner scan access is keyed, and the number of outer probes is small, resulting in an efficient join. The actual join is done in the inner scan instead of the NESTED_JOIN operator. Nested joins support range operations (>=, >, <, <=) in addition to equijoins. The NESTED_JOIN has two child nodes.
PAGE 182
TP663851.fm Page 50 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators NESTED_SEMI_JOIN Operator NESTED_SEMI_JOIN Operator Join Group The NESTED_SEMI_JOIN operator returns only one matched row from the inner table and ignores duplicate matches. See NESTED_JOIN Operator on page 7-48. The NESTED_SEMI_JOIN operator has two child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment.
PAGE 183
TP663851.fm Page 51 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators NEXTVALUEFOR Operator fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master join_type .............. inner semi join_method .............. nested NEXTVALUEFOR Operator The NEXTVALUEFOR operator obtains the next values from the SEQUENCEGENERATOR operator, and then assigns the value to the IDENTITY column in the row.
PAGE 184
TP663851.fm Page 52 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators NESTED_SEMI_JOIN Operator NESTED_SEMI_JOIN Operator ORDERED_HASH_ANTI_SEMI_JOIN Join Group The ORDERED_HASH_ANTI_SEMI_JOIN operator returns only one row for every outer row when no match occurs. This operator preserves the order of the outer table and does not overflow to disk. The reuse feature allows reuse of the hash table for subsequent requests within the same query.
PAGE 185
TP663851.fm Page 53 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ORDERED_HASH_JOIN Operator ORDER BY s_nationkey, s_suppkey; DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... join_type .............. join_method ............ hash_join_predicate .... 0 (none) master inner anti-semi hash (DETCAT.DETSCH.SX1.S_SUPPKEY = DETCAT.DETSCH.PSX1.PS_SUPPKEY) ORDERED_HASH_JOIN Operator Join Group The ORDERED_HASH_JOIN operator joins the data from two child tables.
PAGE 186
TP663851.fm Page 54 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ORDERED_HASH_SEMI_JOIN Operator parallel_join_type Type1 or Type2, depending on parallel join algorithm. text reuse_comparison_values List of values that cause the hash table to be rebuilt when values change. expr(text) selection_predicates Expression of the WHERE clause.
PAGE 187
TP663851.fm Page 55 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ORDERED_HASH_SEMI_JOIN Operator The ORDERED_HASH_SEMI_JOIN operator has two child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments.
PAGE 188
TP663851.fm Page 56 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ORDERED_UNION Operator hash_join_predicate .... (DETCAT.DETSCH.ORDERS.O_ORDERKEY = DETCAT.DETSCH.LX5.L_ORDERKEY) ORDERED_UNION Operator The ORDERED_UNION operator ensures that its left and right children work one at a time. It first receives rows from its left child. It then enables the right child to work. In effect, for the same request, ORDERED_UNION operator produces rows from left child followed by rows from right child.
PAGE 189
TP663851.fm Page 57 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators PACK Operator ALTER TABLE table_a ADD CONSTRAINT table_a_KEY FOREIGN KEY (col1, col2) REFERENCES table_b(col1, col2) on update restrict on delete restrict DROPPABLE ; CREATE TRIGGER table_a_Owner_Count AFTER INSERT ON table_a REFERENCING NEW AS newrow FOR EACH ROW UPDATE table_b SET owner_count = ( SELECT count(*) FROM table_a WHERE (table_b.col1,table_b.col2)= (table_a.col1,table_a.col2) AND table_a.col3 = 1 ) WHERE (table_b.
PAGE 190
TP663851.fm Page 58 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators PARTITION_ACCESS Operator parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor. integer fragment_type Master, ESP, or DP2. text pack_expr Expression used to pack values of a row into a packed row. expr(text) Create a module file. For details on creating module file, see the SQL/MX Programming Manual for C and COBOL.
PAGE 191
TP663851.fm Page 59 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators PARTITION_ACCESS Operator Token Followed by ... Data Type buffer_size Buffer size for messages between PARTITION_ACCESS operator and DAM. integer record_length Length of the record that is returned by DAM. integer begin_key_preds (incl | excl) Predicates to determine the begin key, which might include or exclude the specified key.
PAGE 192
TP663851.fm Page 60 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators PROBE_CACHE Operator Token Followed by ... Data Type child_partitioning_function Type of bottom partitioning and contains summary information about the parallel plan. text begin_part_selection_expr Expression used by SPLIT_TOP or PARTITION_ACCESS to compute the begin_part_no_expr. expr(text) end_part_selection_expr Expression used by SPLIT_TOP or PARTITION_ACCESS to text compute the end_part_no_expr.
PAGE 193
TP663851.fm Page 61 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators ROOT Operator ROOT Operator Root Group The ROOT operator is the root, or the top node, of an execution plan and describes the SQL query. The operator for a ROOT is always ROOT. The ROOT operator has one child. The description field for this operator contains the following: Token Followed by ...
PAGE 194
TP663851.fm Page 62 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SAMPLE Operator The following is an example of the ROOT operator: PREPARE TestQuery15 FROM SELECT CAST(SUM(l_extendedprice*l_discount) AS NUMERIC(18,2)) AS revenue FROM lineitem WHERE l_shipdate >= DATE '1994-0101' AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR AND l_discount BETWEEN .06 - 0.01 AND .06 + 0.01 AND l_quantity < 24; DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type ..........
PAGE 195
TP663851.fm Page 63 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SAMPLE_FILE_SCAN Operator balance_expression Expression representing the sampling expression. A simple random selection, but could be more complex if the sample clause contains a balance clause. expr(text) sample_type Indicates the type of sampling being performed. Possible values are RANDOM, PERIODIC, FIRST, and CLUSTER. text required_order Specified order keys for a sample operation.
PAGE 196
TP663851.fm Page 64 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SAMPLE_FILE_SCAN Operator olt_optimization Indicates whether an optimization for short, simple operations is used. Its value is used or not used. text olt_opt_lean Indicates for short, simple operations whether a further optimization that reduces the physical size of the plan is used. Its value is used or not used. text scan_type FILE_SCAN followed by table name.
PAGE 197
TP663851.fm Page 65 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SEQUENCE Operator parent_frag ............ fragment_type .......... olt_optimization ....... olt_opt_lean ........... scan_type .............. 0 dp2 not used not used sample full scan of table DETCAT.DETSCH.TT22 scan_direction ......... forward key_type ............... simple lock_mode .............. not specified, defaulted to lock cursor access_mode ............ not specified, defaulted to read committed columns_retrieved ......
PAGE 198
TP663851.fm Page 66 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SEQUENCEGENERATOR Operator sequence_functions Represents the list of sequence functions that must be evaluated by this SEQUENCE operator. ItemExpr tree num_history_rows Size of the history buffer (in rows). This number of rows is kept in an integer buffer and is available for access by the sequence functions. Any access to a row outside this buffer results in a NULL value. The default value for this parameter is 1024 rows.
PAGE 199
TP663851.fm Page 67 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators The description field for the operator contains the following: The description field for the operator contains the following: Token Followed by ... Data Type max_card_est Cardinality estimate for this operator. integer fragment_id A sequential number assigned to the fragment. 0 is the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments.
PAGE 200
TP663851.fm Page 68 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators The following is an example of the SEQUENCEGENERATOR operator: sg_cache_maximum The maximum value that influences the default adaptive cache. For details, see the explanation for sg_cache_initial token. int 64 sg_extended_precision The precision value of the data type of the sequence generator {1,2,...,28}. int 64 sg_type The type of sequence generator, EXTERNAL or INTERNAL.
PAGE 201
TP663851.fm Page 69 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SHORTCUT_SCALAR_AGGR Operator sg_currval_sys_host_var _sys_CV_1950613529239555180_1950613529247292079_1948 sg_increment_hv ........ sg_cache_size_hv ....... sg_min_value_hv ........ sg_max_value_hv ........ current_value_from_sg 362054427974368 \:_sys_hostIncrement \:_sys_hostVarNextNValues \:_sys_hostMinValue \:_sys_hostMaxValue CAT.SCH.S1.
PAGE 202
TP663851.fm Page 70 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SORT Operator The SORT operator has one child node. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment.
PAGE 203
TP663851.fm Page 71 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SORT_GROUPBY Operator SORT_GROUPBY Operator Groupby Group The SORT_GROUPBY operator describes a portion of an execution plan that affects a group. The SORT_GROUPBY operator has one child node. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
PAGE 204
TP663851.fm Page 72 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SORT_PARTIAL_AGGR_ROOT Operator The description field for this operator contains the following: Token Followed by... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor.
PAGE 205
TP663851.fm Page 73 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SORT_PARTIAL_GROUPBY_LEAF Operator parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor. integer fragment_type Master, ESP, or DP2. text aggregates Expression of the aggregate function. expr(text) The following is an example of the SORT_PARTIAL_AGGR_ROOT operator: prepare TestQuery17 from select T03.char_100 from PTAB03 T03 where T03.char_100 = (Select min(T00.
PAGE 206
TP663851.fm Page 74 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SORT_PARTIAL_GROUPBY_ROOT Operator The following is an example of the SORT_PARTIAL_GROUPBY_LEAF operator: control query shape sort_groupby(partition_access(sort_groupby(scan))); prepare TestQuery33 from select int64_6,count(*) from PTAB09 where int64_6 IN (0,2,4,5) group by int64_6; DESCRIPTION fragment_id ............ 2 parent_frag ............ 0 fragment_type .......... dp2 grouping_columns ....... DETCAT.DETSCH.PTAB09.
PAGE 207
TP663851.fm Page 75 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SORT_SCALAR_AGGR Operator where int64_6 IN (0,2,4,5) group by int64_6; DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master grouping_columns ....... DETCAT.DETSCH.PTAB09.INT64_6 aggregates .............. sum(count(1 )) SORT_SCALAR_AGGR Operator Groupby Group The SORT_SCALAR_AGGR operator occurs for aggregates without a GROUP BY clause. It returns one row.
PAGE 208
TP663851.fm Page 76 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SPLIT_TOP Operator DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master aggregates ............. min(DETCAT.DETSCH.PARTSUPP.PS_SUPPLYCOST) selection_predicates ... (DETCAT.DETSCH.PSX1.PS_SUPPLYCOST = min(DETCAT.DETSCH.PARTSUPP.
PAGE 209
TP663851.fm Page 77 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SUBSET_DELETE Operator The following is an example of the SPLIT_TOP operator: PREPARE TestQuery19 FROM select cust_id, count(*) as order_count from orders where order_date > (date '2001-01-12') group by cust_id order by cust_id; DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master olt_optimization ....... not used parent_processes ....... 1 child_processes ..........
PAGE 210
TP663851.fm Page 78 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SUBSET_UPDATE Operator index_begin_key Expression of the begin key predicates on index. expr(text) end_key Expression of the end key predicate. expr(text) index_end_key Expression of the end key predicates on index. expr(text) check_constraint Check constraints in the table. expr(text) scan_type Information on indexes. text scan_direction Direction in which table is scanned: forward or reverse.
PAGE 211
TP663851.fm Page 79 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators SUBSET_UPDATE Operator from CURSOR_UPDATE, which performs the read and update in separate operations. The CURSOR_UPDATE operation also involves more messages. This operator has no child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
PAGE 212
TP663851.fm Page 80 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators TRANSPOSE Operator part_key_predicate Predicate expression specified on partitioning key. It is displayed only if partitioning key differs from clustering key. expr(text) iud_type Specifies the type of update operator. Could be subset_update, unique_update, or cursor_update. expr(text) selection_predicate Predicate specified in the WHERE clause of a query.
PAGE 213
TP663851.fm Page 81 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators TUPLE_FLOW Operator parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor. integer fragment_type Master, ESP, or DP2. text transpose_union_vector Represents a transpose set of the transpose clause. If multiple transpose sets, then multiple instances of the token. ItemExpr tree For more information about data mining, see the SQL/MX Data Mining Guide.
PAGE 214
TP663851.fm Page 82 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators TUPLE_FLOW Operator The TUPLE_FLOW operator has two child nodes. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment.
PAGE 215
TP663851.fm Page 83 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators TUPLELIST Operator TUPLELIST Operator Tuple Group The TUPLELIST operator shows the values that you place in the query when the VALUES clause is used. The description field for this operator contains the following: Token Followed by... Data type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments.
PAGE 216
TP663851.fm Page 84 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators UNARY_UNION Operator The description field for this operator contains the following: Token Followed by... Data type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments. integer parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor.
PAGE 217
TP663851.fm Page 85 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators UNIQUE_DELETE Operator SELECT count(*) FROM table_a WHERE (table_b.col1,table_b.col2)= (table_a.col1,table_a.col2) AND table_a.col3 = 1 ) WHERE (table_b.col1,table_b.col2)= (newrow.col1,newrow.col2); insert into table_b values('A', 1, 1, 0); Prepare TestQuery11 from insert into table_a values('A', 1, 1); DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master union_type ...............
PAGE 218
TP663851.fm Page 86 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators UNIQUE_UPDATE Operator olt_opt_lean Indicates for short, simple operations whether a further optimization that reduces the physical size of the plan is used. Its value is used or not used. text iud_type Type of delete followed by table or index name. expr(text) predicate Expression specified on WHERE clause that is not reflected in the begin and end predicates.
PAGE 219
TP663851.fm Page 87 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators UNPACK Operator new_rec_expr Computation of the row to be updated expr(text) predicate Expression specified on WHERE clause that is not reflected in the begin and end predicates. expr(text) key Expression of the key predicate. expr(text) index_key Expression of the key predicates on index. expr(text) olt_optimization Indicates whether an optimization for short, simple operations is used. The value used or not used.
PAGE 220
TP663851.fm Page 88 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators UNPACK Operator elements from the array to use in the query. For more information about rowsets and arrays, see the SQL/MX Programming Manual for C and COBOL. The UNPACK operator has one child. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
PAGE 221
TP663851.fm Page 89 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators VALUES Operator VALUES Operator Tuple Group The VALUES operator calculates an expression for each row it receives from its child node and returns that expression to its parent node. The VALUES operator has one child node. The description field for this operator contains the following: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment.
PAGE 222
TP663851.fm Page 90 Wednesday, January 30, 2013 5:37 PM SQL/MX Operators VALUES Operator (table_a.col1,table_a.col2) AND table_a.col3 = 1 ) WHERE (table_b.col1,table_b.col2)= (newrow.col1,newrow.col2); insert into table_b values('A', 1, 1, 0); Prepare TestQuery11 from insert into table_a values('A', 1, 1); DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master tuple_expr ............. NULL selection_predicates ..... 0. HP NonStop SQL/MX Release 3.2.
PAGE 223
TP663851.fm Page 1 Wednesday, January 30, 2013 5:37 PM 8 Parallelism Using SQL/MX parallel processing within a large query maximizes the efficiency and performance of such queries. Parallel execution can assume multiple forms. You can partition the data or the query execution (or both) and then process the obtained partitions in parallel. This section describes the types of parallelism supported by NonStop SQL/MX.
PAGE 224
TP663851.fm Page 2 Wednesday, January 30, 2013 5:37 PM Parallelism Pipelined Parallelism Pipelined Parallelism Pipelined parallelism is an inherent feature of NonStop SQL/MX because of its data flow architecture. This architecture interconnects all operators by pipes, with the output of one operator being piped as input to the next operator, and so on. The result is that each operator works independently of any other operator, producing its output as soon as its input is available.
PAGE 225
TP663851.fm Page 3 Wednesday, January 30, 2013 5:37 PM Parallelism Exchange Nodes and Plan Fragments root [7] Root node esp exchange [6] Process boundary Internal nodes merge_join [5] partition access [2] partition access [4] Leaf Nodes file_scan ORDERS [1] Process boundary file_scan LINEITEM [3] VST820.vsd Exchange Nodes and Plan Fragments A plan fragment is a portion of a query plan that executes within a single process. The plan fragment boundaries are identified by the Exchange nodes.
PAGE 226
TP663851.fm Page 4 Wednesday, January 30, 2013 5:37 PM Parallelism Exchange Nodes and Plan Fragments For more information about these types of partitioning functions, see the discussion regarding Join With Matching Partitions on page 8-6. A plan fragment executes in one of these processes:    DAM. A plan fragment executes within the DAM process if and only if its root node is a PARTITION_ACCESS node. ESP.
PAGE 227
TP663851.fm Page 5 Wednesday, January 30, 2013 5:37 PM Parallelism DAM and ESP Parallelism root [9] Root Master Executor sort_partial_groupby_root [7] sort [6] 1 esp exchange [5] 4 (range) ESP Fragment split_top [4] ESP ESP ESP ESP 12 (logphys) partition access [3] hash_partial_groupby_leaf [2] DAM Fragment index_scan [1] - LX3 (m) Process Structure of the Plan VST081.
PAGE 228
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM Parallelism Parallel Plan Generation A cost is associated with starting an ESP process. The optimizer balances this cost against the performance gain resulting from the increased parallelism and chooses ESP parallelism only if the gain exceeds the ESP start-up cost. Parallel Plan Generation The SQL/MX optimizer uses different methods to process operators, depending on the general category of operator type, as described next.
PAGE 229
TP663851.fm Page 7 Wednesday, January 30, 2013 5:37 PM Parallelism Join With Matching Partitions In the simple case shown in the next figure, the first partition of Table A is joined with the first partition of Table B, the second partition of Table A is joined with the second partition of Table B, and so on. This method of performing joins works only if, for a given row in Table A, partition 1, all its matching rows are stored in Table B, partition 1.
PAGE 230
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM Parallelism  Join With Matching Partitions Join With Hash Repartitioning If both tables are partitioned in a way that does not facilitate parallel execution for the query, the optimizer can request the executor to repartition (reorganize) both tables at run time. The matching partitions join algorithm is used on the reorganized tables.
PAGE 231
TP663851.fm Page 9 Wednesday, January 30, 2013 5:37 PM Parallelism Join With Matching Partitions 101 TB L2 0 - 100 TB L1 Tbl A Prt 1 0 - 100 Tbl A Prt 2 101 - Tbl B Prt 1 Tbl B Prt 2 Tbl B Prt 3 0 - 50 51 - 100 101 - 150 Left Child Tbl B Prt 4 151 - Right Child VST083.vsd  Join with logical subpartition alignment Logical subpartitioning is typically used when the only difference between the join tables is the partition boundaries or the number of partitions.
PAGE 232
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM Parallelism Join With Parallel Access to the Inner Table Join With Parallel Access to the Inner Table In joins where the inner and outer tables do not match and repartitioning is undesirable or not possible, the optimizer can still achieve parallelism by using parallel access to the inner table. This type of plan is also known as a Type2 join. Type2 joins can be forced by using the CONTROL QUERY SHAPE statement.
PAGE 233
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM Parallelism Unions SELECT * FROM A,B WHERE A.COL1=B.COL2; Combine Join Join Join Join Tbl A Prt 1 Tbl A Prt 2 Tbl A Prt 3 Tbl B Outer Inner VST890.vsd Unions The optimizer can choose to execute the union in parallel when another operator, such as a group by, exists above the MERGE_UNION, which can benefit from receiving parallel streams of data.
PAGE 234
TP663851.fm Page 12 Wednesday, January 30, 2013 5:37 PM Parallelism Sort partial group by must be combined in a serial finalization step before the query result is used. A partial scalar aggregate can be executed in parallel without regard to the partitioning scheme, but the partial aggregate must be combined in a serial finalization step. Sort NonStop SQL/MX can sort in parallel by using multiple ESPs or serially in the master executor.
PAGE 235
TP663851.fm Page 13 Wednesday, January 30, 2013 5:37 PM Parallelism How to Determine if You Have a Parallel Plan Partitioned parallelism in query plans is represented by the operators of the Exchange group: ESP_EXCHANGE and SPLIT_TOP. If your query plan does not contain either of these operators, parallel processing was not used in the plan. An additional exchange operator, PARTITION_ACCESS, appears in parallel plans but signifies neither ESP nor DAM parallelism.
PAGE 236
TP663851.fm Page 14 Wednesday, January 30, 2013 5:37 PM Parallelism How to Determine if You Have a Parallel Plan Figure 8-1. Matching Partitions Join Using ESP Parallelism root [7] esp exchange [6] merge_join [5] partition access [2] partition access [4] file_scan ORDERS [1] file_scan LINEITEM [3] VST085.vsd The query plan shows a parallel matching partitions merge join.
PAGE 237
TP663851.fm Page 15 Wednesday, January 30, 2013 5:37 PM Parallelism How to Determine if You Have a Parallel Plan indicates that two PARTITION_ACCESS nodes are started to access the data in parallel for the two-way range partitioned table. Figure 8-2. Serial Hybrid Hash Join Using DAM Parallelism root [8] hybrid_hash_join [7] split_top [3] split_top [6] partition access [2] partition access [5] file_scan ORDERS [1] file_scan LINEITEM [4] VST086.
PAGE 238
TP663851.fm Page 16 Wednesday, January 30, 2013 5:37 PM Parallelism Plan Fragments with 2 PA(s), log=exactly 1 partition, phys=range partitioned 2 ways on (([150]equiv(O.O_ORDERKEY)))) Plan Fragments To understand the underlying parallelism in your plan, you can divide the query plan into plan fragments.
PAGE 239
TP663851.fm Page 17 Wednesday, January 30, 2013 5:37 PM Parallelism Plan Fragments that communicate with the master executor, this value is always one. The bottom_degree_parallelism token indicates how many instances exist of the fragment rooted in this node. The bottom_partitioning_function token provides information about how the plan is parallelized. For the SPLIT_TOP operator, the top_degree_parallelism token indicates how many fragment instances of the fragment containing the SPLIT_TOP node exist.
PAGE 240
TP663851.fm Page 18 Wednesday, January 30, 2013 5:37 PM Parallelism Plan Fragments Figure 8-3. EXPLAIN statement OPTIONS 'f' SQLQUERY Output for Sample Query Using ESP Parallelism LC -12 11 10 9 2 7 4 5 . 3 . 1 . RC -. . . . 8 . 6 . . . . . .
PAGE 241
TP663851.fm Page 19 Wednesday, January 30, 2013 5:37 PM Parallelism Plan Fragments Figure 8-4. Query Tree With ESP Parallelism root [13] esp exchange [12] sort [11] hash_groupby [10] hybrid_hash_join [9] partition access [2] esp exchange [8] file_scan [1] - LINEITEM merge_join [7] partition access [4] file_scan [3] - CUSTOMER partition access [6] index_scan [5] - ORDERX1 VST087.
PAGE 242
TP663851.fm Page 20 Wednesday, January 30, 2013 5:37 PM Parallelism Plan Fragments indicating the parallelism for the ESP fragment). The bottom_partitioning_function token provides the information about the type of parallel plan. Seq_Num: 12 Operator: ESP_EXCHANGE Left_Child_Seq_Num: 11 Right_Child_Seq_Num: ? Cardinality: 1.2347501E+004 Operator Cost: 5.5621022E-001 Total Cost: 2.7717787E+001 Detail Cost: CPU_TIME: 0.0821787 IO_TIME: 0 MSG_TIME: 0 IDLETIME: 0.
PAGE 243
TP663851.fm Page 21 Wednesday, January 30, 2013 5:37 PM Parallelism Degree of Parallelism Figure 8-6.
PAGE 244
TP663851.fm Page 22 Wednesday, January 30, 2013 5:37 PM Parallelism Degree of Parallelism To further understand the degree of parallelism, this is the same query with no ESPs. Figure 8-7 shows the use of DAM parallelism. The EXPLAIN statement OPTIONS 'f' SQLQUERY shows the output.
PAGE 245
TP663851.fm Page 23 Wednesday, January 30, 2013 5:37 PM Parallelism Degree of Parallelism Figure 8-8.
PAGE 246
TP663851.fm Page 24 Wednesday, January 30, 2013 5:37 PM Parallelism Influencing Parallel Plans Right_Child_Seq_Num: ? Cardinality: 7.2738547E+004 Operator Cost: 5.6920946E-001 Total Cost: 4.6269255E+000 Detail Cost: CPU_TIME: 0.9101162 IO_TIME: 1.7542597 MSG_TIME: 0 IDLETIME: 0.
PAGE 247
TP663851.fm Page 25 Wednesday, January 30, 2013 5:37 PM Parallelism    System Default Settings That Affect Parallelism Complexity and quantity of rows returned. The optimizer chooses parallel plans when complex processing on large amounts of data must occur, resulting in a few rows, such as computing an aggregate. Forcing parallelism. Use the CONTROL QUERY SHAPE statement to force specific types of parallel plans. See Section 5, Forcing Execution Plans. Defaults.
PAGE 248
TP663851.fm Page 26 Wednesday, January 30, 2013 5:37 PM Parallelism  System Default Settings That Affect Parallelism PARALLEL_NUM_ESPS Maximum number of ESPs an operator can use (for fewer CPUs per operator). Limits the maximum number of ESPs per operator to a value less than the number of CPUs in the cluster. Limits the number but does not choose which CPUs to use. The system-defined default setting is the number of processors in the cluster.
PAGE 249
TP663851.
PAGE 250
TP663851.
PAGE 251
TP663851.
PAGE 252
TP663851.
PAGE 253
TP663851.
PAGE 254
TP663851.
PAGE 255
TP663851.
PAGE 256
TP663851.fm Page 8 Wednesday, January 30, 2013 5:37 PM Index Z HP NonStop SQL/MX Release 3.2.