Query optimization

From: Morten <usenet_at_kikobu.com>
Date: 20 Oct 2003 10:46:33 -0700
Message-ID: <75db36f2.0310200946.2335900f@posting.google.com>

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 Received on Mon Oct 20 2003 - 12:46:33 CDT