Oracle index details-2

Oracle index details-2 oracle index details-1 http://www.bkjia.com/database/201304/205591.html I. Index restriction: Index restriction is one of the common mistakes that some inexperienced developers often make. Many traps in SQL statements will make some indexes unusable. Some common problems are discussed below: 1. Use non-equals operators (<> ,! =): Even if the following query has an index in the cust_rating column, the query statement executes a full table scan. Select cust_id, cust_name from MERs where cust_name <> 'a'; if you write the preceding statement as the following query statement, if you use a rule-based optimizer instead of a cost-based optimizer, indexes will be used. Select cust_id, cust_name from MERs where cust_name> 'A' or cust_name <'aa'; when the symbol is not equal to or, the index can be used, to avoid full table scanning. 2. Use is null or is not null: Using is null or is not null also limits the use of the index, because the NULL value is not defined, using null in SQL statements can cause a lot of trouble. Therefore, when creating a table structure, developers can set the column to NOT NULL. If the index column contains NULL values in some rows, no index is used (unless this index is a bitmap index ). 3. Using Functions: If function-based indexes are not used, the optimizer will ignore these indexes when using functions for columns with indexes in the WHERE clause of SQL statements. The query in the following example does not use an index (as long as it is not a function-based index) select empno, ename, deptno from emp where trunc (hiredate) = '01-MAY-81 '; after the preceding statement is rewritten as the following statement, select empno, ename, deptno from emp where hiredate <(to_date ('01-MAY-81 ') can be indexed ') + 0.9999); 4. Unmatched data type: it is also one of the performance problems that are difficult to find. Note that in the following query case, account_number is of the VARCHAR2 type and has no index on account_number. The following statement performs a full table scan: select bank_name, address, city, state, zip from banks where account_number = 99035; this limits the use of indexes, oracle automatically changes the where clause to to_number (account_number) = 99035. You can use the following statement to change it to select bank_name, address, city, state, zip from banks where account_number = '000000'; Note: Comparing unmatched data types automatically limits the use of indexes in oracle. 2. oracle rowid uses the rowid of each row to index oracle to provide the ability to access a single row of data. rowid specifies the road map to access a single row, it can be used to check duplicate values or reference rowid itself. You can use and specify rowid columns in any table.