Re: slow SELECT

Ugly table names.(precede them with tbl and suffix them with table on a table, tripply redundant)
Did you analyze the table and indexes? What is the explain plan and what is the tkprof?

rewriting the sql to look more like what I am used to analyzing:  SELECT ...
 FROM tblDHSampleDuplicates sd ,

              tblAssay ar,
              tblAssay ao

    ar.element = ao.element and
    sd.dataset =  ao.dataset and
    sd.AssociatedSampleID = ao.SampleID and
    sd.DataSet = ar.DataSet and
    sd.SampleID =ar.SampleID and
    ar.element='Ni';

I would expect the following indexes:

unique index tblAssay (element,dataset) compress 1 tblAssay index(dataset)
tblDHSampleDuplicates(dataset,sampleid) compress 2 tblDHSampleDuplicates(sampleid) compress 1

Jim

INNER JOIN (tblAssay OrigTable INNER JOIN
> tblAssay RepeatTable
> ON OrigTable.Element = RepeatTable.Element) ON
> (tblDHSampleDuplicates.DataSet = OrigTable.DataSet)
> AND (tblDHSampleDuplicates.AssociatedSampleID = OrigTable.SampleID)
 

> AND (tblDHSampleDuplicates.DataSet = RepeatTable.DataSet)
> AND (tblDHSampleDuplicates.SampleID = RepeatTable.SampleID)
> WHERE (RepeatTable.Element='Ni')

"Paul Fell" <penzina_at_iinet.net.au> wrote in message news:bea9531b.0306101933.c79efc3_at_posting.google.com...
> I'm running the SQL below on Oracle 9i. Table info :
>
> tblDHSampleDuplicates : PK DataSet (varchar2(60),
> SampleID(varchar2(60))
> non-unique Indexe on SampleID
>
> tblAssay : PK
> dataset NVARCHAR2(60)
> NOT NULL
> , sampleid NVARCHAR2(60)
> NOT NULL
> , genericmethod NVARCHAR2(20)
> NOT NULL
> , element NVARCHAR2(40)
> NOT NULL
> , repeat NVARCHAR2(20)
> NOT NULL
> , preferred NUMBER (5)
> NOT NULL
> , origmethod NVARCHAR2(40)
> NOT NULL
> , labcode NVARCHAR2(40)
> NOT NULL
> , batch_no NVARCHAR2(50)
> NOT NULL
>
> non-unique indexes on SampleID, element
>
> SELECT tblDHSampleDuplicates.DataSet,
> tblDHSampleDuplicates.BatchNo,tblDHSampleDuplicates.SampleID,
> tblDHSampleDuplicates.DuplicateCategory,
> tblDHSampleDuplicates.AssociatedSampleID,
> RepeatTable.sysResult RepeatResult,OrigTable.sysResult OriginalResult,
> OrigTable.GenericMethod,
> OrigTable.Element,
 OrigTable.UnitCode,OrigTable.LimitLower,OrigTable.LabCode,
> RepeatTable.Repeat RepeatRepeatType,OrigTable.Repeat OrigRepeatType
> FROM tblDHSampleDuplicates INNER JOIN (tblAssay OrigTable INNER JOIN
> tblAssay RepeatTable
> ON OrigTable.Element = RepeatTable.Element) ON
> (tblDHSampleDuplicates.DataSet = OrigTable.DataSet)
> AND (tblDHSampleDuplicates.AssociatedSampleID = OrigTable.SampleID)
> AND (tblDHSampleDuplicates.DataSet = RepeatTable.DataSet)
> AND (tblDHSampleDuplicates.SampleID = RepeatTable.SampleID)
> WHERE (RepeatTable.Element='Ni')
>
> This query, when the WHERE clause is omitted, returns resultset in
> less than 4 seconds. When the WHERE is included it takes over an hour.
> The same query, with the WHERE clause included, runs fine on SQL
> Server 2000 and returns resultsets in less than 3 seconds. Table
> structures/PK's/Indexes are exactly the same on both platforms. Any
> ideas how I can tune SQL and/or Tables?
>
> Paul
Received on Wed Jun 11 2003 - 06:32:07 CEST