PartitionedIndexEfficiency Although it is generally true that rebuilding an index represents a waste of time and resources, there nevertheless exist situations where degenerated indexes might benefit from reorganisation. When you think you are in such a situation, instead of shooting in the dark and rebuilding all indexes, you are better off finding and rebuilding only those indexes occupying more spac… Read more
In Oracle, under certain circumstances, an implicit data type conversion precludes the use of indexes. Perhaps you have a vague idea of what an implicit data type conversion hierarchy is, and you might even ignore the subtlety of the implicit data type conversion direction within a query predicate expression. Hopefully this article will shed some light on this concept. 1… Read more
In the previous article about the Oracle 12c TOP-Frequency histogram, we saw that Oracle carries out a preliminary determination step to see how dominant the TOP-N values are with regards to the total number of rows in the data set. I demonstrated that a TOP-Frequency is possible provided a threshold condition is satisfied. This second article explores the Hybrid histogram ty… Read more
As of Oracle version 12.1, if a number of buckets N in a histogram is less than the number of distinct values in the data set, Oracle will not generate the instable and imprecise legacy Height Balanced histogram. Instead, two new types of histogram are possible: TOP-Frequency and Hybrid. In order to select between the later and the former ty… Read more
Designing an index is not done through “trial and error”. This kind of strategy seems to doom many engineered indexes to imprecision, explosion, and locking issues. Designing a precise index very often tends to be a “kill two birds with one stone” strategy. In this article I am going to show you how to design a single index with whic… Read more
1. Introduction When a critical report which used to complete very quickly starts performing poorly, there is a big chance that the generic reason for this time response degradation is due to a change in an execution plan. If you want to avoid this flip-flop on plan and execution time you have at your disposal a feature called SQL Plan… Read more
