Re: case insensitive sorting & searching in oracle 10g

in oracle 10g, you can issue:

ALTER SESSION SET NLS_COMP = ansi;
ALTER SESSION SET NLS_SORT = binary_ci;

do you think this is an elegant solution for case insensitive sorting &
searching? is there interest in seeing this in postgres?

--
dave

create a functional index on lower case value of your column.
ORDER BY lower case value of your column.

> in oracle 10g, you can issue:
>
> ALTER SESSION SET NLS_COMP = ansi;
> ALTER SESSION SET NLS_SORT = binary_ci;
>
> do you think this is an elegant solution for case insensitive sorting &
> searching? is there interest in seeing this in postgres?
>

- not transparent
- can't automatically make all values fed to SELECT case-converted
- not transparent

Pierre-Frédéric Caillaud wrote:
>
> create a functional index on lower case value of your column.
> ORDER BY lower case value of your column.
>
>
>> in oracle 10g, you can issue:
>>
>> ALTER SESSION SET NLS_COMP = ansi;
>> ALTER SESSION SET NLS_SORT = binary_ci;
>>
>> do you think this is an elegant solution for case insensitive sorting
>> & searching? is there interest in seeing this in postgres?

--
dave

Hi,

I don't like the solution. "Select ... order by ..." should be self-contained
and not dependant of some settings. Case-insensitive sort should be specified
in the order-by-clause like "select ... order by lower(a)".

Tommi

Am Donnerstag, 5. August 2004 11:04 schrieb David Garamond:
> in oracle 10g, you can issue:
>
> ALTER SESSION SET NLS_COMP = ansi;
> ALTER SESSION SET NLS_SORT = binary_ci;
>
> do you think this is an elegant solution for case insensitive sorting &
> searching? is there interest in seeing this in postgres?

On Thu, 5 Aug 2004, David Garamond wrote:

> in oracle 10g, you can issue:
>
> ALTER SESSION SET NLS_COMP = ansi;
> ALTER SESSION SET NLS_SORT = binary_ci;
>
> do you think this is an elegant solution for case insensitive sorting &
> searching? is there interest in seeing this in postgres?

IMHO, no on both questions. There's always danger on relying on the
value of session variables in general in that an application must either
set the variable immediately before sending queries that use it (breaking
the transparency) or must be willing to deal with the fact that it might
not be what you expect. For the second, I don't see how this really does
much that the standard spec collation stuff can't do better and I'd think
that'd be a much better route to go.

Stephan Szabo wrote:
>>in oracle 10g, you can issue:
>>
>> ALTER SESSION SET NLS_COMP = ansi;
>> ALTER SESSION SET NLS_SORT = binary_ci;
>>
>>do you think this is an elegant solution for case insensitive sorting &
>>searching? is there interest in seeing this in postgres?
>
> IMHO, no on both questions. There's always danger on relying on the
> value of session variables in general in that an application must either
> set the variable immediately before sending queries that use it (breaking
> the transparency) or must be willing to deal with the fact that it might
> not be what you expect. For the second, I don't see how this really does
> much that the standard spec collation stuff can't do better and I'd think
> that'd be a much better route to go.

Could you point me where in the archives can I read more? I'm having a
bit of trouble finding discussion on this. Thanks.

--
dave

On Thu, 5 Aug 2004, David Garamond wrote:

> Stephan Szabo wrote:
> >>in oracle 10g, you can issue:
> >>
> >> ALTER SESSION SET NLS_COMP = ansi;
> >> ALTER SESSION SET NLS_SORT = binary_ci;
> >>
> >>do you think this is an elegant solution for case insensitive sorting &
> >>searching? is there interest in seeing this in postgres?
> >
> > IMHO, no on both questions. There's always danger on relying on the
> > value of session variables in general in that an application must either
> > set the variable immediately before sending queries that use it (breaking
> > the transparency) or must be willing to deal with the fact that it might
> > not be what you expect. For the second, I don't see how this really does
> > much that the standard spec collation stuff can't do better and I'd think
> > that'd be a much better route to go.
>
> Could you point me where in the archives can I read more? I'm having a
> bit of trouble finding discussion on this. Thanks.

I didn't spend too much time looking, but there are a few that look like
they'll touch upon related issues:

http://archives.postgresql.org/pgsql-hackers/2003-11/msg01299.php
http://archives.postgresql.org/pgsql-hackers/2001-11/msg00610.php
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00515.php

And a message where I pulled some text out of the SQL92 draft:
http://archives.postgresql.org/pgsql-general/2003-08/msg00620.php

> IMHO, no on both questions. There's always danger on relying on the
> value of session variables in general in that an application must either

And what if you use a connection sharing/pooling software ? What happens
with the session vars ?

Stephan Szabo wrote:
>>Could you point me where in the archives can I read more? I'm having a
>>bit of trouble finding discussion on this. Thanks.
>
> I didn't spend too much time looking, but there are a few that look like
> they'll touch upon related issues:
>
> http://archives.postgresql.org/pgsql-hackers/2003-11/msg01299.php
> http://archives.postgresql.org/pgsql-hackers/2001-11/msg00610.php
> http://archives.postgresql.org/pgsql-hackers/2002-11/msg00515.php

So, as I understand it, the current plan is:

1. charset + encoding will be tagged to each column (as per SQL standard)

2a. individual string values will be tagged with charset+encoding. this
incurs an overhead of 1-2 bytes per value.

or

2b. all string values will be stored in a single charset+encoding (e.g.
unicode + utf8). this will of course upset some people, e.g. japanese.

Is it 1+2a or 1+2b? Recent language implementations/VM like Parrot and
Ruby2 are inclined to 2a, I think.

--
dave

David Garamond wrote:
> 2a. individual string values will be tagged with charset+encoding. this
> incurs an overhead of 1-2 bytes per value.

forgot to add: this overhead is just for "in-memory" or temporary value
(e.g. when being passed as arguments). in the storage itself, this is
not needed because charset+encoding is recorded in the column definition.

--
dave