oracle order by 求帮优化

select A.aId, (select count(1) from B where B.aId = a.aId) c from A
order by c

表A有30W+数据
表B有18W+数据
用上面sql查要10分钟，如果去掉order by则在2秒内，求高手指导怎么优化

有高人指点，这样写能控制在10秒左右，但不是理解
select A.aId, NVL(B.c,0) from A LEFT JOIN (select aId,count(1) c from B group by aId) B ON A.AID=B.AID
order by B.c

在 A.aId 上建索引
必要时在 B.aId 也建索引

c 是计算字段，不宜用于排序
可尝试
select *
from
(select A.aId, (select count(1) from B where B.aId = a.aId) c from A) T
order by c

我没看explain plan但是我猜你的SQL首先不太对(我假设B.aId,A.aId有index）

SELECT aId, 1 FROM
select DISTINCT(A.aId, A.c) from A， B where B.aId = a.aId D
ored by c

具体情况不了解，试试这样看能不能快点！
select a.aId,
sum(case when B.aId is not null then 1 else 0 end) tot
from A left join B
on B.aId = a.aId
group by a.aId;