Re: Can you use a cursor variable in a WHERE..IN Statement??

DMichaelFSU_at_hotmail.com (Michael Green) writes:

> Hi. I am trying to see if it is possible to use a cursor as the list
> of a WHERE..IN statement.
>
> I have 20+ tables that hold hourly data for 7500+ different objects.
> I need to come up with a daily summary of the data in the 20+ table
> for each object. The 20+ tables only contain data and a key. A
> table, ENTRY_ID, relates the key to the date, time, object's name,
> etc. I am currently trying to use a cursor to query the ENTRY_ID
> table to get a list of keys for a particular object on a particular
> date and use that result set to summarize data in the rest of the
> tables. This way I only have to query ENTRY_ID once, not 20+ times
> for each of teh 7500 objects.
>
> Eg: Replace this query:
> SELECT sum(t1.col1), sum(t1.col2), ...
> FROM table1 t1
> WHERE t1.id IN (SELECT e1.id
> FROM entryid e1
> WHERE e1.aDate = <date>
> AND e1.obj = '<str>');
>
> with something like this:
> SELECT sum(t1.col1), sum(t1.col2), ...
> FROM table1 t1
> WHERE t1.id IN (id_cur)
>

Warning!. I've only been doing oracle and plsql for about 4 months, but I'll have a go and put in my 2 cents worth.

I doubt you can do what your trying for a number of reasons and even if you could, I'm not sure it would gain you any real advantage. Just in case I totally misunderstand your objectives, I'll try and re-state them so that we are clear and it is either obvious I have totally missed your point or that I may be contributing something!

I gather your major concern is the performance hit of doing the sub-select for every row of the main select and having to do it for every one of the 20+ summary tables and you hope to reduce this by using a cursor in the sub-select. The main problem I see here is how will you iterate through your cursor to get each row? I don't see any performance gain here either as you are still going to be performing the same query and you are still going to need to perform it for each row in the outer select. Because you will have to iterate through the rows returned for each cursor you will need to open and close the cursor to re-set it each time and this means you will be re-doing the select in exactly the same way as the non-cursor based solution.

Suggested Alternatives:

1. Triggers (as suggested by Daniel M.)
2. Temporary table which holds just the ids from the sub-select sot hat you only have to do that select once.

The triggers solution is probably the easiest - possibly the only concern would be if he amount of data (frequency) is so high that the minor additional overhead of adding to a summary table via the trigger causes a hit on performance.

Also, I'd make sure you can't achieve the same results as the one you are getting with the sub-select via a simple join between the two tables and then just summing - at least you would avoid a select for every row in the otter table.

Tim Received on Sat Jun 15 2002 - 21:00:13 CDT