Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> extremely slow query on particular tables
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.
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