[
https://issues.apache.org/jira/browse/DERBY-6942?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Harshvardhan Gupta updated DERBY-6942:
--------------------------------------
Attachment: z12.txt
Please find attached the optimizer trace of a specimen query.
Table DDL -
create table abcd(id integer);
create index idx on abcd(id);
Query issued -
select * from ABCD where id is NULL
As the optimizer trace shows - There are two possible access paths - one via
the table scan and one via the conglomerate backing the index. Each of the
access paths can be queried via two lookup (or join strategies in case of
joins), in our case a loop or a hash lookup.
During the index scan, the predicate (ID is NULL) acts as a valid start and
stop predicate and store makes accurate representation regarding cardinality
estimates from index lookup. In case of table scan, the predicate acts as a
qualifier (a predicate that will restrict or filter the result set obtained via
scan but won't help to locate the position of starting or ending point as
opposed to index lookups). As evident in the trace output, a hard wired
selectivity of 0.1 is being used here in this case of table scan for
cardinality estimate. We have a opportunity to use statistics in such cases to
remove our dependancy on hardwired estimates.
> Utilise additional statistics for selectivity estimates.
> --------------------------------------------------------
>
> Key: DERBY-6942
> URL: https://issues.apache.org/jira/browse/DERBY-6942
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Reporter: Harshvardhan Gupta
> Assignee: Harshvardhan Gupta
> Priority: Minor
> Attachments: z12.txt
>
>
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
