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
Automatic Indexing . Exams of Oracle
Character and Binary String Types-Datatypes
On 21/06/2024 by Robert CorvinoThe character datatypes in Oracle are CHAR, VARCHAR2, and their “N” variants. The CHAR and NCHAR can store up to 2000 bytes of text. The VARCHAR2 and NVARCHAR2 can store up to 4000 bytes of information. Note Starting with Oracle 12c, VARCHAR2, NVARCHAR2, and RAW datatypes can be configured to store up to 32,767 bytes
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 . 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
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
Automatic Indexing . Exams of Oracle . The Clustering Factor
Do Nulls and Indexes Work Together?-Indexes
On 24/03/2023 by Robert CorvinoBTree 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
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
