Character Strings . Exams of Oracle . The Clustering Factor
Bitmap Indexes Wrap-Up-Indexes-2
On 02/12/2022 by Robert CorvinoWe can see this query took two-tenths of a CPU second to execute and had to do a full scan on the table. The function MY_SOUNDEX was invoked almost 10,000 times (according to our counter). We’d like it to be called much less frequently, however. Let’s see how indexing the function can speed up things.
Automatic Indexing . Exams of Oracle . The Clustering Factor
Bitmap Indexes Wrap-Up-Indexes-1
On 23/11/2022 by Robert CorvinoWhen in doubt, try it out (in your non-OLTP system, of course). It is trivial to add a bitmap index to a table (or a bunch of them) and see what it does for you. Also, you can usually create bitmap indexes much faster than B*Tree indexes. Experimentation is the best way to see if
Auto Indexing Wrap-Up . Exams of Oracle . The Clustering Factor
Physical Organization-Indexes-1
On 10/10/2022 by Robert CorvinoHow the data is organized physically on disk deeply impacts these calculations, as it materially affects how expensive (or inexpensive) index access will be. Suppose you have a table where the rows have a primary key populated by a sequence. As data is added to the table, rows with sequential sequence numbers might be, in
Auto Indexing Wrap-Up . Character Strings . Exams of Oracle . The Clustering Factor
When Should You Use a Bitmap Index?-Indexes
On 06/10/2022 by Robert CorvinoBitmap indexes are most appropriate on low distinct cardinality data (i.e., data with relatively few discrete values when compared to the cardinality of the entire set). It is not really possible to put a value on this—in other words, it is difficult to define what low distinct cardinality is truly. In a set of a
Automatic Indexing . Exams of Oracle . Index Case Summary
Physical Organization-Indexes-2
On 28/05/2022 by Robert CorvinoTHE EFFECT OF ARRAYSIZE ON LOGICAL I/O It is interesting to note the effect of the ARRAYSIZE on logical I/O performed. ARRAYSIZE is the number of rows Oracle returns to a client when they ask for the next row. The client will then buffer these rows and use them before asking the database for the
Automatic Indexing . Exams of Oracle . The Clustering Factor
The Clustering Factor-Indexes
On 09/04/2022 by Robert CorvinoNext, let’s look at some of the information Oracle will use. We are specifically going to look at the CLUSTERING_FACTOR column found in the USER_INDEXES view. The Oracle Database Reference manual tells us this column has the following meaning: Indicates the amount of order of the rows in the table based on the values of
Auto Indexing Wrap-Up . Character Strings . Exams of Oracle . Index Case Summary
B*Trees Wrap-Up-Indexes
On 27/03/2022 by Robert CorvinoB*Tree indexes are by far the most common and well-understood indexing structures in the Oracle database. They are an excellent general-purpose indexing mechanism. They provide very scalable access times, returning data from a 1000-row index in about the same amount of time as a 100,000-row index structure. When to index and what columns to index
Automatic Indexing . Exams of Oracle . The Clustering Factor
Bitmap Join Indexes-Indexes
On 04/02/2022 by Robert CorvinoNormally, an index is created on a single table, using only columns from that table. A bitmap join index breaks that rule and allows you to index a given table using columns from some other table. In effect, this allows you to denormalize data in an index structure instead of in the tables themselves. Consider
Auto Indexing Wrap-Up . Automatic Indexing . Exams of Oracle . Index Case Summary . The Clustering Factor
Indexing Only Some of the Rows-Indexes
On 24/01/2022 by Robert CorvinoIn addition to transparently helping out queries that use built-in functions like UPPER, LOWER, and so on, function-based indexes can be used to selectively index only some of the rows in a table. As we’ll discuss a little later, B*Tree indexes do not contain entries for entirely NULL keys. That is, if you have an
Auto Indexing Wrap-Up . Character Strings . Exams of Oracle . Index Case Summary
When Should You Use a B*Tree Index?-Indexes
On 30/12/2021 by Robert CorvinoNot being a big believer in “rules of thumb” (there are exceptions to every rule), I don’t have any rules of thumb for when to use (or not to use) a B*Tree index. To demonstrate why I don’t have any rules of thumb for this case, I’ll present two equally valid ones: •\ Only use