Considerations in adding many tables to a query
My experience is more on OLTP before and this is the first time that I'll be working on a datawarehouse type of system. This is not really a datawarehouse database, but datawarehouse-like in a sense that it has many records. The database is normalized.
In my application, I need to work with 10 tables. The accounts table can have millions of records. I need to join it to the other 9 tables to get the details of the accounts.
From what I've learned, it is better to do all processes in one SQL statement, if possible, than use PL/SQL. I'm concerned with the memory if I join a lot of tables. I need it to run as fast as possible but it should not cause the database to crash. I'm new to this number of records so I don't know what are the limits to consider. So this is the logic as it is now: