Re: Query Performance Problem

You need to give us more information about the situation. Table DATAPOINT is a partitioned table, we need to know the partitioning column(s) (it looks as if vector_id is one of them), and the definition of the primary key which is being used in the plan, and how many partitions there are in the table.

The most significant difference in the approaches you have quoted is that if vector_id is the partition key, then the in-list allows Oracle to eliminate partitions at parse time - the other examples do NOT allow Oracle to do any partition
elimination at all.

Have a look at the utlplnp.sql script in $ORACLE_HOME/rdbms/admin, which
reports extra information about the partition ranges (or the sample on my web-site, under miscellaneous -> explain 8). This may also help you understand why one query runs
so much faster than the rest.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison Wesley Longman
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
Order from Amazon via: http://www.jlcomp.demon.co.uk

yitbsal_at_statcan.ca wrote in message <8v424g$2f8$1_at_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.