Slow Query

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 Received on Mon May 13 2002 - 15:32:13 CDT