oracle| Performance | optimization
36. Replace or with union (applicable to indexed columns)
In general, replacing or in a WHERE clause with union would have a better effect. Using or for an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, query efficiency may be reduced because you have not selected an OR.
In the following example, indexes are built on both loc_id and region.
Efficient:
SELECT loc_id, Loc_desc, REGION
From LOCATION
WHERE loc_id = 10
UNION
SELECT loc_id, Loc_desc, REGION
From LOCATION
WHERE REGION = "MELBOURNE"
Low efficiency:
SELECT loc_id, Loc_desc, REGION
From LOCATION
WHERE loc_id = ten OR REGION = "MELBOURNE"
If you insist on using or, you need to return the least recorded index column to the front.
Attention:
WHERE KEY1 = 10 (returns minimum records)
OR KEY2 = 20 (returns maximum Records)
ORACLE internally converts the above to
WHERE KEY1 =
(Not KEY1 = ten) and KEY2 = 20)
Translator by:
The following test data is for reference only: (A = 1003 Returns a record, b = 1 returns 1003 records)
The effect of union can be seen from the decrease of data exchange amount of consistent gets and sql*net
37. Replace or with in
The following query can be replaced by more efficient statements:
Low efficiency:
SELECT ....
From LOCATION
WHERE loc_id = 10
OR loc_id = 20
OR loc_id = 30
Efficient
SELECT ...
From LOCATION
WHERE loc_in in (10,20,30);
Translator by:
This is a simple and easy to remember rule, but the actual execution effect also needs to examine, under Oracle8i, the two execution path seems to be the same.
38. Avoid using is null and are NOT NULL on indexed columns
To avoid using any nullable columns in the index, Oracle will not be able to use the index. For Single-column indexes, this record will not exist in the index if the column contains a null value. For composite indexes, if each column is empty, the record also does not exist in the index. If at least one column is not empty, the record exists in the index.
Example:
If the uniqueness index is based on the columns A and B of the table, and the A,b value for a record exists in the table (123,null), Oracle will not accept the next record (insert) with the same a,b value (123,null). However, if
All indexed columns are empty, and Oracle will assume that the entire key value is empty and empty is not equal to NULL. So you can insert 1000
Records with the same key value, of course they are empty!
Because a null value does not exist in an indexed column, a null comparison of an indexed column in a WHERE clause causes Oracle to deactivate the index.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
