Oracle automatically makes simple type conversions to columns when comparing data of different data types.
Suppose Empno is an indexed column of a numeric type.
SELECT ...
From EMP
WHERE EMPNO = ' 123 '
In fact, after Oracle type conversion, the statement translates to:
SELECT ...
From EMP
WHERE EMPNO = to_number (' 123 ')
Fortunately, the type conversion did not occur on the index column, and the purpose of the index was not changed.
Now, suppose Emp_type is an indexed column of character type.
SELECT ...
From EMP
WHERE Emp_type = 123
This statement is converted by Oracle to:
SELECT ...
From EMP
WHERE to_number (Emp_type) =123
This index will not be used because of a type conversion occurring internally!
Translator by:
To avoid an implicit type conversion of Oracle to your SQL, it is a good idea to explicitly display the type conversion. Note that when characters and numeric comparisons are compared, Oracle converts numeric types to character types preferentially.
45. The WHERE clause needs to be careful
The WHERE clause in some SELECT statements does not use an index. Here are some examples.
In the following example, '!= ' will not use the index. Remember, an index can only tell you what exists in the table, not what doesn't exist in the table.
Do not use indexes:
SELECT account_name
From TRANSACTION
WHERE AMOUNT!=0;
Use index:
SELECT account_name
From TRANSACTION
WHERE AMOUNT >0;
In the following example, ' | | ' is a character join function. As with other functions, the index is deactivated.
Do not use indexes:
SELECT Account_name,amount
From TRANSACTION
WHERE account_name| | Account_type= ' Amexa ';
Use index:
SELECT Account_name,amount
From TRANSACTION
WHERE account_name = ' AMEX '
and account_type= ' A ';
In the following example, ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated.
Do not use indexes:
SELECT account_name, AMOUNT
From TRANSACTION
WHERE AMOUNT + 3000 >5000;
Use index:
SELECT account_name, AMOUNT
From TRANSACTION
WHERE AMOUNT > 2000;
In the following example, the same indexed columns cannot be compared to each other, which enables full table scans.
Do not use indexes:
SELECT account_name, AMOUNT
From TRANSACTION
WHERE account_name = NVL (: acc_name,account_name);
Use index:
SELECT account_name, AMOUNT
From TRANSACTION
WHERE account_name like NVL (: acc_name, '% ');
Translator by:
If you must enable indexing on columns that use functions, Oracle's new functionality: A function-based index may be a better solution.
CREATE INDEX emp_i on EMP (UPPER (ename)); /* Establish a function based index * *
SELECT * from emp WHERE UPPER (ename) = ' blacksnail '; /* will use 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.