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


ADVERTISEMENT

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 View Related

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 :: 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

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

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

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

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

SQL & PL/SQL :: Unique Constraint When 1 Column Is Not Null

Jun 15, 2012

I have 3 columns in a table: colX, colY, colZ.

Trying to find a way to prevent duplicates with these, but only if colX is not null.

For example, if there are already values for: colX = 1, colY = 1, colZ = 1

then:

Allowed: colX = null, colY = 1, colZ = 1
Not allowed: colX = 1, colY = 1, colZ = 1

I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.

I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.

Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.

View 4 Replies View Related

SQL & PL/SQL :: Multiple Unique Values In One Column?

Aug 26, 2011

I have a problem with some tables in database.

Table has three columns: userid, pname, pvalue.

Userid has unique values, for example: 234, 123, 587, etc.

In the field pname, there is three possible values: MAC, IP, S/N.

So if I go like this:

select pvalue
from table
where pname = 'MAC';

i get the values of MAC.

If I go like this:

select pvalue
from table
where pname = 'IP';

i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.

View 11 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 :: Updating Column With Unique Values Without Using Sequences

Apr 17, 2013

i am having a table with out pk along with data.

Now , i need to add one column to that table , and update this column with the sequence no

like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.

how can i do.

View 1 Replies View Related

ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jan 14, 2013

I'm trying to make a foreign key against a table which has a unique index instead of a primary key.

i get this error:

Error SQL: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.

