Re: Slow Query

"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

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address