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
Auto Indexing Wrap-Up . Exams of Oracle
NLS Overview-Datatypes-2
On 24/07/2024 by Robert CorvinoNotice how in the 7-bit session I received the letter “a” three times with no diacritical marks. However, the DUMP function is showing me that in the database there are, in fact, three separate distinct characters, not just the letter “a.” The data in the database hasn’t changed—just the values this client received. If this
Auto Indexing Wrap-Up . Automatic Indexing . Exams of Oracle
Automatic Indexing in Action-Indexes
On 25/02/2024 by Robert CorvinoNow that you have some background with the automatic indexing feature, let’s enable it and see how it works:$ sqlplus system/foo@PDB1SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_MODE’,’IMPLEMENT’); PL/SQL procedure successfully completed. Next, I’ll create a table to test with:$ sqlplus eoda/foo@PDB1SQL> create table d (d varchar2(30));Table created. Now I’ll insert some random number data into this table:SQL> insert into
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 . Exams of Oracle . Index Case Summary
Automatic Indexing-Indexes
On 24/12/2023 by Robert CorvinoStarting with version 19c, the Oracle database ships with an automatic indexing feature. This allows you to delegate some index management features to the database. This feature creates, rebuilds, and drops indexes based on the workload of your application. Indexes that are managed by this feature are known as auto indexes. Here are the main
Auto Indexing Wrap-Up . Exams of Oracle . The Clustering Factor
Why Isn’t My Index Getting Used?-Indexes-2
On 24/11/2023 by Robert CorvinoCase 4We have indexed a character column. This column contains only numeric data. We query using the following syntax:SQL> select * from t where indexed_column = 5 Note that the number 5 in the query is the constant number 5 (not a character string). The index on INDEXED_COLUMN is not used. This is because the
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 . Exams of Oracle . Index Case Summary
Virtual Column Solution-Indexes
On 03/09/2023 by Robert CorvinoThe 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
Auto Indexing Wrap-Up . Automatic Indexing . Exams of Oracle . The Clustering Factor
Invisible Indexes-Indexes
On 21/08/2023 by Robert CorvinoYou 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
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
