count() query taking more than 20 seconds

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

take开发者_如何学Pythons 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?

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.

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.