[
https://issues.apache.org/jira/browse/DERBY-6938?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16048780#comment-16048780
]
Harshvardhan Gupta commented on DERBY-6938:
-------------------------------------------
To view and compare the estimates row counts and true row counts for base
tables and for intermediate results the following queries can be used on xplain
tables, the value of OP_IDENTIFIER can be changed to get data for nodes for a
particular operation such as HASHJOIN, NLJOIN etc
select SEEN_ROWS, SEEN_ROWS_RIGHT, RETURNED_ROWS, EST_ROW_COUNT from
SYSXPLAIN_RESULTSETS,SYSXPLAIN_STATEMENTS where OP_IDENTIFIER = 'HASHJOIN' and
SYSXPLAIN_STATEMENTS.STMT_ID = SYSXPLAIN_RESULTSETS.STMT_ID;
To couple the scan information for nodes involved in scans -
select SEEN_ROWS, RETURNED_ROWS, EST_ROW_COUNT , OP_IDENTIFIER from
SYSXPLAIN_STATEMENTS, SYSXPLAIN_RESULTSETS, SYSXPLAIN_SCAN_PROPS where
SYSXPLAIN_STATEMENTS.STMT_ID = SYSXPLAIN_RESULTSETS.STMT_ID and
SYSXPLAIN_RESULTSETS.SCAN_RS_ID = SYSXPLAIN_SCAN_PROPS.SCAN_RS_ID and
OP_IDENTIFIER like '%SCAN';
> Obtain cardinality estimates and true estimates for base tables as well as
> for intermediate results for queries involving multiple joins.
> -------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-6938
> URL: https://issues.apache.org/jira/browse/DERBY-6938
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Reporter: Harshvardhan Gupta
> Assignee: Harshvardhan Gupta
> Attachments: explain.txt
>
>
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
