Re: Query Performance Problem

Yes, I tried the query below, and there's no difference.

The tables and indexes are analyzed.

Salaam

In article <8v47kd$7b8$1_at_nnrp1.deja.com>,   jdarrah_co_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 20 2000 - 10:37:35 CST