Hi,
I have a table, mytable (id number, refid varchar2(64), clob text)
where refid (a project identifier, identifies N records in mytable)
is FK to another table and id is PK.
I wish to extract a usable list of "distinct" text's in the table,
and for that I use the following:
SELECT t1.id, t2.id, t1.text
FROM mytable t1, mytable t2
WHERE dbms_lob.compare(t1.text, t2.text) = 0
AND t1.id = (SELECT MIN(t3.id)
FROM mytable t3
WHERE dbms_lob.compare(t1.text, t3.text) = 0
AND t3.refid = t1.refid)
AND t1.refid = '15B500BE43C64CB5BF9E7ACF69400AF3'
AND t2.refid = t1.refid
ORDER BY t1.id
This gives results like:
1, 1, 'hep'
1, 5, 'hep'
1, 8, 'hep'
2, 2, 'boo'
....
So I can use the first column to control the "distinct" aspect,
and the second column to see which records have the same text
value.
I've experienced that this query to be slow even though there's
an index on refid. Is it at all possible to rewrite the query for
better performance? My concern is the dbms_lob.compare.
Br,
Morten