Hi everybody,
For performance reasons, I would like to load an index into the buffer cache at a specific time, for example every day at 6AM. Actually, a user runs a query at 6:30 and waits to long for it. So I would like to run the query before in order to accelerate his query because the data would be already on cache. The query runs with bad performances and I cannot act on the database schema.
For technical reasons I can neither modifiy the database schema nor use patching nor rewrite the query the user runs because it's generated from a piece of software. So my approach is to cache what I can before the user runs the query in interactive mode to avoid him waiting to much time in front of his screen.
I have already used in the past the "CACHE" hint for full table scans, in ordre to ensure that my table data would be kept in the buffer cache longer than by default. Here is an example :
SELECT /*+ FULL(MY_TABLE) CACHE(MY_TABLE) */ c1, c2, c3, c4
FROM MY_TABLE
This ensures that data is kept into the buffer cache as MRU. I would like to do the same with indexes, but of what I read on Oracle documentation, it's not possible to use the CACHE hint for indexes :
SELECT /*+ INDEX(MY_TABLE IDX1) CACHE(MY_TABLE IDX1)*/ c1, c2, c3, c4
FROM MY_TABLE
I haven't found a way to do it or maybe i missed understand how the buffer cache works.
Can you tell me how I can do it ? I am running on Oracle 11.1.0.7.0.
Thank you for your help.
Best regards,
MS