?
Solved

Oracle comparison operators versus ORDER BY sequence

Posted on 2009-12-16
12
Medium Priority
?
1,071 Views
Last Modified: 2013-12-18
Hello,

I have a simple problem. I need to navigate a long list of names, using mixed upper and lower case. The ORDER BY clause will sort them in alphabetical order (case insensitive), but comparison operators are case sensitive. For example, if I need the next name after "Hello", I could write:

SELECT (
SELECT * FROM TheTable WHERE TheName > 'Hello' ORDER BY TheName
) WHERE ROWNUM = 1;

The next name could be "help", but not "HELP", so it's inconsistent with the ORDER BY clause.

I know I can solve this problem using functions, but I need this to be an index search (it must be fast). So my question is two-fold:

1) Is there an operator like > meaning "later in standard alphabetical order" instead of "using greater character codes"?

2) Does Oracle perform an index search for

        NLS_UPPER(TheName) > NLS_UPPER('Hello')

This would solve the problem, naturally. It seems unnatural that this expression could be optimised, but I'm not familiar with Oracle.


Thanks
Markus -- (°v°)
0
Comment
Question by:harfang
11 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 26064026
if you want indexes access to function results you will need to create a function based index on UPPER(thename)

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 26064033
you can create the function based index on  NLS_UPPER instead  of UPPER too (or other functions as well)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 26064052
the syntax would look something like this...

CREATE INDEX idx_upper_name ON thetable (UPPER(thename));
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 58

Author Comment

by:harfang
ID: 26064303
Thank you sdstuber,

I'm well aware of the possibility to create calculated indexes. However, this just seems wrong.

There is an index on TheName, and Oracle uses that index when I write "ORDER BY TheName". So I already have a case insensitive index, don't I? My question is really how to use that index in a comparison operation, or what is the optimal way to write string comparisons to make use of existing indexes.

[Sorry for the zone mix-up, I thought I had done it right...]

(°v°)
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 26064337
sorry, you can't do what you want "as is"

you can't use a case sensitive index for a case in-sensitive comparison

only a case insensitive index (via a function based index) will allow you to do that.

0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 500 total points
ID: 26065496
Hi,
There is no case insensitive index. Indexes are in binary order.
Can you share the explain plan where a case insensitive sort uses the index ? Are you sure there is no sort operation after the index access ?
NLS_UPPER is the right function in order to have same result as case insensitive sort. And you can have a function index on it.
Regards,
Franck.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 26066455
I agree with sdstuber and franckpachot.  The only way to get Oracle to do a case-insentive, indexed search is by creating a function-based index on the table that exactly matches the function(s) you will use in your query.  Also, before you can use function-based indexes, you may need to change an initialization parameter or two.

Which version of Oracle do you have?
0
 
LVL 58

Author Comment

by:harfang
ID: 26067873
Thank you for all the comments.

I tried wrapping my criteria with NLS_UPPER, i.e.

   ... WHERE NLS_UPPER(TheName) > NLS_UPPER('Hello') ORDER BY TheName

This works, and apparently uses the index as well (given the speed compared with other types of function calls).

franckpachot,

> There is no case insensitive index.

That might be, but the clause "ORDER BY TheName" produces a case insensitive ordering, depending on the current language settings. And that ordering is based on the index. I firmly believe that the index is in "database natural language" or that Oracle has some method to use a binary index to produce the required sort order in a flash.

I'm only trying to use the same ordering in the WHERE and ORDER BY clauses.

It's just inconsistent, don't you think? Maybe it's just me, but it seems logical that in the expression

   ... WHERE TheName > 'Hello' ORDER BY TheName

I want to "next" name after "Hello" to appear first. Whether in binary or natural language order, but the next, consistently. How come '>' uses binary, and ORDER BY uses natural language? There must be a way to make the clauses agree. Currently they operate as if I had written:

   ... WHERE TheName (binary greater) 'Hello' ORDER BY NLS_UPPER(TheName)

It's probably because I'm coming from Access. Both the behaviour of ORDER BY and the behaviour of '>' can be modified, and they normally refer to the same ordering (at least in queries). And indexes are in natural language order or in binary order depending on the field type (text or binary text).


I'll run some more tests tomorrow, but apparently, I can use NSL_UPPER without a new index (which makes sense in a way).

Cheers!
(°v°)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 26067927
"and apparently uses the index as well (given the speed compared with other types of function calls)."


don't judge by the execution speed.  check the explain plan to be sure
0
 
LVL 58

Author Comment

by:harfang
ID: 26067989
> check the explain plan to be sure

Yes. I guess I'll have to do some heavy reading tomorrow and learn how to do that. I already spent hours trying to find a good reference on comparison operators and strings, I couldn't even find one *mentioning* the implied sort order used... let alone how to control it. (I'm sure you can feel I'm a bit frustrated at the moment.)

Thanks again for all the comments.
(°v°)
0
 
LVL 58

Accepted Solution

by:
harfang earned 0 total points
ID: 26080544
Well. I wasn't able to use the EXPLAIN PLAN feature in any useful way, but that will be another question, there is just too much I don't know about Oracle yet.

The answers I was looking for are the session parameters NLS_SORT and NLS_COMP. The behaviour I was observing is due to incoherent settings in my environment (I should have suspected something like that, but that's another story): NSL_SORT is set to "FRENCH" (case sensitive, accent sensitive, French sort order by default), but NLS_COMP is left with "BINARY" (so comparison operators do not follow suit). A rather useless combination.

Indeed, by default indexes on text fields are binary. However, linguistic indexes can be created by using the function NLSSORT, which will be used automatically in ORDER BY (if the setting NLS_SORT matches the index) and in WHERE comparisons (if the setting NLS_COMP is set to LINGUISTIC -- which doesn't work in my version -- or ANSI)

So you were both right: I need a calculated index, and ORDER BY only uses compatible indexes (default binary index when NLS_SORT is 'binary', and the compatible linguistic index when NLS_SORT is not 'binary'). If no appropriate index is found, a full table scan is performed. This was happening in my case due to the global setting for NLS_SORT, I was only assuming the index was used.

Thank your for your answers. I will accept my own comment, but split the points.

(°v°)
create index ndx_generic on TheTable (NLSSORT(TheName,'nls_sort=generic_m_ai'));
 
alter session set nls_sort = generic_m_ai;
alter session set nls_comp = ANSI;
 
select TheName from TheTable where TheName>'Hello' order by TheName;

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question