Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow Query

Re: Slow Query

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 14 May 2002 14:24:23 -0700
Message-ID: <bdf69bdf.0205141324.2999711@posting.google.com>


yitbsal_at_yahoo.com (Salaam Yitbarek) 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?!

With table size smaller 10 times hash join becomes more attractive, while the cost of nested loop with index join practically doesn't change. The breakup between HJ and NL depends on many factors and I saw cases where there was large discrepancy between buffer gets and execution time because index and table data were clustered and cached so that NL was much quicker even though buffer gets were aproximately the same.

Could you also

set autotrace on

to see actual buffer gets and disk reads? Received on Tue May 14 2002 - 16:24:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US