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 -> extremely slow query on particular tables

extremely slow query on particular tables

From: Kenny Yu <kyu_at_biodiscovery.com>
Date: Mon, 25 Nov 2002 16:37:14 -0800
Message-ID: <uu5g72fbiqpu69@corp.supernews.com>


extremely slow query on particular tables

It first appears that a select query does not use the unique index, with the leading index column in the where-clause with a specific value. It turns out to be much worse than that.

The query is like the following, which normally takes 2 seconds. The gd_entity_name table has only 1000s rows.



set timing on
declare
ee varchar2(30);
begin
for v_counter in 1..10000 loop
SELECT n.entity_id
into ee
FROM gd_entity_name n
WHERE n.SCOPE = 'DEMO' AND n.NAME = '187577'; end loop;
end;
/

This script can take an hour to finish. A similar query on a similar table in the same schema takes <2 seconds.

I then perform 'create tmp_table as select * from gd_entity_name' and run the above script on the newly created un-indexed tmp_table; it finishes in 30 seconds.
I then create an index, the same as on gd_entity_name, and the script finishes in 2 seconds.
I then drop the table gd_entity_name and rename tmp_table to gd_entity_name. Problem solved!
But WHAT CAUSES THE PROBLEM??? What is it that is so much slower than full table scan? And how to prevent it?

The same problem occurred on our customers' site. dbms_stats doesn't help. Dropping the index and recreating it has no effect. some other tables are starting to show similar problems. The database installation used default settings. The table ddl is as follows:

CREATE TABLE GD_ENTITY_NAME (
NAME VARCHAR2 (100) NOT NULL,
ENTITY_TYPE VARCHAR2 (30) NOT NULL,
SCOPE VARCHAR2 (30),
ENTITY_ID NUMBER (20),
CONSTRAINT GD_ENT_NAME_NAME_PK
UNIQUE (NAME, ENTITY_TYPE, SCOPE) ) ; I appreciate any input.

Kenny Received on Mon Nov 25 2002 - 18:37:14 CST

Original text of this message

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