CODEWITHSUNDEEP
for-loopplsql
Gianluca_V
Gianluca_V

Reputation: 1

PL/SQL: Looping through a list string

Please forgive me if I open a new thread about looping in PL/SQL but after reading dozens of existing ones I'm still not able to perform what I'd like to.

I need to run a complex query on a view of a table and the only way to shorten running time is to filter through a where clause based on a variable to which such table is indexed (otherwise the system ends up doing a full scan of the table which runs endlessly)

The variable the table is indexed on is store_id (string) I can retrieve all the store_id I want to query from a separate table: e.g select distinct store_id from store_anagraphy

Then I'd like to make a loop that iterate queries with the store_id identified above e.g select *complex query from view_of_sales where store_id = 'xxxxxx' and append (union) all the result returned by each of this queries

Thank you very much in advance. Gianluca

Upvotes: 0

Views: 98

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

In theory, you could write a pipelined table function that ran multiple queries in a loop and made a series of pipe row calls to return the results. That would be pretty unusual but it could be done.

It would be far, far more common, however, to simply combine the two queries and run a single query that returns all the rows you want

select something
  from your_view
 where store_id in (select distinct store_id
                      from store_anagraphy)

If you are saying that you have tried this query and Oracle is choosing to do a table scan rather than using the index then what you really have is a tuning problem. Most likely, statistics on one or more objects are inaccurate which leads Oracle to expect that this query would return more rows than it really will thus favoring the table scan. You should be able to fix that by fixing the statistics on the objects. In a pinch, you could also use hints to force an index to be used.

Upvotes: 1

Related Questions