Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Sun, 18 Jun 2017 13:57:00 +0000 (UTC)
From: "Harshvardhan Gupta (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.13080654.1497792917000.43208.1497794220032@Atlassian.JIRA>
In-Reply-To: <JIRA.13080654.1497792917000@Atlassian.JIRA>
References: <JIRA.13080654.1497792917000@Atlassian.JIRA> <JIRA.13080654.1497792917826@jira-lw-us.apache.org>
Subject: [jira] [Comment Edited] (DERBY-6940) Enhance derby statistics for
 more accurate selectivity estimates.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Sun, 18 Jun 2017 13:57:10 -0000


    [ https://issues.apache.org/jira/browse/DERBY-6940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16053198#comment-16053198 ] 

Harshvardhan Gupta edited comment on DERBY-6940 at 6/18/17 1:56 PM:
--------------------------------------------------------------------

Attaching an initial patch which add the following statistics information - 

1) The minimum value of each column appearing in an index. 
2) The maximum value of each column appearing in an index.
3) The null counts for each column appearing in an index.

I am currently storing column level values for minValue and maxValue as DataValueDescriptor object. I am not entirely sure if this is the best way to store it for later use in comparison for determining selectivity estimates.

To invoke the procedure extraStatistics call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS().
Currently the extraStatistics procedure is not invoked by the create Index statement, we will need to write a variant for "Create Index" statements to collect the extra statistics during index creation time as well.


was (Author: harshvardhan145):
Attaching an initial patch which add the following statistics information - 

1) The minimum value of each column appearing in an index. 
2) The maximum value of each column appearing in an index.
3) The null counts for each column appearing in an index.

I am currently storing column level values as DataValueDescriptor object. I am not entirely sure if this is the best way to store it for later use in comparison for determining selectivity estimates.

To invoke the procedure extraStatistics call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS().
Currently the extraStatistics procedure is not invoked by the create Index statement, we will need to write a variant for "Create Index" statements to collect the extra statistics during index creation time as well.


> Enhance derby statistics for more accurate selectivity estimates.
> -----------------------------------------------------------------
>
>                 Key: DERBY-6940
>                 URL: https://issues.apache.org/jira/browse/DERBY-6940
>             Project: Derby
>          Issue Type: Sub-task
>          Components: SQL
>            Reporter: Harshvardhan Gupta
>            Assignee: Harshvardhan Gupta
>            Priority: Minor
>         Attachments: derby-6940.diff
>
>
> Derby should collect extra statistics during index build time, statistics refresh time which will help optimizer make more precise selectivity estimates and chose better execution paths.


--
This message was sent by Atlassian JIRA
(v6.4.14#64029)