| >> |
| >>create table TAB1 (col1 int, col2 int, col3 int); |
| |
| --- SQL operation complete. |
| >>create table TAB2 (col1 int, col2 int, col3 int); |
| |
| --- SQL operation complete. |
| >>create table TAB3 (col1 int, tname char(60), sname char(60)); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into TAB1 values (1, 2, 3), |
| +> (2, 3, 4), |
| +> (3, 4, 5), |
| +> (4, 5, 6), |
| +> (5, 6, 7), |
| +> (6, 7, 8), |
| +> (7, 8, 9), |
| +> (8, 9, 0), |
| +> (9, 0, 1), |
| +> (0, 1, 2); |
| |
| --- 10 row(s) inserted. |
| >> |
| >>insert into TAB2 values (1, 2, 3), |
| +> (4, 5, 6), |
| +> (7, 8, 9), |
| +> (0, 1, 2), |
| +> (3, 4, 5), |
| +> (6, 7, 8), |
| +> (9, 0, 1), |
| +> (2, 3, 4), |
| +> (5, 6, 7); |
| |
| --- 9 row(s) inserted. |
| >> |
| >>insert into TAB3 values (1, 'TAB1', 'S1'), |
| +> (2, 'TAB2', 'S2'), |
| +> (3, 'TAB3', 'S3'); |
| |
| --- 3 row(s) inserted. |
| >> |
| >> |
| >>?section prepare_queries |
| >> |
| >>-- QUERYS to be explained |
| >>-- Every query to be explained has a CQS in order to make the |
| >>-- test insensitive to plan changes. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape hybrid_hash_join(partition_access( |
| +>scan(path 'TAB1', forward, mdam off)),partition_access( |
| +>scan(path 'TAB2', forward, mdam off))); |
| >>#else |
| >>control query shape hybrid_hash_join( |
| +>scan(path 'TAB1', forward, mdam off), |
| +>scan(path 'TAB2', forward, mdam off)); |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S1 from |
| +>select * |
| +>from TAB1 , TAB2 |
| +>where TAB1.col1 = TAB2.col1 |
| +> and TAB1.col1 < 15 |
| +> and TAB2.col2 < 20 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape hybrid_hash_join(partition_access( |
| +>scan(path 'TAB1', forward, mdam off)),partition_access( |
| +>scan(path 'TAB3', forward, mdam off))); |
| >>#else |
| >>control query shape hybrid_hash_join( |
| +>scan(path 'TAB1', forward, mdam off), |
| +>scan(path 'TAB3', forward, mdam off)); |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S2 from |
| +>select * |
| +>from TAB1 , TAB3 |
| +>where TAB1.col1 = TAB3.col1 |
| +> and TAB1.col2 < 10 |
| +> and TAB3.col1 < 15 |
| +> and TAB3.tname is not null |
| +> and TAB3.sname is null |
| +> and (TAB1.col2 + TAB1.col3 < TAB3.col1 or char_length(TAB3.tname) > 10) |
| +> and substring(TAB3.tname from 1 for 3) <> 'ABC' |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape hybrid_hash_join(partition_access( |
| +>scan(path 'TAB3', forward, mdam off)),partition_access( |
| +>scan(path 'TAB2', forward, mdam off))); |
| >>#else |
| >>control query shape hybrid_hash_join( |
| +>scan(path 'TAB3', forward, mdam off), |
| +>scan(path 'TAB2', forward, mdam off)); |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S3 from |
| +>select * |
| +>from TAB2 , TAB3 |
| +>where TAB2.col1 = TAB3.col1 |
| +> and TAB2.col2 < 10 |
| +> and TAB3.col1 < 10 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape sort_groupby(nested_join(sort(partition_access( |
| +>scan(path 'TAB1', forward, mdam off))), |
| +>partition_access(scan(path 'TAB2', forward, mdam off)))); |
| >>#else |
| >>control query shape sort_groupby(nested_join(sort( |
| +>scan(path 'TAB1', forward, mdam off)), |
| +>scan(path 'TAB2', forward, mdam off))); |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S4 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.col2 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape partition_access(insert); |
| >>#else |
| >>control query shape insert; |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S5 from |
| +>insert into TAB1 |
| +> values (42,42,42) |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape nested_join(anything,partition_access(insert)); |
| >>#else |
| >>control query shape nested_join(anything,insert); |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S6 from |
| +>insert into TAB1 |
| +> values (42,42,42), |
| +> (52,52,52), |
| +> (1,2,3) |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape partition_access(update); |
| >>#else |
| >>control query shape update; |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S7 from |
| +>update TAB1 |
| +> set col1 = 12, |
| +> col2 = col1 + col2, |
| +> col3 = 12 * 4 |
| +>where col1 < 10 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape hybrid_hash_join(partition_access( |
| +>scan(path 'TAB1', forward, mdam off)),partition_access( |
| +>scan(path 'TAB2', forward, mdam off))); |
| >>#else |
| >>control query shape hybrid_hash_join( |
| +>scan(path 'TAB1', forward, mdam off), |
| +>scan(path 'TAB2', forward, mdam off)); |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S8 from |
| +>select * from TAB1 |
| +>where TAB1.col1 in (select col2 from TAB2 where TAB2.col1 < 10) |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>control query shape hybrid_hash_join(partition_access( |
| +>scan(path 'TAB1', forward, mdam off)),partition_access( |
| +>scan(path 'TAB2', forward, mdam off))); |
| >>#else |
| >>control query shape hybrid_hash_join( |
| +>scan(path 'TAB1', forward, mdam off), |
| +>scan(path 'TAB2', forward, mdam off)); |
| |
| --- SQL operation complete. |
| >>#endif |
| >> |
| >>prepare S9 from |
| +>select * from TAB1 |
| +>where TAB1.col1 in (select col2 from TAB2 where TAB2.col1 < TAB1.col2) |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>-- remove shape: |
| >>control query shape cut; |
| |
| --- SQL operation complete. |
| >> |
| >>?section explain_queries |
| >> |
| >>-- QUERY 1 - A simple Explain query with a predicate |
| >>select |
| +>#ifMX |
| +> tname, |
| +>#ifMX |
| +>#ifMP |
| +> substring(tname, 1, 4), |
| +>#ifMP |
| +> operator |
| +>from table (explain(NULL,'S1')) |
| +>where tname LIKE '%TAB1%' |
| +>order by |
| +>operator |
| +>; |
| |
| TNAME OPERATOR |
| ------------------------------------------------------------ ------------------------------ |
| |
| TAB1 TRAFODION_SCAN |
| |
| --- 1 row(s) selected. |
| >> |
| >>?ignore |
| >>-- this query returns different values for cost on different |
| >>-- platforms with different releases. |
| >>-- It is not a good query for regressions. |
| >>-- QUERY 2 - The cost fields of the explain table. |
| >>select |
| +> operator, |
| +> operator_cost, |
| +> total_cost, |
| +> detail_cost |
| +>from table (explain(NULL,'S1')) |
| +>order by |
| +>operator |
| +>; |
| >>?ignore |
| >> |
| >>-- QUERY 3 - The explain table is used in a JOIN. |
| >>select |
| +> operator, |
| +> expp.tname |
| +>from table (explain (NULL, 'S1')) as expp, TAB3 |
| +>where substring(expp.tname from position('TAB' in expp.tname) for 4) = TAB3.tname |
| +>order by |
| +>1,2--operator |
| +>; |
| |
| OPERATOR TNAME |
| ------------------------------ ------------------------------------------------------------ |
| |
| TRAFODION_SCAN TAB1 |
| TRAFODION_SCAN TAB2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- QUERY 4 - The explain table is used in a JOIN. |
| >>select |
| +> operator, |
| +> seq_num |
| +>from table (explain (NULL, 'S1')) as expp, TAB1 |
| +>where expp.seq_num = TAB1.col1 |
| +>order by |
| +>operator,seq_num |
| +>; |
| |
| OPERATOR SEQ_NUM |
| ------------------------------ ----------- |
| |
| HYBRID_HASH_JOIN 3 |
| ROOT 4 |
| TRAFODION_SCAN 1 |
| TRAFODION_SCAN 2 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- QUERY 5 - The explain function is used within a correlated |
| >>-- subquery. |
| >>select |
| +> * |
| +>from TAB3 |
| +>where TAB3.tname in |
| +> (select substring(tname from position('TAB' in tname) for 4) |
| +> from table (explain (NULL,TAB3.sname))) |
| +>order by tname |
| +>; |
| |
| COL1 TNAME SNAME |
| ----------- ------------------------------------------------------------ ------------------------------------------------------------ |
| |
| 1 TAB1 S1 |
| 3 TAB3 S3 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- QUERY 6 - Wildcarding is used in the explain parameters. |
| >>select |
| +> module_name, |
| +> statement_name |
| +>from table (explain (NULL, 'S%')) |
| +>order by |
| +>module_name |
| +>,statement_name |
| +>; |
| |
| MODULE_NAME STATEMENT_NAME |
| ------------------------------------------------------------ ------------------------------------------------------------ |
| |
| ? S1 |
| ? S1 |
| ? S1 |
| ? S1 |
| ? S2 |
| ? S2 |
| ? S2 |
| ? S2 |
| ? S3 |
| ? S3 |
| ? S3 |
| ? S3 |
| ? S4 |
| ? S4 |
| ? S4 |
| ? S4 |
| ? S4 |
| ? S4 |
| ? S5 |
| ? S5 |
| ? S6 |
| ? S6 |
| ? S6 |
| ? S6 |
| ? S7 |
| ? S7 |
| ? S7 |
| ? S7 |
| ? S8 |
| ? S8 |
| ? S8 |
| ? S8 |
| ? S9 |
| ? S9 |
| ? S9 |
| ? S9 |
| |
| --- 36 row(s) selected. |
| >> |
| >>--QUERY 7 - Wildcarding is used in the explain parameters. |
| >>select |
| +> operator |
| +>from table (explain (NULL, 'S%')) as expp |
| +>where expp.left_child_seq_num is not null |
| +>and expp.right_child_seq_num is not null |
| +>order by operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| HYBRID_HASH_JOIN |
| HYBRID_HASH_JOIN |
| HYBRID_HASH_SEMI_JOIN |
| HYBRID_HASH_SEMI_JOIN |
| NESTED_JOIN |
| TUPLE_FLOW |
| TUPLE_FLOW |
| |
| --- 8 row(s) selected. |
| >> |
| >>-- QUERY 8 |
| >> |
| >>select seq_num, |
| +> left_child_seq_num, |
| +> operator, |
| +> right_child_seq_num |
| +>from table (explain(NULL, 'S%')) |
| +>order by |
| +>seq_num |
| +>,left_child_seq_num |
| +>,operator |
| +>,right_child_seq_num |
| +>; |
| |
| SEQ_NUM LEFT_CHILD_SEQ_NUM OPERATOR RIGHT_CHILD_SEQ_NUM |
| ----------- ------------------ ------------------------------ ------------------- |
| |
| 1 ? TRAFODION_INSERT ? |
| 1 ? TRAFODION_SCAN ? |
| 1 ? TRAFODION_SCAN ? |
| 1 ? TRAFODION_SCAN ? |
| 1 ? TRAFODION_SCAN ? |
| 1 ? TRAFODION_SCAN ? |
| 1 ? TRAFODION_SCAN ? |
| 1 ? TRAFODION_SCAN ? |
| 1 ? TUPLELIST ? |
| 2 1 ROOT ? |
| 2 1 SORT ? |
| 2 ? TRAFODION_INSERT ? |
| 2 ? TRAFODION_SCAN ? |
| 2 ? TRAFODION_SCAN ? |
| 2 ? TRAFODION_SCAN ? |
| 2 ? TRAFODION_SCAN ? |
| 2 ? TRAFODION_SCAN ? |
| 2 ? TRAFODION_UPDATE ? |
| 3 1 TUPLE_FLOW 2 |
| 3 1 TUPLE_FLOW 2 |
| 3 2 HYBRID_HASH_JOIN 1 |
| 3 2 HYBRID_HASH_JOIN 1 |
| 3 2 HYBRID_HASH_JOIN 1 |
| 3 2 HYBRID_HASH_SEMI_JOIN 1 |
| 3 2 HYBRID_HASH_SEMI_JOIN 1 |
| 3 ? TRAFODION_SCAN ? |
| 4 2 NESTED_JOIN 3 |
| 4 3 ROOT ? |
| 4 3 ROOT ? |
| 4 3 ROOT ? |
| 4 3 ROOT ? |
| 4 3 ROOT ? |
| 4 3 ROOT ? |
| 4 3 ROOT ? |
| 5 4 SORT_GROUPBY ? |
| 6 5 ROOT ? |
| |
| --- 36 row(s) selected. |
| >> |
| >>--QUERY 9 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S1')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| ROOT |
| TRAFODION_SCAN |
| TRAFODION_SCAN |
| |
| --- 4 row(s) selected. |
| >> |
| >>--QUERY 10 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S2')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| ROOT |
| TRAFODION_SCAN |
| TRAFODION_SCAN |
| |
| --- 4 row(s) selected. |
| >> |
| >>--QUERY 11 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S3')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_JOIN |
| ROOT |
| TRAFODION_SCAN |
| TRAFODION_SCAN |
| |
| --- 4 row(s) selected. |
| >> |
| >>--QUERY 12 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S4')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| NESTED_JOIN |
| ROOT |
| SORT |
| SORT_GROUPBY |
| TRAFODION_SCAN |
| TRAFODION_SCAN |
| |
| --- 6 row(s) selected. |
| >> |
| >>--QUERY 13 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S5')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| ROOT |
| TRAFODION_INSERT |
| |
| --- 2 row(s) selected. |
| >> |
| >>--QUERY 14 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S6')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| ROOT |
| TRAFODION_INSERT |
| TUPLELIST |
| TUPLE_FLOW |
| |
| --- 4 row(s) selected. |
| >> |
| >>--QUERY 15 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S7')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| ROOT |
| TRAFODION_SCAN |
| TRAFODION_UPDATE |
| TUPLE_FLOW |
| |
| --- 4 row(s) selected. |
| >> |
| >>--QUERY 16 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S8')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_SEMI_JOIN |
| ROOT |
| TRAFODION_SCAN |
| TRAFODION_SCAN |
| |
| --- 4 row(s) selected. |
| >> |
| >>--QUERY 17 |
| >>select |
| +> operator |
| +>from table (explain(NULL,'S9')) |
| +>order by |
| +>operator |
| +>; |
| |
| OPERATOR |
| ------------------------------ |
| |
| HYBRID_HASH_SEMI_JOIN |
| ROOT |
| TRAFODION_SCAN |
| TRAFODION_SCAN |
| |
| --- 4 row(s) selected. |
| >> |
| >>--QUERY 18 |
| >>-- Tests description field |
| >>-- Currently commented out because query gives results that are not repeatable |
| >>-- (predicates printed contain "funny names" that change from run to run |
| >>-- in their columns, probably the way to fix it is to change the "getText()" |
| >>-- method of the column item expr. 05/97) |
| >>-- select seq_num, |
| >>-- operator, |
| >>-- description |
| >>-- from table (explain(NULL, 'S%')) |
| >>-- ; |
| >> |
| >>--QUERY 19 |
| >>-- Solution 10-060524-6738. Make sure "explain" with access options |
| >>-- work. |
| >>#ifMX |
| >>explain options 'f' select * from TAB1 where col1 < 15 |
| +> for read committed access; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 1 . 2 root 3.29E+001 |
| . . 1 trafodion_scan TAB1 3.29E+001 |
| |
| --- SQL operation complete. |
| >>#ifMX |
| >> |
| >>--QUERY 20 |
| >>-- Solution 10-060601-6896. This should cause a syntax error. |
| >>explain explain select * |
| +> from TAB1 , TAB2 |
| +> where TAB1.col1 = TAB2.col1 |
| +> and TAB1.col1 < 15 |
| +> and TAB2.col2 < 20 |
| +>; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| explain explain select * from TAB1 , TAB2 where TAB1.col1 = TAB2.col1 |
| ^ (15 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>--QUERY 21 |
| >>-- Solution 10-060606-7000. This should cause a syntax error. |
| >>explain; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| explain; |
| ^ (8 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >> |
| >>obey TEST020(ddb); |
| >>drop table TAB1; |
| |
| --- SQL operation complete. |
| >>drop table TAB2; |
| |
| --- SQL operation complete. |
| >>drop table TAB3; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>log; |
