Home » SQL & PL/SQL » SQL & PL/SQL » UNIQUE CONSTRAINT (10.2.0.1.0,RHEL)
UNIQUE CONSTRAINT [message #432590] Wed, 25 November 2009 01:36 Go to next message
johnbach
Messages: 32
Registered: June 2009
Member
CREATE TABLE TXN_LOG
(
TXN_TYPE NUMBER,
RRN VARCHAR2(20)
OTHERS VARCHAR2(200)
);

CREATE UNIQUE INDEX IDXTX1 ON TXN_LOG
(CASE "TXN_TYPE" WHEN 200 THEN "RRN" ELSE NULL END );


SAMPLE DATA
TXN_TYPE RRN
200 1111
200 2222
200 3333

400 3333
400 3333

RRN will be unique for records with TXN_TYPE 200.
I have a query on records with TXN_TYPE 200(ONLY)
like
SELECT * from TXN_LOG where TXN_TYPE=200 and RRN='something';

and i found that the index is not used.
Is it possible to use the above index,Or one more index is must like
CREATE UNIQUE INDEX IDXTX2 ON TXN_LOG(RRN);


Re: UNIQUE CONSTRAINT [message #432592 is a reply to message #432590] Wed, 25 November 2009 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and i found that the index is not used.

To use the index you must have the same expression in query than in index definition.

Quote:
Or one more index is must like

Which is not possible as you can the same value of rrn several times.

Regards
Michel
Re: UNIQUE CONSTRAINT [message #432594 is a reply to message #432592] Wed, 25 November 2009 01:56 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
1.RRN is unique when TXN_TYPE is 200
2.I want to fetch a record with TXN_TYPE 200(always fixed) and with some RRN(specified in runtime)


What is the optimal solution for the above two requirement,Is is possible to do this with a single index

Currently i am having two index(Sorry second one is a non-unique index)

[Updated on: Wed, 25 November 2009 01:56]

Report message to a moderator

Re: UNIQUE CONSTRAINT [message #432599 is a reply to message #432594] Wed, 25 November 2009 02:27 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot enforce the constraint AND the usage of the index for THIS query with a single index.

Regards
Michel
Previous Topic: how to get percentage on the row
Next Topic: Weekly bucket (merged 3)
Goto Forum:
  


Current Time: Wed Dec 07 03:24:05 CST 2016

Total time taken to generate the page: 0.19060 seconds