Home » SQL & PL/SQL » SQL & PL/SQL » How unique index duplicated ? (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi ; Export: Release 10.2.0.1.0)
How unique index duplicated ? [message #323277] Wed, 28 May 2008 01:09 Go to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

HI All ,

We tried to import one schema (A) from another schema (B) in another Database . Then it came to know that it failed to create a unique index on the biggest table rated_call ( Pseudo name) .

It is having UNIQUE index on that table rated_call in Schema A.

CREATE UNIQUE INDEX RATED_CALL$UNIQUE ON RATED_CALL
(CALL_DATE, PARTNER_CODE, CALL_DIRECTION, SERVICE_CODE, A_NUMBER,B_NUMBER, TIME_TYPE,AGREEMENT_TYPE)
LOGGING TABLESPACE TAB01;


Interstingly when I executed a query as in SCHEMA A (even in schema B )

select   CALL_DATE, PARTNER_CODE, CALL_DIRECTION, SERVICE_CODE, 
         A_NUMBER,B_NUMBER, TIME_TYPE,AGREEMENT_TYPE ,count(*)
from     RATED_CALL 
group by CALL_DATE, PARTNER_CODE, CALL_DIRECTION, SERVICE_CODE, A_NUMBER,
         B_NUMBER, TIME_TYPE, AGREEMENT_TYPE
having count(*) >1


it returns 1000's of record !!!!!!

The RATED_CALL is having about 50 M records.
Index status is still valid.

I am justwondering any chance for this scenario ...

Thumbs Down
Rajuvan

[Updated on: Wed, 28 May 2008 02:20]

Report message to a moderator

Re: Strange !!! Any chance to happen ? [message #323279 is a reply to message #323277] Wed, 28 May 2008 01:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Strange !!! Any chance to happen ? [message #323284 is a reply to message #323277] Wed, 28 May 2008 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reread what you posted, now think we only have this information, can you say something? Can you reproduce the case with only what you posted?

Regards
Michel
Re: Strange !!! Any chance to happen ? [message #323291 is a reply to message #323277] Wed, 28 May 2008 01:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understood the problem ...

OK, index isn't created. Why? We know it was supposed to be a unique one, but - was there any error? Duplicates in a table? Did you run out of space?

Index is not necessary for Oracle to work. It may help retrieve result faster, but - without it, every query will work. Therefore, I don't know what the fact that index isn't created has to do with the fact that SELECT statement returns records from a table?

What's the question, again?
How unique index duplicated ? [message #323301 is a reply to message #323277] Wed, 28 May 2008 02:17 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@Anna ..
I am not sure , which rule i violated even after 1000 post . might be I am excited with this error Smile

@ Micheal and Littefoot :

The Issue is that the table is having Unique index on 8 fields and these fields are duplicating even in the table. That seems to be so strange. (count(*) >1 )


Quote:
select CALL_DATE, PARTNER_CODE, CALL_DIRECTION, SERVICE_CODE,
A_NUMBER,B_NUMBER, TIME_TYPE,AGREEMENT_TYPE ,count(*)
from RATED_CALL
group by CALL_DATE, PARTNER_CODE, CALL_DIRECTION, SERVICE_CODE, A_NUMBER,
B_NUMBER, TIME_TYPE, AGREEMENT_TYPE
having count(*) >1


Returns about One million Row with count > 1 .

Index is staill valid status . What could be the reason ?

Intersting thing is that , We came to knwo bout this issue when we tried to import the table into another schema and import failed to apply the unique index .

Feel free for any information for further analysis .

Thumbs Down
Rajuvan.


[Updated on: Wed, 28 May 2008 02:25]

Report message to a moderator

Re: How unique index duplicated ? [message #323311 is a reply to message #323301] Wed, 28 May 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We still don't have anything to reproduce.
So maybe you are excited by the error but we are not. We can't even validate what you say.

Regards
Michel
Re: How unique index duplicated ? [message #323318 is a reply to message #323277] Wed, 28 May 2008 03:14 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
We still don't have anything to reproduce.


Even I am not able to reproduce.
ie , If I import from Schema A to Schema B . Its not creating unique index in B.. while unique is there in schema A (even with duplication ) .

Quote:
So maybe you are excited by the error but we are not


As an Oracle expert , you are supposed to be excited about such rare instance. !!!!!

I am not for a solution actually .
But Just want to know anybody has came across such instance.
And interested to know what could be the causes .



regards,
Rajuvan.

[Updated on: Wed, 28 May 2008 03:15]

Report message to a moderator

Re: How unique index duplicated ? [message #323325 is a reply to message #323318] Wed, 28 May 2008 03:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have you checked the log file from the import - if the index hasn't been created, then there is probably an error in there - quite possibly some sort of storage error.

What happens if you try and create the index by hand, exactly as specified in the export .DMP file - I'm betting it will error.

Re: How unique index duplicated ? [message #323331 is a reply to message #323277] Wed, 28 May 2008 04:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Is the table partitioned ? If so, the index on that table is it a local index or a global index ?

Regards

Raj
Re: How unique index duplicated ? [message #323335 is a reply to message #323318] Wed, 28 May 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As an Oracle expert , you are supposed to be excited about such rare instance. !!!!!

I should be excited if I was sure you made any error.
I'm not excited for something I have not information enough and can't investigate.

Regards
Michel
Re: How unique index duplicated ? [message #323392 is a reply to message #323277] Wed, 28 May 2008 06:40 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thank you for the reply .

@ S.Rajaram :

I Missed out to tell you, that the table is Partioned table .
date range partition .
The Indexes are Global Indexes only.

@JRowbottom :

Importing the Unique index on this table fails because of duplicated entries in table.(Its a partion table). Tried to create Unique index . It is also failed as expected.

But still wondering .. why the unique index is not violated in Source schema and it is violated in target .


Rajuvan

[Updated on: Wed, 28 May 2008 06:42]

Report message to a moderator

Re: How unique index duplicated ? [message #323421 is a reply to message #323392] Wed, 28 May 2008 08:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The data in your source schema is (self evidently) different to the data in your new schema.

Have you by any chance imported the data twice?
Re: How unique index duplicated ? [message #323577 is a reply to message #323421] Wed, 28 May 2008 22:24 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Prove it to us. Show us the contents of DBA_INDEXES and DBA_IND_COLUMNS that show the index, its status, uniqueness, base table and columns. Then show us the SQL that demonstrates non-uniqueness.

Do this in both databases, demonstrating that the data is both identical and non-unique.

Ross Leishman
Re: How unique index duplicated ? [message #323671 is a reply to message #323277] Thu, 29 May 2008 03:35 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thanks for the replies .


By the way , there is no chance to import the data twice to same table . because the import is dome to newly created schema that too only Once.

Unfortunately there is no source schema table exists to show the duplictae rows , because the schema is dropped because of Oracle reinstalation in that server ( Here DBAs failed to clone the database to the target database , because of that we had to do these exp-imp tasks ). Andwe started working new schema and table from the scratch (after truncating them).

Now We need to monitor how the duplicates are present for the unique key index fiels , though it seems to be fine as of now in new schema.


Thanks once again to rleishman , JRowbottom , S.Rajaram , Littlefoot and Michael Cadot for their excellent support.

Thumbs Up
Rajuvan.
Re: How unique index duplicated ? [message #323872 is a reply to message #323671] Thu, 29 May 2008 22:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Since you can no longer see the original database and confirm that a VALID UNIQUE index contained duplicate rows, I would contend that the index was in fact INVALID.

An INVALID index is not like a DISABLED constraint. If you imported a disabled constraint it would stay disabled, but (and I haven't verified this) an invalid index is not a desirable state - an import would try to validate it.

My guess is that the original source database has a DIRECT PATH LOAD that inserts rows into it. At least one of these direct path loads inserted duplicates at some point in time and the error was not discovered and rectified.

Ross Leishman
Re: How unique index duplicated ? [message #324841 is a reply to message #323277] Wed, 04 June 2008 05:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi All,

The Process is re-executed in the newlt created schema on the truncated table . But the issue of duplicates on unique key still remains.

Here is some sample to show something is going wrong somewhere.
Please find the create table script of table (partition + Index)
(It is too big to paste here )


1. The Indexes on the current table .
RATED_CALLS$UNIQUE_CDR is the unique key

SQL> SQL> select  INDEX_NAME,INDEX_TYPE,UNIQUENESS
  2  from  user_INDEXES
  3  where TABLE_NAME='RATED_CALLS';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
RATED_CALLS$ROAM_CDRS        NORMAL                      NONUNIQUE
RATED_CALLS$UNIQUE_CDR       NORMAL                      UNIQUE
RATED_CALLS$FILE_ID          NORMAL                      NONUNIQUE
RATED_CALLS$SUMMARY_SEQ_NO    NORMAL                      NONUNIQUE
RATED_CALLS$REPRICE_SEQ_NO   NORMAL                      NONUNIQUE

SQL> select INDEX_NAME, COLUMN_NAME , COLUMN_POSITION PS,
  2         COLUMN_LENGTH LN, CHAR_LENGTH CLN, DESCEND
  3  from user_IND_COLUMNS
  4  where TABLE_NAME='RATED_CALLS';

INDEX_NAME                     COLUMN_NAME           PS  LN CLN DESC
------------------------------ -------------------- --- --- --- ----
RATED_CALLS$SUMMARY_SEQ_NO    SUMMARY_SEQ_NO         1  22   0 ASC
RATED_CALLS$FILE_ID          INPUT_FILE_ID          1  22   0 ASC
RATED_CALLS$UNIQUE_CDR       CALL_DATE              1   7   0 ASC
RATED_CALLS$UNIQUE_CDR       PARTNER_CODE           2   3   3 ASC
RATED_CALLS$UNIQUE_CDR       CALL_DIRECTION         3   1   1 ASC
RATED_CALLS$UNIQUE_CDR       SERVICE_CODE           4   3   3 ASC
RATED_CALLS$UNIQUE_CDR       A_NUMBER               5  25  25 ASC
RATED_CALLS$UNIQUE_CDR       B_NUMBER               6  25  25 ASC
RATED_CALLS$UNIQUE_CDR       TIME_TYPE              7   3   3 ASC
RATED_CALLS$UNIQUE_CDR       AGREEMENT_TYPE         8   1   1 ASC
RATED_CALLS$ROAM_CDRS        AGREEMENT_TYPE         1   1   1 ASC
RATED_CALLS$ROAM_CDRS        PARTNER_CODE           2   3   3 ASC
RATED_CALLS$ROAM_CDRS        ROAMING_STATUS         3   1   1 ASC
RATED_CALLS$ROAM_CDRS        DATA_TYPE_INDICATOR    4   1   1 ASC
RATED_CALLS$ROAM_CDRS        FILE_SEQUENCE_NUMBER   5  22   0 ASC
RATED_CALLS$ROAM_CDRS        CALL_DATE              6   7   0 ASC
RATED_CALLS$REPRICE_SEQ_NO   REPRICE_SEQ_NO         1  22   0 ASC

17 rows selected.

SQL>


2. Query to show the Duplication on Unique index fields .

SQL> SELECT * FROM (
  2  SELECT CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  3         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE, COUNT(*)
  4  FROM RATED_CALLS
  5  GROUP BY CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  6         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE
  7  HAVING COUNT(*) >1   )
  8  WHERE  ROWNUM <4;

CALL_DATE           PAR C SER A_NUMBER     B_NUMBER     TIM A   COUNT(*)
------------------- --- - --- ------------ ------------ --- - ----------
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I          3
24/04/2008 19:11:29 FK6 I 000 256751048192 256712806516 STA I          5
24/04/2008 19:12:24 3QQ I 003 256774738315 256714564353 STA I          3

SQL>


3. The queries are executed for the first non-unique combination.

24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I          3


The following 3 queries returns One record each !!!



Q1) with all unique parameter

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE  from
  3  RATED_CALLS
  4  where CALL_DATE = TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
  5  and PARTNER_CODE='FK6'
  6  and CALL_DIRECTION='I'
  7  and SERVICE_CODE='000'
  8  and A_NUMBER='256753557095'
  9  and B_NUMBER='256714765489'
 10  and TIME_TYPE='STA'
 11  AND AGREEMENT_TYPE='I'
 12  ;

CALL_DATE           PAR C SER A_NUMBER     B_NUMBER     TIM A
------------------- --- - --- ------------ ------------ --- -
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I


Q2 ) Query without B_NUMBER

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE
  3  from  RATED_CALLS
  4  where CALL_DATE = TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')

  5  and PARTNER_CODE='FK6'
  6  and CALL_DIRECTION='I'
  7  and SERVICE_CODE='000'
  8  and A_NUMBER='256753557095'
  9  and TIME_TYPE='STA'
 10  AND AGREEMENT_TYPE='I';

CALL_DATE           PAR C SER A_NUMBER     B_NUMBER     TIM A
------------------- --- - --- ------------ ------------ --- -
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I


Q3) with all unique parameter within Expanded call_date range

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE
  3   from  RATED_CALLS
  4  where CALL_DATE  between
  5   TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
  6   and  TO_DATE('4/24/2008 7:11:59 PM','MM/DD/YYYY HH:MI:SS PM')
  7  and PARTNER_CODE='FK6'
  8  and CALL_DIRECTION='I'
  9  and SERVICE_CODE='000'
 10  and A_NUMBER='256753557095'
 11  and B_NUMBER='256714765489'
 12  and TIME_TYPE='STA'
 13  AND AGREEMENT_TYPE='I'
 14  /

CALL_DATE           PAR C SER A_NUMBER     B_NUMBER     TIM A
------------------- --- - --- ------------ ------------ --- -
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I


Now Comes Drama !!!

Q4) All with all unique parameter within Expanded call_date range without B_NUMBER.

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE
  3   from  RATED_CALLS
  4  where CALL_DATE  between
  5   TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
  6   and  TO_DATE('4/24/2008 7:11:59 PM','MM/DD/YYYY HH:MI:SS PM')
  7  and PARTNER_CODE='FK6'
  8  and CALL_DIRECTION='I'
  9  and SERVICE_CODE='000'
 10  and A_NUMBER='256753557095'
 11  and TIME_TYPE='STA'
 12  AND AGREEMENT_TYPE='I';

CALL_DATE           PAR C SER A_NUMBER     B_NUMBER     TIM A
------------------- --- - --- ------------ ------------ --- -
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I


Notice thats all call_date are 24/04/2008 19:10:27 (Session nls_date_parameter is changed to 'DD/MM/YYYY HH24:MI:SS'). Quesion is why this records were not returned in Q1 , Q2 and Q3 ; and now only in Q4 .

And interestingly ... B_NUMBER length is found to be same !!!

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE, LENGTH(B_NUMBER) LEN
  3   from  RATED_CALLS
  4  where CALL_DATE  between
  5   TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
  6   and  TO_DATE('4/24/2008 7:11:59 PM','MM/DD/YYYY HH:MI:SS PM')
  7  and PARTNER_CODE='FK6'
  8  and CALL_DIRECTION='I'
  9  and SERVICE_CODE='000'
 10  and A_NUMBER='256753557095'
 11  and TIME_TYPE='STA'
 12  AND AGREEMENT_TYPE='I';

CALL_DATE           PAR C SER A_NUMBER     B_NUMBER     TIM A  LEN
------------------- --- - --- ------------ ------------ --- - ----
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I   12
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I   12
24/04/2008 19:10:27 FK6 I 000 256753557095 256714765489 STA I   12


I suspect some thing wrong with the fields B_NUMBER and CALL_DATE . Please get back to me in case any additional information is needed.

Rajuvan

[Updated on: Wed, 04 June 2008 06:01]

Report message to a moderator

Re: How unique index duplicated ? [message #324847 is a reply to message #324841] Wed, 04 June 2008 05:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you do the following please.

a) You missed to include the status column while selecting indexes.
b) Execute the last query along with the dump command.
c) Tkprof of the last executed query. The reason I am asking you to do a tkprof is that it's purely a guess. Is there by any chance do you have context set on that table and it is doing a cartesian product on certain conditions. I don't need to tell you but tkprof will show you the actual query which is getting executed.

Regards

Raj
Re: How unique index duplicated ? [message #324870 is a reply to message #323277] Wed, 04 June 2008 06:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

The index status is Ok

SQL> select  INDEX_NAME,INDEX_TYPE,UNIQUENESS, status
   from  user_INDEXES
   where TABLE_NAME='RATED_CALL'; 
   
INDEX_NAME                     INDEX_TYPE                  UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
RATED_CALL$ROAM_CDRS        NORMAL                      NONUNIQUE VALID
RATED_CALL$UNIQUE_CDR       NORMAL                      UNIQUE    VALID
RATED_CALL$FILE_ID          NORMAL                      NONUNIQUE VALID
RATED_CALL$SUMMARY_SEQ_NO    NORMAL                      NONUNIQUE VALID
RATED_CALL$REPRICE_SEQ_NO   NORMAL                      NONUNIQUE VALID


Also please find the tkprof file . Looks Ok.

Rajuvan.
  • Attachment: perf.prf
    (Size: 9.65KB, Downloaded 74 times)
Re: How unique index duplicated ? [message #324878 is a reply to message #324870] Wed, 04 June 2008 07:13 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
b) Execute the last query along with the dump command.


What I mean is dump(b_number).

Regards

Raj

[Updated on: Wed, 04 June 2008 07:14]

Report message to a moderator

Re: How unique index duplicated ? [message #324950 is a reply to message #323277] Wed, 04 June 2008 10:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Rajaram ,

Sorry for the late reply .

Actually there issome issue with datatype conversion . A number b_numbers are of varchar2 datatype . But when I use those Number without quotes , its returning 3 rows .Same is happening whe i use B=Number like '%xxxx' !!!!

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE from  RATED_CALL
  3  where CALL_DATE  between
  4   TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
  5   and  TO_DATE('4/24/2008 7:10:40 PM','MM/DD/YYYY HH:MI:SS PM')
  6  and PARTNER_CODE='FK6'
  7  and CALL_DIRECTION='I'
  8  and SERVICE_CODE='000'
  9  and A_NUMBER= '256753557095'
 10  and B_NUMBER = '256714765489'
 11  and TIME_TYPE='STA'
 12  AND AGREEMENT_TYPE='I';

CALL_DATE PAR C SER A_NUMBER                  B_NUMBER                  TIM A
--------- --- - --- ------------------------- ------------------------- --- -
24-APR-08 FK6 I 000 256753557095              256714765489              STA I

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE from  RATED_CALL
  3  where CALL_DATE  between
  4   TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
  5   and  TO_DATE('4/24/2008 7:10:40 PM','MM/DD/YYYY HH:MI:SS PM')
  6  and PARTNER_CODE='FK6'
  7  and CALL_DIRECTION='I'
  8  and SERVICE_CODE='000'
  9  and A_NUMBER= '256753557095'
 10  and B_NUMBER like '%256714765489'
 11  and TIME_TYPE='STA'
 12  AND AGREEMENT_TYPE='I';

CALL_DATE PAR C SER A_NUMBER                  B_NUMBER                  TIM A
--------- --- - --- ------------------------- ------------------------- --- -
24-APR-08 FK6 I 000 256753557095              256714765489              STA I
24-APR-08 FK6 I 000 256753557095              256714765489              STA I
24-APR-08 FK6 I 000 256753557095              256714765489              STA I

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE from  RATED_CALL
  3  where CALL_DATE  between
  4   TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
  5   and  TO_DATE('4/24/2008 7:10:40 PM','MM/DD/YYYY HH:MI:SS PM')
  6  and PARTNER_CODE='FK6'
  7  and CALL_DIRECTION='I'
  8  and SERVICE_CODE='000'
  9  and A_NUMBER= '256753557095'
 10  and B_NUMBER =256714765489
 11  and TIME_TYPE='STA'
 12  AND AGREEMENT_TYPE='I';

CALL_DATE PAR C SER A_NUMBER                  B_NUMBER                  TIM A
--------- --- - --- ------------------------- ------------------------- --- -
24-APR-08 FK6 I 000 256753557095              256714765489              STA I
24-APR-08 FK6 I 000 256753557095              256714765489              STA I
24-APR-08 FK6 I 000 256753557095              256714765489              STA I




This is the output and tkprof result you wanted to verify.

SQL> alter session set timed_statistics=true;

Session altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
  2         B_NUMBER ,TIME_TYPE,AGREEMENT_TYPE, dump(B_NUMBER), Length(B_NUMBER) LEN 
  3   from  RATED_CALL
  4   where CALL_DATE  between
  5   TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
  6   and  TO_DATE('4/24/2008 7:10:40 PM','MM/DD/YYYY HH:MI:SS PM')
  7  and PARTNER_CODE='FK6'
  8  and CALL_DIRECTION='I'
  9  and SERVICE_CODE='000'
 10  and A_NUMBER= 256753557095
 11  and B_NUMBER =256714765489
 12  and TIME_TYPE='STA'
 13  AND AGREEMENT_TYPE='I';

CALL_DATE PAR C SER A_NUMBER                  B_NUMBER                  TIM A DUMP(B_NUMBER)                                                   LEN
--------- --- - --- ------------------------- ------------------------- --- - ---------------------------------------------------------------- ----------
24-APR-08 FK6 I 000 256753557095              256714765489              STA I Typ=1 Len=12: 50,53,54,55,49,52,55,54,53,52,56,57                12

24-APR-08 FK6 I 000 256753557095              256714765489              STA I Typ=1 Len=12: 50,53,54,55,49,52,55,54,53,52,56,57		       12

24-APR-08 FK6 I 000 256753557095              256714765489              STA I Typ=1 Len=12: 50,53,54,55,49,52,55,54,53,52,56,57		       12

SQL> alter session set sql_trace=false;

Session altered.



Please find the TKprof result as

Rajuvan.

  • Attachment: tk_prof1.prf
    (Size: 5.53KB, Downloaded 82 times)
Re: How unique index duplicated ? [message #324970 is a reply to message #324950] Wed, 04 June 2008 11:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Can you rename your tkprof1.prf to tkprof1.txt? My virus protection thinks it is an executable file and is rejecting it.
Re: How unique index duplicated ? [message #324974 is a reply to message #323277] Wed, 04 June 2008 12:08 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thank you Barbara for your time .

Pleas find the attchment.

Thumbs Up
Rajuvan
  • Attachment: tk_prof1.txt
    (Size: 5.53KB, Downloaded 117 times)
Re: How unique index duplicated ? [message #324982 is a reply to message #324974] Wed, 04 June 2008 12:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
We need to see the tkprof for the select statement that has the count(*) in it.
Re: How unique index duplicated ? [message #324985 is a reply to message #324974] Wed, 04 June 2008 12:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The tkprof in the text file was kind of an unformatted mess. Here it is with a some partial formatting for anyone else who wants to look at it. Thus far, I don't see anything strange in the tkprof or dump.

select CALL_DATE, PARTNER_CODE, CALL_DIRECTION,SERVICE_CODE,A_NUMBER,
       B_NUMBER, TIME_TYPE, AGREEMENT_TYPE, dump(B_NUMBER), Length(B_NUMBER) LEN 
from   RATED_CALL
where  CALL_DATE  between
 TO_DATE('4/24/2008 7:10:27 PM','MM/DD/YYYY HH:MI:SS PM')
and    TO_DATE('4/24/2008 7:10:40 PM','MM/DD/YYYY HH:MI:SS PM')
and PARTNER_CODE='FK6'
and    CALL_DIRECTION='I'
and SERVICE_CODE='000'
and A_NUMBER= 256753557095
and B_NUMBER =256714765489
and    TIME_TYPE='STA'
AND AGREEMENT_TYPE='I'



call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0       1068          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0       1068          0           3
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 48  

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  PARTITION RANGE SINGLE PARTITION: 56 56 (cr=1068 pr=0 pw=0 time=3785 us)
      3   TABLE ACCESS FULL rated_calls PARTITION: 56 56 (cr=1068 pr=0 pw=0 time=3741 us)

********************************************************************************



Re: How unique index duplicated ? [message #324987 is a reply to message #324974] Wed, 04 June 2008 12:49 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Also, I feel it's worthfile to output the rowid along with your select statement.

Regards

Raj
Re: How unique index duplicated ? [message #324999 is a reply to message #324987] Wed, 04 June 2008 14:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I am inclined to agree with Ross that the most likely culprit is a direct path load that ignores the unique index. The following demonstrates how that can happen using a SQL*Loader direct path load, but the same can happen with an import. However, this does not explain how the index shows valid or the discrepancy between the counts. My best guess is that the index is corrupted, but not marked unusable, so one query may attempt to use the index to retrieve the individual values, while another may not.

SCOTT@orcl_11g> CREATE TABLE test_tab
  2    (col1  NUMBER,
  3  	col2  NUMBER)
  4  /

Table created.

SCOTT@orcl_11g> CREATE UNIQUE INDEX test_idx ON test_tab (col1, col2)
  2  /

Index created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log DIRECT=TRUE

SCOTT@orcl_11g> SELECT col1, col2, COUNT (*)
  2  FROM   test_tab
  3  GROUP  BY col1, col2
  4  HAVING COUNT (*) > 1
  5  /

      COL1       COL2   COUNT(*)
---------- ---------- ----------
         1          2          2

SCOTT@orcl_11g> SELECT col1, col2
  2  FROM   test_tab
  3  WHERE  col1 = 1
  4  AND    col2 = 2
  5  /

      COL1       COL2
---------- ----------
         1          2
         1          2

SCOTT@orcl_11g> 

[Updated on: Wed, 04 June 2008 14:40]

Report message to a moderator

Re: How unique index duplicated ? [message #325019 is a reply to message #323277] Wed, 04 June 2008 15:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thank you for Barbara and Rajaram for spending their time.

By the way , We dropped the idea of importing the Schema , because it was failing to apply unique index. So we truncated the RATED_CALL table and reprocessed the data again. So I am pretty sure that this duplicates are not due to the Export-Import with Direct Option. Morover as far as I know , we are not using such bulk loading for the processing of such data.

Now I am at our home and about to fall asleep Bored . It was an end of another not-so-fruitful day.

I am not having access to the database now. Even the DB is down , because of some server maintenance process . I will try to give you the desired outputs once the DB is up by tomorrow morning.

Good Night for the Lousy heads Smile

Thumbs Up
Rajuvan

[Updated on: Wed, 04 June 2008 15:59]

Report message to a moderator

Re: How unique index duplicated ? [message #326348 is a reply to message #325019] Wed, 11 June 2008 02:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The B_NUMBER and date columns seem to be the most suspect. As suggested, run your SQL with those columns DUMP()ed.

Ross Leishman
Re: How unique index duplicated ? [message #326661 is a reply to message #323277] Thu, 12 June 2008 03:34 Go to previous messageGo to next message
rajavu
Messages: 11
Registered: March 2005
Location: Bangalore
Junior Member
Thank you rleishman for the reply .

Unfortunately the the above said schama and table is no more in database. It is dropped .

And recenty we found the culprit . The culprit is some bulk insertion into the rated_call table . Development people made some change and it is working fine now though my concern still remains why Oracle allows duplication for unique key in case of bulk insertion.

Thanks for all once again

Thumbs Up
Rajuvan
Re: How unique index duplicated ? [message #326684 is a reply to message #326661] Thu, 12 June 2008 04:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why did you swap user-accounts?
Re: How unique index duplicated ? [message #326708 is a reply to message #323277] Thu, 12 June 2008 06:06 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ooops !!!

Actually 'rajavu' was my first username in Orafaq . Later i forgot the password and re-registered as 'rajavu1'.

Whe i tried today , i typed username as rajavu by mistake and posted .ie. password is same for both Laughing Laughing Laughing

Thumbs Up
Rajuvan
Previous Topic: SQL Help
Next Topic: Timestamp value with US Time zone.
Goto Forum:
  


Current Time: Sat Dec 10 22:21:25 CST 2016

Total time taken to generate the page: 0.08169 seconds