How to improve query performance in oracle?

Let's take a closer look at what we're talking about.

The index of a database is a data structure that improves the speed of operations, allowing quick access to the records of a table, which is why they are usually used on those fields on which frequent searches are going to be carried out since its operation is similar to the index of a book: saving pairs of elements to be indexed together with their position in the database, so that to search for an element that is indexed, we only need to search the index of said element to , once found, return the record that is in the position marked by the index.

The database can be prepared for both fast random searches and efficient sorting of records by using one or more columns.

The B, B+, B* trees are used to build the indices.

The disk space required to store the index is typically less than the storage space of the table (since indexes generally contain only the key fields on which the table will be sorted, and exclude all other details of the table). table), which gives the possibility of storing in memory the indexes of tables that would not fit in it. An index is a copy of a table.

The size of the index will be shown by the following query, which will show the size in megabytes of the index.

The size occupied by an index is shown in the following query.

It's important to periodically examine and determine which indices are likely to be reconstructed.

Some parts of an index may be unbalanced because they are accessed more often than others.

The ALTER INDEX command is used to rebuild an index and change an existing index in a database.

If you want to execute this command, you must have the alter any index privilege, or the index must be in the schema where you are trying to execute it. In order to rebuild an index, we need enough quota on the tablespace that we launch it from.

It would be enough to launch the following statement to rebuild an index.

We could rebuild a partition of the index.

It's not possible to rebuild an index that has some kind of corruption. In this case, the solution is to remove the index.

Various actions are carried out to achieve delivery of the result when an "SQL" statement is received from an oracle database.

The CBO is one of the most important steps among the various steps. In order for the "CBO" to accurately determine the execution plan for a "SQL Query", he must have the information on the statistics of the tables and indexes that participate in the "SQL Query", this information is commonly known as "Optimizer statistics" "Optimizer statistics", it describes how the object is composed and distributed internally.

The best execution plan for each statement is decided by these statistics.

It can take a long time to collect the statistics. Various methods can be used in the handler to try to shorten the time of this task.

Keeping the statistics updated is important.

If we want to know if the statistics are being launched correctly, we can make a query on the ALL_INDEXES table in oracle 11 and see when the statistics were executed on the index.

Note: the column “LAST_ANALYZED” which can be found in views such as: “DBA_TABLES”, “DBA_INDEXES”, “DBA_TAB_COL_STATISTICS” indicates the date the statistics for that object were last calculated.

If we want to know when the last statistics were executed on all the tables belonging to a certain database, we will launch the following query.

The table name from all of the Indras should be selected.