jump to navigation

Introduction To Linguistic Indexes – Part II January 9, 2008

Posted by Richard Foote in Indexing Tricks, Linguistic Indexes, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.
trackback

As previously discussed, Linguistic Indexes can potentially be useful with case-insensitive searches and sorts.

However, they have a number of issues and disadvantages.

The first issue is that once the NLS_COMP parameter is set to ‘LINGUISTIC’ and the NLS_SORT parameter is set to something other than ‘BINARY’, standard binary indexes can no longer be used and are ignored by the CBO. This means one needs to have a very careful and consistent indexing strategy to ensure no SQL statements are compromised while Linguistic related NLS parameters are set. Simple demo highlighting issues with mixing Linguistic and Binary Indexes here.  Note these demos follow those in Introduction To Linguistic Indexes Part I.

The next issue is that Linguistic Indexes are ignored for some types of predicate conditions. MIN, MAX and LIKE can not be used with Linguistic Indexes (although LIKE can now be used with 11g). Simple demo highlighting problems with these predicate conditions here.

Finally, Linguistic Indexes typically use more storage than Binary indexes and so have more associated overheads and costs. The differences in storage is dependent on the charactersets associated with the various indexes. Some examples of differences shown here. Warning: This demo has lots of block dumps !!

Linguistic Indexes are worthy of consideration, but so are the associated costs and disadvantages.

Comments»

1. Brian Tkatch - January 9, 2008

