Non Unique Index Using A Non Unique Field

Aug 14, 2013

Using Oracle 11g, below is the table, partitions, unique and non-unique local index: 

CREATE TABLE DOCA(  DOCA_ID  NUMBER  NOT NULL ,  DOCA_BKG_PAX_ID  NUMBER  NULL ,  ROW_PURGE_DATE  DATE  NULL ,)PARTITION BY RANGE(ROW_PURGE_DATE)INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))(  PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')),  PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),)  TABLESPACE T0; ALTER TABLE DOCA ENABLE ROW MOVEMENT;    

CREATE UNIQUE INDEX XPKDOCA ON DOCA(  DOCA_ID  ASC,  ROW_PURGE_DATE ASC)LOCALREVERSE TABLESPACE I0;  ALTER TABLE DOCA  ADD CONSTRAINT  XPKDOCA PRIMARY KEY (DOCA_ID);    CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA(  DOCA_BKG_PAX_ID  ASC)LOCALREVERSETABLESPACE I0;  

I would like to know the difference between the performance of the unique and non-unique local indexes?.

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Unique Index Shorting

Jun 18, 2012

I have a running application where i have a table with pk defined on it and the pk clumn is inde in asc order by default. Can i alter the index in desc order as i always need to see the data in desc order.

View 7 Replies View Related

SQL & PL/SQL :: How To Add Column In Unique Index

Jan 17, 2011

I have created one unique index on one column of my table. Now i would like to add one more column in the same index without dropping the index.

SQL > CREATE TABLE DEBUG_TABLE
2 (
3 SLNO NUMBER,
4 MESSAGE VARCHAR2(4000 BYTE),
5 CREATED_DATE DATE DEFAULT SYSDATE,
6 CREATED_TIME TIMESTAMP(6) DEFAULT SYSDATE
7 );

Table created.

SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);

Index created.

SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE;
ALTER INDEX index_debug1 ADD COLUMN MESSAGE
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL >

View 6 Replies View Related

SQL & PL/SQL :: Update Each Row - Unique Index

Jun 23, 2011

I've got a table which has no unique identifier for a row. I have a field for an ID value but somhow there are duplicate values in this field.

Now I want to create an unique index over this field but therefore I have to remove the duplicate values.

So what I need is an update statement, maybe in a for each loop, which updates the ID field in each row starting with 1 and increasing the value.

In Informix there is a rowid value which is automatically set by Informix. But I think there´s no correspondent function/value in Oracle....

View 17 Replies View Related

Unique Function Based Index

Jun 27, 2011

I have a table which sees a lot of use for queries

CREATE TABLE CASE_STAGE
(
ID NUMBER(9) NOT NULL,
STAGE_ID NUMBER(9) NOT NULL,
CASE_PHASE_ID NUMBER(9) NOT NULL,
"CURRENT" NUMBER(1) NOT NULL,
--and other columns
)

ID is a primary key
CASE_PHASE_ID is a foreign key

"CURRENT" should only ever have values of 0 or 1. When it has a value of 1 it is unique for that CASE_PHASE_ID

What I have tried that doesn't work is

create unique index case_stage_F_IDX1 on case_stage("CURRENT", case_Phase_id) which gives me
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

What is the correct syntax, something like ("CURRENT"=1,case_phase_id) seems right but fails with an error about a missing bracket. Do I need to use a CASE statement here?

View 1 Replies View Related

How To Create Unique Index On A View

Aug 24, 2012

11.2.0.1...How do I create an index on a view or any workaround that my view won't get duplicates?

SQL> create unique index indx01 on db_backup_details_vw(id);
create unique index indx01 on db_backup_details_vw(id)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here

View 7 Replies View Related

Grabbing 2 Unique Values Based On A Field?

Nov 29, 2012

I have a list sample:

Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
141138899 MLIQUID02T 368308 CARLENA
5078916 MLIQUID02T 368308 DONNA

[code]....

What I'd like to do is run SQL that can create output where I get 2 records from each Location. Example:

Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
13999694 MLIQUID03T 368308 TINA
175439805 MLIQUID03T 368308 RANDI
4801973 MLIQUID05T 368308 DIANA
55907648 MLIQUID05T 368308 DESIREE

