Oracle sql查询将(几乎)永远运行

我的一个应用程序正在尝试执行一个count(*)查询,大约30分钟后返回。奇怪的是,查询非常简单,所涉及的表很大,但不是很大(10000和50000条记录)。

需要30分钟的查询是:

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'

数据库架构基本上是:

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);

我检查了数据库中是否有块,但没有。我还运行了以下几段查询,除最后两段外,其余都立即返回:

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

有人能解释一下发生了什么事吗?如何提高查询的性能?谢谢。