CREATE TABLE A (A INT, B VARCHAR2(2));
INSERT INTO A VALUES(1, ’42’);
INSERT INTO A VALUES(1, ‘4f’);
INSERT INTO A VALUES(1, ’57’);
INSERT INTO A VALUES(1, ’49’);
INSERT INTO A VALUES(1, ’45’);
INSERT INTO A VALUES(2, ’42’);
INSERT INTO A VALUES(2, ‘6f’);
INSERT INTO A VALUES(2, ’57’);
INSERT INTO A VALUES(2, ’69’);
INSERT INTO A VALUES(2, ’45’);
INSERT INTO A VALUES(3, ’42’);
INSERT INTO A VALUES(3, ‘6f’);
INSERT INTO A VALUES(3, ’77’);
INSERT INTO A VALUES(3, ’69’);
INSERT INTO A VALUES(3, ’65’);
INSERT INTO A VALUES(4, ’54’);
INSERT INTO A VALUES(4, ’48’);
INSERT INTO A VALUES(4, ’49’);
INSERT INTO A VALUES(4, ‘4e’);
INSERT INTO A VALUES(4, ’20’);
INSERT INTO A VALUES(4, ’57’);
INSERT INTO A VALUES(4, ’48’);
INSERT INTO A VALUES(4, ’49’);
INSERT INTO A VALUES(4, ’54’);
INSERT INTO A VALUES(4, ’45’);
INSERT INTO A VALUES(4, ’20’);
INSERT INTO A VALUES(4, ’44’);
INSERT INTO A VALUES(4, ’55’);
INSERT INTO A VALUES(4, ‘4b’);
INSERT INTO A VALUES(4, ’45’);
INSERT INTO A VALUES(5, ’01’);
INSERT INTO A VALUES(5, ‘ef’);
INSERT INTO A VALUES(5, ’02’);
INSERT INTO A VALUES(5, ’38’);
INSERT INTO A VALUES(5, ’02’);
INSERT INTO A VALUES(5, ’64’);
INSERT INTO A VALUES(5, ’02’);
INSERT INTO A VALUES(5, ‘1b’);
INSERT INTO A VALUES(5, ’01’);
INSERT INTO A VALUES(5, ‘fe’);
INSERT INTO A VALUES(5, ’00’);
INSERT INTO A VALUES(5, ’00’);
INSERT INTO A VALUES(5, ’02’);
INSERT INTO A VALUES(5, ’02’);
INSERT INTO A VALUES(5, ’02’);
INSERT INTO A VALUES(5, ’02’);
INSERT INTO A VALUES(5, ’02’);
INSERT INTO A VALUES(6, ’01’);
INSERT INTO A VALUES(6, ‘ef’);
INSERT INTO A VALUES(6, ’02’);
INSERT INTO A VALUES(6, ’38’);
INSERT INTO A VALUES(6, ’02’);
INSERT INTO A VALUES(6, ’64’);
INSERT INTO A VALUES(6, ’02’);
INSERT INTO A VALUES(6, ‘1b’);
INSERT INTO A VALUES(6, ’01’);
INSERT INTO A VALUES(6, ‘fe’);
INSERT INTO A VALUES(6, ’00’);
INSERT INTO A VALUES(6, ’00’);
INSERT INTO A VALUES(6, ’02’);
INSERT INTO A VALUES(6, ’02’);
INSERT INTO A VALUES(6, ’02’);
INSERT INTO A VALUES(6, ’02’);
INSERT INTO A VALUES(6, ’02’);
INSERT INTO A VALUES(7, ’01’);
INSERT INTO A VALUES(7, ‘ef’);
INSERT INTO A VALUES(7, ’02’);
INSERT INTO A VALUES(7, ’38’);
INSERT INTO A VALUES(7, ’02’);
INSERT INTO A VALUES(7, ’64’);
INSERT INTO A VALUES(7, ’02’);
INSERT INTO A VALUES(7, ‘1b’);
INSERT INTO A VALUES(7, ’01’);
INSERT INTO A VALUES(7, ‘fe’);
INSERT INTO A VALUES(7, ’00’);
INSERT INTO A VALUES(7, ’00’);
INSERT INTO A VALUES(7, ’02’);
INSERT INTO A VALUES(7, ’02’);
INSERT INTO A VALUES(7, ’02’);
INSERT INTO A VALUES(7, ’02’);
INSERT INTO A VALUES(7, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’55’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’13’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ‘1b’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’32’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’64’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’13’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ‘1b’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’55’);
INSERT INTO A VALUES(8, ’01’);
INSERT INTO A VALUES(8, ‘fe’);
INSERT INTO A VALUES(8, ’01’);
INSERT INTO A VALUES(8, ‘f7′);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ‘5b’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’23’);
INSERT INTO A VALUES(8, ’01’);
INSERT INTO A VALUES(8, ‘fe’);
INSERT INTO A VALUES(8, ’00’);
INSERT INTO A VALUES(8, ’00’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(8, ’02’);
INSERT INTO A VALUES(9, ’62’);
INSERT INTO A VALUES(9, ‘6f’);
INSERT INTO A VALUES(9, ’77’);
INSERT INTO A VALUES(9, ’69’);
INSERT INTO A VALUES(9, ’65’);
INSERT INTO A VALUES(9, ’00’);
INSERT INTO A VALUES(10, ’62’);
INSERT INTO A VALUES(10, ‘6f’);
INSERT INTO A VALUES(10, ’77’);
INSERT INTO A VALUES(10, ’69’);
INSERT INTO A VALUES(10, ’65’);
INSERT INTO A VALUES(10, ’00’);
INSERT INTO A VALUES(11, ’62’);
INSERT INTO A VALUES(11, ‘6f’);
INSERT INTO A VALUES(11, ’77’);
INSERT INTO A VALUES(11, ’69’);
INSERT INTO A VALUES(11, ’65’);
INSERT INTO A VALUES(11, ’00’);
INSERT INTO A VALUES(12, ’74’);
INSERT INTO A VALUES(12, ’68’);
INSERT INTO A VALUES(12, ’69’);
INSERT INTO A VALUES(12, ‘6e’);
INSERT INTO A VALUES(12, ’20’);
INSERT INTO A VALUES(12, ’77’);
INSERT INTO A VALUES(12, ’68’);
INSERT INTO A VALUES(12, ’69’);
INSERT INTO A VALUES(12, ’74’);
INSERT INTO A VALUES(12, ’65’);
INSERT INTO A VALUES(12, ’20’);
INSERT INTO A VALUES(12, ’64’);
INSERT INTO A VALUES(12, ’75’);
INSERT INTO A VALUES(12, ‘6b’);
INSERT INTO A VALUES(12, ’65’);
INSERT INTO A VALUES(12, ’00’);

BREAK ON A SKIP 1
SELECT A, CHR(TO_NUMBER(B, ‘XX’)) FROM A;

The first set (of 4) was as it was INSERTed, and the third was similar, just all lowercase. The second set, however, was not similar.

Like

Reply
2. Aparna - July 19, 2010

Hi Richard,

Both the articles on linguistic indexes were really helpful.
I have a question for you though,
I have an entire application, using Oracle11g. If I have to do a traditional Chinese sort on the complete application, does it mean, that I have to have linguistic indexes on all the CHAR columns?

Like

Reply
Richard Foote - July 20, 2010

Hi Aparna

Yes, if you want to consistently sort in a manner which makes sense in Chinese on such columns. It’s something that needs to be defined and thought out from the start of the development process.

Like

Reply
Aparna - July 20, 2010

Hi Richard,

Unfortunately, we have a new customer wanting the existing application doing a Chinese sort. I was not very sure if this was the right way to do it, and found no answers from Oracle Forums either.
Thanks for your immediate reply, now I know I am in the right direction. Will still have to figure out if there is an easier way to find and replace the existing indexes.
Thanks once again! This was really helpful.

Like

Reply
3. Aparna - July 19, 2010

Uhh, I meant, replacing all the existing binary indexes on CHAR columns with linguistic indexes?

Like

Reply
4. Richard Foote - July 22, 2010

Hi Aparna,

No worries, good luck with it.

Like

Reply

Leave a comment