Oracle Database Discussions

Hi all,

i have two tables A and B in the same schema. Table A contains daily transactions for accounts, say 2 million transactions per day. Table B is a reference data table, which contains informations for Accounts. Table B has a primary key index on ID column (IDXPK), whereas there is a non-unique index on column ACCOUNT_ID of table A (IDX01). Table B column ACCOUNT_ID is not referenced by Table A ID column. Table A contains only 50% accounts of Table B.

I use the following query to get the columns from both tables. The query gives me an INDEX FULL SCAN, In my opinion the table B should not be scanned completely, as it contains only 50% values inclusion from table A.

SELECT /*+ INDEX(A IDX01) INDEX(B IDXPK) */ * FROM A, B
WHERE a.account_id = b.ID

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','OS:061608150145','TYPICAL'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1238459847

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3932K| 330
| 1 | MERGE JOIN | | 3932K| 330
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | A | 3883K| 196
| 3 | INDEX FULL SCAN | IDX01 | 3883K|
|* 4 | SORT JOIN | | 217K| 7434
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| B | 217K| 7434
| 6 | INDEX FULL SCAN | IDXPK | 217K|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."ACCOUNT_ID"="B"."ID")
filter("A"."ACCOUNT_ID"="B"."ID")
19 rows selected

what should i do to avoid INDEX FULL SCAN? or to optimize the above query.

Thank you in advance.