my question is (i've searched several times with no results) can i create a foreign key with a table wich has no primary key but a unique index ?example:

CREATE TABLE TABLE1(
IDTABLE1 NUMBER(5),
NAME VARCHAR2(30)
);
CREATE TABLE TABLE2(

[code]....

or TABLE1.IDTABLE1 must be a 'Primary Key' instead of unique index ?I know it should be primary, but i need to know if it would work with the index somehow. The reason is i'm migrating a large database and the original structure in mysql uses fk with indexes and no pks in some tables (48 to be precise)

View 11 Replies View Related

SQL & PL/SQL :: Column DA_OBJECT_NAME Is Not Unique / Same Object Name Can Have Multiple Entries

Jul 22, 2011

I have a table DN_ACTIONS with 9.5 million records. Column DA_OBJECT_NAME is not unique and same object name can have multiple entries. Column DA_ACTION_STATUS can have any values between 1 to 5.

I want to find all the DA_OBJECT_NAME in DN_ACTIONS where all of its entries has DA_ACTION_STATUS=3.

CREATE TABLE DN_ACTIONS
(
DA_ID NUMBER,
DA_OBJECT_NAME VARCHAR2(192 BYTE),
DA_ACTION_STATUS NUMBER(1)
);

best way as it does a self join on big table of 9.5 million.

(select distinct a.DA_OBJECT_NAME from DN_ACTIONS a where not exists ( select distinct DA_OBJECT_NAME from DN_ACTIONS b where a.DA_ OBJECT_NAME = b.DA_OBJECT_NAME and b.DA_ACTION_STATUS != 3))

What are the better ways to rewrite this query?

View 2 Replies View Related

Reports & Discoverer :: Get Unique Values With Formula Column

Jul 5, 2013

I am facing a problem in leave_form report! want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP' repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'

problem is report is showing 22 records of leavetype 'CL' of same leavedate!

i want that report should show the actual leavedate and leavetype records.using 9i database 6i developer server2003

For leavetype

FUNCTION Cf_1formula
RETURN CHAR
IS
v_leavetype CHAR(40);
BEGIN
SELECT LT.description
INTO v_leavetype
FROM hrm_attendance L,
hrm_leavetype LT,
hrm_employees E
[code]....

View 2 Replies View Related

SQL & PL/SQL :: Creating Composite Unique Key With Only 1 Column Allowed To Be Changed

Jul 3, 2013

I have a table say MY_TAB with columns as below

emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)

insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');

How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.

insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.

View 13 Replies View Related

PL/SQL :: Bitmap Index On Number Column X Char Column?

Jul 23, 2013

is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:  

select ass.column20,  ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'    

If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO

View 7 Replies View Related

Server Utilities :: ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jul 6, 2010

I was cloning a schema user1 as user2 in the same database.

user1 had quota on 2 tablespaces user1_data and user1_index.

I created user with name as user2.

I created tablespace user2_data only and granted user2 unlimited quota on that tablespace only (did not grant him 'resource' role or unlimited tablespace privilege) Now exported user1 schema as follows

exp system/<passowrd> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user1_schema_exp.log owner=user1 rows=y constraints=y triggers=y indexes=y statistics=none recordlength=65535 compress=no consistent=n grants=y

then imported in in user2 schema as follows

imp system/<password> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user2_schema_imp.log fromuser=user1 touser=user2 rows=y constraints=y indexes=y statistics=none recordlength=65535 grants=y

during import i encountered following errors for so many constraints

"ALTER TABLE "table2" ADD CONSTRAINT "constraint_name1" FOREIGN KEY ("CTR_ID") REFERENCES "table1" ("CTR_ID") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:

I found that the it happened as the primary key of table1 was not created for which error was logged in the log file

. . importing table "table1" 19441 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "table1" ADD CONSTRAINT "T1_PK79" PRIMARY KEY ("CTR_"
"ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER1_INDEX" LOGGING ENABLE "
. . importing table "table5" 0 rows imported

However, I checked that the T1_PK79 does not exist in the user2 schema though it exists in user1 schema Neither the index for priamry key (T1_PK79) existed in user2 schema not the table <table1> existed before this import Then what could be the reason that I am getting an error "IMP-00015: following statement failed because the object already exists"?

I assume tablespace for index would not be an issue here as other indexes got created properly in user2_index tablespace during this import.

I tried this twice, once with user2 schema and then with user3 schema as well (with different tablespace), but result is the same.

There were no users connected to the database during export and no background jobs were modiying any data in schema user1 while export.

View 4 Replies View Related

Index On Null Column

Oct 31, 2012

I have a table with column A which contains very few null values. I need to select these rows. I am considering two options:

a) create function based index on NVL(A, 0) and use this in where clause NVL(A, 0)=0 (column doesn't have values 0)
b) create function based index on NVL2(A, 0, NULL) and and use this in where clause NVL2(A, 0, NULL) = 0

First idea was option A. But I realized in option B the index will be much smaller, because most of values of column A isn't NULL so NVL2 will return NULL and index will not have as much leafs as in NVL. It is good idea to use NVL2? Is there any against to use option B instead of A?

View 1 Replies View Related

PL/SQL :: Make Index On Column?

Oct 5, 2013

If my query is under execution and I want to make an index on a column which is very much needed by my query. Will a simple index solve the purpose or is there any extra keyword required ?

View 2 Replies View Related

PL/SQL :: Index On Date Column

Aug 2, 2012

Is the index suggested on the date column.

Here is the nature of the date column in my case:

1. The table populates with 1000's of records every day with date being always incremental (current date).

2. The search criteria from the weba application (ADF) is based on the date, user gives the range.

3. From ADF I am referring to it as sql.Timestamp when building the query.

Does Index suggested on the date column here and if so what type of index ?

View 4 Replies View Related

Creating Index On Hashed Column

Sep 23, 2010

I currently have a 5 column index on a table with over 2 billion records (paritioned on created_date (weekly) that is not very effective.I am contemplating replacing this 5 key index and creating a new single column index made up by hashing of all the 5 five columns.

Is this a wise stratgey? How can I implement this so it is most effective and I dont shoot myself in the foot?

View 4 Replies View Related

Multi-column Index - Low Cardinality?

Oct 13, 2011

I have a table with, for example, three columns: A, B,C.

I execute on this table only one select: CODESELECT * FROM TABLE WHERE A = :1 AND B=:2

Column A has a lot of distinct values (numbers), but B can have only two values: 'Y' or 'N' (cardinality about 50%/50%). It is worth to create index on two columns: A, B? Does query using index on A column will be much slower than using index on A, B?

View 4 Replies View Related

Creating A Multi Column Index

Jan 24, 2012

I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.

the way to create multi_column indexes on the table?

What are the rule to define the best-position of a column in an index?

index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?

View 4 Replies View Related







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