Tricky hql-question

Hello!

This is a similar problem that you guys helped me out with a couple of weeks ago.

I have a task to increase performance of a HQL-question.
I guess we could solve it with SQL if it is easier, but the previous developer has used Hibernate.
The purpose for this HQL is to fetch rows from a table.

The background of the problem is that we have 2 tables like:

Table A contains id:s, names and a flag for deletion.
tbl_a
id,  name, flag
1 a 0
2 b 1
3 c 0
4 d 1
5 e 0
6 f 1
7 g 0
8 h 1

Table B contains references to Table A and a counter.
tbl_b
id, ref_id, counter
1 1 10
2 2 0
3 3 10
4 4 5

We  are now supposed to fetch those id's from table A that should be deleted.
The condition for fetching for deletion is:
flag = 1
and
(
    (id = ref_id and counter = 0)
or
    (id not exist as ref_id in tbl_b)
)




Then the HQL looks like.

from tbl_a
where
tbl_a.flag =:Bool
and
tbl_a.id not in
(select distinct ref_id from tbl_b
where
(
  counter > 0
)
)

The HQL above seems to fulfill the condition.
To me these rows would be fetched  from tbl_a:

2 b 1
6 f 1
8 h 1

But the problem seems to be "not in", which propably performs a full table scan.
And with tables with like 10 000 000 rows, this takes a lot of time.

So in some how I need to figure out a better way to express the HQL, or if needed in SQL.

I would really appreciate some comments of how you would solve this.

Best regards
Fredrik