Re: primary key & nlssort

I think you're going to need two indexes - one to support the primary key, and one to support the ordering/CI requirement.

drop table mytest;

CREATE TABLE MYTEST (
 ID NUMBER(5,0) NOT NULL,
 TYPE VARCHAR2(16) NOT NULL,
 MSG VARCHAR2(1024) NOT NULL
);

CREATE UNIQUE INDEX MYTEST_2 ON MYTEST(ID, NLSSORT(TYPE, 'NLS_SORT=BINARY_CI'));
ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST PRIMARY KEY(ID, TYPE); This gets you uniqueness on the two different conditions whilst still allowing you to do case-insensitive conditions on the TYPE column.

With the following (on my 10.2.0.3)

set autotrace traceonly explain

select * from mytest
where id = 1 and type = 'ASD';

alter session set nls_sort = binary_ci;
alter session set nls_comp = ANSI;

select * from mytest
where id = 2 and type = 'ASD';

set autotrace off

I used the index pk_mytest for the first query, but index mytest_2 for the second.

Note - I have created the nls index before the primary key so that on an insert Oracle will test the uniqueness in that order, minimising the cost of an attempted duplication.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


<stvchien0_at_gmail.com> wrote in message 
news:598b83lj41dtjrm8uou1tlld1amj3pqb72_at_4ax.com...


> On Fri, 29 Jun 2007 14:00:00 -0700, DA Morgan <damorgan_at_psoug.org>

> wrote:

>

>>stvchien0_at_gmail.com wrote:

>>> Hi,

>>>

>>>   Since Oracel will create indexe for PRIMARY KEY constraint, can we

>>> use the NLSSORT with the column(s) which is(are) part of the PRIMARY

>>> KEY?

>>>

>>>   Thanks!

>>>

>>> - STeve

>>

>>What version and please rewrite your inquiry and better describe what

>>you are asking. An example might be helpful.

>

> Hi,

>

>  We're using Oracle 10g Release 2. Here is the example,

>

> CREATE TABLE MYTEST

> (ID NUMBER(5,0) NOT NULL,

> TYPE VARCHAR2(16) NOT NULL,

> MSG VARCHAR2(1024) NOT NULL);

>

> CREATE UNIQUE INDEX PK_MYTEST ON MYTEST(ID, NLSSORT(TYPE,

> 'NLS_SORT=BINARY_CI'));

>

> ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST PRIMARY KEY(ID, TYPE);

>

>  We got 'ORA-00955: name is already used by an existing object'

> error.

>

>  The other case didn't work either.

>

> CREATE TABLE MYTEST

> (ID NUMBER(5,0) NOT NULL,

> TYPE VARCHAR2(16) NOT NULL,

> MSG VARCHAR2(1024) NOT NULL);

>

> alter table mytest add constraint PK_mytest primary key(ID,

> NLSSORT(TYPE, 'NLS_SORT=BINARY_CI'));

>

>  We got 'ORA-00904: : invalid identifier' error.

>

>  Hopefully, this example could show what we would like to achieve.

> Thanks!

>

> - Steve