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?.
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.
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
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?
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
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.
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?
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
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).
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.
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.
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
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.
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.
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?
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:
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?
