RE: index doesn't contain duplicates
- From: TESTAJ3@xxxxxxxxxxxxxx
- To: Richard.Goulet@xxxxxxxxxxx
- Date: Tue, 9 Jun 2009 14:31:21 -0400
you could use the dump function on the values to see if they are truly the
same.
joe
_______________________________________
Joe Testa, Oracle Certified Professional
(Work) 614-677-1668
(Cell) 614-312-6715
Interested in helping out your marriage?
Ask me about "Weekend to Remember"
Dec 11-13, 2009 here in Columbus.
From:
"Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
To:
<Josh.Collier@xxxxxxxxxxxx>, <mark.powell@xxxxxxx>,
<oracle-l@xxxxxxxxxxxxx>
Date:
06/09/2009 02:23 PM
Subject:
RE: index doesn't contain duplicates
Sent by:
oracle-l-bounce@xxxxxxxxxxxxx
Josh,
One question & this one has taken me to the cleaners more times that
I'd like to admit. The two rows that "appear" to be the same, are they of
the same length? Here's an example of what I mean:
Row1: 'DATAVALUE'
Row2: 'DATAVALUE '
There is a very slight difference, in this case a trailing space that
really does make the two rows different. Other characters that can cause
similar problems is anything in the extended ascii character set which
will show up like spaces.
Dick Goulet
Senior Oracle DBA
PAREXEL International
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Josh Collier
Sent: Tuesday, June 09, 2009 2:12 PM
To: mark.powell@xxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: index doesn't contain duplicates
Version 9208
The way the table was created was this..
Table was created with a CTAS
Non-unique index was built
Primary key was built on a column, specifying index created in step2.
Enable novalidate. The data in question was inserted after the primary was
in place.
The plan that produces the incorrect results is a fast full scan on the
non-unique index
The plan that produces the correct result is a full tablescan on the
table.
We
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Powell, Mark D
Sent: Tuesday, June 09, 2009 10:22 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: index doesn't contain duplicates
Yes, the full Oracle version would be nice plus the select with plan the
produces the proper results and one that does not.
Things you can try:
Run analyze index validate
Run analyze table validate cascade (depending on results from above)
Drop and re-create index
-- Mark D Powell --
Phone (313) 592-5148
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Josh Collier
Sent: Tuesday, June 09, 2009 12:11 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: index doesn't contain duplicates
All,
I have an table that has duplicate values for a column. I have a
non-unique index on this column. When I run a query that uses a fast full
scan to retrieve the rows, it only retrieves one of the two rows. When I
force a full tablescan both of the rows come back. I have used all my sql
tricks to try my best to determine if the value has trailining or leading
non-printable characters and it doesn’t appear so. I have also moved the
values into a varchar 11 field in another table, the value is 11 chars
long. Both of the duplicate rows where accepted. Does anyone know of a bug
or a reason why both of the rows wouldn’t appear in the index?
Thanks for your time,
Josh C.
Other related posts:
