Re: Slow Query

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<ue0epgcdoskj16_at_corp.supernews.com>...
> "Salaam Yitbarek" <yitbsal_at_yahoo.com> wrote in message
> news:77439c33.0205131232.5d49df77_at_posting.google.com...
> > Oracle 8.1.7 on Solaris 5.7.
> >
> > Query 1:
> >
> > SELECT /*+ index (matrix_vector matrix_vector_i1) use_nl */
> > count(distinct matrix_id)
> > FROM matrix_vector
> > WHERE vector_id IN
> > (SELECT /*+ index (ciiw_array_vector pk_ciiw_array_vector) */
> > vector_id
> > FROM ciiw_array_vector
> > WHERE array_id = 3040015)
> > AND opcode NOT IN (2, 3)
> >
> > Explain plan for Query 1:
> >
> > SELECT STATEMENT Hint=CHOOSE 1 49433
> > SORT GROUP BY 1 21
> > HASH JOIN 30 K 619 K 49433
> > TABLE ACCESS BY INDEX ROWID CIIW_ARRAY_VECTOR 30 K 294 K 6582
> > INDEX RANGE SCAN PK_CIIW_ARRAY_VECTOR 30 K 106
> > TABLE ACCESS BY INDEX ROWID MATRIX_VECTOR 228 K 2 M 42842
> > INDEX FULL SCAN MATRIX_VECTOR_I1 228 K 2336
> >
> > Query 2:
> >
> > SELECT /*+ index (matrix_vector matrix_vector_i1) */ count(distinct
> > matrix_id)
> > FROM matrix_vector
> > WHERE vector_id IN
> > (SELECT /*+ index (cii_array_vector pk_cii_array_vector) */ vector_id
> > FROM cii_array_vector
> > WHERE array_id = 3040015)
> > AND opcode NOT IN (2, 3)
> >
> > Explain plan for Query 2:
> >
> > SELECT STATEMENT Hint=CHOOSE 1 22527
> > SORT GROUP BY 1 21
> > NESTED LOOPS 7 K 147 K 22527
> > TABLE ACCESS BY INDEX ROWID CII_ARRAY_VECTOR 7 K 70 K 1008
> > INDEX RANGE SCAN PK_CII_ARRAY_VECTOR 7 K 27
> > TABLE ACCESS BY INDEX ROWID MATRIX_VECTOR 228 K 2 M 3
> > INDEX RANGE SCAN MATRIX_VECTOR_I1 228 K 2
> >
> >
> > - Query 1 takes about 1.5 minutes, Query 2 runs instantly.
> > - Table cii_array_vector has about 11 million records, and table
> > ciiw_array_vector has about 1.1 million records.
> > - The subqueries of the two queries return the same result set.
> > - Adding a use_nl hint results in full table scan of the matrix_vector
> > table, and an even slower query.
> >
> > I suppose the explain plans are different because of the table sizes.
> > But the smaller table should be faster, no?! BTW, the DBA says he's
> > rebuilt the indexes, so I don't think that's the problem.
> >
> > Please help!
> >
> > Thanks,
> > Salaam
>
> Which query refers to
> ciiw_array_vector
> Are we getting the whole picture?
> Probably not!
>
> The most limiting table should be driving.
> This is not necessarily the smallest table.
>
> Regards

Query 1 refers to ciiw_array_vector. Query 2 uses cii_array_vector. The two tables are identical in structure and much of the data in ciiw_array_vector is the same as that in cii_array_vector. In this case, for example, for array_id 3040015, the respective subqueries of Query 1 and Query 2:

> > (SELECT /*+ index (ciiw_array_vector pk_ciiw_array_vector) */
> > vector_id
> > FROM ciiw_array_vector
> > WHERE array_id = 3040015)

and

> > (SELECT /*+ index (ciiw_array_vector pk_ciiw_array_vector) */
> > vector_id
> > FROM cii_array_vector
> > WHERE array_id = 3040015)

return the same result set.

The most limiting table in Query 1 is ciiw_array_vector, and in Query 2 is cii_array_vector, with the above subqueries returning 3232 records.

Sorry, the use_nl in Query 1 was a typo--use_nl (tablename) is indeed ignored when used properly in Query 1.

I've tried:

Query 1a:

select count(distinct matrix_id)
from matrix_vector a, ciiw_array_vector b where b.array_id = 3050015
and a.vector_id = b.vector_id
and a.opcode not in (2,3)

with various hints and with the order of the tables switched, and it always wants to do a full table scan on both matrix_vector and ciiw_array_vector. Ironically, Query 2a:

select count(distinct matrix_id)
from matrix_vector a, cii_array_vector b where b.array_id = 3050015
and a.vector_id = b.vector_id
and a.opcode not in (2,3)

even without hints will use the index on cii_array_vector (array_id).

Salaam

Salaam Received on Tue May 14 2002 - 07:20:29 CDT