count() query taking more than 20 seconds

Question

Table_a = 7022536 rows

Table_b (GTT) = 5601 rows

Query:

  SELECT COUNT (a.ssn_head)
    FROM table_a a, table_b b
   WHERE b.hoh = a.head AND a.flag = 'Y';

takes 20+ seconds to bring 17214 records.

Explain plan is:

Plan hash value: 1901401324
--------------------------------------------------------------------------------
| Id  | Operation           | Name                           | Rows  | Bytes | C
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                                |     1 |    25 | 1
|   1 |  SORT AGGREGATE     |                                |     1 |    25 |
|*  2 |   HASH JOIN         |                                |   114K|  2801K| 1
|   3 |    TABLE ACCESS FULL| table_b                        | 49188 |   528K|
|   4 |    REMOTE           | table_a                        |  7022K|    93M| 1
--------------------------------------------------------------------------------

table_b (GTT) has no indices on it...I think since the query is going through all of table_b it will always do a full table scan..right?

table_a has index on head

What other way is there to make this query run faster?

Answer 1

Make a materialized view of table_a on the local server and operate off of that.

It might also help (mildly) to put an index on a.flag, but this will be minor compared to operating locally.

Answer 2

IS hoh in table_b unique ? If so, then

SELECT COUNT (a.ssn_head)
FROM table_a a, table_b b
WHERE b.hoh = a.head AND a.flag = 'Y';

is logically equivalent to

SELECT COUNT (a.ssn_head)
FROM table_a a
WHERE a.flag = 'Y'
and a.head in (select hoh FROM table_b);

Given that the larger data volume is on the remote server, I'd suggest pushing the query over there with the DRIVING_SITE hint.

SELECT /*+DRIVING_SITE (r) */ COUNT (r.col_a)
FROM owner.table@other r
WHERE r.col_b in (select l.col_c FROM local l);

That should work with a synonym instead of table@dblink. But it probably won't work with a view.