Personally, I don't need the top value(s), but it would be nice.I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two.

View 1 Replies View Related

Can Create Unique Index On Subset Of Record

Apr 9, 2013

I have following records in table (T)

Idtype_cdNbrDt
1A1001/1/2013
2A101Null
3B100Null
4C100Null
5A100Null

Based on the type-cd, I need to create Unique index on Nbr & Dt columns

In above record set, I want to create Unique index on type_cd=A and not on B&C (Here i expect duplictes for a combination of Nbr & Dt)

Can we create Unique index on subset of record?

View 1 Replies View Related

SQL & PL/SQL :: Table With A Non-unique Index Consisting Of Three Columns

Sep 13, 2013

I have a table with a non-unique index consisting of three columns. The first column is not null while the remaining two are nullable. Queries using this index will chiefly be made in two ways.

1. Column one and two having values. Column three is null.
2. Column one and thre having values. Column two is null.

In both cases I expect range scan will be used since it's non-unique. In the first case the scan will be on values in column one and two. But what happens in case two. Will the range be on colum one, column two(being null) and cxolumn three? Or will it be on just column one since the second column is null? I have done some testing. I can see , using EXPLAN PLAN, that range scan is used in both cases. how the index is used?

Is there any other drawbacks with an index like this?

View 13 Replies View Related

SQL & PL/SQL :: Duplicated Entries In A Valid Unique Index?

May 26, 2010

I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.

Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.

And it is still in VALID state

SQL> set autotrace on
SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);
select ri from scratch1_p where scnum='444393975';
RI SCNUM
---------- ----------
536955574 444393975

[code].....

View 14 Replies View Related

Find Situations Where Single Unique Value Of Field A Has Both Values Z AND X In B?

Feb 19, 2011

In literal terms, I'm interested in two fields, I'll call them Field A and Field B. I want to find all situations where a single unique value of Field A has both values Z AND X in Field B (not either or, but both together).

To go into some detail -

I need to make a query that finds ONLY occurrences where one employee id has a certain set of values together (without going into specifics, I'll say PermissionA, PermissionB, PermissionC). I can easily make a query that returns all user id's and all permissions belonging to them, and I can use criteria to filter the results to Permission A B and C only so as to exclude other permissions from getting returned (since there are hundreds),

however my objective is to get ONLY results where the same employee ID has all of those permissions (not just any one or two of the three). However, I don't want to have any criteria that limits the employee ID (I want to search all employee id's, and get a list of those with permissions a and b and c, not just any combination thereof, but all of them). I'm currently able to organize the output using a pivot table by employee id > role,

