Best architectural approach for nightly ETL to data warehouse?
Tables A, B, C, D, E have +/- 50 columns each.
Table A always has several columns extracted for all rows.
Query on table A does a LEFT OUTER JOIN to tables B, C, D, E, one or two columns needed from B, C, D, E.
Typically tables B, C, D, E have anywhere from 10% to 60% of row matches with table A.
Typical execution plan uses all HASH JOIN RIGHT OUTERs and TABLE ACCESS FULL. I have no issues with full table scans since it probably is less costly than indexes,
Without changes to source tables (can't reduce the number of columns), what are possible approaches to reduce I/O and elapsed time? One or more of:
- Bitmap join indexes?
- Materialized views?
- Forcing nested loops?
- In-Memory?
Thanks.