Re: Query Performance Problem

have you tried writing the query this way

SELECT /*+ ordered use_nl(a,b) */ COUNT(vector_id)   FROM TMP_VECTORIDLIST A,
       DATAPOINT B
 WHERE A.vector_id = B.vector_id

Are the tables and indexes analyzed?

Hope that helps
In article <8v424g$2f8$1_at_nnrp1.deja.com>,   yitbsal_at_statcan.ca wrote:
> Summary:
>
> The query:
>
> SELECT COUNT(A.VECTOR_ID)
> FROM DATAPOINT A
> WHERE EXISTS (
> SELECT 1
> FROM TMP_VECTORIDLIST B
> WHERE A.VECTOR_ID = B.VECTOR_ID)
> AND A.VERSION = 0;
>
> is extremely slow(4 minutes), whereas the query:
>
> SELECT COUNT(VECTOR_ID)
> FROM DATAPOINT A
> WHERE VECTOR_ID IN (x1, x2, ..., x262)
> AND A.VERSION = 0;
>
> where (x1, x2, ..., x262) is a list of all 262 VECTOR_ID's from the
> TMP_VECTORIDLIST
>
> is very fast (instantaneous).
>
> Details:
>
> Database is Oracle 8.6.2 on Sun 450 with single CPU and loads of
 memory!
>
> The tables concerned are:
>
> DATAPOINT(VECTOR_ID NUMBER(12), REFDATE DATE, VERSION)
> primary key (VECTOR_ID,REFDATE,VERSION)
> partitioned by VECTOR_ID
> 100,000,000 million rows
>
> TMP_VECTORIDLIST(VECTOR_ID NUMBER(12))
> primary key (VECTOR_ID)
> 262 rows
>
> The following queries are extremely slow (4 minutes):
>
> 1.
> SELECT COUNT(VECTOR_ID)
> FROM DATAPOINT
> WHERE VECTOR_ID IN (
> SELECT VECTOR_ID
> FROM TMP_VECTORIDLIST)
> AND VERSION = 0;
>
> explain plan:
> SELECT STATEMENT Optimizer=CHOOSE (Cost=17207 Card=1 Bytes=20)
> SORT (AGGREGATE)
> NESTED LOOPS (Cost=17207 Card=20546244 Bytes=410924880)
> TABLE ACCESS (FULL) OF TMP_VECTORIDLIST (Cost=1 Card=82
> Bytes=1066)
> PARTITION RANGE (ITERATOR)
> INDEX (FAST FULL SCAN) OF PK_DATAPOINT (UNIQUE) (Cost=20353
> Card=25056395 Bytes=175394765)
>
> 2.
> SELECT COUNT(A.VECTOR_ID)
> FROM DATAPOINT A
> WHERE EXISTS (
> SELECT 1
> FROM TMP_VECTORIDLIST B
> WHERE A.VECTOR_ID = B.VECTOR_ID)
> AND A.VERSION = 0;
>
> explain plan:
> SELECT STATEMENT Optimizer=CHOOSE (Cost=20353 Card=1 Bytes=7)
> SORT (AGGREGATE)
> FILTER
> PARTITION RANGE (ALL)
> INDEX (FAST FULL SCAN) OF PK_DATAPOINT (UNIQUE) (Cost=20353
> Card=1252820 Bytes=8769740)
> INDEX (UNIQUE SCAN) OF PK_TMP_VECTORIDLIST (UNIQUE) (Cost=1
> Card=1 Bytes=13)
>
> But, the following query is very fast (instantaneous):
>
> SELECT COUNT(VECTOR_ID)
> FROM DATAPOINT A
> WHERE VECTOR_ID IN (x1, x2, ..., x262)
> AND A.VERSION = 0;
>
> where (x1, x2, ..., x262) is a list of 262 VECTOR_ID's from the
> TMP_VECTORIDLIST table.
>
> explain plan:
> SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=7)
> SORT (AGGREGATE)
> INLIST ITERATOR
> PARTITION RANGE (ITERATOR)
> INDEX (RANGE SCAN) OF PK_DATAPOINT (UNIQUE) (Cost=3 Card=50
> Bytes=350)
>
> Please help?!
>
> Salaam Yitbarek
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 17 2000 - 15:23:33 CST