SQL & PL/SQL

Hi,

I've recently posted a question about this but right now, I'm to understand the concept. I'm going to give a bit of background in order to make it more understandable.

We have an application in which an object (A) contains a list of objects (B). Each of these objects in turn may contain objects (C). Each of these is a different type of object

Currently, we have designed a very simplistic pattern that follows:
- Load all Objects A according to user criteria
- Build a list of comma-separated IDs of Object A and retrieve Objects B using SELECT * FROM B WHERE ID IN (1, 2, 3, etc...)
- Use the B objects returned to build a comma-separated list of IDs of Object B and retrieve Objects C using this list as: SELECT * FROM C WHERE ID IN (4, 5, 6, etc...)

Now, this works, but only in very simple conditions.

Obviously my main concern is that it just won't work if you return more than 1000 Objects A or B at any point in time. The main problem I am facing now is regarding the way to make this work better, in terms of coding pattern and SQL optimization. IN is not that great with large sets as I understand it since it will use conditions such as 1 OR 2 OR 3 etc...

I've checked out Tom Kyte's website and the table type solution (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061). It works, but my string parameter passed in could be more than 4,000 bytes for large reports... Soooo, I kept on reading and saw the BULK INSERT idea.

My question is the following: In an application where many users may run reports at the same time, is it even valid to consider doing this?

- LOAD --> BULK INSERT --> LOAD USING BULK INSERTED VALUES --> CLEAN UP CUSTOM TABLE

Hereby preventing other users from running their reports until this one is finished (unless I have one custom table per type of object)?

Or is the simple: LOAD OBJECTS A --> LOAD OBJECTS B USING SUBQUERY TO LOAD OBJECTS A better from the beginning? My main thing against it is that we run a query again after we just ran it so we already have the data we need but we have no choice but to re-write it.

I don't have anything against the BULK INSERT method etc... but for index usage and maintenance, the other solution just seems more complex for a performance that just would not be that great.

What do you think about it? And if you have the same problem as I do right now, how do you go around it?

Greg