When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.

It is part of a test question, where students were asked to define and describe pinning in relation to indexes.

Does anyone have an idea?

2 Answers 2


Search for Keep Pool. You're probably better off not using it unless you know its purpose and limitations very well and it fits the problem you are trying to solve.

  • Is this what pinning is in the context of indexing? Jun 1, 2012 at 17:30
  • Yes, this is the answer to your question. Jun 1, 2012 at 17:51
  • I also found this somewhere else, is it the same? It is frequently used for hot indexes that see a lot of queries against them, but might ordinarily age out of Oracle's cache (and thus have to be re-read from the disk). By pinning the index in memory the index scan will theoretically never require a disk access. If the index is large and the cache cycles frequently this could be a substantial performance improvement over frequently refreshing the cache. Jun 1, 2012 at 19:05
  • Yes, that concept is talking about pinning/keep pool. You really should check out my second link above. It may help the specific index pinned, but to the detriment of overall performance. Jun 4, 2012 at 2:24
  • Your keep pool link is broken. Also, although this is related to pinning, it is different to the traditional concept which is simply to request a page stays in memory while it is in use by, say, a transaction.
    – beldaz
    Aug 1, 2016 at 6:23

If you decide to use pinning, do searches for scripts that help ID good prospects. Most of the time, the best prospects are small indexes that do age out often. Avoid the large ones, because you can end up committing a big chunk of your SGA. One question though. If you are having rapid aging out issues, is it time to look at your memory settings, or moving your DB to a larger server w more RAM? If objects are aging out really fast, then you may not have the SGA to lock up by pinning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.