Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <11540889.1185461943773.JavaMail.jira@brutus>
Date: Thu, 26 Jul 2007 07:59:03 -0700 (PDT)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2916) Change/error? in 'Ordered null
 semantics' output from 'SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()' in
 lang/wisconsin.java
In-Reply-To: <4877764.1184097605209.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ 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: 10.4.0.0
>         Environment: OS: All
> JVM: All
>            Reporter: Ole Solberg
>            Priority: Minor
>
> SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() on
> '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.