i have a java program executing 3 separate sqls with a same inline view - it takes about 20 minutes each time to build the inline view when the sqls are executed - is there a way to cache or reuse it ? - trying to avoid temporary table solution because it needs to be delegated to a plsql since the java program does not have rights to create schema objects. ps: oracle 10g
views:
31answers:
2
A:
Oracle will do its best to cache the result if you let it. But you can still have a temp table if you really want to, and it is fairly small. If you use a package you can create the temp table the first time it is called and use the cached data the other 2 times -- try out the package variables and see if that does what you want.
MJB
2010-05-11 23:11:04
A:
Oracle temp tables should not be created on the fly. Docs here. If you use a temporary table for this create it once and just use it in the procedure.
Another option may be a materialized view. Docs here. The materialized view would be either refreshed on demand or on a schedule.
With the information given it is not possible to tell you which would fit your situation better.
Todd Pierce
2010-05-12 08:05:06
were using procedures earlier for creating and dropping on the fly temporary tables before switching to inline views - switch was largely due to issues in team development environment sharing one DB! adding to this is that the program is executed in 10 parallel threads for different data ranges - for now planning to switch back to temporary tables as development is done and now its performance- may be 11g RESULT_CACHE hint is the answer ?
prabhackar
2010-05-12 22:03:09
Creating and dropping global temporary tables in oracle will cause issues. If you use them leave them in the database. This will avoid you issue of needing to create objects. And any data in them is session specific and truncated at the end of a transaction or session. So no need to drop them.
Todd Pierce
2010-05-13 21:14:10