SQL & PL/SQL

Hi there,

I would like to get some advice from Oracle experts about the best architecture in 11g for a somewhat simple storage situation: I have a table in a datawarehouse with millions of records being added every day. Each record can be associated with one (99.9% of all cases) or, more rarely, several sub-records (up to 3 or 4).

1) The natural relational approach would call for a master-detail relationship, with a key shared between the two tables. Some questions in this case:
- What would the best structure be here? Since its rows are going to be quite small, the child table should probably be an index-organized table. But could a cluster be considered?
- Also what key should be used in the relationship? The master records are only ever going to be accessed using full table scans, so they have no primary key nor indexes. Is it possible to avoid creating a surrogate key only for the sake of joining the master and detail tables, e.g., using a rowid of some sort?

2) Another reasonable approach it seems would be to use a nested table structure to store the detail records.
- Is it an interesting solution? It looks like it avoids the need for a surrogate key, and looks like a "natural" way of doing things. But generally people do not seem very keen on this feature, maybe because of unfamiliarity?
- In this situation, would it still be possible to scan the detailed records using pure SQL, like one would do with a join in case 1?

3) Although I have not studied the pb in detail yet, it seems that the right tools to populate both tables at the same time from external tables would be to use PIPELINE and FORALL PL/SQL constructs, can anyone confirm?

Thanks for your help,
Chris