db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Harshvardhan Gupta (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6938) Obtain cardinality estimates and true estimates for base tables as well as for intermediate results for queries involving multiple joins.
Date Wed, 14 Jun 2017 07:13:02 GMT

    [ 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)

Mime
View raw message