Auto Indexing Wrap-Up . Automatic Indexing . Character Strings . Exams of Oracle . Index Case Summary . The Clustering Factor
Character Strings-Datatypes
On 25/07/2024 by Robert CorvinoThere are four basic character string types in Oracle, namely, CHAR, VARCHAR2, NCHAR, and NVARCHAR2. All of the strings are stored in the same format in Oracle. On the database block, they will have a leading length field of 1–3 bytes followed by the data; when they are NULL, they will be represented as a
Character Strings . Exams of Oracle
An Overview of Oracle Datatypes-Datatypes-2
On 01/05/2024 by Robert Corvino•\ TIMESTAMP WITH TIME ZONE: This is a fixed-width 13-byte date/ time datatype, but it also provides for TIME ZONE support. Additional information regarding the time zone is stored with the TIMESTAMP in the data, so the TIME ZONE originally inserted is preserved with the data. •\ TIMESTAMP WITH LOCAL TIME ZONE: This is a
Auto Indexing Wrap-Up . Character Strings . Exams of Oracle
Managing Automatic Indexing-Indexes
On 30/01/2024 by Robert CorvinoAutomatic indexing is managed via the internal DBMS_AUTO_INDEX PL/SQL package. The default mode of automatic indexing is REPORT ONLY. In this mode, automatic indexing is on, but any indexes it creates are invisible (meaning the indexes won’t be used by the optimizer). You can enable/disable indexing at the root container level or at the pluggable
Auto Indexing Wrap-Up . Automatic Indexing . Character Strings . Exams of Oracle
Should Foreign Keys Be Indexed?-Indexes
On 22/10/2023 by Robert CorvinoThe 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
Auto Indexing Wrap-Up . Automatic Indexing . Character Strings . Exams of Oracle . Index Case Summary
Implementing Selective Uniqueness-Indexes
On 06/07/2023 by Robert CorvinoAnother 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
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 Robert CorvinoFunction-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
Character Strings . Exams of Oracle . The Clustering Factor
Function-Based Index Solution-Indexes
On 24/04/2023 by Robert CorvinoThe 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
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.
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
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
