The DISPLAY_QC command accesses the information in the QUERYCACHE function and displays these columns:
>>DISPLAY_QC;
AVGSIZE CURSIZE MAXSIZE NPINNED NRECOM NRETR NCACHE NHITS --- --- --- --- --- --- --- --- 31 35 1024 0 0 0 1 0 --- SQL operation complete.
Column Name Type Source column in QUERYCACHE Function
AVGSIZE CHAR(8) AVG_PLAN_SIZE
CURSIZE CHAR(8) CURRENT_SIZE
MAXSIZE CHAR(8) MAX_CACHE_SIZE
NPINNED CHAR(8) NUM_PINNED
NRECOM CHAR(8) NUM_RECOMPILES
NRETR CHAR(8) NUM_RETRIES
NCACHE CHAR(8) NUM_CACHEABLE_PARSING + NUM_CACHEABLE_BINDING NHITS CHAR(8) NUM_CACHE_HITS_PARSING +
Query Plan Caching
HP NonStop SQL/MX Query Guide —640323-001 6- 16
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:
DISPLAY_QC_ENTRIES;
ROWID TEXT NUMHITS PH COMPTIME AVGHTIME --- --- --- - --- --- 0 select * from job; 0 B 88 0 1 select * from dept; 0 B 115 0 2 select * from employee; 0 B 1605 0 --- SQL operation complete.
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
HP NonStop SQL/MX Query Guide —640323-001 7 -1
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.
This section defines all operators in alphabetic order:
•
BLOCKED_UNION Operator on page 7-4•
CALL Operator on page 7-5•
CURSOR_DELETE Operator on page 7-7•
CURSOR_UPDATE Operator on page 7-9•
ESP_EXCHANGE Operator on page 7-10•
EXPLAIN Operator on page 7-13•
EXPR Operator on page 7-14•
EXPLAIN_CMD Operator on page 7-15•
FILE_SCAN Operator on page 7-15•
FILE_SCAN_UNIQUE Operator on page 7-17•
FirstN Operator on page 7-20•
HASH_GROUPBY Operator on page 7-20•
HASH_PARTIAL_GROUPBY_LEAF Operator on page 7-22•
HASH_PARTIAL_GROUPBY_ROOT Operator on page 7-23•
HYBRID_HASH_ANTI_SEMI_JOIN Operator on page 7-24•
HYBRID_HASH_JOIN Operator on page 7-25•
HYBRID_HASH_SEMI_JOIN Operator on page 7-26•
INDEX_SCAN Operator on page 7-27•
INDEX_SCAN_UNIQUE Operator on page 7-29•
INSERT Operator on page 7-31•
INSERT_VSBB Operator on page 7-32•
LEFT_HYBRID_HASH_JOIN Operator on page 7-33•
LEFT_MERGE_JOIN Operator on page 7-34•
LEFT_NESTED_JOIN Operator on page 7-36•
LEFT_ORDERED_HASH_JOIN Operator on page 7-37•
MATERIALIZE Operator on page 7-38•
MERGE_ANTI_SEMI_JOIN Operator on page 7-39•
MERGE_JOIN Operator on page 7-40•
MERGE_SEMI_JOIN Operator on page 7-42•
MERGE_UNION Operator on page 7-43•
MultiUnion Operator on page 7-44•
NESTED_ANTI_SEMI_JOIN Operator on page 7-44•
NESTED_JOIN Operator on page 7-45•
NESTED_SEMI_JOIN Operator on page 7-47•
ORDERED_HASH_ANTI_SEMI_JOIN on page 7-48•
ORDERED_HASH_JOIN Operator on page 7-49•
ORDERED_HASH_SEMI_JOIN Operator on page 7-50•
ORDERED_UNION Operator on page 7-52SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7 -2
•
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_GROUPBY_LEAF Operator on page 7-66•
SORT_PARTIAL_GROUPBY_ROOT Operator on page 7-67•
SORT_SCALAR_AGGR Operator on page 7-69•
SPLIT_TOP Operator on page 7-70•
SUBSET_DELETE Operator on page 7-71•
SUBSET_UPDATE Operator on page 7-72•
TRANSPOSE Operator on page 7-74•
TUPLE_FLOW Operator on page 7-75•
TUPLELIST Operator on page 7-76•
UNARY_UNION Operator on page 7-77•
UNIQUE_DELETE Operator on page 7-79•
UNIQUE_UPDATE Operator on page 7-80•
UNPACK Operator on page 7-81•
VALUES Operator on page 7-82For information about reading the EXPLAIN output, see Description of the EXPLAIN Function Results on page 4-3.
Operator groups are used to conveniently group operators of a similar type. For example, the Join group contains all operators that use joins.
Group Operator
User-Defined Routine
CALL
DAM Subset FILE_SCAN INDEX_SCAN SUBSET_DELETE SUBSET_UPDATE DAM Unique CURSOR_DELETE
CURSOR_UPDATE FILE_SCAN_UNIQUE INDEX_SCAN_UNIQUE UNIQUE_DELETE UNIQUE_UPDATE
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7 -3
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_ORDERED_HASH_JOIN MERGE_ANTI_SEMI_JOIN MERGE_JOIN MERGE_SEMI_JOIN NESTED_ANTI_SEMI_JOIN NESTED_JOIN NESTED_SEMI_JOIN ORDERED_HASH_ANTI_SEMI_JOIN ORDERED_HASH_JOIN ORDERED_HASH_SEMI_JOIN TUPLE_FLOW Materialize MATERIALIZE Union BLOCKED_UNION MERGE_UNION ORDERED_UNION UNARY_UNION Root ROOT Group Operator
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7 -4
Operators