Oracle Big Data Query optimization

1, for columns like the state, not many, you can add bitmap index, for unique columns, add a unique index, the rest of the creation of normal indexes.

2. Try not to use a query such as SELECT * To specify the columns you want to query.

3. Use hits Select/*+index (index name) index (index name) */supply_id from css_supply where ...

4. Redefine table tables as partitioned tables, use hash partitions or range partitions on the Name column

5, if it is exedata platform, do not use index, try to use less hint;

6, in parallel;

---------------------

| Index of Oracle |

---------------------

Oracle provides a number of indexing options that use indexes to speed up queries, reduce I/O operations, and eliminate disk sorting, so under what circumstances will indexing work? Normally indexed rules [2] can be described as follows: (1) The primary and foreign keys of a table must be indexed, and (2) The connection fields of tables that are frequently connected to other tables should be indexed; (3) The fields that often appear in the WHERE clause should be indexed (especially for tables with large data volumes); (4) Fields with high selectivity should be indexed, and the selectivity of the index refers to the ratio of the number of different values in the index column to the number of records in the table. If there are 1000 records in the table, the table index column has 950 different values, then the selectivity of this index is 0.95. The best possible choice is 1.0, based on a unique index of a non-null value column, usually with a selectivity of 1.0. (5) The small section should be indexed, for longer text fields or even long fields, do not index; (6) The establishment of composite indexes needs to be carefully analyzed, as far as possible to consider using single-field index instead; ① the correct selection of the main column field in the composite index is generally preferable to select "Selective" field; ② composite Index Are fields often present in the WHERE clause with and? Are there very few or no queries for single fields?     If either of the two cases or both, you should create a composite index, otherwise consider the single-field index; ③ if the fields contained in the composite index often appear separately in the WHERE clause, the ④ composite Index contains more than 3 fields, otherwise it needs to be carefully considered; ⑤ If you have both a single-field index and a composite index that contains these fields, you can generally delete the composite index; ⑥ the special case of building a composite index: if the data in the table is fairly stable and the fields are not large, consider fully indexing a table, that is, creating a composite index that includes all the columns that are typically selected during the query. All data requested by the query can be provided through index access, avoiding any subsequent table accesses to the index scan.

(7) Frequently insert, delete operations of the table, do not set up too many indexes, (8) Delete useless index, to avoid negative impact on the execution plan;

These are some of the more common criteria for establishing indexes. Too many indexes and insufficient, incorrect indexes are not good for performance because each index established on the table increases the storage overhead, and the index increases processing overhead for insert, delete, and update operations. In addition, too many composite indexes, in the case of single-field index, generally have no value; Conversely, it also reduces performance when data is being deleted, especially for tables that are frequently updated, with greater negative impact.

---------------------

| optimization of SQL statements |

---------------------

By using indexes correctly, you can use resources to make the data query faster. However, after indexing does not mean that the speed of the query is improved, it is also necessary to have a good SQL statement support, in order to make use of the index in the query to improve the speed of the query. The following is an analysis of how to improve query speed by using indexes in SQL statements.

(1) is null and is not NULL cannot be indexed with NULL, and any column containing null values will not be included in the index. Even if the index has multiple columns, the column is excluded from the index as long as there is a column in the column that contains null. This means that if a column has a null value, even indexing the column does not improve performance. Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

(2) Join columns for columns with joins, the optimizer does not use the index even if the last join value is a static value. For example, the following SQL statement SELECT * from oil where oil_name| | ' ' | | oil_id = ' sheng Tuo shengtuo ';

To query the oil field as "Shengtuo" of the oil field data, assuming that the oil_id column has been indexed, although the SQL statement can also implement the correct query, but the system optimizer on the oil_id-based index is not used. If you change to the following SQL statement, select * from oil where oil_name = ' sheng Tuo ' and oil_id= ' Shengtuo ';

Oracle systems can take an index created based on oil_id.

(3) A like statement with a wildcard (%)

This is also the case with the above example. The current demand is such that the oil table should be queried for the names containing the oils of the winning lump.     You can use the following query SQL statement: SELECT * FROM employee where oil_name like '% sheng Tuo% '; The wildcard character (%) in the above SQL statement appears at the beginning of the search term, so the Oracle system does not use the oil_id index. In many cases, however, this may not be possible, but it is necessary to understand that using wildcards in this way can slow down queries. However, when wildcards appear elsewhere in the string, the optimizer can take advantage of the index, for example, the following query can take advantage of the index: SELECT * from the employee where Oil_name like ' sheng Tuo% ';

