Reverse key indexes on remote database are not visible to the optimizer http://jonathanlewis.wordpress.com/?s=reverse+key+ And the effect it coud have on the clustering factor of the reversed index can damage its desirability by the CBO http://jonathanlewis.wordpress.com/2009/09/15/index-explosion-4/ Best regards Mohamed Houri www.hourim.wordpress.com 2014-03-17 17:03 GMT+01:00 Uzzell, Stephan <SUzzell@xxxxxxxxxx>: > Hi Riyaj, > > > > I had actually seen your blog entry - that's what got me as far down the > path as I made it. > > > > I'm wondering though - you've made it pretty clear you don't think highly > of reverse key indices. I understand that they can't be range scanned - but > are there other minuses to them? > > > > In this particular case, the PK isn't generally used by any queries > against the table. Since there are no range scans involved, I'm wondering > if that eliminates some (all?) of the negatives of a reverse key index? > > > > Thanks! > > > > *Stephan Uzzell* > > > > *From:* Riyaj Shamsudeen [mailto:riyaj.shamsudeen@xxxxxxxxx] > *Sent:* Monday, 17 March, 2014 11:39 > *To:* Uzzell, Stephan > *Cc:* oracle-l@xxxxxxxxxxxxx > > *Subject:* Re: Index Contention / Sequence Caching > > > > Hello Stephen, > > > > If you had sequence caching issues, then the object number for the gc > buffer busy event would be set to the object number of seq$ table. > > > > This is a classic right-hand-growth-index-leaf-block contention. I have a > blog entry: > http://orainternals.wordpress.com/2010/09/27/gc-buffer-busy-waits/ > > > > Partitioning the index is probably the optimal solution. Of course, for > heavily used sequences, you should increase the cache to much higher value > even in a single instance. > > > > Only if I don't have partitioning license, I would use reverse key > indexes. Even then, reverse key indexes can induce few other problems, > simply, avoid if possible. > > > > Cheers > > Riyaj Shamsudeen > Principal DBA, > Ora!nternals - http://www.orainternals.com - Specialists in Performance, > RAC and EBS > Blog: http://orainternals.wordpress.com/ > Oracle ACE Director and OakTable member <http://www.oaktable.com/> > > Co-author of the books: Expert Oracle > Practices<http://tinyurl.com/book-expert-oracle-practices/> > , Pro Oracle SQL, <http://tinyurl.com/ahpvms8>Expert RAC Practices > 12c.<http://tinyurl.com/expert-rac-12c> > Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices> > > > > > -- Bien Respectueusement Mohamed Houri