Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
Is this what pinning is in the context of indexing? –  Database User Jun 1 '12 at 17:30
    
Yes, this is the answer to your question. –  Leigh Riffel Jun 1 '12 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. –  Database User Jun 1 '12 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. –  Leigh Riffel Jun 4 '12 at 2:24
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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