Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (herse.apache.org: local policy)
Message-ID: <45F985A2.2070505@sbcglobal.net>
Date: Thu, 15 Mar 2007 10:42:58 -0700
From: Mike Matrigali <mikem_app@sbcglobal.net>
Reply-To: mikem_app@sbcglobal.net
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: Collation implementation WAS Re: Should COLLATION attribute
 related
 code go in BasicDatabase?
References: <d9619e4a0703130942r48166cf4w83c5d11df4f52d66@mail.gmail.com>
	 <45F6E90F.2090706@apache.org>
	 <d9619e4a0703131147l111eccc9x72590cec337f7a23@mail.gmail.com>
	 <d9619e4a0703141225m51fb7307x74c1e38b979f04b3@mail.gmail.com>
	 <45F9620E.4050105@apache.org>
 <45F96B54.7000502@sun.com>	 <45F96D80.1080102@apache.org>
 <45F980D9.5080706@sbcglobal.net>
 <d9619e4a0703151025h89f9250l2c906d3dbbb0f7ff@mail.gmail.com>
In-Reply-To: <d9619e4a0703151025h89f9250l2c906d3dbbb0f7ff@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Mamta Satoor wrote:
> Excuse my ignorance, but what does it mean by "order by that spills to 
> the disk"?

My interpretation of this is that when derby executes a statement with
an order by it has to somehow sort the rows.  Derby has 2 methods of
doing this:
1) Carefully craft the execution plan to take advantage of an existing 
index that exactly matches the order by.
2) Throw all the result rows into the sorter.

In case 2 there are basically 2 paths through the code:
1) if we have enough memory we sort all the rows in memory.
2) if we don't have enough memory we "spill to disk", basically we use
    a disk based sort algorithm.  This code is different from the btree
    index code.

>  
> Mamta
> 
>  
> On 3/15/07, *Mike Matrigali* <mikem_app@sbcglobal.net 
> <mailto:mikem_app@sbcglobal.net>> wrote:
> 
> 
> 
>     Daniel John Debrunner wrote:
>      > Rick Hillegas wrote:
>      >
>      >> Daniel John Debrunner wrote:
>      >>
>      >>> ...
>      >>>
>      >>> - The collation type (the integer) is written into the
>     meta-data for
>      >>> an index just as ascending/descending is today (including the btree
>      >>> control row, thus making the information available for recovery).
>      >>> Collation type applies to all character columns in the index.
>      >>>
>      >> This suggests that all of the columns in the index must have the
>     same
>      >> collation? I don't think that is powerful enough to support the
>      >> full-blown SQL collation language, which allows you to mix
>     differently
>      >> collated columns in an ORDER BY clause. Why can't the collation
>     type
>      >> be an array of ints just as the sort direction is an array of
>     booleans
>      >> in IndexDescriptor?
>      >
>      >
>      > That would be more flexible, but is it required? Is an order by that
>      > spills to disk implemented using a BTREE?
> 
>     No, order by that spills to disk is implemented by the sorter.
> 
>      >
>      > Thanks,
>      > Dan.
>      >
>      >
>      >
> 
>