Auto Indexing Wrap-Up . Automatic Indexing . Character Strings . Exams of Oracle

Should Foreign Keys Be Indexed?-Indexes

On 22/10/2023 by

The question of whether or not foreign keys should be indexed comes up frequently. We touched on this subject in Chapter 6 when discussing deadlocks. There, I pointed out that unindexed foreign keys are the biggest single cause of deadlocks that I encounter, due to the fact that an update to a parent table’s primary Read More

Auto Indexing Wrap-Up . Automatic Indexing . Exams of Oracle . Index Case Summary

Virtual Column Solution-Indexes

On 03/09/2023 by

The idea here is to first create a virtual column applying a SQL function on the extended column that returns a value less than 6398 bytes. Then that virtual column can be indexed, and this provides a mechanism for better performance when issuing queries against extended columns. An example will demonstrate this. First, create a Read More

Auto Indexing Wrap-Up . Automatic Indexing . Exams of Oracle . The Clustering Factor

Invisible Indexes-Indexes

On 21/08/2023 by

You have the option of making an index invisible to the optimizer. The index is only invisible in the sense that the optimizer won’t use the index when creating an execution plan. You can either create an index as invisible or alter an existing index to be invisible. Here, we create a table, load it Read More

Auto Indexing Wrap-Up . Automatic Indexing . Character Strings . Exams of Oracle . Index Case Summary

Implementing Selective Uniqueness-Indexes

On 06/07/2023 by

Another useful technique with function-based indexes is to use them to enforce certain types of complex constraints. For example, suppose you have a table with versioned information, such as a projects table. Projects have one of two statuses: either ACTIVE or INACTIVE. You need to enforce a rule such that “Active projects must have a Read More

Auto Indexing Wrap-Up . Automatic Indexing . Character Strings . Exams of Oracle . The Clustering Factor

Function-Based Indexes Wrap-Up-Indexes

On 01/05/2023 by

Function-based indexes are easy to use and implement, and they provide immediate value. They can be used to speed up existing applications without changing any of their logic or queries. Many orders of magnitude improvement may be observed. You can use them to precompute complex values without using a trigger. Additionally, the optimizer can estimate Read More

Exams of Oracle . Index Case Summary . The Clustering Factor

Multiple Indexes on the Same Column Combinations-Indexes

On 26/04/2023 by

Prior to Oracle Database 12c, you could not have multiple indexes defined on one table with the exact same combination of columns. For example:$ sqlplus eoda/foo@PDB1SQL> create table t(x int);Table created.SQL> create index ti on t(x);Index created.SQL> create bitmap index tb on t(x) invisible; Starting with 12c, you can define multiple indexes on the same Read More

Character Strings . Exams of Oracle . The Clustering Factor

Function-Based Index Solution-Indexes

On 24/04/2023 by

The concept here is that you’re building an index and applying a function to it in a way that limits the length of the index key and also results in a usable index. Here, I use the same code (as in the prior section) to create a table with an extended column and populate it Read More

Automatic Indexing . Exams of Oracle . The Clustering Factor

Do Nulls and Indexes Work Together?-Indexes

On 24/03/2023 by

BTree indexes, except in the special case of cluster BTree indexes, do not store completely null entries, but bitmap and cluster indexes do. This side effect can be a point of confusion, but it can actually be used to your advantage when you understand what not storing entirely null keys implies. To see the effect Read More

Auto Indexing Wrap-Up . Exams of Oracle . The Clustering Factor

Why Isn’t My Index Getting Used?-Indexes-1

On 24/02/2023 by

There are many possible causes of this. In this section, we’ll take a look at some of the most common. Case 1We’re using a B*Tree index, and our predicate does not use the leading edge of an index. In this case, we might have a table T with an index on T(x,y). We query SELECT Read More

Exams of Oracle . Index Case Summary . The Clustering Factor

Index Case Summary-Indexes

On 24/01/2023 by

In my experience, these six cases are the main reasons I find that indexes are not being used. It usually boils down to a case of “They cannot be used—using them would return incorrect results,” or “They should not be used—if they were used, performance would be terrible.” Myth: Space Is Never Reused in an Read More

1 2 3 4