Re: SQL Query - How to and what am I doing wrong

Atkins, Jenifer wrote:

>Hello - to be open and honest I am writing up a second year University
>project. The sample code I have included is to create dummy data and to
>query this data stored in a database table and what I will talk about for
>the remainder of this post.
>
>Here is the table and its content.
>
>
>CREATE TABLE tblResult43(id NUMBER,value NUMBER);
>
>CREATE INDEX idxResult43
> ON tblResult43(id);
>
>CREATE UNIQUE INDEX idxResult43b
> ON tblResult43(id,value);
>
>INSERT INTO tblResult43(id,value) VALUES (1,15);
>INSERT INTO tblResult43(id,value) VALUES (1,231);
>INSERT INTO tblResult43(id,value) VALUES (1,100);
>INSERT INTO tblResult43(id,value) VALUES (1,67);
>INSERT INTO tblResult43(id,value) VALUES (2,2);
>INSERT INTO tblResult43(id,value) VALUES (2,16);
>INSERT INTO tblResult43(id,value) VALUES (2,31);
>INSERT INTO tblResult43(id,value) VALUES (2,231);
>INSERT INTO tblResult43(id,value) VALUES (3,5);
>INSERT INTO tblResult43(id,value) VALUES (4,2);
>INSERT INTO tblResult43(id,value) VALUES (4,16);
>INSERT INTO tblResult43(id,value) VALUES (5,231);
>INSERT INTO tblResult43(id,value) VALUES (5,15);
>INSERT INTO tblResult43(id,value) VALUES (5,61);
>INSERT INTO tblResult43(id,value) VALUES (5,19);
>INSERT INTO tblResult43(id,value) VALUES (6,15);
>INSERT INTO tblResult43(id,value) VALUES (6,231);
>INSERT INTO tblResult43(id,value) VALUES (7,54);
>INSERT INTO tblResult43(id,value) VALUES (7,67);
>INSERT INTO tblResult43(id,value) VALUES (11,15);
>INSERT INTO tblResult43(id,value) VALUES (11,231);
>INSERT INTO tblResult43(id,value) VALUES (11,67);
>INSERT INTO tblResult43(id,value) VALUES (11,100);
>INSERT INTO tblResult43(id,value) VALUES (11,234);
>INSERT INTO tblResult43(id,value) VALUES (12,15);
>INSERT INTO tblResult43(id,value) VALUES (12,231);
>INSERT INTO tblResult43(id,value) VALUES (12,233);
>INSERT INTO tblResult43(id,value) VALUES (12,67);
>INSERT INTO tblResult43(id,value) VALUES (12,100);
>INSERT INTO tblResult43(id,value) VALUES (12,200);
>
>
>What I want to do is to retrieve the ID's for entries in tblResult43 that
>have values 15, 231, 100, and 67 but also have no other values (by this I
>mean that in the sample data id=1 is valid because it has 15, 231, 100, and
>67; id=12 is not valid because even though it contains 15, 231, 100, 67 it
>also contains 200 and 233; and id=11 is not valid because if contains 15,
>231, 100, 67 BUT AN ADDITIONAL 234; and all the others are not valid because
>they do not contain all the numbers 15, 231, 100, and 67).
>
>The query I have come up with so far is:
>
> SELECT t1.id
> FROM tblResult43 t1, tblResult43 t2,
> tblResult43 t3, tblResult43 t4
> WHERE t1.id=t2.id
> AND t1.id=t3.id
> AND t1.id=t4.id
> AND t1.value=15
> AND t2.value=231
> AND t3.value=100
> AND t4.value=67
> AND NOT EXISTS (SELECT id
> FROM tblResult43
> WHERE t1.id=id
> AND ROWID NOT IN (t1.ROWID, t2.ROWID, t3.ROWID,
>t4.ROWID)
> AND value NOT IN (15,231,100,67)
> );
>
>This query works really well and gives me the answer I am wanting (ie id=1)
>confirming I am approaching the logic well. Unfortunately it runs really
>slow (although the sample table data I have included above doesn't contain
>many entries, the sample data provided by the tutor contains about 10,000
>rows and my query is taking over an hour to execute on my home computer).
>
>I have obviously approached the problem incorrectly because of the time my
>query is taking to execute. But I cannot see an alternative. Could someone
>not give me the answer but point me in the right direction on how I should
>be constructing my query to improve the speed a bit.
>
>Thank you for thinking about my question.
>Jenny
>Leeds, UK
>
>(ps. The University is using Oracle 8i on a Windows NT network, at home I am
>using Oracle 8i personal on a Windows Xp computer).
>
>

I can think of some approaches to this that might, and I emphasize 'might', be more efficient. But before
treading down that road lets start with the basics because this Explain Plans pretty well.

1. Which version of Oracle?
2. Which optimizer mode?
3. If 8i or 9i and CBO did you collect statistics with DBMS_STATS?
4. Have you run an Explain Plan to see if your indexes are being used?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)