⚡️What features have been added in the new version(23ai) compared to the older ones to index performance ?
Alireza Kamrani
13/10/2024
Today I will represent some useful of technically notes about Rebuilding of indexes.
How/When shoud rebuild a index?
Time to rebuild indexes in the Oracle database?
One of the most controversial topics among DBAs is whether to rebuild indexes or not.
But it is so generic that the answer to the question depends on the database version, the index type and the reasons behind doing it.
In most cases, we consider b-tree indexes for rebuilt. Note that b stands not for “binary” but for “balanced”.
We create indexes mostly for performance reasons. If performance is the real concern, we need to first understand at least at high level how those indexes are organized.
The MOS note Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1) suggests that indexes considered for rebuilt are indexes for which:
– deleted entries represent 20% or more of the current entries
– the index depth is more then 4 levels
And possible candidate for bitmap index were considered when the distinctiveness was more than 99%:
if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
end if;
Determining if an Index Needs to Be Rebuilt
In Oracle, you can get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command. Here's some sample output from the INDEX_STATS Table:
SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;
Statement processed.
SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW
---------- ------ ------ ---------- ----------
MyIndex 2 1 3 6
Another MOS article, Script to investigate a b-tree index structure (Doc ID 989186.1), provides a script which is quite handy as it verifies the structure of a b-tree index based on the existing table and index statistics.
♦️The script calculates the following items:
– Estimate the size the index should be as optimal packing can be specified
– The index layout
This script keeps a history of the the data gathered in the INDEX_HIST table.
This can be useful to prevent a pattern in index rebuilding. The history log can be user defined.
An another MOS article, Index Rebuild, the Need vs the Implications (Doc ID 989093.1), specifies that the most common justifications given for rebuilding an index are:
– index becomes fragmented
– index grows and grows
– deleted space is not re-used
– index clustering factor becomes out of sync
♦️However, as noted, the impact of rebuilding the index can be quite significant:
* Most scripts around depend on the index_stats dynamic table
* Redo activity may increase and general performance might be impacted as a direct result of rebuilding an index
♦️An index coalesce is often preferred instead of an index rebuild. It has the following advantages:
* does not require approximately 2 times the disk storage
* always online
* does not restructure the index, but combines index leaf blocks as much as possible, avoiding system overhead
📍Due to the reasons listed above, it is strongly advised not to rebuild indexes on a regular basis but instead use proper diagnostics.
♦️Furthermore, here is my personal experience from rebuilding indexes: either drop and create or simply rebuild. I have seen 3 main use cases:
1. For performance reasons:
because of too many deleted rows or because of split index nodes. With every new release of Oracle, it looks like that we need less and less time on performing such a rebuild operation.
2. Indexes get fragmented over time and occupy too much space.
There was a mission critical production database where data was about 10GB and the indexes all together were about 3TB. You can release sometimes quite a lot of space.
3. Indexes should be in my opinion in a separate tablespace. Not where the data is.
If something gets wrong with the index tablespace: logical or physical corruption, or deleting files unintentionally, then it is just a matter of time to recreate the indexes again.
📍Clustering factor of indexes :
The clustering factor is a number which represent the degree to which data is randomly distributed in a table. It is the number of “block switches” while reading a table using an index.
It is used by Oracle’s optimizer to help determine the cost associated with index range scans in comparison to full table scans.
To calculate the clustering factor of an index during the gathering of index statistics, Oracle does the following.
For each entry in the index Oracle compares the entry’s table rowid block with the block of the previous index entry.
If the block is different, Oracle increments the clustering factor by 1.
Typically, Clustering factor can drastically increase if the table insertions or unordered as the index entry has to revisit the block again and again.
In addition to above, if the table is in ASSM tablespace and concurrent inserts from multiple sessions (parallel sessions) can increase clustering factor due to the fact of freelists and each session will use it own block to insert the same value of data and apparently index entries are have to unordered.
Basically, the CF is calculated by performing a Full Index Scan and looking at the rowid of each index entry. If the table block being referenced differs from that of the previous index entry, the CF is incremented. If the table block being referenced is the same as the previous index entry, the CF is not incremented. So the CF gives an indication of how well ordered the data in the table is in relation to the index entries (which are always sorted and stored in the order of the index entries). The better (lower) the CF, the more efficient it would be to use the index as less table blocks would need to be accessed to retrieve the necessary data via the index.
However, there’s a basic flaw here. The CF calculation doesn’t take into consideration the fact the referenced table block, although maybe different from theprevious one index entry, might already have recently been accessed. As such, during an index scan, the table block being accessed is almost certainly still cached in the buffer cache from the previous access, thereby not reducing the effectiveness of the index in any appreciable manner. A classic example of this would be a table with a few freelists. Although the data being inserted is not ordered precisely within the same data blocks, the data might actually be very well clustered within only a few blocks of each other.
Picture a table with 100 rows being inserted by 2 sessions simultaneously, each inserting 50 rows based on an ordered sequence. With one freelist, the data is basically inserted in one block first and then once full a second table block. The data is therefore perfectly ordered/clustered and the CF will evaluate to a value of 2 on such an indexed column. But with 2 freelists, one session could insert data into one block while the other session inserts into a second block, with the ordered sequenced values being randomly distributed among the 2 blocks. The CF could now potentially evaluate to a value of 100 as the rows are jumbled or “toggled” across the two blocks. This is a much much worse value (2 vs. 100) that can adversely impact the CBO calculations, although the efficiency of such an index is really almost identical as both table blocks are certain to be cached during an index scan regardless.
Be aware on creating composite index that usually have a higher CF instead of index creation per columns, but another recommendation is that usually preferred composite index instead of one column indexed.
So creating of index need many considerations and need a deep investigation.
♦️In Oracle 19c , there is a feature called Automatic Indexing that have 5 steps internally and newd a period of time to analyze and calculate essentials metadata and informations about work loads of database and have sime mode such as Reporting Only, Implementation, so on.
We all know that indexes are critical for database performance as they significantly speed up query execution. However, managing indexes manually can be complex and time-consuming. Automatic Index Optimization simplifies this process by monitoring database usage patterns, identifying inefficient or redundant indexes, and optimizing them without requiring manual intervention.
Consider a scenario where a large retail database experiences frequent query performance issues due to inefficient indexing.
Automatic Index Optimization can help by continuously monitoring the workload, creating new indexes where needed, dropping unused indexes, and rebuilding fragmented ones.
Might not be applicable everywhere so I would rather advise to exercise caution and test this in a lower environment first.
♦️In Oracle 21c, there is a new feature called Automatic Index Optimization. The optimization process includes 3 actions:
• Compress: Compresses portions of the key values in an index segment (~3 times)
• Shrink: Merges the contents of index blocks where possible to free blocks for reuse
• Rebuild: Rebuilds an index to improve space usage and access speed
For a very long time, both DBAs and Developers, have been struggling with what indexes should be created, what type of indexes they should be created as and what indexes should be dropped from the database.
By far, the most interesting new feature of Oracle Database 19c is Automatic Index creation (AI Creation).
In the long run, this is to be one of the most important features in the Oracle database. Note that you cannot rebuild or change storage parameters an auto index!
Nice and useful capability for AI is that Oracle automatically rebuilds indexes that are marked as “Unusable”.
Also Automatic indexing capable to remove unusable auto indexes suggestions that has not implement on Dmls automaticaly after 373 days (configurable manually) and many views to control automatica indexing processes and historical view information.
♦️Automatic indexing Characteristics:
– The auto index candidates are created as invisible auto indexes
– If the performance of SQL statements is not improved from the auto indexes, then the indexes are marked as unusable and the corresponding SQL statements are blacklisted
– Auto indexes cannot be used for any first time SQL run against the database
– Auto indexes are created as either single, concatenated indexes or function-based indexes and they all use advanced low compression
– The unused auto indexes are deleted after 373 days (can be changed)
– The unused non-auto indexes (manual indexes) are never deleted by the automatic indexing process but can be deleted automatically if needed
The Auto Indexing can be disabled at any time or can be set to set to reporting mode (new auto indexes as created asinvisible indexes, so that they cannot be used in SQL) with the following commands:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
Here is a way to ask Oracle to create new auto indexes in a separate tablespace called AUTO_INDEX_TS:
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TS');
Generally rebuild index when the clustering factor exceeds eight times the number of dirty blocks in the base table, when the levels exceed two or when there are excessive brown nodes in the index.
Another good options to release a large index space that can be released spaces is using Move online to another Tablespace but this solution has some performance penalty on workload and you should use when have a minimum workload.
Also internal index related BLOB columns can move online in new versions of databases.
♦️Another recommendations about Index Rebuilding when index size is very large with fewer time:
1. PARALLEL Scan:
Start an index rebuild with a full- scan, and this full-scan can be parallelized according to your cpu_count.
2. NOLOGGING:
You can also use the NOLOGGING option for super-fast index rebuilding. The only danger with using nologging is that you must re-run the create index syntax if you perform a roll-forward database recovery. Using nologging with create index can make index rebuilding up to 30% faster.
3. Partition the index:
Using partitioning in Large tables and indexes is very important , you can rebuild a local partitioned index faster than a single large index in online.
4. Partial index :
this feature help you to olny have index on some vital partitioned table and older data that consider as archived data.
5. Faster Disk:
Some shops will use temporary solid-state disk to speed-up the initial index writes and move the index to platter disk storage at a later time.
6. Parallel index rebuild jobs:
Also, remember that if you have the spare CPU and the indexes are on different disks, you can submit many index rebuild jobs simultaneously. On a large server you can simultaneously rebuild dozens of indexes, each using parallel query, sort of a parallel parallelism for fast index rebuilds.
♦️Here is a summary of the differences between parallel index rebuilding techniques:
Alter index rebuild online parallel : During a online index rebuild, Oracle will make a snapshot log on the target table to hold DML activity, read the table in a full-table scan (read consistent), build the new index and then apply the changes from the snapshot log after the index has been rebuilt. You can use NOLOGGING and PARALLEL with the online rebuild:
Sql>alter index my_idx rebuild online parallel 48 nologging;
alter index my_idx noparallel;
alter index my_idx logging;
Alter index rebuild parallel: During a "regular" index rebuild, an exclusive lock occurs as the existing index is read. Hence, this command is designed for scheduled downtime periods where there is no DML activity. However, this operation can be parallelized and run in NOLOGGING mode:
Sql>alter index my_idx rebuild parallel 48 nologging;
alter index my_idx noparallel;
alter index my_idx logging;
SQL Server:
Microsoft recommends fixing index fragmentation issues by rebuilding the index if the fragmentation percentage of the index exceeds 30%, where it recommends fixing the index fragmentation issue by reorganizing the index if the index fragmentation percentage exceeds 5% and less than 30%.
♦️Do you think that index size decreases after rebuild?
No usually,
Just note that rebuilding an index doesn’t necessarily mean the resultant index will now be smaller or more efficient or more pristine.
In many cases, rebuilding an index blindly can result in a larger index structure than it was prior to the rebuild.
If we rebuild an index before the index has had the opportunity to use its available free space, an index rebuild can introduce more free space, not less.
Now this may not necessarily be a bad thing, but it’s another example of the index potentially being bigger, not smaller after a rebuild.
Although you can use compression features on indexes but this not very easy configuration and need to exactly parameters and optimizer tuning to achieve better performance.
Performance on indexes usage is related to many parameters, environment tyles,schema design, database settings, storages settings, and so on...
♦️What happend on 23c?
Automatic Indexing Enhancements
Enhancements in automatic indexing ensure that the database automatically tunes itself by creating and managing indexes based on the workload, without requiring much manual intervention from DBAs.
Key Enhancements:
- Improved Index Selection Algorithms: Enhanced algorithms analyze the workload more effectively to determine the most beneficial indexes, reducing the risk of unnecessary or redundant indexes.
- Automatic Index Validation: New mechanisms validate the effectiveness of automatically created indexes before making them visible to queries, ensuring they genuinely improve performance.
- Periodic Re-Evaluation: The system periodically re-evaluates the effectiveness of indexes to adapt to changing workloads and remove or modify indexes that are no longer beneficial.
- Better Integration with Database Workload: Integration with workload capture and replay features ensures that indexes are optimized based on realistic usage patterns.
- Enhanced Reporting and Monitoring: Improved reporting features provide detailed insights into automatic indexing activities, making it easier for DBAs to monitor and understand the impact of automatic indexing.
Automatic Indexing works by continuously monitoring the SQL workload, identifying candidates for indexing, and automatically creating, validating, and managing these indexes. DBAs can still configure, manage, and monitor automatic indexing on the database.
Below are a few examples:
--Enable Automatic Indexing:
ALTER SYSTEM SET AUTO_INDEXING = TRUE;
--Check Automatic Indexing Status:
SELECT parameter_name, parameter_value
FROM DBA_AUTO_INDEX_CONFIG
WHERE parameter_name = 'AUTO_INDEXING';
--Review Automatic Index Activity:
SELECT * FROM DBA_AUTO_INDEX_EXECUTIONS;
--Review Proposed Indexes:
SELECT * FROM DBA_AUTO_INDEX_IMPLEMENTATIONS
WHERE STATUS = 'PROPOSED';
--Monitor Index Validations:
SELECT * FROM DBA_AUTO_INDEX_VALIDATIONS;
--Review Index Usage:
SELECT INDEX_NAME, TABLE_NAME, TABLE_OWNER, LAST_USED
FROM DBA_INDEXES
WHERE AUTO = 'YES';
--Disable Automatic Indexing:
ALTER SYSTEM SET AUTO_INDEXING = FALSE;
--Configure Automatic Indexing Parameters:
BEGIN
DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT'); -- Options: 'IMPLEMENT', 'REPORT ONLY'
END;
BEGIN
DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_IDLE_INDEXES', 30); -- Retention period in days
END;
BEGIN
DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', 50);
-- Space budget as a percentage of total space
END;
Monitoring and Reporting:
A DBA can get detailed insights through dynamic performance views and reports. For example,
#Automatic Indexing Summary:
SELECT * FROM DBA_AUTO_INDEX_SUMMARY;
#Detailed Execution Log:
SELECT * FROM DBA_AUTO_INDEX_EXECUTIONS;
#Index Impact Analysis:
SELECT INDEX_NAME, TABLE_NAME, IMPACT, STATUS
FROM DBA_AUTO_INDEX_IMPLEMENTATIONS;
♦️Also in 23ai we see advanced features for indexing JSON data type:
Indexes for JSON Data
You can index scalar values in your JSON data using function-based indexes. In addition, you can define a JSON search index, which is useful for both ad hoc structural queries and full-text queries.
♦️Overview of Indexing JSON Data
You can index particular scalar values within your JSON data using function-based indexes. You can index JSON data in a general way using a JSON search index, for ad hoc structural queries and full-textqueries.
As always, function-based indexing is appropriate for queries that target particular functions, which in the context of SQL/JSON functions means particular SQL/JSON path expressions.
This indexing is not very helpful for queries that are ad hoc, that is, arbitrary. Define a function-based index if you know that you will often query a particular path expression.
Regardless of the SQL data type you use to store JSON data, you can use a B-tree or bitmap function-based index for SQL/JSON function json_value queries. Such an index targets a single scalar JSON value.
A bitmap index can be appropriate wherever the number of possible values for the function is small.
For example, you can use a bitmap index for json_valueif the values targeted are expected to be few.
For JSON data that is stored as JSON type you can use a multivalue function-based index for SQL/JSON condition json_exists.
Such an index targets scalarJSON values, either individually or (especially) as elements of a JSON array.
Although a multivalue index can index a single scalar value, if you expect a path expression to target such a value then it is more performant to use a B-tree or bitmap index.
Use a multivalue index especially to index a path expression that you expect to target an array of scalar values.
SQL/JSON path expressions that contain filter expressions can be used in queries that pick up a function-based index. But a path expression that you use to define a function-based index cannot contain filter expressions.
If you query in an ad hoc manner then define a JSON search index. This is a general index, not targeted to any specific path expression. It is appropriate for structural queries, such as looking for a JSON field with a particular value, and for full-text queries using Oracle SQL condition json_textcontains, such as looking for a particular word among various string values.
You can of course define both function-based indexes and a JSON search index for the same JSON column.
A JSON search index is an Oracle Text (full-text) index designed specifically for use with JSON data.
♦️Creating Bitmap Indexes for JSON_VALUE
You can create a bitmap index for SQL/JSON function json_value. A bitmap index can be appropriate whenever your queries target only a small set of JSON values.
--Creating a Bitmap Index for JSON_VALUE
This is an appropriate index to create provided there are only a few possible values for field CostCenter in your data.
CREATE BITMAP INDEX cost_ctr_idx ON j_purchaseorder
(json_value(po_document, '$.CostCenter'));
♦️Creating B-Tree Indexes for JSON_VALUE
You can create a B-tree function-based index for SQL/JSON function json_value. You can use the standard syntax for this, explicitly specifying json_value, or you can use dot-notation syntax with an item method. Indexes created in either of these ways can be used with both dot-notation queries and json_value queries.
♦️Creating a Function-Based Index for a JSON Field: Dot Notation
Item method number() causes the index to be of numeric type. Always apply an item method to the targeted data when you use dot notation to create a function-based index.
CREATE UNIQUE INDEX po_num_idx1 ON j_purchaseorder po
(po.po_document.PONumber.number());
Note: By default, a function-based index does not include NULL values. If ajson_value expression that's used by an index returns NULL, then by default the index is not used when obtaining a matching document. This implies that by default a function-based index isn't used if NULL is used as filter predicate (for example,json_value ... IS NULL) or if json_valueis used in an ORDER BY clause.
To index NULL values, and thus enable the use of json_value in an ORDER BY clause you need to add a constant value (any value) to the index creation statement:
CREATE INDEX po_num_idx1 ON j_purchaseorder po
(po.po_document.PONumber.number(), 42);
This does, however, increase the index size.
♦️Creating a Function-Based Index for a JSON Field: JSON_VALUE
Item method number() causes the index to be of numeric type. Alternatively you can instead use clause RETURNING NUMBER.
CREATE UNIQUE INDEX po_num_idx2 ON j_purchaseorder
(json_value(po_document, '$.PONumber.number()'
ERROR ON ERROR));
♦️Specifying NULL ON EMPTY for a JSON_VALUE Function-Based Index
Clause RETURNING VARCHAR2(200) causes the index to be a SQL string of maximum length 200 characters. You could use item method string() in the path expression instead, but in that case the default return type of VARCHAR2(4000) is used.
Because of clause NULL ON EMPTY, index po_ref_idx1 can index JSON documents that have no Reference field.
CREATE UNIQUE INDEX po_ref_idx1 ON j_purchaseorder
(json_value(po_document, '$.Reference'
RETURNING VARCHAR2(200) ERROR ON ERROR
NULL ON EMPTY));
♦️Oracle recommends that you create a function-based index for json_value using one of the following forms. In each case the index can be used in both dot-notation and json_value queries that lead to a scalar result of the specified JSON data type.
- Dot-notation syntax, with an item method applied to the value to be indexed. The indexed values are only scalars of the data type specified by the item method.
- A json_value expression that specifies aRETURNING data type. It can optionally use ERROR ON ERROR and NULL ON EMPTY. The indexed values are only scalars of the data type specified by the RETURNING clause.
Indexes created in either of these ways can thus be used with both dot-notation queries and json_valuequeries.
♦️Maintaining Oracle Text Indexes:
♦️Creating a CONTEXT Index
The CONTEXT index type is well suited for indexing large, coherent documents in formats such as Microsoft Word, HTML, or plain text. With a CONTEXTindex, you can also customize your index in a variety of ways. The documents must be loaded in a text table.
♦️CONTEXT Index and DML
A CONTEXT index is not transactional. When you delete a record, the index is changed immediately. That is, your session no longer finds the record from the moment you make the change, and other users cannot find the record after you commit.
For inserts and updates, the new information is not visible to text searches until an index synchronization has occurred. Therefore, when you perform inserts or updates on the base table, you must explicitly synchronize the index with CTX_DDL.SYNC_INDEX.
Default CONTEXT Index Example
The following statement creates a default CONTEXTindex called myindex on the text column in the docstable:
CREATE INDEX myindex ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT;
When you use the CREATE INDEX statement without explicitly specifying parameters, the system completes the following actions by default for all languages:
- Assumes that the text to be indexed is stored directly in a text column. The text column can be of type CLOB, BLOB,BFILE, VARCHAR2, or CHAR.
- Detects the column type and uses filtering for the binary column types of BLOB and BFILE. Most document formats are supported for filtering. If your column is plain text, the system does not use filtering.
Note:For document filtering to work correctly in your system, you must ensure that your environment is set up correctly to support the AUTO_FILTER filter. - Assumes that the language of the text to index is the language specified in your database setup.
- Uses the default stoplist for the language specified in your database setup. Stoplists identify the words that the system ignores during indexing.
- Enables fuzzy and stemming queries for your language, if this feature is available for your language.
You can always change the default indexing behavior by customizing your preferences and specifying those preferences in the parameter string of CREATEINDEX.
Incrementally Creating an Index with ALTER INDEX and CREATE INDEX
The ALTER INDEX and CREATE INDEX statements support incrementally creating a global CONTEXTindex.
- For a global index, use CREATE INDEX to support the NOPOPULATE keyword in the REPLACE parameter of the REBUILDclause. By doing so, you can create indexes incrementally. This keyword is valuable for creating Oracle Text indexes in large installations that cannot afford to have the indexing process running continuously.
- For a local index partition, modify the ALTER INDEX ... REBUILD partition ... parameters ('REPLACE ...') parameter string to support the NOPOPULATEkeyword.
- For a partition on a local index, CREATEINDEX ... LOCAL ... (partition ... parameters ('NOPOPULATE')) is supported. The partition-level POPULATEor NOPOPULATE keywords override any POPULATE or NOPOPULATE specified at the index level.
CTXCAT Index and DML Operations
A CTXCAT index is transactional. When you perform inserts, updates, and deletes on the base table, Oracle Text automatically synchronizes the index.
Unlike a CONTEXT index, no CTX_DDL.SYNC_INDEX is necessary.
Note:Applications that insert without invoking triggers, such as SQL*Loader, do not result in automatic index synchronization as described in this section.
About CTXCAT Subindexes and Their Costs
A CTXCAT index contains subindexes that you define as part of your index set. You create a subindex on one or more columns to improve mixed query performance.
However, the time Oracle Text takes to create a CTXCAT index depends on its total size, and the total size of a CTXCAT index is directly related to the following factors:
- Total text to be indexed
- Number of subindexes in the index set
- Number of columns in the base table that make up the subindexes
Many component indexes in your index set also degrade the performance of insert, update, and delete operations, because more indexes must be updated.
Because of the added index time and disk space costs for creating a CTXCAT index, before adding it to your index set, carefully consider the query performance benefit that each component index gives your application.
♦️Creating a Search Index for JSON
Oracle Text supports a simpler alternative syntax for creating a search index on JavaScript Object Notation (JSON). The JSON search index is created on the table column name.
Creating an Oracle Text Search Index
You can create a CONTEXT index using a simplified SEARCH INDEX syntax.
The Oracle Text SEARCH INDEX is a new index type which supports CONTEXT index functionality but also supports sharded databases and system managed partitioning for index storage.
♦️About Automatic Maintenance
Index maintenance is the process of updating index data structures (in-memory and on-disk) as a result of performing DML operations.
Overview
Indexes with automatic maintenance are synchronized in the background without any user intervention.
♦️Automatic maintenance is the default method for synchronizing Oracle Text CONTEXT and search indexes (Oracle Text, JSON, and XML search indexes) that are created in Oracle Database 23ai and later releases.
Both the automatic maintenance and synchronization (SYNC) methods involve processing pending updates, inserts, and deletes to the base table.
However, the automatic maintenance and SYNC specifications are orthogonal.
Automatic maintenance uses an asynchronous maintenance framework to perform SYNC operations in the background, and provides the following capabilities:
- Eliminates time-based or manual SYNC operations:
In an automatic maintenance mode, IRnn background processes automatically perform index maintenance operations in an optimal manner. This feature internally determines an optimal synchronization interval (based on the DML arrival) and automatically schedules background SYNCoperations, as required. You cannot override the automatically determined intervals. - Reduces the frequency of background jobs:
Background processes maintain indexes rather than the database scheduler. The background mechanism breaks each CTX_DDL.SYNC_INDEX operation into separate events (sync stages) and launches each event only when needed.
- Provides the default maintenance configuration:
These indexes do not require you to configure a SYNC type or set any synchronization interval. By default, indexes are configured with a combination of automatic maintenance and SYNC (MANUAL). No other SYNC settings are compatible with these indexes.
Note that the SYNC (MANUAL) behavior is different in this mode. Unlike the regular SYNC (MANUAL) type (where you must manually synchronize an index), here CTX_DDL.SYNC_INDEX is automatically called in the background.
♦️Why and When to Use Automatic Maintenance?
Oracle recommends that you use automatic maintenance in cases where sync requirements for indexes are not clear or you want to synchronize a large number of indexes in an optimal manner.
In addition to reducing the administrative tasks of managing your indexes, the benefit of using this framework is that it automatically determines when a background SYNC operation needs to be performed, by tracking the DML queue. It also provides more control over the frequency of different background jobs running at any given time, instead of creating independent jobs for each index or index partition per pluggable database (PDB). As a result, automatic maintenance helps in reducing the workload on database resources, eliminates scheduling conflicts, and enhances query performance.
With SYNC (EVERY), which also enables automatic background synchronization, you must manually specify sync interval using interval-string. Although SYNC (EVERY) allows you to explicitly control the synchronization interval, automatic maintenance provides an efficient usage of database resources especially when supporting multiple PDBs. In addition, SYNC (EVERY) may result in excessive launching of background sync jobs, based on the user's estimate of how frequently new index data may arrive.
♦️Rebuilding an Index
You can rebuild a valid index by using ALTER INDEX.
♦️Rebuilding an index does not allow most index settings to be changed. You might rebuild an index when you want to index with a new preference.
♦️Generally, there is no advantage in rebuilding an index over dropping it and re-creating it with the CREATEINDEX statement.
An ALTER INDEX REBUILD 'rebuild_params' statement rebuilds the index using supplied parameters. Spatial index creation involves creating and inserting index data, for each row in the underlying table column being spatially indexed, into a table with a prescribed format. All rows in the underlying table are processed before the insertion of index data is committed, and this requires adequate rollback segment space.
The ONLINE keyword rebuilds the index without blocking the index; that is, queries can use the spatial index while it is being rebuilt. However, after all queries issued during the rebuild operation have completed, you must clean up the old index information by entering a SQL statement in the following form:
ALTER INDEX [schema.]index REBUILD ONLINE PARAMETERS ('index_status=cleanup');
The following limitations apply to the use of the ONLINE keyword:
- Only query operations are permitted while the index is being rebuilt. Insert, update, and delete operations that would affect the index are blocked while the index is being rebuilt; and an online rebuild is blocked while any insert, update, or delete operations that would affect the index are being performed.
- You cannot use the ONLINE keyword for a rebuild operation if the index was created using the 'sdo_non_leaf_tbl=TRUE'parameter.
- You cannot use the ONLINE keyword for a partitioned spatial index.
The following example rebuilds OLDINDEX and specifies the tablespace in which to create the index data table.
ALTER INDEX oldindex REBUILD PARAMETERS('tablespace=TBS_3');
🔆🔆🔆🔆🔆🔆🔆🔆🔆🔆🔆🔆
💢Finally,
I hope you have come to the conclusion that rebuilding the index at any time will not solve your performance problems and it is better to focus on modifying the structure of the tables , schema design and the type of indexes that can be used and focus on quey execution plans and selectivity of your indexes with considering CF, also with improving the standard infrastructure you can achieve to create better indexes to get better performance.
-------------------------------------
I hope this tutorial be useful for you.
Alireza Kamrani
