Oracle sql query running for (almost) forever

Question

An application of mine is trying to execute a count(*) query which returns after about 30 minutes. What's strange is that the query is very simple and the tables involved are large, but not gigantic (10,000 and 50,000 records).

The query which takes 30 minutes is:

select count(*) 
from RECORD r inner join GROUP g 
  on g.GROUP_ID = r.GROUP_ID 
where g.BATCH_ID = 1 and g.ENABLED = 'Y'

The database schema is essentially:

create table BATCH (
    BATCH_ID int not null,
    [other columns]...,
    CONSTRAINT PK_BATCH PRIMARY KEY (BATCH_ID)
);

create table GROUP (
    GROUP_ID int not null,
    BATCH_ID int,
    ENABLED char(1) not null,
    [other columns]...,
    CONSTRAINT PK_GROUP PRIMARY KEY (GROUP_ID),
    CONSTRAINT FK_GROUP_BATCH_ID FOREIGN KEY (BATCH_ID)
        REFERENCES BATCH (BATCH_ID),
    CONSTRAINT CHK_GROUP_ENABLED CHECK(ENABLED in ('Y', 'N'))
);

create table RECORD (
    GROUP_ID int not null, 
    RECORD_NUMBER int not null,
    [other columns]...,
    CONSTRAINT PK_RECORD PRIMARY KEY (GROUP_ID, RECORD_NUMBER),
    CONSTRAINT FK_RECORD_GROUP_ID FOREIGN KEY (GROUP_ID)
        REFERENCES GROUP (GROUP_ID)
);

create index IDX_GROUP_BATCH_ID on GROUP(BATCH_ID);

I checked whether there are any blocks in the database and there are none. I also ran the following pieces of the query and all except the last two returned instantly:

select count(*) from RECORD -- 55,501

select count(*) from GROUP -- 11,693

select count(*) 
from RECORD r inner join GROUP g 
  on g.GROUP_ID = r.GROUP_ID
-- 55,501

select count(*) 
from GROUP g 
where g.BATCH_ID = 1 and g.ENABLED = 'Y' 
-- 3,112

select count(*) 
from RECORD r inner join GROUP g 
  on g.GROUP_ID = r.GROUP_ID 
where g.BATCH_ID = 1 
-- 27,742 - took around 5 minutes to run

select count(*) 
from RECORD r inner join GROUP g 
  on g.GROUP_ID = r.GROUP_ID 
where g.ENABLED = 'Y' 
-- 51,749 - took around 5 minutes to run

Can someone explain what's going on? How can I improve the query's performance? Thanks.

Answer 1

SELECT COUNT(*)  
FROM   RECORD R
LEFT OUTER JOIN GROUP G ON G.GROUP_ID = R.GROUP_ID  
       AND G.BATCH_ID = 1
       AND G.ENABLED = 'Y'

Try that and let me know how it turns out. Not saying this IS the answer, but since I don't have access to a DB right now, I can't test it. Hope it works for ya.

Answer 2

An explain plan would be a good place to start.

See here:

http://stackoverflow.com/questions/3425730/strange-speed-changes-with-sql-query/3425914#3425914

for how to use the explain plan syntax (and query to see the result.)

If that doesn't show anything suspicious, you'll probably want to look at a trace.

Answer 3

A coworker figured out the issue. It's because the table statistics weren't being updated and the last time the table was analyzed was a couple of months ago (when the table was essentially empty). I ran analyze table RECORD compute statistics and now the query is returning in less than a second.

I'll have to talk to the DBA about why the table statistics weren't being updated.