Oracle Learning Notes (iv)

First, the principle of efficient SQL statement

• In where = relational operation
• Avoid using functions in relational operations, unless you use a function to make an index
• Try not to implicitly convert data types, data types must match
• Try to divide a sentence of SQL into multiple statements to complete
• Do not differential, use union ALL to combine
• The sub-query Exist is suitable for the appearance of small, inner table large in suitable for large appearance, inner table small
• Considerations for using Views
• The connection of the complex view should be careful, especially when there are foreign keys
• When querying a partial table referenced in a view, do not use the view, or create a new, smaller table
• Storing intermediate results
• For a result set that may be called multiple times in a query, save
• Consider using materialized views
• Phased completion of complex queries that cannot be optimized
• Minimizing the number of data accesses
• Using Case statements
• Using Advanced grouping Rollup,cube
• Using Stored Procedures
• Using the returning clause

Second, full table scan

• Full table Scan: Read the data blocks below the high watermark.
• The Db_file_multiblock_read_count parameter determines the speed of the scan, and the value of the parameter is multiplied by the block
• Size should be less than the operating system's maximum IO
• Because the range is contiguous blocks, the full table scan is read continuously and is highly efficient.

Iii. when to use full table scan

1. Small table

2. Index missing, no index on condition determinant column

3. Use hint to force full-table scanning

4. Reading data is significantly larger. Generally more than 10% of the data will be read to select a full table scan

5. Parallel query, and index is opposite, both must choose a

Four, full table scan optimization
1. Recovery of high water mark line
2.db_file_multiblock_read_count enlargement
3. Make more data per block, reduce pctfree
4. Using parallel queries
alter system set DB_FILE_MULTIBLOCK_READ_COUNT=32; Modify parameter configuration, default is 16 blocks
ALTER TABLE T1 Pctfree 0; Fill each block of data
ALTER TABLE T1 move tablespace users; Move table Space

V. Optimization of database query, Hints
Hints, the prompt, forces the statement to run in the manner we specify, scoped to the current statement, without affecting the subsequent statement. When writing, be sure to put it behind the first word (select). There are two types of Hints:/*+ hint */,--+ hint

Vi. SQL Query optimization

1. Try to avoid using the like '%parm1% ' percent sign in a complex query to cause the index of the related column to be unusable, preferably not.

• • Input search for drop-down boxes on the front-end
  • Query scope control at the backend without querying irrelevant data

2. Index

• • When the data volume is large, the index is built
  • Avoid computing operations on indexes
  • Avoid using not on indexed columns, <>! =
  • Avoid using is null and is not NULL on indexed columns
  • Avoid using data type conversions on indexed columns
  • . Avoid using functions on indexed columns
  • Avoid using null values on indexed columns

3. Complex queries can be split into complex queries in step

4. Use UNION ALL Union as much as possible to sort the data and remove duplicate rows, compare the consumed resources union all without sorting, not removing duplicate rows

5. In the WHERE statement reasonable use in, not in, exist,not exist exist suitable for small appearance, inner table large in suitable for large appearance, inner table small

6. Sorting avoids the use of resource-consuming operations, with distinct, uinon,minus,intersect,order by statements start the SQL engine, distinct need to be sorted one at a time, while others need to perform at least 2 sorting.

7. Temporary tables use temporal tables cautiously and can greatly improve performance.

Oracle Learning Notes (iv)