so that I can easily look at each employee and the roles they have, but I want to undertake a project that will involve searching a much larger number of employees (a # that makes it impractical for me to look through the list, I need to have a query that limits the results to the combinations that I'm looking for, as in this example - permission a b and c together).

View 1 Replies View Related

Performance Tuning :: Force Index Unique Scan?

Jun 14, 2012

Is there any hint to force "Index Unique Scan" over "Index Range Scan".

My query is generating different plans with the above two, and very slow when it uses "Index Range Scan".

View 19 Replies View Related

SQL & PL/SQL :: What Should Be Order Of Columns While Creating B-tree Non-unique Index

Jul 19, 2012

What should be the order of columns while creating b-tree non-unique index.

Low cardinality first
or
Hight cardinality first

View 15 Replies View Related

When Inserting Data Unique Index Initially In Unusable State

Apr 9, 2013

We are getting an error as below when trying to load data into a table.

INSERT /*+ APPEND parallel(IA_SBSCR_DED_MAX,4) */ INTO EDW.IA_SBSCR_DED_MAX
*
ERROR at line 1:

ORA-12801: error signaled in parallel query server P000
ORA-26026: unique index EDW.XPKIA_SBSCR_DED_MAX_A initially in unusable state

The index has been rebuilt but we still have this issue.

View 8 Replies View Related

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

View 2 Replies View Related

SQL & PL/SQL :: How To Get Unique Value In Column

Oct 25, 2012

i am using one query which should return unique value. I have one table that has one column punch date datatype is date.

here value is stored in two rows that is

1. 24-10-12
2. 24-10-12

and my query is this:

SELECT distinct(PunchDate) FROM Trans_RawProcessDailyData ORDER BY PunchDate ASC;

but still getting two values.

View 11 Replies View Related

Non Unique Alternate Key Of A Table

Feb 11, 2011

Can a table conatin composite Alternate key which is not unique for each record in that table i.e combination of these columns have unique values which are repeated for one or more rows in that table.

View 8 Replies View Related

Unique Constraint Violated?

Mar 26, 2007

From a step by step instructions I'm asked to put the following into sql*plus:

CREATE TABLE Lab2Lecturer
(staffNO VarCHAR2(10) NOT NULL,
title VARCHAR2(3),
fName VARCHAR2(30),

[code]...

Then the following:

INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode, country, lecturerLevel, bankNO, bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Johanna','Santoso',
'3 Robinson Av', 'Kew', 'Melbourne', '3080', 'Australia', 'C', '1000567237', 'CommBank', 65000.00,1.0, 'O-R DB');

and finally,

INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode,country, lecturerLevel, BankNO,bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Justine', 'Martin', '6 Algorithm AV', 'Montmorency', 'Melbourne', '3089', 'Australia', 'D', '1000123456', 'CommBank', 89000.00, 1.0, 'CBR');

when I try entering in the second one I get an error 'unique constraint violated'.So whats wrong exactly?

View 1 Replies View Related

SQL & PL/SQL :: How To Find Unique ID When Several Matches

Feb 1, 2012

I have a simple question, but i can't resolve it.

I have a table like this :

ID_A | VALUE
-------------
A 1
A 2
B 1
B 2
B 3
C 1
C 3
D 1

I need to fid the unique ID that match perfectly VALUE = 1 or 2.

I tried SOME/ANY/ALL/IN

View 14 Replies View Related

SQL & PL/SQL :: How To Get First Transaction For Every Unique Client

Nov 2, 2010

I have a table called pf_stock_txns which just stores all of a clients transactions

If I do select distinct(client_id) from pf_stock_txns then I get back a unqiue list of all clients.

However, I need a query that will give me the first transaction for every unqiue client.

View 17 Replies View Related

SQL & PL/SQL :: Generating Unique Code

Jun 27, 2011

I have data like this:

a1 a2 a3 a4
ABC ABC xx zz
ABC xx mm mm
fg ui ok pl

I want to give them unique codes like :

Code for column
a1 Code1
ABCC-1
ABCC-1
fg C-2

then column
a2 Code2
ABC C-1
xxC-3
uiC-4

then column
a3 Code3
xxC-3
mmC-5
okC-6

then column
a4 Code4
zzC-7
mmC-5
plC-8

I can add these `columns in the table Code1 , Code2 etc columns to update.

View 1 Replies View Related

SQL & PL/SQL :: Generate Unique Combination

Dec 17, 2011

I need a "solution", guidance to a problem I have to solve. I have different letters with a value associate to it like

A:10, B:20, ..., G:250 and I have a Target to reach TARGET= 90

and I need to find among all letters I have which combination is equal or closest to my target.

A:10
B:20
C:20
D:20
E:30
F:40
G:250

IF there is more than one solution possible, the first found is perfect. With my example I can reach 90 with different combinations:

A+B+C+F
A+B+D+F
A+C+D+F
B+C+D+E
B+E+F
C+E+F
D+E+F

To complicate the things I have up to 10 different letters that need to combine to match my target. If there is no combination that exactly match my target, the closest higher combination is picked.

View 31 Replies View Related

How To Generate Unique Identifier

Jun 21, 2012

Is there a way to generate a unique identifier(length 8), which can contain numbers (0-9) and letters(a-z) in pl/sql or sql ?

Note :- in oracle 9i.

View 23 Replies View Related

PL/SQL :: Unique Values Of Each Column

Aug 21, 2013

I have a problem .I have a oracle table its column count more then 500 . I want unique values of each column and insert one table.

View 44 Replies View Related

PL/SQL :: Unique Constraint Violated

Oct 4, 2013

My DB version is

BANNER                                                     
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production                       
CORE 10.2.0.4.0 Production                                     
TNS for Linux: Version 10.2.0.4.0 - Production               
NLSRTL Version 10.2.0.4.0 - Production  

These error codes I'm getting in production.First of all, I've no duplicates present in that table for which this error has been raised.I've checked the index and related columns as well. NO DATA is there.So NO CHANCE for unique constraint violation.

SELECT * FROM ORDER_OCC_REQUISITION_X_REF WHERE LAB_ORDER_OCC_TEST_ID IN(SELECT LAB_ORDER_OCC_TEST_ID FROM LAB_ORDER_OCC_TEST WHERE LAB_ORDER_OCC_ID = 7944858);

no rows selected

 Now when I'm trying to insert one row inside this table I'm getting this error, as you are seeing no records for this occurrence_id. 

SELECT * FROM USER_INDEXES WHERE INDEX_NAME = 'ORD_OCC_REQ_UQ_TEST_IX_04';
--ORDER_OCC_REQUISITION_X_REF (Table name)
--MERGE_DT, LAB_ORDER_OCC_TEST_ID, TEST_ID, ACTIVE_YN (columns for the index 'ORD_OCC_REQ_UQ_TEST_IX_04')
As you can see there is no data then this error should not be raised.  Update procedure.
/*******************************************************************************************************************
     * Name                    : UPDATE_REQUISITION_X_REF
     * Description             : This Procedure update ORDER_OCC_REQUISITION_X_REF table with requisition_id
     *                           that was generated due to merge process.
     * In  parameters          : IN_merge_id         NUMBER   The order_ref_no of the orders to be merged (comma seperated)
     ***********************************************************************************************************************/
    PROCEDURE   UPDATE_REQUISITION_X_REF ( IN_merge_id  IN TT_ORD_REQUISITION_WORK_AREA.merge_id%TYPE)
    IS
  
[Code]....

View 8 Replies View Related

PL/SQL :: Getting Unique Records From Table

Aug 24, 2012

Oracle Database Version is 10g ( 10.2.0.4.0)

I have two table emp_perm & employee.

1) Table emp_perm is a lookup table with 2 columns, temp VARCHAR2 & perm VARCHAR2.

Table emp_perm have below data in it:-
temp              perm
1064885        0349034
0099982       7399982
6476456        9170346
5252525       52525252)

Now table employee might have following combinations of rows.

Records for both Temps file (1064885) and perm file number (0349034) does exist in employee table, in this case Delete Temp File Number records i.e. 1064885 and keep Permanent File Number (0349034) records as it is.

Records for only temp file number(0099982) does exists and corresponding perm file(7399982) does not exists in employee table in this case convert these records i.e. update 0099982 with its perm file number 7399982 using lookup table emp_perm.

Records for only perm file number exists (9170346) and corresponding temp file number (6476456) does not then do not amend employee table, as we are interested in perm file numbers.

If file number does exist in lookup table in emp_perm and both of them are equal (5252525) then do not amend employee table as temp and per file number are same.

View 5 Replies View Related

SQL & PL/SQL :: Replace Unique Identifier?

May 27, 2012

How to replace uniqueidentifier in PL/SQL ,I have query like this,

@EmployeeID uniqueidentifier = NULL.

View 11 Replies View Related

SQL & PL/SQL :: Finding UNIQUE Combination

Oct 4, 2012

I have contents like below:

BREAD,BISCUIT
BREAD,MILK,BISCUIT
COKE,MILK
MILK,SUGAR

Now combination from each row will be like below (Just for understanding):

BREAD,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {BISCUIT,BREAD}
BREAD,MILK,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {MILK} {BISCUIT,BREAD} {BISCUIT,MILK} {BREAD,MILK} {BISCUIT,BREAD,MILK}
COKE,MILK --> [COMBINATION] {COKE} {MILK} {COKE,MILK}
MILK,SUGAR --> [COMBINATION] {MILK} {SUGAR} {MILK,SUGAR}

Now the ultimate aim is to find unique combinations (even if the same combination is present in different rows, we have to consider it as one combination), along with its frequency, result will be like below:

BISCUIT [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
BREAD [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
COKE [occurence in 1 transactions and total 4 transactions] = 2/4 = .25
MILK = 3/4 = .75

[Code]....

writing query to find unique combination like above? What I have tried is as below:

create table test (row_no number, col_no number, item varchar2(50))
/

insert into test values (1,1,'BREAD');
insert into test values (1,2,'BISCUIT');
insert into test values (2,1,'BREAD');

[Code]....

But I am not able to form the exact query. let me know if this can't be done through a single query.

View 20 Replies View Related

RMAN Unique Constraint Violation

Aug 23, 2011

Our RMAN backup failed because of the error:

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/22/2011 22:19:07
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 08/22/2011 22:19:07
ORA-00001: unique constraint (ODBA.DF_U2) violated

Searching metalink, its related to bug 6014994 and the proposed workaround is to delete the constraint:

Cause:
Dropping a datafile from a tablespace followed immediately by adding
another datafile to the same tablespace will cause this Unique Key violation.
Taking a RMAN debug trace will show the file# related to the error
This is reported as bug <<6014994>> Unpublished on Metalink and fixed in 11g
RMAN RESYNC catalog signals DF_U2 violated constraint when a file# is reused in
the same tablespace
Solution
WORKAROUND: Drop df_u2 constraint

where can I delete the constraint, is it possible to do in RMAN or in the target instance?

View 1 Replies View Related

How To Handle Unique Constraint Violation

Feb 5, 2013

I've set up Oracle Streams replication between multiple N-way zones like this:

CODE------------------------------
Nway1 |Intersect| Nway2
DB1 --|-- DB3 --|-- DB5              DB1, DB2, DB3, DB4 - N-way1
  |   |    |    |    |               DB3, DB4, DB5, DB6 - N-way2
DB2 --|-- DB4 --|-- DB6              DB3, DB4 - intersection zone
      |         |  
-------------------------------

Physically DB1 .... DBN connected sequentially, so I want to prevent segmentation if some DB is unaccessible, but at the same time fight unneeded redundancy which uses too much link bandwidth to send N-1 LCR-s to all members of a single N-way group (so I want to split one big N-way zone into smaller ones and sequentially connect them into chain - it significantly reduces load on link if N is big enough (>10)). Also I want to have 2 DB in intersection zone to prevent single point of failure.

This scheme has one drawback - if change originated on DB3 or DB4, then it will be propagated (more correctly - applied and captured again) to DB5 and DB6 by both DB1 and DB2 (and, as far as I know, I have no means in capture rules to detect state of DB2 from DB1 and vise versa), so on DB5 and DB6 I get:

CODEORA-00001: unique constraint (DUMMYUSR.UNIQUE_RECORDS) violated error

I've set up standard conflict handler for apply process:

CODE

declare                                                                                        
    cols DBMS_UTILITY.NAME_ARRAY;                                                              
begin                                                                                          
    cols(1) := 'no';                                                                          
    cols(2) := 'name';                                                                        
    cols(3) := 'ddate';                                                                        
    dbms_apply_adm.set_update_conflict_handler(                                                
        object_name       => 'DUMMYUSR.DUMMYTBL',                                              
        method_name       => 'DISCARD',                                                        
        resolution_column => 'no',                                                            
        column_list       => cols                                                              
    );                                                                                        
end;

but it seems that it does not handle uniqueness conflicts. What is the best way to handle uniqueness conflict (is there a better way than to write custom error handler) and how serious is the impact on insert performance of having unique constraint and corresponding error handler. (In real world I will have to deal with tables with metainformation and without any keys).

Also, how to proceed with no error or raise exception from apply error handler with error that caused this handler to run? In oracle docs I can find only example that modifies LCR and runs lcr.EXECUTE(TRUE), but what to do if I don't want to reexecute LCR, but merely check error code and propagate error if it is not ORA-00001?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved