You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:18:26 UTC
[jira] [Created] (TRAFODION-853) LP Bug: 1407807 - In explain
output, trafodion_index_scan operator description/table_name references
base table rather than index.
Alice Chen created TRAFODION-853:
------------------------------------
Summary: LP Bug: 1407807 - In explain output, trafodion_index_scan operator description/table_name references base table rather than index.
Key: TRAFODION-853
URL: https://issues.apache.org/jira/browse/TRAFODION-853
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Reporter: Julie Thai
Assignee: Anoop Sharma
In "explain options 'f'" output, for trafodion_index_scan operator, base table name is displayed rather than index name.
>>create table mytbl( a int not null, b int, c int);
--- SQL operation complete.
>>insert into mytbl values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
--- 5 row(s) inserted.
>>create index myidx on mytbl(b);
--- SQL operation complete.
>>prepare XX from select * from mytbl where b = 3;
--- SQL command prepared.
>>explain options 'f' XX;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
4 . 5 root 1.00E+001
1 3 4 nested_join 1.00E+001
2 . 3 probe_cache 4.00E-001
. . 2 trafodion_scan MYTBL 4.00E-001
. . 1 trafodion_index_scan MYTBL 1.00E+001 <-- MYTBL is referenced here; expected MYIDX
--- SQL operation complete.
>>explain XX;
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
PLAN_ID .................. 212287252940704541
ROWS_OUT ................ 10
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from mytbl where b = 3;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 5 ONLY CHILD 4
REQUESTS_IN .............. 1
ROWS_OUT ................ 10
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
est_memory_per_cpu ..... 1012 KB
max_max_cardinality ... 10
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
SCHEMA ................. JULSCH
SHOWCONTROL_SHOW_ALL ... OFF
ObjectUIDs ............. 60853707823455777
select_list ............ TRAFODION.JULSCH.MYTBL.A, %(3),
TRAFODION.JULSCH.MYTBL.C
input_variables ........ %(3), execution_count
NESTED_JOIN =============================== SEQ_NO 4 CHILDREN 1, 3
REQUESTS_IN .............. 1
ROWS_OUT ................ 10
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner
join_method ............ nested
PROBE_CACHE =============================== SEQ_NO 3 ONLY CHILD 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 0.4
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est .......... 90
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
est_memory_per_cpu ..... 1012 KB
probe_columns .......... TRAFODION.JULSCH.MYIDX.SYSKEY, %(3), execution_count
num_cache_entries 16,384
num_inner_tuples 18,432
TRAFODION_SCAN ============================ SEQ_NO 2 NO CHILDREN
TABLE_NAME ............... MYTBL
REQUESTS_IN .............. 1
ROWS_OUT ................. 0.4
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est .......... 90
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan of table TRAFODION.JULSCH.MYTBL
object_type ............ Trafodion
cache_size ........... 100
probes ................ 10
successful_probes ..... 10
unique_probes .......... 2
duplicated_succ_probes 8
rows_accessed ......... 10
key_columns ............ SYSKEY
executor_predicates .... (B = %(3))
begin_key .............. (SYSKEY = TRAFODION.JULSCH.MYIDX.SYSKEY)
end_key ................ (SYSKEY = TRAFODION.JULSCH.MYIDX.SYSKEY)
TRAFODION_INDEX_SCAN ====================== SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... MYTBL
REQUESTS_IN .............. 1
ROWS_OUT ................ 10
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan of index TRAFODION.JULSCH.MYIDX(TRAFODIO
N.JULSCH.MYTBL)
object_type ............ Trafodion
cache_size ........... 100
probes ................. 1
rows_accessed ......... 10
key_columns ............ TRAFODION.JULSCH.MYIDX.B,
TRAFODION.JULSCH.MYIDX.SYSKEY
executor_predicates .... (TRAFODION.JULSCH.MYIDX.B = %(3))
begin_key .............. (TRAFODION.JULSCH.MYIDX.B = %(3)),
(TRAFODION.JULSCH.MYIDX.SYSKEY = <min>)
end_key ................ (TRAFODION.JULSCH.MYIDX.B = %(3)),
(TRAFODION.JULSCH.MYIDX.SYSKEY = <max>)
--- SQL operation complete.
To reproduce:
create table mytbl( a int not null, b int, c int);
insert into mytbl values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
create index myidx on mytbl(b);
prepare XX from select * from mytbl where b = 3;
explain options 'f' XX;
explain XX;
This reproduced on:
MY_SQROOT=/home/trafodion/traf_20141216_0830
who@host=trafodion@rhel-qa1
JAVA_HOME=/usr/java/jdk1.7.0_67
linux=2.6.32-431.20.3.el6.x86_64
redhat=6.5
Release 0.9.1 (Build release [0.9.0-232-gd0fac92_Bld30], branch d0fac92-master, date 20141216_083000)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)