Click here to Skip to main content
15,038,345 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
my select query has 4 table joining,whenever i cache using result_cache it does not work, if i used single table select it works fine,
EX1:SELECT /*+ RESULT_CACHE */ id from tab1 it works fine
but for another query like this
ex2:select /*+ RESULT_CACHE */ ORDERDET*(SELECT COL1,COL2,COL3,COL4
FROM TAB1,TAB2,TAB3,TAB4)ORDERDET
THIS ex2 DOES WORK WHILE CACHING IN ORACLE 11G
Posted

1 solution

You should provide more data why you think the caching isn't working. One cause could be that the data in the tables have been changed thus invalidating the cached result.

In overall, server side caching is rarely needed because the server is caching data block in-memory anyway. The benefit from query specific caching is often so small and situation specific that it would make sense to put the effort to access plan optimization instead.
   
Comments
Member 10809057 12-Jan-15 0:05am
   
BUT QUERY IS NOT STORED IN V$RESULT_CACHE_OBJECT TABLE
Wendelius 12-Jan-15 0:32am
   
If I'm looking correctly, the example 2 contains a subquery as far as I know the subquery results aren't cached if that's what you're looking for.

Also the query contains cartesian joins which most likely means that the size of the result is big. Have a look at the following parameter. Perhaps they provide more information:
- RESULT_CACHE_MAX_SIZE
- RESULT_CACHE_MAX_RESULT
Member 10809057 12-Jan-15 1:22am
   
HOW MUCH SIZE NEED TO ALLOCATE FOR 3L RECORDS FOR RESULT_CACHE_MAX_SIZE
Wendelius 12-Jan-15 1:32am
   
That depends greatly on the records. However, as I said if you don't have specific justification for result caching I would let the database to handle the caching based on the data blocks. Just make sure that the size of your db buffer is large enough and concentrate on the actual plans of the queries.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900