TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM HP NonStop SQL/MX Release 3.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.
TP663851.fm Page 2 Monday, October 17, 2011 11:48 AM Document History Part Number Product Version Published 640323-001 NonStop SQL/MX Release 3.0 February 2011 663851-001 NonStop SQL/MX Release 3.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM Legal Notices Copyright 2011 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. The information contained herein is subject to change without notice.
TP663851.
TP663851.fm Page i Monday, October 17, 2011 11:48 AM HP NonStop SQL/MX Release 3.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 SQL/MX 3.1 manual: ix Changes to the H06.22/J06.11 manual: ix 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 x Changes to the H06.
TP663851.fm Page ii Monday, October 17, 2011 11:48 AM Contents 3.
TP663851.fm Page iii Monday, October 17, 2011 11:48 AM Contents 5. Forcing Execution Plans Simple Query Example Using Measure 4-22 4-22 5.
TP663851.fm Page iv Monday, October 17, 2011 11:48 AM Contents 7.
TP663851.fm Page v Monday, October 17, 2011 11:48 AM Contents 8.
TP663851.
TP663851.fm Page vii Monday, October 17, 2011 11:48 AM Contents Figure 8-6. Figure 8-7. Figure 8-8. Tables Sample Query Plan Showing Plan Fragments 8-21 EXPLAIN statement OPTIONS 'f' SQLQUERY Output for Sample Query Using DAM Parallelism 8-22 Query Tree for Sample Plan Using DAM Parallelism 8-23 Tables Table 1-1. Table 3-1. Table 3-2. Valid Queries and Operators for OLT Optimization Histogram Temporary Tables 3-2 Histogram Statistics Tables 3-3 HP NonStop SQL/MX Release 3.
TP663851.fm Page viii Monday, October 17, 2011 11:48 AM Contents HP NonStop SQL/MX Release 3.
TP663851.fm Page ix Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page x Monday, October 17, 2011 11:48 AM What’s New in This Manual Changes to the H06.20/J06.09 manual: Updated Querying the Query Plan Caching Virtual Tables on page 6-13. Added PROBE_CACHE Operator on page 7-59. Updated Optimization Tips on page 4-9. 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.
TP663851.fm Page xi Monday, October 17, 2011 11:48 AM What’s New in This Manual Changes to the H06.05 Manual Changes to the H06.05 Manual Section New or Changed Information Section 2, Accessing SQL/MX Data Example added that shows placing statements for a forced shape into a separate module. 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.
TP663851.fm Page xii Monday, October 17, 2011 11:48 AM What’s New in This Manual Changes to the H06.05 Manual HP NonStop SQL/MX Release 3.
TP663851.fm Page xiii Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.fm Page xv Monday, October 17, 2011 11:48 AM About This Manual Related Documentation 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). Includes information about installing the sample database.
TP663851.fm Page xvi Monday, October 17, 2011 11:48 AM About This Manual Related Documentation Data Management Guides SQL/MX Data Mining Guide Describes the SQL/MX data structures and operations to carry out the knowledge-discovery process. SQL/MX Report Writer Guide Describes how to produce formatted reports using data from an SQL/MX database. DataLoader/MX Reference Manual Describes the features and functions of the DataLoader/MX product, a tool to load SQL/MX databases.
TP663851.fm Page xvii Monday, October 17, 2011 11:48 AM About This Manual Notation Conventions These manuals are part of the SQL/MP library of manuals and are essential references for information about SQL/MP Data Definition Language (DDL) and SQL/MP installation and management: Related SQL/MP Manuals SQL/MP Reference Manual Describes the SQL/MP language elements, expressions, predicates, functions, and statements.
TP663851.fm Page xviii Monday, October 17, 2011 11:48 AM About This Manual General Syntax Notation [ ] Brackets. Brackets enclose optional syntax items. For example: TERM [\system-name.]$terminal-name INT[ERRUPTS] A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list may be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines.
TP663851.fm Page xix Monday, October 17, 2011 11:48 AM About This Manual Change Bar Notation Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: CALL STEPMOM ( process-id ) ; If there is no space between two items, spaces are not permitted. In the following example, there are no spaces permitted between the period and any other items: $process-name.#su-name Line Spacing.
TP663851.fm Page xx Monday, October 17, 2011 11:48 AM About This Manual Change Bar Notation HP NonStop SQL/MX Release 3.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM 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-19 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.
TP663851.fm Page 2 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 3 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 4 Monday, October 17, 2011 11:48 AM 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).
TP663851.fm Page 5 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 6 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 7 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 9 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM Compiling and Executing a Query Improving Query Performance Programming Manual for C and COBOL, SQL/MX Programming Manual for Java, and the SQL/MX Installation and Management Guide 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.
TP663851.fm Page 11 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 12 Monday, October 17, 2011 11:48 AM 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).
TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM Compiling and Executing a Query Improving Query Performance Example 1-1.
TP663851.fm Page 14 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 15 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 16 Monday, October 17, 2011 11:48 AM 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. >> >>EXECUTE yy; *** ERROR[8690] An invalid character value encountered in TRANSLATE function.
TP663851.fm Page 17 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 18 Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.fm Page 20 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM 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-13 Access Methods This subsection describes the access methods used by the SQL/MX compiler.
TP663851.fm Page 2 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 3 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 4 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 5 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 6 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 7 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 9 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 11 Monday, October 17, 2011 11:48 AM Accessing SQL/MX Data Understanding Unexpected Access Paths All columns to be updated have an equality predicate in the WHERE clause. For example: UPDATE INVNTRY SET RETAIL_PRICE = RETAIL_PRICE * 1.
TP663851.fm Page 12 Monday, October 17, 2011 11:48 AM Accessing SQL/MX Data Understanding Unexpected Access Paths Make sure the forced shape applies only to the statement and table intended. Turn the forced shape off as soon as you are finished (CONTROL QUERY SHAPE OFF). Isolate this forced shape in its own section and perform it from the inline application code. Place all statements affected by the forced shape in separate modules, called as services by other modules.
TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM Accessing SQL/MX Data MultiDimensional Access Method (MDAM) 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.COLUMN_NUMBER FOR READ UNCOMMITTED ACCESS order by 1, 2, 3, 5 ; exec sql control query shape off; ...
TP663851.fm Page 14 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 15 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 16 Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.fm Page 18 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 19 Monday, October 17, 2011 11:48 AM Accessing SQL/MX Data MDAM’s Use of DENSE and SPARSE Algorithms When the compiler chooses a SPARSE algorithm, the executor executes only the SPARSE algorithm and does not attempt to switch to a DENSE algorithm. You can force the choice by specifying the SPARSE or DENSE option in the CONTROL QUERY SHAPE statement.
TP663851.fm Page 20 Monday, October 17, 2011 11:48 AM Accessing SQL/MX Data MDAM’s Use of DENSE and SPARSE Algorithms HP NonStop SQL/MX Release 3.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 2 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 3 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 4 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 5 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 6 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 7 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 9 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 2 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 3 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 4 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 5 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 6 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 7 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 9 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 11 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 12 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 14 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 15 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 16 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 17 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 18 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 19 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 20 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 21 Monday, October 17, 2011 11:48 AM 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. Use the DISPLAY STATISTICS command to view statistics.
TP663851.fm Page 22 Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.fm Page 24 Monday, October 17, 2011 11:48 AM Reviewing Query Execution Plans Using Measure other queries, frequency of execution within a single transaction, and other performance-related measurements. Then, generate query plans with the Explain function for the queries to help identify reasons for poor performance. Sometimes a specific type of problem is common to a set of queries. Stopwatch measurements can also be helpful.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM 5 Forcing Execution Plans Use the information in this section to make decisions about forcing query execution plans.
TP663851.fm Page 2 Monday, October 17, 2011 11:48 AM Forcing Execution Plans Checklist for Forcing Plans In these situations, forcing a plan gives you the power to control the plan shape. The optimizer chooses the optimal plan that matches the forced shape. Checklist for Forcing Plans Before you can force a plan, you need to know the contents of the plan: 1. Display the optimized plan for a prepared statement with the EXPLAIN function. 2.
TP663851.fm Page 3 Monday, October 17, 2011 11:48 AM 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.deptnum, job.
TP663851.fm Page 4 Monday, October 17, 2011 11:48 AM 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. Query Plan Output in Visual Tree Format ROOT 9 HYBRID_HASH_JOIN 8 HYBRID_HASH_JOIN 7 PARTITION_ACCESS 6 PARTITION_ACCESS 4 FILE_SCAN 5 EMPLOYEE FILE_SCAN_UNIQUE 3 JOB PARTITION_ACCESS 2 FILE_SCAN_UNIQUE 1 DEPT VST062.
TP663851.fm Page 5 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 6 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 7 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 9 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 11 Monday, October 17, 2011 11:48 AM Forcing Execution Plans Forcing Group By Operations to the Data Access Manager Hash group by uses hashing operations to perform grouping and has no ordering requirement on children. In general, hash group by is more cost effective than sort group by. The compiler uses algorithms based on cost to determine which group by to perform, hash or sort. The aggregate functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, and VARIANCE.
TP663851.fm Page 12 Monday, October 17, 2011 11:48 AM 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).
TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 14 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 15 Monday, October 17, 2011 11:48 AM Forcing Execution Plans Forcing Parallel Plans use a CUT for the children, or use an ESP_EXCHANGE above the children for any necessary repartitioning.
TP663851.fm Page 16 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 17 Monday, October 17, 2011 11:48 AM Forcing Execution Plans Forcing Parallel Plans 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. HP NonStop SQL/MX Release 3.
TP663851.fm Page 18 Monday, October 17, 2011 11:48 AM Forcing Execution Plans Forcing Parallel Plans HP NonStop SQL/MX Release 3.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 2 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 3 Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.
TP663851.fm Page 6 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 7 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 9 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 11 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 12 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.fm Page 15 Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.
TP663851.
TP663851.fm Page 5 Monday, October 17, 2011 11:48 AM 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: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment.
TP663851.fm Page 6 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 7 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM 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 .
TP663851.fm Page 9 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM SQL/MX Operators CURSOR_UPDATE Operator The CURSOR_UPDATE operator has no child nodes. The description field for this operator contains: 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.
TP663851.fm Page 11 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 12 Monday, October 17, 2011 11:48 AM SQL/MX Operators ESP_EXCHANGE Operator Token Followed by ... Data Type max_card_set 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.
TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM SQL/MX Operators ESP_EXCHANGE Operator 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. integer fragment_type Master, ESP, or DP2.
TP663851.
TP663851.
TP663851.fm Page 16 Monday, October 17, 2011 11:48 AM 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: 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.
TP663851.fm Page 17 Monday, October 17, 2011 11:48 AM 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: 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.
TP663851.fm Page 18 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 19 Monday, October 17, 2011 11:48 AM SQL/MX Operators FILE_SCAN_UNIQUE Operator 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.r_name = 'EUROPE') ORDER BY s_acctbal desc, n_name, s_name, p_partkey; DESCRIPTION fragment_id ........... 10 parent_frag ............ 0 fragment_type ..........
TP663851.fm Page 20 Monday, October 17, 2011 11:48 AM SQL/MX Operators FILE_SCAN_UNIQUE Operator The FILE_SCAN_UNIQUE operator has no child nodes. The description field for this operator contains: 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.
TP663851.fm Page 21 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 22 Monday, October 17, 2011 11:48 AM 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: 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.
TP663851.fm Page 23 Monday, October 17, 2011 11:48 AM SQL/MX Operators HASH_GROUPBY Operator The HASH_GROUPBY operator has one child node. The description field for this operator contains: 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.
TP663851.fm Page 24 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 25 Monday, October 17, 2011 11:48 AM 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-24.
TP663851.fm Page 26 Monday, October 17, 2011 11:48 AM 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-27 and HYBRID_HASH_SEMI_JOIN Operator on page 7-28. The HYBRID_HASH_ANTI_SEMI_JOIN has two child nodes. The description field for this operator contains: Token Followed by ...
TP663851.fm Page 27 Monday, October 17, 2011 11:48 AM 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. It creates a hash table for the inner table and joins the outer table by hashing each outer row and looking for matches in the hash table.
TP663851.fm Page 28 Monday, October 17, 2011 11:48 AM 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 .....
TP663851.fm Page 29 Monday, October 17, 2011 11:48 AM 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.TAB2.
TP663851.fm Page 30 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 31 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 32 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 33 Monday, October 17, 2011 11:48 AM 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: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment.
TP663851.fm Page 34 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 35 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 36 Monday, October 17, 2011 11:48 AM SQL/MX Operators LEFT_MERGE_JOIN Operator The LEFT_HYBRID_HASH_JOIN operator has two child nodes. The description field for this operator contains: 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.
TP663851.fm Page 37 Monday, October 17, 2011 11:48 AM 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-42. The LEFT_MERGE_JOIN operator has two child nodes. The description field for this operator contains: 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.
TP663851.fm Page 38 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 39 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 40 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 41 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 42 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 43 Monday, October 17, 2011 11:48 AM SQL/MX Operators MERGE_JOIN Operator The MERGE_JOIN operator has two child nodes. The description field for this operator contains: 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.
TP663851.fm Page 44 Monday, October 17, 2011 11:48 AM 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-42. The MERGE_SEMI_JOIN operator has two child nodes. The description field for this operator contains: Token Followed by...
TP663851.fm Page 45 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 46 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 47 Monday, October 17, 2011 11:48 AM SQL/MX Operators NESTED_JOIN Operator The NESTED_ANTI_SEMI_JOIN operator has two child nodes. The description field for this operator contains:. 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.
TP663851.fm Page 48 Monday, October 17, 2011 11:48 AM 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. The description field for this operator contains: Token Followed by ...
TP663851.fm Page 49 Monday, October 17, 2011 11:48 AM 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-47. The NESTED_SEMI_JOIN operator has two child nodes. The description field for this operator contains: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment.
TP663851.fm Page 50 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 51 Monday, October 17, 2011 11:48 AM SQL/MX Operators and does not overflow to disk. The reuse feature allows reuse of the hash table for subsequent requests within the same query. Choose this operator when you need to preserve the order of the outer table or if you can benefit from the reuse feature. It should be chosen only if the inner table is small enough to fit in memory. The ORDERED_HASH_ANTI_SEMI_JOIN operator has two child nodes.
TP663851.fm Page 52 Monday, October 17, 2011 11:48 AM SQL/MX Operators ORDERED_HASH_JOIN Operator ORDERED_HASH_JOIN Operator Join Group The ORDERED_HASH_JOIN operator joins the data from two child tables. This operator preserves the order of the outer table and does not overflow to disk. It creates a hash table from the inner table, joins the outer table by hashing each outer row, and looks for matches in the hash table.
TP663851.fm Page 53 Monday, October 17, 2011 11:48 AM SQL/MX Operators ORDERED_HASH_SEMI_JOIN Operator WHERE s_suppkey = ps_suppkey GROUP BY s_nationkey, s_suppkey ORDER BY s_nationkey, s_suppkey; DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... join_type .............. join_method ............ hash_join_predicate .... 0 (none) master inner hash (DETCAT.DETSCH.SX1.S_SUPPKEY = DETCAT.DETSCH.PSX1.
TP663851.fm Page 54 Monday, October 17, 2011 11:48 AM SQL/MX Operators ORDERED_UNION 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 they change. expr(text) selection_predicates Expression of the WHERE clause.
TP663851.fm Page 55 Monday, October 17, 2011 11:48 AM SQL/MX Operators ORDERED_UNION Operator fragment_type Master, ESP, or DP2. text sort_order Sort order of the result of the union. text merge_expression Expression used to determine which child operator to read from next—read from left if true and read from right if false. expr(text) union_type Merge, physical or unspecified. text condExpr Expression used for conditional union. Occurs with IF statement in compound statements.
TP663851.fm Page 56 Monday, October 17, 2011 11:48 AM SQL/MX Operators PACK Operator Prepare TestQuery11 from insert into table_a values('A', 1, 1); DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master union_type ...............
TP663851.fm Page 57 Monday, October 17, 2011 11:48 AM SQL/MX Operators PARTITION_ACCESS Operator (cast(JAVCAT.JAVSCH.T.T3 AS INTEGER SIGNED)RowsetArrayInto 200 ) PARTITION_ACCESS Operator Exchange Group Use the PARTITION_ACCESS operator to describe a portion of an execution plan for a file system interface in which requests are made to DAM. The DAM process runs in parallel to the PARTITION_ACCESS (no waited interface). For more information about exchange operators, see Section 8, Parallelism.
TP663851.fm Page 58 Monday, October 17, 2011 11:48 AM SQL/MX Operators PARTITION_ACCESS Operator Token Followed by ... Data Type begin_part_no_expr Expression to calculate the start partition number (appears instead of begin_key_preds and begin_key_exclusion_expr). expr(text) end_part_no_expr Expression to calculate the end partition number (appears instead of end_key_preds and end_key_exclusion_expr).
TP663851.fm Page 59 Monday, October 17, 2011 11:48 AM SQL/MX Operators PROBE_CACHE Operator fragment_type .......... buffer_size ............ record_length .......... space_usage ............ dp2 31,000 11 19:8:8:40 eid_space_computation on begin_key_preds_(incl) (DETCAT.DETSCH.PTAB00.SINT32_UNIQ = ) end_key_preds_(incl) ... (DETCAT.DETSCH.PTAB00.SINT32_UNIQ = ) begin_part_no_expr ..... \:_sys_hostVarPAPartNo_1606919584 end_part_no_expr .........
TP663851.fm Page 60 Monday, October 17, 2011 11:48 AM SQL/MX Operators ROOT Operator olt_optimization Indicates whether an optimization for short, simple operations is used. The value used is returned if this optimization is used. text statement_index Statement index of this statement as reported by the Measure product. integer upd_action_on_error Determines the type of statement atomicity chosen for a query: text XN_ROLLBACK: Transaction is rolled back if an error occurs.
TP663851.fm Page 61 Monday, October 17, 2011 11:48 AM SQL/MX Operators SAMPLE Operator SAMPLE Operator Data Mining Group The SAMPLE operator occurs as a result of a sample clause in a query. The SAMPLE operator has one child node. The description field for this operator contains: 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.
TP663851.fm Page 62 Monday, October 17, 2011 11:48 AM SQL/MX Operators SAMPLE_FILE_SCAN Operator case(if_then_else((0 <= 0), 0, if_then_else((0 < 1), 1, if_then_else((1 > 0), ((0 assign (1 - 100)) block 1 ), 0))))) SAMPLE_FILE_SCAN Operator The SAMPLE_FILE_SCAN operator occurs as a result of a sample clause in a query, where it is possible to read randomly after satisfying the conditions mentioned in the query. This operator has no child node.
TP663851.fm Page 63 Monday, October 17, 2011 11:48 AM SQL/MX Operators SEQUENCE Operator key_columns Columns used as the primary key. expr(text) executor_predicates Any predicate expression that is not a key predicate evaluated by the executor in DAM. expr(text) mdam_disjunct Disjunct key predicates used by MDAM expr(text) part_key_predicate Predicate expression specified on partitioning key. It is displayed only if partitioning key differs from clustering key.
TP663851.fm Page 64 Monday, October 17, 2011 11:48 AM SQL/MX Operators SEQUENCE Operator The SEQUENCE operator has one child node. The description field for this operator contains: 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.
TP663851.fm Page 65 Monday, October 17, 2011 11:48 AM SQL/MX Operators SEQUENCEGENERATOR Operator SEQUENCEGENERATOR Operator The SEQUENCEGENERATOR operator provides access to the sequence generator table to get the next value or the next block of values. It temporarily suspends the current user transaction, and starts and commits a new transaction to access the sequence generator table. The SEQUENCEGENERATOR operator is isolated into its own ESP by the ESP_ACCESS operator.
TP663851.fm Page 66 Monday, October 17, 2011 11:48 AM SQL/MX Operators SHORTCUT_SCALAR_AGGR Operator sg_cache_initial The initial cache value that influences the default adaptive cache. The cache value dynamically changes at runtime based on the following CQD settings: SEQUENCE_GENERATOR_CACHE_INITIAL value is multiplied by the SEQUENCE_GENERATOR_CACHE_INCRE MENT value, and is compared with SEQUENCE_GENERATOR_CACHE_MAXIM UM.
TP663851.fm Page 67 Monday, October 17, 2011 11:48 AM SQL/MX Operators SORT Operator fragment_type Master, ESP, or DP2. text aggregates Expression of the aggregate function. expr(text) selection_predicate Expression of the WHERE clause. expr(text) The following is an example of the SHORTCUT_SCALAR_AGGR operator: prepare TestQuery25 from select min(f2) from tt22; DESCRIPTION fragment_id ............ 2 parent_frag ............ 0 fragment_type .......... dp2 aggregates ............... min(DETCAT.
TP663851.fm Page 68 Monday, October 17, 2011 11:48 AM SQL/MX Operators SORT_GROUPBY Operator 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.r_name = 'EUROPE') ORDER BY s_acctbal desc, n_name, s_name, p_partkey; DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type ........... master self_referencing_update forced_sort sort_key ............... inverse(DETCAT.DETSCH.SUPPLIER.
TP663851.fm Page 69 Monday, October 17, 2011 11:48 AM SQL/MX Operators SORT_PARTIAL_AGGR_LEAF Operator fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master grouping_columns ....... DETCAT.DETSCH.PTAB09.INT64_6 aggregates ............... count(1 ) SORT_PARTIAL_AGGR_LEAF Operator Groupby Group The SORT_PARTIAL_AGGR_LEAF operator executes a partial group by operation as close to where the data is read as is cost effective.
TP663851.fm Page 70 Monday, October 17, 2011 11:48 AM SQL/MX Operators SORT_PARTIAL_GROUPBY_LEAF Operator operator finalizes the group by at the ESP level. This operator consists of a one-row aggregate without standard aggregate functions (SUM, MIN, MAX, and so on). The root portion occurs in the root. The description field for this operator contains: Token Followed by... Data Type fragment_id A sequential number assigned to the fragment.
TP663851.fm Page 71 Monday, October 17, 2011 11:48 AM SQL/MX Operators SORT_PARTIAL_GROUPBY_ROOT Operator The SORT_PARTIAL_GROUPBY_LEAF operator has one child node. The description field for this operator contains: 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.
TP663851.fm Page 72 Monday, October 17, 2011 11:48 AM SQL/MX Operators SORT_PARTIAL_GROUPBY_ROOT Operator The SORT_PARTIAL_GROUPBY_ROOT operator has one child node. The description field for this operator contains: 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.
TP663851.fm Page 73 Monday, October 17, 2011 11:48 AM SQL/MX Operators SORT_SCALAR_AGGR Operator SORT_SCALAR_AGGR Operator Groupby Group The SORT_SCALAR_AGGR operator occurs for aggregates without a GROUP BY clause. It returns one row. The description field for this operator contains: 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.
TP663851.fm Page 74 Monday, October 17, 2011 11:48 AM SQL/MX Operators SPLIT_TOP Operator SPLIT_TOP Operator Exchange Group The SPLIT_TOP operator describes a portion of an execution plan for a file system interface in which requests to DAM occur with some level of parallel processing. The operator for a SPLIT_TOP operator is always SPLIT_TOP. For more information about exchange operators, see Section 8, Parallelism. The SPLIT_TOP operator has one child node.
TP663851.fm Page 75 Monday, October 17, 2011 11:48 AM SQL/MX Operators SUBSET_DELETE Operator parent_processes ....... 1 child_processes .......... 3 parent_partitioning_fun grouped 1 to 3,PAPA with 3 PA(s), exactly 1 partition child_partitioning_func range partitioned 3 ways on (T03.SINT32_UNIQ) SUBSET_DELETE Operator DAM Subset Group The SUBSET_DELETE operator describes a portion of an execution plan that details how a certain access path is scanned: it deletes more than one row.
TP663851.fm Page 76 Monday, October 17, 2011 11:48 AM SQL/MX Operators SUBSET_UPDATE Operator 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. text columns_retrieved Estimate of the number of columns to be returned.
TP663851.fm Page 77 Monday, October 17, 2011 11:48 AM SQL/MX Operators SUBSET_UPDATE Operator This operator has no child nodes. The description field for this operator contains: 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.
TP663851.fm Page 78 Monday, October 17, 2011 11:48 AM SQL/MX Operators TRANSPOSE Operator The following is an example of the SUBSET_UPDATE operator: PREPARE TestQuery20 FROM UPDATE customer SET c_nationkey = c_nationkey + 1 WHERE c_custkey > 300; DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... Scan_Direction ......... olt_optimization ....... olt_opt_lean ........... iud_type ............... lock_mode ..............
TP663851.
TP663851.fm Page 80 Monday, October 17, 2011 11:48 AM SQL/MX Operators TUPLELIST Operator Token Followed by ... Data Type join_type Inner, left, natural, inner semi, or inner antisemi-join. text join_method Name of join method:nested or in-order nested join.
TP663851.fm Page 81 Monday, October 17, 2011 11:48 AM SQL/MX Operators UNARY_UNION Operator The following is an example of the TUPLELIST operator: PREPARE TestQuery21 FROM INSERT INTO nation VALUES (43, 'botswana', 16, 'african country'), (44, 'france', 23, 'european country'), (45, 'nepal', 88, 'asian country'); DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... tuple_expr .............
TP663851.
TP663851.fm Page 83 Monday, October 17, 2011 11:48 AM SQL/MX Operators UNIQUE_DELETE Operator UNIQUE_DELETE Operator DAM Unique Group The UNIQUE_DELETE operator describes a portion of an execution plan that works on one row only. It deletes zero or one row. The UNIQUE_DELETE operator has no child nodes. The description field for this operator contains: Token Followed by ... Data Type fragment_id A sequential number assigned to the fragment.
TP663851.fm Page 84 Monday, October 17, 2011 11:48 AM SQL/MX Operators UNIQUE_UPDATE Operator parent_frag ............ 0 fragment_type .......... dp2 Scan_Direction ......... forward olt_optimization ....... used olt_opt_lean ........... used iud_type ............... unique_delete DETCAT.DETSCH.TT22 lock_mode .............. not specified, defaulted to lock cursor access_mode ............ not specified, defaulted to read committed columns_retrieved ...... 2 key .....................
TP663851.fm Page 85 Monday, October 17, 2011 11:48 AM SQL/MX Operators UNPACK Operator part_key_predicate Predicate specified on the partitioning key. Displayed only if partitioning key differs from clustering key. expr(text) check_constraint Check constraints in the update table. expr(text) iud_type Type of update followed by table or index name.
TP663851.fm Page 86 Monday, October 17, 2011 11:48 AM SQL/MX Operators VALUES Operator unpack_expression Expression used to extract values from a packed row expr(text) index_value System-generated index used when accessing a packed row expr(text) packing_factor Used to extract the packing factor from the packed row. The packing factor is the number of logical rows in the packed row. integer Create a module file.
TP663851.fm Page 87 Monday, October 17, 2011 11:48 AM SQL/MX Operators VALUES 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 tuple_expr The tuple produced by this node.
TP663851.fm Page 88 Monday, October 17, 2011 11:48 AM SQL/MX Operators VALUES Operator tuple_expr ............. NULL selection_predicates ..... 0. HP NonStop SQL/MX Release 3.
TP663851.fm Page 1 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 2 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 3 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 4 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 5 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 6 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 7 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 9 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 11 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 12 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 14 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 15 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 16 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 17 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 18 Monday, October 17, 2011 11:48 AM 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 . . . . . .
TP663851.fm Page 19 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 20 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 21 Monday, October 17, 2011 11:48 AM Parallelism Degree of Parallelism Figure 8-6.
TP663851.fm Page 22 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 23 Monday, October 17, 2011 11:48 AM Parallelism Degree of Parallelism Figure 8-8.
TP663851.fm Page 24 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 25 Monday, October 17, 2011 11:48 AM 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.
TP663851.fm Page 26 Monday, October 17, 2011 11:48 AM 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.
TP663851.
TP663851.
TP663851.
TP663851.
TP663851.
TP663851.
TP663851.
TP663851.fm Page 8 Monday, October 17, 2011 11:48 AM Index Z HP NonStop SQL/MX Release 3.
