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
Exams of Oracle . Index Case Summary
An Overview of Oracle Datatypes-Datatypes-1
On 04/04/2024 by Robert CorvinoOracle provides 23 different SQL datatypes. Briefly, they are as follows: •\ CHAR: A fixed-length character string that will be blank padded with spaces to its maximum length. A non-null CHAR(10) will always contain 10 bytes of information using the default National Language Support (NLS) settings. We will cover NLS implications in more detail shortly.
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 . 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 . 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
Exams of Oracle . Index Case Summary . The Clustering Factor
Multiple Indexes on the Same Column Combinations-Indexes
On 26/04/2023 by Robert CorvinoPrior 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
Exams of Oracle . Index Case Summary . The Clustering Factor
Index Case Summary-Indexes
On 24/01/2023 by Robert CorvinoIn 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
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
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
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
