db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2916) Change/error? in 'Ordered null semantics' output from 'SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()' in lang/wisconsin.java
Date Thu, 26 Jul 2007 14:59:03 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2916?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12515724

Bryan Pendleton commented on DERBY-2916:

I'm hoping Army or somebody else pipes in , but here's my thoughts: I believe that "Ordered
null semantics" has to do with a performance optimization that the optimizer can choose and
pass down to the store's index scan code. There is a crucial comment in PredicateList.java
that discusses this, around line 3501 of the trunk code:

/* beetle 4464, performance work.  If index column is not nullable
 * (which is frequent), we should treat it as though nulls are
 * ordered (indeed because they don't exist) so that we do not have
 * to check null at scan time for each row, each column.  This is
 * an overload to "is null" operator, so that we have less overhead,
 * provided that they don't interfere.  It doesn't interfere if it
 * doesn't overload if key is null.  If key is null, but operator
 * is not orderedNull type (is null), skipScan will use this flag
 * (false) to skip scan.

Now, in the case of the particular query in question, we have an OUTER join, which means that
there is an implicit IS NULL or IS NOT NULL (I'm not positive which) predicate that is included
in the join predicate.

But this implicit NULL check only makes sense for a NULLable column, so that's what the optimizer
is checking for (and reporting on in the query plan output).

So I think that this portion of the query plan output is trying to tell us about the optimizer's
decision with respect to whether or not it will instruct the store's index scanning code that
it can safely omit null checking during the index scanning.

Unfortunately, I'm not quite sure whether the change in output means that the optimizer is
now including the performance optimization for column 0, and was not including it before,
or vice versa.

Hope this helps.

> Change/error? in 'Ordered null semantics' output from 'SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()'
in lang/wisconsin.java
> -----------------------------------------------------------------------------------------------------------------------
>                 Key: DERBY-2916
>                 URL: https://issues.apache.org/jira/browse/DERBY-2916
>             Project: Derby
>          Issue Type: Bug
>    Affects Versions:
>         Environment: OS: All
> JVM: All
>            Reporter: Ole Solberg
>            Priority: Minor
> 'Statement Text: 
> 	select * from TENKTUP1
> 		left outer join TENKTUP2 on
> 		(
> 			TENKTUP1.unique1 = TENKTUP2.unique1
> 		)
> 		left outer join ONEKTUP on
> 		(
> 			TENKTUP2.unique2 = ONEKTUP.unique2
> 		)
> 		left outer join BPRIME on
> 		(
> 			ONEKTUP.onePercent = BPRIME.onePercent
> 		)
> '
> now returns extra "0" in
> '	Ordered null semantics on the following columns: 
> 0 
> 					stop position: 
> 	> on first 1 column(s).
> 	Ordered null semantics on the following columns: 
> 0 
> '

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message