HP NonStop SQL/MX 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.0 Supported Release Version Updates (RVUs) This publication supports J06.
Document History Part Number Product Version 640323-001 NonStop SQL/MX Release 3.
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.
HP NonStop SQL/MX Query Guide Glossary Index Examples Figures Legal Notices What’s New in This Manual vii Manual Information vii New and Changed Information viii About This Manual xi Audience xi Organization xi Related Documentation xii Notation Conventions xv 1.
2. Accessing SQL/MX Data (continued) Contents 2. Accessing SQL/MX Data (continued) Influencing the Optimizer to Use MDAM 2-17 Controlling the Number of Key Columns Used by MDAM MDAM’s Use of DENSE and SPARSE Algorithms 2-18 2-18 3.
5. Forcing Execution Plans (continued) Contents 5.
7. SQL/MX Operators (continued) Contents 7.
7. SQL/MX Operators (continued) Contents 7.
8. Parallelism (continued) Contents 8. Parallelism (continued) Plan Fragments 8-16 Degree of Parallelism 8-21 Influencing Parallel Plans 8-24 System Default Settings That Affect Parallelism 8-25 Index Examples Example 1-1. Example 1-2. OR Optimization DDL 1-13 EXPLAIN statement OPTIONS ‘f’ Output for Query Using CHECK_CONSTRAINT_PRUNING 1-18 Figures Figure 1-1. Figure 4-1. Figure 4-2. Figure 5-1. Figure 5-2. Figure 5-3. Figure 5-4. Figure 5-5. Figure 5-6. Figure 5-7. Figure 8-1. Figure 8-2.
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.0 Supported Release Version Updates (RVUs) This publication supports J06.
Changes to the H06.20/J06.09 manual: What’s New in This Manual Changes to the H06.20/J06.09 manual: • • • Updated the DISPLAY_EXPLAIN function with EXPLAIN statement on pages 1-8, 1-9, 1-11, 1-17, 1-18, 4-1, 4-2, 4-5, 4-6, 4-8, 5-2, 5-8, 8-17, 8-18, and 8-22. Updated 7, SQL/MX Operators to add new tokens to the existing operators and to provide an example for each operator.
Changes to the H06.05 Manual What’s New in This Manual Section New or Changed Information Section 3, Keeping Statistics Current Added information about HIST_SCRATCH_VOL. Section 4, Reviewing Query Execution Plans Clarified information about buffer size settings for PARTITION_ACCESS operators. Section 7, SQL/MX Operators Removed examples and corrected token information.
What’s New in This Manual Changes to the H06.
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.
Related Documentation About This Manual Related Documentation This manual is part of the HP NonStop SQL/MX library of manuals, which includes: Reference Manuals Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start SQL/MX Reference Manual SQL/MX Messages Manual Connectivity Manuals Installation Guides SQL/MX Installation and Management Guide NSM/web Installation Guide SQL/MX Connectivity Service Manual NonStop NS-Series Database Migration Guide SQL/MX Connectivity Ser
Related Documentation About This Manual 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. Reference Manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements.
Related Documentation About This Manual 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.
Notation Conventions About This Manual 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. SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage an SQL/MP database.
General Syntax Notation About This Manual [ ] 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. For example: FC [ num ] [ -num ] [ text ] K [ X | D ] address { } Braces.
Change Bar Notation About This Manual 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.
Change Bar Notation About This Manual HP NonStop SQL/MX Query Guide —640323-001 xviii
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.
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. The necessary table information is listed in the schema metadata tables from the SQL/MX catalog.
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.
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).
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. Such plans or subplans can be executed by the executor because they have physical properties, and their cost can be estimated by the optimizer.
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.
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. For example: WHERE AGE_IN_DAYS>(2005-1960)*365 When constant folded, this expression becomes WHERE AGE_IN_DAYS>16425.
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. Performance-related default values include OPTIMIZATION_LEVEL, which indicates the effort the optimizer should use in optimizing queries.
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.
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.
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. This query uses the DDL shown in Example 1-1 on page 1-13.
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).
Compiling and Executing a Query Improving Query Performance Example 1-1.
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.
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. >> >>CONTROL QUERY SHAPE HASH_JOIN (CUT, HASH_JOIN (CUT, CUT)); --- SQL operation complete.
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. --- 0 row(s) selected. >> Note.
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.
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.
Compiling and Executing a Query Factors That Can Affect Compile Time Factors That Can Affect Compile Time • • • • • • The number of tables joined, which is the most important factor in determining query complexity Other query complexity factors, such as the number of group bys, unions, and subqueries The number of predicates on the query and the number of columns in the predicate The presence of indexes, which can increase complexity as the new access path must be considered Whether statistics have been
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.
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.
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. Index-Only Access Index-only access refers to an index that fully satisfies a query without accessing the base table.
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.
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. The index row is located by positioning to the requested data.
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. (If necessary, SQL can also read the table in reverse order.
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.
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.
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.
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.
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.
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.1 WHERE RETAIL_PRICE = 20 AND ITEM = 7; The rows will either get changed, in which case the selection condition does not qualify for the newly updated row, or the rows will get updated to the same exact value as before, in which case the index row does not change.
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.
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; ...
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.
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. By reducing the number of blocks read, you also reduce the cost of applying the predicates to the records, because fewer records are scanned.
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.
Accessing SQL/MX Data Influencing the Optimizer to Use MDAM This IN predicate is converted into: COL1 = 1 OR COL1 = 2 OR COL1 = 3 Consider: SELECT * FROM T WHERE ((A = 4 AND B IN (2,5)) OR (A = 7 AND B IN (6,9)) ; The optimizer transforms this query into these predicate sets: (A (A (A (A = = = = 4 4 7 7 AND AND AND AND B B B B = = = = 2) OR 5) OR 6) OR 9) Redundant and Contradictory Predicates MDAM eliminates predicates that conflict with other predicates.
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. For example, if the EMPLOYEE table contains the columns STATE, JOBCODE, DEPTNUM, LAST_NAME, FIRST_NAME and you frequently perform queries based on JOBCODE, STATE, and DEPTNUM, place the columns in that order.
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. If you force a DENSE algorithm, the executor does an adaptive DENSE or SPARSE and switches accordingly when it finds that the chosen algorithm is not efficient for the column it is accessing.
Accessing SQL/MX Data MDAM’s Use of DENSE and SPARSE Algorithms HP NonStop SQL/MX Query Guide —640323-001 2- 20
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.
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. If statistics have not been updated, the compiler uses the block count from the file label.
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. File names catalog.schema.HISTOGRAMS catalog.schema.
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. The index is INDEX.
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.
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.
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. The default value is SYSTEM.
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.
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. In MXCI, use EXPLAIN to review the cost information for your query. e.
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. If necessary, restore backup histogram tables: > DELETE FROM HISTOGRAMS; > INSERT INTO HISTOGRAMS SELECT * FROM myhist where table_uid in (select object_uid from CAT.DEFINITION_SCHEMA_VERSION_3000.
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.
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.
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. Summary detail for the operators displays in the lower right pane.
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. For a full list of valid operator types, see Section 7, SQL/MX Operators.
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.
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. The EXPLAIN function returns one row for each operator used to evaluate the query.
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.
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.
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.EMPLOYEE.
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.
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. The compiler performs a thorough two-pass optimization.
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.
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.
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. Visual Query Planner uses the EXPLAIN function internally to extract the information about the query plan.
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. After you are connected to your data source, notice that the status message along the bottom of the window displays the data source name.
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. Selecting Tasks on the Toolbar You can also select tasks on the toolbar positioned immediately below the menu bar. VST502.
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. Accessing Additional Information About Operators To access additional information about each operator in the Properties dialog box, right-click an operator entry.
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. Select the ? icon for contextsensitive help for the operator.
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.tablename of the table scanned.
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.
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.
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.00 --- 62 row(s) selected.
Reviewing Query Execution Plans • • • Using Measure The number of times static SQL statements were recompiled and the elapsed time needed for recompilation The number of times the executor server processes were started up and the elapsed time to do this The number of open requests issued by SQL and the elapsed time to do this Statement Execution (SQLSTMT) The SQLSTMT report provides information for specific statements of modules executed by an SQL process.
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. When compared to Measure information, they can reveal network problems or other types of delays.
5 Forcing Execution Plans Use the information in this section to make decisions about forcing query execution plans. • • • • • • Why Force a Plan? on page 5-1 Checklist for Forcing Plans on page 5-2 Displaying the Optimized Plan on page 5-2 Reviewing the Optimized Plan on page 5-3 Translating the Operator Tree to Text Format on page 5-5 Writing the Forced Shape Statement on page 5-8 The SQL/MX optimizer attempts to generate the most cost-efficient plan available.
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. Review the optimized plan and costs associated with the operations. 3.
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.jobcode >+FROM dept, employee, job >+WHERE dept.
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.
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.
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. The text format for the shape is displayed immediately before the query output.
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. For more information about using the Visual Query Planner, see Section 4, Reviewing Query Execution Plans.
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. Write FILE_SCAN and FILE_SCAN_UNIQUE nodes as SCAN.
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. If you specify a partial tree, ANYTHING marks the point where you want the optimizer to “take over” and choose the best solution.
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.
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.
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). This result is passed on to the EXCHANGE node.
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.
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.
Forcing Execution Plans Forcing Parallel Plans use a CUT for the children, or use an ESP_EXCHANGE above the children for any necessary repartitioning.
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. • Deferring to the optimizer to choose exchange or sort operators In the preceding forced join examples, you needed to specify the choice of exchange and sort operators as part of the CONTROL QUERY SHAPE statement.
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.
Forcing Execution Plans Forcing Parallel Plans HP NonStop SQL/MX Query Guide —640323-001 5- 18
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.
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.
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.
Query Plan Caching • Examples of Queries That Are Not Cacheable Math functions (ABS, ATAN, ATAN2, CEILING, COS, COSH, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, SIGN, SIN, SINH, SQRT, TAN, TANH are cacheable): SELECT ABS(i), ATAN(10), ATAN2(x,y), CEILING(r), COS(i), COSH(i), DEGREES(i), EXP(i), FLOOR(i), LOG(i), LOG10(i), PI(), POWER(b,e), RADIANS(i), SIGN(i), SIN(i), SINH(i), SQRT(i), TAN(i), TANH(i) FROM T; • Replace functions are cacheable: UPDATE T SET job=replace(job, 'IM', 'IT') WHERE K
Query Plan Caching Examples of Queries That Are Not Cacheable However, a LIKE predicate conjunct of a key equipredicate is cacheable: SELECT * FROM T WHERE K=? AND S LIKE 'c%'; • Queries that have only OR predicates are not cacheable: SELECT * FROM T WHERE a=1 OR b=2; -- is not cacheable However, an OR predicate conjunct of a key equipredicate is cacheable: SELECT * FROM T WHERE K=? and (a=1 OR b=2); • Queries that have BETWEEN predicates are not cacheable: SELECT * FROM T WHERE a BETWEEN 1 AND 9; --
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.
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.
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.
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. When a query is pinned in the cache, it usually cannot be displaced from the cache unless the cache becomes full of pinned queries.
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.
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.
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. COMPILATION_TIME INT UNSIGNED Time in milliseconds it took to compile the query associated with this entry.
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.
Query Plan Caching Querying the Query Plan Caching Virtual Tables This query selects all columns of the QUERYCACHEENTRIES virtual table (formatted for readability): SELECT * FROM TABLE(QUERYCACHEENTRIES()); ROW_ID -----0 1 2 NUM_HITS -------1 0 0 PLAN_ID -----------------211894097543468116 211894097552547493 211894097548497817 PHASE ---------BINDING BINDING BINDING NUM_PARAMS ---------0 0 0 OPTIMIZATION_LEVEL ---------------------2 2 2 PARAM_TYPES ----------- AVERAGE_HIT_TIME SHAPE ---------------4
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. If no query plans have been cached, no rows are returned.
Query Plan Caching Reviewing the Query Plan Caching Statistics With the DISPLAY_QC and DISPLAY_QC_ENTRIES DISPLAY_QC_ENTRIES Command The DISPLAY_QC_ENTRIES command accesses the information in the QUERYCACHEENTRIES function and displays these columns: Column Name Type Source column in QUERYCACHEENTRIES Function ROWID CHAR(8) ROW_ID TEXT CHAR(36) TEXT NUMHITS CHAR(8) NUM_HITS PH CHAR(1) PHASE COMPTIME CHAR(8) COMPILATION_TIME AVGHITTIME CHAR(8) AVERAGE_HIT_TIME DISPLAY_QC_ENTRIES; ROWID
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.
SQL/MX Operators • • • • • • • • • • • • • • • • • • • • • • • • • • PACK Operator on page 7-53 PARTITION_ACCESS Operator on page 7-54 PROBE_CACHE Operator on page 7-56 PROBE_CACHE Operator on page 7-56 SAMPLE Operator on page 7-58 SAMPLE_FILE_SCAN Operator on page 7-59 SEQUENCE Operator on page 7-61 SHORTCUT_SCALAR_AGGR Operator on page 7-62 SORT Operator on page 7-63 SORT_GROUPBY Operator on page 7-64 SORT_PARTIAL_AGGR_LEAF Operator on page 7-65 SORT_PARTIAL_AGGR_ROOT Operator on page 7-65 SORT_PARTIAL_
SQL/MX Operators Group Operator Data Mining SAMPLE SEQUENCE TRANSPOSE Exchange ESP_EXCHANGE PARTITION_ACCESS SPLIT_TOP Groupby HASH_GROUPBY HASH_PARTIAL_GROUPBY_LEAF HASH_PARTIAL_GROUPBY_ROOT SHORTCUT_SCALAR_AGRR SORT_GROUPBY SORT_PARTIAL_AGGR_LEAF SORT_PARTIAL_AGGR_ROOT SORT_PARTIAL_GROUPBY_LEAF SORT_PARTIAL_GROUPBY_ROOT SORT_SCALAR_AGGR Insert INSERT INSERT_VSBB Join HYBRID_HASH_ANTI_SEMI_JOIN HYBRID_HASH_JOIN HYBRID_HASH_SEMI_JOIN LEFT_HYBRID_HASH_JOIN LEFT_MERGE_JOIN LEFT_NESTED_JOIN LEFT_OR
SQL/MX Operators Operators Group Operator Rowset PACK UNPACK Sort SORT Stored Function EXPLAIN Tuple EXPR TUPLELIST VALUES 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.
SQL/MX Operators CALL Operator ); create table table_b (col1 char(5) NOT NULL NOT DROPPABLE , col2 int NOT NULL NOT DROPPABLE , col3 smallint , owner_count int , CONSTRAINT table_b_PRIMARY_KEY PRIMARY KEY (col1 ASC, col2 ASC) NOT DROPPABLE ); ALTER TABLE table_a ADD CONSTRAINT table_a_KEY FOREIGN KEY (col1, col2) REFERENCES table_b(col1, col2) on update restrict on delete restrict DROPPABLE; CREATE TRIGGER table_a_Owner_Count AFTER INSERT ON table_a REFERENCING NEW AS newrow FOR EACH ROW UPDATE table_b SE
SQL/MX Operators CALL Operator The CALL 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. The value is (none) for the master executor. integer fragment_type Master, ESP, or DP2.
SQL/MX Operators CURSOR_DELETE Operator The following is an example of the CALL operator: create procedure u300populateA ( in table_name char(20) ) external name 'TEST300.populateA' language java parameter style java external path 'W:/regress/udr' modifies sql data; prepare TestQuery36 from call u300populateA('customer'); DESCRIPTION fragment_id ............ parent_frag ............ fragment_type .......... parameter_modes ........ routine_name ........... routine_label .......... sql_access_mode ........
SQL/MX Operators CURSOR_DELETE Operator fragment_type Master, ESP, or DP2. text olt_optimization Indicates whether an optimization for short and simple operations is used. Its value is used or not used. text olt_opt_lean Indicates for short, simple operations whether a further optimization that reduces the physical size of the plan is used. Its value is used or not used. text iud_type Type of delete followed by table or index name. expr(text) begin_key Expression of the begin key predicates.
SQL/MX Operators CURSOR_UPDATE Operator CURSOR_UPDATE Operator DAM Unique Group The CURSOR_UPDATE operator describes a portion of an execution plan that works on one row only. The CURSOR_UPDATE operation is performed by first retrieving rows from the table and then updating each row that is required. This operation differs from SUBSET_UPDATE, in which the read and update are performed in a combined operation. The CURSOR_UPDATE operator has no child nodes.
SQL/MX Operators ESP_EXCHANGE Operator AND table_a.col3 = 1 ); DESCRIPTION fragment_id ............ 4 parent_frag ............ 0 fragment_type .......... dp2 Scan_Direction ......... forward olt_optimization ....... not used olt_opt_lean ........... not used iud_type ............... cursor_update DETCAT.DETSCH.TABLE_B lock_mode .............. not specified, defaulted to lock cursor access_mode ............ not specified, defaulted to read committed columns_retrieved ...... 4 new_rec_expr ...........
SQL/MX Operators ESP_EXCHANGE Operator Token Followed by ... Data Type parent_processes Indicates the number of processes that the ESPs in the ESP_EXCHANGE operator communicate with either additional ESPs (as indicated by another ESP_EXCHANGE operator) or the master process. integer child_processes Indicates the number of processes that supply the ESPs of an operator with rows.
SQL/MX Operators ESP_EXCHANGE Operator insert into tabl3 values (1,2,3,1,1),(4,5,6,1,1),(7,8,9,1,1),(1,2,3,1,1),(4,5,6,1,1), (7,8,9,1,1), (9,2,3,1,1),(4,5,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1), (7,8,9,1,1), (9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1), (7,8,9,1,1), (9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1), (7,8,9,1,1), (9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1), (7,8,9,1,1), (9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1), (7,8,9,1,1), (1
SQL/MX Operators EXPLAIN Operator DESCRIPTION fragment_id ........... 13 parent_frag ............ 0 fragment_type .......... esp buffer_size ............ 6,250 record_length ......... 40 parent_processes ....... 1 child_processes ........ 2 child_partitioning_func hash partitioned 2 ways on (([0]ValueIdUnion(\DMR11.$DATA04.ZSDWC1HX.ZVB7V200 .A, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.A, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.A, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.A, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.
SQL/MX Operators EXPR Operator 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. 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.
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. integer parent_frag The fragment_id for the parent fragment of the current fragment.
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. text 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.
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 .......... dp2 olt_optimization ....... not used olt_opt_lean ..........
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. The value is (none) for the master executor.
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. text fast_scan Indicates whether an optimization for a simple scan operation is used.
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.
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. The value is (none) for the master executor.
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. HASH_PARTIAL_GROUPBY_LEAF Operator Groupby Group The HASH_PARTIAL_GROUPBY_LEAF operator executes a partial group by operation as close to where the data is read as is cost effective.
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-22. The HASH_PARTIAL_GROUPBY_ROOT operator has one child node.
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-25 and HYBRID_HASH_SEMI_JOIN Operator on page 7-26. The HYBRID_HASH_ANTI_SEMI_JOIN 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.
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.
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 ............ hash_join_predicate ....
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.
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. text key_type Simple or MDAM.
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.C_CUSTKEY = 1000) end_key ................ (DETCAT.DETSCH.CX1.
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. It is displayed only if partitioning key differs from clustering key.
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. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
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.DETSCH.CUSTOMER.C_ACCTBAL), (C_MKTSEGMENT assign DETCAT.
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 constrains in the insert table.
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. The value is (none) for the master executor.
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-40. 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.
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. If an outer row finds no matches in the inner table, the outer row is returned, and nulls are supplied for inner table values. See NESTED_JOIN Operator on page 7-45.
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.
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.
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.col1 and TAB2.col2 < 30 group by TAB1.col1, TAB1.col2 order by TAB1.
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.
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. The value is (none) for the master executor.
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-40. The MERGE_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.
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. The operator for a MERGE_UNION operator is always MERGE_UNION.
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.
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. The value is (none) for the master executor.
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 ...
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-45. 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. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
SQL/MX Operators ORDERED_HASH_ANTI_SEMI_JOIN fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master join_type .............. inner semi join_method .............. nested ORDERED_HASH_ANTI_SEMI_JOIN Join Group The ORDERED_HASH_ANTI_SEMI_JOIN operator returns only one row for every outer row when no match occurs. This operator preserves the order of the outer table and does not overflow to disk.
SQL/MX Operators ORDERED_HASH_JOIN Operator ( SELECT ps_suppkey from partsupp) 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 anti-semi hash (DETCAT.DETSCH.SX1.S_SUPPKEY = DETCAT.DETSCH.PSX1.
SQL/MX Operators ORDERED_HASH_SEMI_JOIN Operator parallel_join_type Type1 or Type2, depending on parallel join algorithm. text reuse_comparison_values List of values that cause the hash table to be rebuilt when values change. expr(text) selection_predicates Expression of the WHERE clause.
SQL/MX Operators ORDERED_HASH_SEMI_JOIN Operator The ORDERED_HASH_SEMI_JOIN operator has two child nodes. The description field for this operator contains: 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.
SQL/MX Operators ORDERED_UNION Operator hash_join_predicate .... (DETCAT.DETSCH.ORDERS.O_ORDERKEY = DETCAT.DETSCH.LX5.L_ORDERKEY) ORDERED_UNION Operator The ORDERED_UNION operator ensures that its left and right children work one at a time. It first receives rows from its left child. It then enables the right child to work. In effect, for the same request, ORDERED_UNION operator produces rows from left child followed by rows from right child.
SQL/MX Operators PACK Operator ALTER TABLE table_a ADD CONSTRAINT table_a_KEY FOREIGN KEY (col1, col2) REFERENCES table_b(col1, col2) on update restrict on delete restrict DROPPABLE ; CREATE TRIGGER table_a_Owner_Count AFTER INSERT ON table_a REFERENCING NEW AS newrow FOR EACH ROW UPDATE table_b SET owner_count = ( SELECT count(*) FROM table_a WHERE (table_b.col1,table_b.col2)= (table_a.col1,table_a.col2) AND table_a.col3 = 1 ) WHERE (table_b.col1,table_b.col2)= (newrow.col1,newrow.
SQL/MX Operators PARTITION_ACCESS Operator parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor. integer fragment_type Master, ESP, or DP2. text pack_expr Expression used to pack values of a row into a packed row. expr(text) Create a module file. For details on creating module file, see the SQL/MX Programming Manual for C and COBOL.
SQL/MX Operators PARTITION_ACCESS Operator Token Followed by ... Data Type buffer_size Buffer size for messages between PARTITION_ACCESS operator and DAM. integer record_length Length of the record that is returned by DAM. integer begin_key_preds (incl | excl) Predicates to determine the begin key, which might include or exclude the specified key. expr(text) end_key_preds (incl | excl) Predicates to determine the end key, which might include or exclude the specified key.
SQL/MX Operators PROBE_CACHE Operator Token Followed by ... Data Type child_partitioning_function Type of bottom partitioning and contains summary information about the parallel plan. text begin_part_selection_expr Expression used by SPLIT_TOP or PARTITION_ACCESS to compute the begin_part_no_expr. expr(text) end_part_selection_expr Expression used by SPLIT_TOP or PARTITION_ACCESS to text compute the end_part_no_expr.
SQL/MX Operators ROOT Operator ROOT Operator Root Group The ROOT operator is the root, or the top node, of an execution plan and describes the SQL query. The operator for a ROOT is always ROOT. The ROOT operator has one child. The description field for this operator contains: Token Followed by ...
SQL/MX Operators SAMPLE Operator l_discount BETWEEN .06 - 0.01 AND .06 + 0.01 AND l_quantity < 24; DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master statement_index ........ 0 xn_access_mode ......... read_only plan_version ........... 2,200 SCHEMA ................. DETCAT.DETSCH HASH_JOINS ............. ON NESTED_JOINS ........... ON select_list ............ cast(cast((sum((cast(DETCAT.DETSCH.LX3.L_EXTENDEDPRI CE AS BIG NUM(12,2) SIGNED) * cast(DETCAT.
SQL/MX Operators SAMPLE_FILE_SCAN Operator balance_expression Expression representing the sampling expression. A simple random selection, but could be more complex if the sample clause contains a balance clause. expr(text) sample_type Indicates the type of sampling being performed. Possible values are RANDOM, PERIODIC, FIRST, and CLUSTER. text required_order Specified order keys for a sample operation. expr(text) For more information about data mining, see the SQL/MX Data Mining Guide.
SQL/MX Operators SAMPLE_FILE_SCAN Operator olt_optimization Indicates whether an optimization for short, simple operations is used. Its value is used or not used. text olt_opt_lean Indicates for short, simple operations whether a further optimization that reduces the physical size of the plan is used. Its value is used or not used. text scan_type FILE_SCAN followed by table name. text scan_direction Direction in which table is scanned: forward or reverse.
SQL/MX Operators SEQUENCE Operator parent_frag ............ fragment_type .......... olt_optimization ....... olt_opt_lean ........... scan_type .............. 0 dp2 not used not used sample full scan of table DETCAT.DETSCH.TT22 scan_direction ......... forward key_type ............... simple lock_mode .............. not specified, defaulted to lock cursor access_mode ............ not specified, defaulted to read committed columns_retrieved ...... 2 fast_replydata_move .... used key_columns ............
SQL/MX Operators SHORTCUT_SCALAR_AGGR Operator sequence_functions Represents the list of sequence functions that must be evaluated by this SEQUENCE operator. ItemExpr tree num_history_rows Size of the history buffer (in rows). This number of rows is kept in an integer buffer and is available for access by the sequence functions. Any access to a row outside this buffer results in a NULL value. The default value for this parameter is 1024 rows.
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.DETSCH.II22.
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.S_ACCTBAL), DETCAT.DETSCH.NATION.N_NAME, DETCAT.DETSCH.
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. This strategy reduces the amount of data that must be redistributed for a query.
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. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments.
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. The value is (none) for the master executor.
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. The value is (none) for the master executor.
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.
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. The description field for this operator contains: Token Followed by ...
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. A subset operation performs the read and delete in a combined operation.
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.
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. The value is (none) for the master executor. integer fragment_type Master, ESP, or DP2.
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 .............. 2 0 dp2 forward not used not used subset_update DETCAT.DETSCH.
SQL/MX Operators TUPLE_FLOW Operator The following is an example of the TRANSPOSE operator: prepare TestQuery23 from insert into T061_T232OR1 ( select cast(c1 || c2 || c3 || cast(c1 || c2 || c3 || cast(c1 || c2 || c3 || cast(c1 || c2 || c3 || from (values(1)) t transpose '0', '1', '2', as c1 transpose '0', '1', '2', as c2 transpose '0', '1', '2', as c3 transpose '0', '1', '2', as c4 transpose '0', '1', '2', as c5 ); c4 c4 c4 c4 || || || || c5 c5 c5 c5 as as as as int), int), int), int) '3', '4', '5'
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.
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 .............
SQL/MX Operators UNARY_UNION Operator The following is an example of the UNARY_UNION operator: create table table_a (col1 char(5) NOT NULL NOT DROPPABLE , col2 int NOT NULL NOT DROPPABLE , col3 smallint , CONSTRAINT table_a_PRIMARY_KEY PRIMARY KEY (col1 ASC, col2 ASC) NOT DROPPABLE ); create table table_b (col1 char(5) NOT NULL NOT DROPPABLE , col2 int NOT NULL NOT DROPPABLE , col3 smallint , owner_count int , CONSTRAINT table_b_PRIMARY_KEY PRIMARY KEY (col1 ASC, col2 ASC) NOT DROPPABLE ); ALTER TABLE tab
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. 0 is always the master executor and 1 is reserved for the EXPLAIN plan.
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 .....................
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 constrains in the update table. expr(text) iud_type Type of update followed by table or index name. expr(text) The following is an example of the UNIQUE_UPDATE operator: prepare TestQuery27 from UPDATE tt22 SET f2 = 1 WHERE f1 = 2009; DESCRIPTION olt_optimization ....... fragment_id ............
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. For details on creating module file, see the SQL/MX Programming Manual for C and COBOL.
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.
SQL/MX Operators VALUES Operator tuple_expr ............. NULL selection_predicates ..... 0.
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.
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. Pipelining is seen in almost all query plans.
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.
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. A plan fragment executes within an ESP process if and only if its root node is an ESP_EXCHANGE node. Master executor or root.
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.vsd For more details about understanding plan fragments, plan fragment boundaries, and reading the EXPLAIN output, see Explaining Parallel Plans on page 8-12.
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.
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.
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.
Parallelism Join With Matching Partitions 0 - 100 TB L1 Tbl A Prt 1 0 - 100 Tbl A Prt 2 101 - 101 TB L2 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. Hash partitioned tables cannot use logical subpartitioning.
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. For more information, see Influencing Parallel Plans on page 8-24.
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. For parallelism of the MERGE_UNION operator, the optimizer supports a matching partitions union (based on the matching partitions join algorithm).
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. A parallel sort requires a merge of sorted streams by the master executor or by other ESPs.
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.
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. Two ESPs are started to perform the merge join in parallel (as indicated by the bottom_degree_parallelism token for the ESP_EXCHANGE).
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.
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.
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.
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 . . . . . .
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.vsd Figure 8-5 shows the ESP_EXCHANGE node that forms the boundary between the root fragment (master executor) and the first ESP fragment. Figure 8-5.
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.262715 PROBES: 1 merged_order: inverse(cast(cast((cast(sum((cast(indexcol (TPCD.XMPS.LINEITEM.
Parallelism Degree of Parallelism Figure 8-6.
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.
Parallelism Degree of Parallelism Figure 8-8.
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.7 PROBES: 1 top_degree_parallelism: 1 bottom_degree_parallelism: 2 top_num_data_streams: 1 bottom_num_data_streams: 2 bottom_partitioning_function: logphys partitioned(grouping, PAPA with 2 PA(s), log=exactly 1 partition, phys=range partitioned 2 ways on (([260]equiv(TPCD.XMPS.ORDERS.
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. Certain defaults might affect the parallel plan, as noted next.
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. If this attribute is not set by the user (SYSTEM setting), the optimizer calculates the degree of parallelism.
Index A Access cost alternate-index access 2-5 index-only access 2-4 MDAM 2-15 single subset access 2-14 storage-key access 2-2 table scan 2-6 Access method alternate index 2-4 alternative to key-sequenced access 2-5 index only 2-2 storage-key 2-1 Access path description of 2-1 forcing 2-11 MDAM 2-13 unexpected 2-9 Alternate-index access approximate cost 2-5 description of 2-4 update issues 2-9 ATTEMPT_ASYNCHRONOUS_ACCESS system default setting 8-25 ATTEMPT_ESP_PARALLELISM system default setting 8-25 B Bin
E Index E ESP (executor server process) cost associated with 8-6 how chosen 8-13 operators 8-13 parallelism 5-13 plan fragments 8-16 ESP_EXCHANGE operator 7-10, 8-16 Exchange nodes 8-3 Executor 1-19 EXPLAIN operator 7-13 EXPLAIN output columns in 4-3 description of 4-1 generating 5-2 operator tree 4-3 tokens for parallelism 8-16 EXPLAIN output columns CARDINALITY 4-4 DESCRIPTION 4-4 DETAIL_COST 4-4 LEFT_CHILD_SEQ_ NUM 4-4 MODULE_NAME 4-3 OPERATOR 4-4 OPERATOR_COST 4-4 PLAN_ID 4-4 RIGHT_CHILD_ SEQ_NUM 4-4
I Index HIST_SCRATCH_VOL default attribute 3-5 HYBRID_HASH_ANTI_SEMI_JOIN operator 7-24 HYBRID_HASH_JOIN operator 7-25 HYBRID_HASH_SEMI_JOIN operator 7-26 I Implementation rules 1-5 Independent parallelism 8-2 Index, performance improvement 1-10 Index-only access approximate cost 2-4 description of 2-2 using 2-3 INDEX_SCAN operator 7-27 INDEX_SCAN_UNIQUE operator 7-29 INSERT operator 7-31 Insert, in parallel plans 8-12 INSERT_VSBB operator 7-32 INTERACTIVE_ACCESS system default setting 2-6 J Joins alter
N Index MDAM (continued) tips for using 2-17 Measure entity SQLPROC 4-22 SQLSTMT 4-23 Measure product 4-22 MERGE_ANTI_SEMI_JOIN operator 7-39 MERGE_JOIN operator 7-40 MERGE_SEMI_JOIN operator 7-41 MERGE_UNION operator 7-43 Migrating from SQL/MP, forced plans 5-10 Multicolumn statistics 3-8 MultiUnion operator 7-44 N Nested join, replicate, no broadcast 8-10 NESTED_ANTI_SEMI_JOIN operator 7-44 NESTED_JOIN operator 7-45 NESTED_SEMI_JOIN operator 7-46 NonStop Data Access Manager (DAM) See DAM Normalizer 1-2
Q Index Parallelism combining different types 8-12 degree of 8-21 EXPLAIN output 8-12 forcing plans with 5-13 group by 8-11 how optimizer avoids repartitioning 8-8 independent 8-2 influencing 8-24 insert 8-12 matching partitions algorithm 8-6 multilevel 8-12 operator 8-2 parallel access to inner table 8-10 parallel joins 8-8 partitioned 8-1 pipelined 8-2 plan fragments 8-16, 8-21 recognizing in plans 8-13 relationship of ESPS to CPUs 8-24 scans 8-6 select 8-12 sort 8-12 system default settings 8-25 tokens
R Index Query plan caching (continued) description 6-1 noncacheable queries 6-4 overview of 6-1 sizing 6-6 Query tree compiler 1-2 Queues 1-19 R Range predicates, intervening, how MDAM handles 2-16 Redundant predicates, how MDAM handles 2-17 REMOTE_ESP_ALLOCATION system default setting 4-12 Repartition 8-7, 8-8 Replicate by broadcast, in hash join 8-10 Replicate, no broadcast, in nested join 8-10 ROOT operator 7-56 Rows, duplicate 2-17 S SAMPLE operator 7-57 SAMPLE_FILE_SCAN operator 7-58 Sampling accur
T Index Storage-key access approximate cost 2-2 description of 2-1 SUBSET_DELETE operator 7-69 SUBSET_UPDATE operator 7-70 System default setting GEN_EIDR_BUFFER_SIZE 4-9 GEN_PA_BUFFER_SIZE 4-9 JOIN_ORDER_BY_USER 4-10 OPTIMIZATION_LEVEL 4-10 OPTS_PUSH_DOWN_DAM 4-11 REMOTE_ESP_ALLOCATION 4-12 ZIG_ZAG_TREES 4-13 System default settings and parallelism 8-25 V VALUES operator 7-80 Views, forced plans 5-10 Visual Query Planner accessing 4-14 connecting to ODBC with 4-15 online help 4-14 purpose of 4-14 requir
Z Index HP NonStop SQL/MX Query Guide —640323-001 Index -8
Legal Notices Legal Notices 1 What’s New in This Manual vii Manual Information vii New and Changed Information vii About This Manual xi Audience xi Organization xi Related Documentation xii Notation Conventions xv 1. Compiling and Executing a Query Overview 1-1 How the Compiler Works 1-2 Compilation Steps 1-2 Parsing, Binding, and Normalizing 1-3 Query Plan Caching 1-5 Improving Query Performance 1-6 Factors That Can Affect Compile Time 1-19 How the Executor Processes the Plan 1-19 2.
4. Reviewing Query Execution Plans Performance Issues and Accuracy in Sampling Collecting Statistics for Multiple Columns 3-8 Testing the Results of UPDATE STATISTICS 3-8 Testing the Results for SQL/MP Tables 3-8 Testing the Results for SQL/MX Tables 3-9 3-7 4.
6. Query Plan Caching What Happens if No Plan Is Returned? 5-10 Migrating Forced Shapes From NonStop SQL/MP 5-10 Forcing Group By Operations to the Data Access Manager Forcing Parallel Plans 5-13 5-10 6.
7.
8. Parallelism TUPLELIST Operator 7-76 UNARY_UNION Operator 7-77 UNIQUE_DELETE Operator 7-79 UNIQUE_UPDATE Operator 7-80 UNPACK Operator 7-81 VALUES Operator 7-82 8.
Index HP NonStop SQL/MX Query Guide —640323-001 6
Examples Examples Example 1-1. Example 1-2.
Examples HP NonStop SQL/MX Query Guide —640323-001 2
Figures Figures Figure 1-1. Figure 4-1. Figure 4-2. Figure 5-1. Figure 5-2. Figure 5-3. Figure 5-4. Figure 5-5. Figure 5-6. Figure 5-7. Figure 8-1. Figure 8-2. Figure 8-3. Figure 8-4. Figure 8-5. Figure 8-6. Figure 8-7. Figure 8-8.
Figures HP NonStop SQL/MX Query Guide —640323-001 2
Tables Tables Table 1-1. Table 3-1. Table 3-2.
