Query Performance Problem

From: <yitbsal_at_statcan.ca>
Date: Fri, 17 Nov 2000 19:49:39 GMT
Message-ID: <8v424g$2f8$1@nnrp1.deja.com>

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. Received on Fri Nov 17 2000 - 13:49:39 CST