removing redundant "distinct" might help in this query too ... Raj -------------------------------------------------------------------------------- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. select standard_disclaimer from company_requirements; QOTD: Any clod can have facts, having an opinion is an art ! -----Original Message----- Guang Mei wrote: > I understand that the cost value does not mean anything. But from my > experience, when I see a query's cost from explain plan reaches six > digits, usually something is not right. > > Anyway the actual query has much more stuff in it. The sql I showed in my > orginal post was only part of it and that was the part that slowed the thing > down. > I can not run the sql on 9i now since they are other stuff running. But > the orgianl complex query took 20 minutes to finish on 8i, and the > exact query produced this in 9i's alert log file: > > ORA-01555 caused by SQL statement below (Query Duration=199335 sec, SCN: > 0x0000.01db218e): > Sun Feb 29 23:20:46 2004 > select distinct accession, id > from ( > select distinct to_char(PID) accession, identifier.ID > from mt.genbankinfo, mt.seqtable, mt.identifier > where seqtable.ID = genbankinfo.SeqtabID > and seqtable.GeneID = identifier.GeneID > and PID is not null > and identifier.type != 'A' > and identifier.speciesid in > (24,31,2,19,18,17,23,21,27,32,20,34,30,22,25,26,28,29) > and nvl(seqtable.valid, 'Y') != 'N' > union all > select distinct upper(strings.strtok(protein_id, '.')) accession, > identifier.id > from mt.genbankinfo, mt.seqtable, mt.identifier > where seqtable.ID = genbankinfo.SeqtabID > and seqtable.GeneID = identifier.GeneID > and protein_id is not null > and identifier.t > ..... ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------