Oracle Indexes

Richard Foote's picture

Clustering Factor Calculation Improvement Part II (Blocks On Blocks)

My previous post on the new TABLE_CACHED_BLOCKS statistics gathering preference certainly generated some interest My blog hits for the week have gone off the charts !! One of the concerns raised by this new capability was that setting such a preference might result in really unrealistic and inaccurate Clustering Factor (CF) values, especially for those […]

Richard Foote's picture

Important !! Clustering Factor Calculation Improvement (Fix You)

Believe me, this article is worth reading I’m currently not allowed to discuss Oracle 12c Database goodies but I am allowed to discuss things perhaps initially intended for 12c that are currently available and already back-ported to 11g. This includes a wonderful improvement in the manageability of how the Clustering Factor (CF) of an index can now […]

Richard Foote's picture

Storage Indexes vs Database Indexes IV: 8 Column Limit (Eight Line Poem)

As Exadata Storage Indexes (SI) are purely memory only structures located on the Exadata storage servers, care needs to be taken in how much memory they can potentially consume. As a result, there is a limit of 8 columns (or 8 SIs) that can be defined for a given 1M storage region at any point […]

Richard Foote's picture

Storage Indexes vs Database Indexes Part III: Hidden Values (Hide Away)

OK, my holiday to Hawaii is now slowing fading away into distant memory. Time for a new post In my previous post on differences between Exadata Storage Indexes and Database Indexes Part II, I discussed how the clustering of data within the data is an important factor (pun fully intended !!) in the performance and […]

Richard Foote's picture

Storage Indexes vs Database Indexes Part II: Clustering Factor (Fast Track)

Two posts in two days !! Well, with Christmas just around the corner, I thought I better finish off a couple of blog posts before I get fully immersed in the festive season The Clustering Factor (CF) is the most important index related statistic, with the efficiency of an index performing multi-row range scans very much […]

Richard Foote's picture

Storage Indexes vs Database Indexes Part I MIN/MAX (Maxwell’s Silver Hammer)

It’s often stated that in Exadata, you don’t need conventional database indexes anymore as everything runs so damn fast that indexes are simply a waste of time and space. Simply drop all database indexes and things will run just as fast. Well, not quite … There are many many scenarios where database indexes are still […]

Richard Foote's picture

Exadata Storage Indexes Part V: Warming Up (Here Come The Warm Jets)

As I mentioned in a previous post, there are a number of Similarities between Storage Indexes and Database Indexes. One of these similarities is the “warming up” process that needs to take place before indexes become “optimal” after either the Storage Server (in the case of Storage Indexes) or the Database Server (in the case […]

Richard Foote's picture

Exadata Storage Indexes Part IV – Fast Full Table Scans (Speed of Life)

OK, let’s look at Storage Indexes in action. But first, following is the setup for the various demos to come. I basically create one table called BIG_BOWIE that’s about 1GB in size and then simply create another table called DWH_BOWIE where the contents of this are re-insert into itself a few times to get to about […]

Richard Foote's picture

Exadata Storage Indexes Part III – Similarities With Database Indexes (Same Old Scene)

As discussed previously, there are quite a number of differences between Storage Indexes (SIs) and Database Indexes (DIs). However, there are also a number similarities between both of them as well. The obvious one is that they’re both designed specifically to reduce the overheads associated with retrieving the required data out of the database. Both index structures provides […]

Richard Foote's picture

Exadata Storage Indexes Part II – Differences With Database Indexes (Space Dementia)

Let’s explore some of the key differences between Storage Indexes (SI) and Database Indexes (DI). In no particular order, they include: SIs are structures that exist only within the storage servers of an Exadata box, while DIs logically exist and can be accessed within the database servers. SIs are purely memory only structures while DIs are […]

Syndicate content