LuckyLucks
asked on
Alternative to GTT for a temp table for further reuse in Oracle
Hi:
I have a long set of subqueries that are UNION ed. I can not create Global Temp Tables (permission issue on production systems), but would like to reuse this query instead of repeating its definition every place I want to use it. Is there another way I can stuff my humungous query into some type of object and cast it back as a table to use it like a temp table, thereby, accomplishing the same thing as a GTT?
Thanks
I have a long set of subqueries that are UNION ed. I can not create Global Temp Tables (permission issue on production systems), but would like to reuse this query instead of repeating its definition every place I want to use it. Is there another way I can stuff my humungous query into some type of object and cast it back as a table to use it like a temp table, thereby, accomplishing the same thing as a GTT?
Thanks
>>would like to reuse this query instead of repeating its definition every place I want to use it
Can you explain what you mean by this?
A GTT stores the results of a query that can be reused. The query definition is something else.
Are you wanting to execute the query once and reuse the results over and over in additional queries or just store the huge query and re-query it over and over?
If you want to store the results and depending on your requirements, you can look into PL/SQL collections.
Anyway, with most solutions, you will need to create some object of some type inside the database, so, might as well create the GTT.
Can you explain what you mean by this?
A GTT stores the results of a query that can be reused. The query definition is something else.
Are you wanting to execute the query once and reuse the results over and over in additional queries or just store the huge query and re-query it over and over?
If you want to store the results and depending on your requirements, you can look into PL/SQL collections.
Anyway, with most solutions, you will need to create some object of some type inside the database, so, might as well create the GTT.
ASKER
Hi:
So I need a refreshed result set everytime. As it will become a part of another query down the road, it needs to be compact not several lines long, which is why a GTT alternative might have been a good option. That way, I would just need a table name.
Would PL/SQL collections need permissions that are more limited than say required for creating a GTT?
So I need a refreshed result set everytime. As it will become a part of another query down the road, it needs to be compact not several lines long, which is why a GTT alternative might have been a good option. That way, I would just need a table name.
Would PL/SQL collections need permissions that are more limited than say required for creating a GTT?
To clarify: A GTT is only ever created once. Have the DBA create it and everyone uses it. You don't need create permissions. You'll need insert permissions. Maybe delete but that should be it.
>>Would PL/SQL collections need permissions
They shouldn't. They are objects created inside the PL/SQL code.
>>Would PL/SQL collections need permissions
They shouldn't. They are objects created inside the PL/SQL code.
So, why wouldn't a view work? It would compact the query that you need to write as most of it would be hidden within the view definition. As it seems like you are worried about query complexity and not stored results, a view should do what you are looking for.
ASKER
Hi:
I am developing something that needs to run in production with minimal object creation. It is throw away work and hence I wont be granted permissions to create a view or other such things in production.
That is why I need to know - if it is possible to stuff this resultset of the view into a PL/SQL object , how would that object look/be?
Any advise?
I am developing something that needs to run in production with minimal object creation. It is throw away work and hence I wont be granted permissions to create a view or other such things in production.
That is why I need to know - if it is possible to stuff this resultset of the view into a PL/SQL object , how would that object look/be?
Any advise?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you create a view? If you can then that would hold your query, but it would have to run every time it is accessed.
Can you create a materialized view? Needs a refresh to get current data, but would hold the data like a temporary table and could be indexed. I would suggest refresh manually and just run the refresh procedure when you need to update the data.