(4) The ORDER BY statement determines how Oracle will sort the returned query results. The ORDER BY statement has no special restrictions on the columns to be sorted, or it can be added to a column (like joins or additions, etc.). Any non-indexed item in the ORDER BY statement, or a computed expression, will slow down the query. Double-check the order BY statement to find non-indexed items or expressions that degrade performance. The solution to this problem is to rewrite the order BY statement to use the index, or you can establish another index for the column you are using, and you should absolutely avoid using an expression in the ORDER BY clause.

(5) Not in queries, we often use logical expressions in the WHERE clause, such as greater than, less than, equal to, and not equal to, and also use and (and), or (or), and not (non). Not can be used to negate any logical operation symbol, such as ... where not (oil_id= ' Shengtuo ') if you want to use not, you should precede the negation phrase with parentheses and precede the phrase with the NOT operator. The NOT operator is included in another logical operator, which is the not equal to (<>) operator. It is also possible to say that not is not in the operator even if the not word is not explicitly added to the query where clause.

Look at the following two SQL statements:

SELECT * from oil where productoil <>3000; SELECT * from oil where productoil <3000 or Productoil >3000;

Although the results of these two queries are the same, the second query scenario is faster than the first query scenario. The second query allows Oracle to use indexes on productoil columns, while the first query cannot use indexes.

(6) in and exists

In the query, we also often use two columns to compare the case, the simplest way is to use a subquery in the where sentence, and this seed query generally have two forms. One is to use the In operator: .... where column in (SELECT * from ...); The other is to use the exist operator: ... where exists (select ' X ' from ...);

For subqueries in both formats, the first is a query with the in operator, because the statement is easier and more intuitive, but it is actually more efficient for the second form of subqueries.     In Oracle, almost all in-operator queries can be rewritten as subqueries that use exists. In the second format, the subquery begins with ' select ' X '. Using the EXISTS clause no matter what data the subquery extracts from the table it only looks at the characteristics of the WHERE clause, so that the optimizer does not have to traverse the entire table and only the index to complete the work (this assumes that the column used in the WHERE statement has an index).     As opposed to the in clause, exists uses a concatenated subquery, which is more difficult to construct than in subqueries. By using the exists,oracle system, the primary query is checked first, and then the subquery is run until the first match is found, which saves time. When the Oracle system executes an in subquery, the subquery is executed first, and the resulting list is stored in an indexed temporary table. Before executing a subquery, the system first suspends the primary query, and executes the subquery until it has been executed, and then performs the main query after the query is held in the temporary table. This is why using exists is faster than using in usually queries.

---------------------

| Temporary Tables |

---------------------

At present, most of the system data using Oracle as a database is large, we often use a number of tables associated with the situation, and these tables are much larger, But when it comes to associating with a table or a few tables associated with a small result set and a very fast query of the result set, then we can consider creating a "temporal table" in Oracle.  This makes it much faster to query the temporary table when the data is used multiple times in the project, and when the data in the table is exhausted, the Oracle's temporary table is created with no table space. Unlike using a permanent table, adding or changing data in a temporary table does not generate a redo log entry, but it generates an undo log entry. Another difference between a permanent table and a temporary table is the allocation of fragments. Temporary tables use temporary fragments, and no temporary fragments are allocated to the table until the data is actually inserted in the table.

There are two types of temporal tables for an Oracle database system, one is a transactional temporary table and one is a session-type temporary table. A temporal table of things is the emptying of the contents of a temporary table when a transaction ends, while a session temporary table is emptied when a session is interrupted or the data table is reconnected. When the Oracle database clears the staging table, it clears only the data and does not know the temporary table itself. Oracle also provides a more granular transactional temporal table. There may be more than one transaction in a session. In other words, Oracle empties temporal data tables more finely, and can purge temporary tables based on different transactions in the same session. It is also important to note that Oracle's conversational ad content is independent for individual users. Specifically, each user can store data in a temporary table while the session is in progress, but when the user queries the data in the staging table, they can only query what they have created, and cannot see the records added by other users.     This is extremely secure for temporary table security. Temporary tables also have limitations, such as:

(1) The temporary table is valid only within the current connection;

(2) The temporary table is not indexed, so if the amount of data is large or multiple queries, it is not recommended;

(3) The data processing is more complicated when the time is faster, the converse view is fast.

---------------------

| Summary |

---------------------

In summary, in the simple query, make full use of the index, and reasonable optimization of SQL statements to effectively use the index, can reduce response time. The use of temporal tables in engineering reduces the amount of data access and improves the efficiency of database operations, thus reducing response time. However, the above optimization methods are used as reference, the optimization must be adjusted with the change of the system application and the change of data quantity, and the optimization method can be adopted flexibly to improve the efficiency of the system effectively.