Discuss Oracle exists usage __oracle

Oracle exists problems are frequently encountered when learning Oracle, and this will address the Oracle exists solution. Oracle uses a complex, self-balanced b-tree structure. In general, querying data through an index is faster than full table scans. The Oracle optimizer uses indexes when Oracle finds the best path to execute queries and UPDATE statements. It is also possible to use indexes when joining multiple tables to improve efficiency.

Another advantage of using the index is that he provides uniqueness validation for the primary key (primary key). Those long or long raw data types, you can index almost any column. In general, using indexes in large tables is particularly effective. Of course, you'll also find that using indexes can also improve efficiency when scanning small tables.

Although using the index can improve query efficiency, we must also pay attention to his price. Indexes require space for storage and regular maintenance, and the index itself is modified whenever a record is added or subtracted from the table or the index column is modified. This means that for each record insert,delete, update will pay 4 or 5 more disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow down query response times. Periodic refactoring of indexes is necessary:

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

replace distinct with Oracle exists:

Avoid using DISTINCT in the SELECT clause when submitting a query that contains a one-to-many table of information, such as a department table and an employee table. It is generally possible to consider Oracle exist replacements, and Oracle exists makes queries faster because the RDBMS core module returns the results as soon as the criteria for the subquery are met. Example:

Select DISTINCT dept_no,dept_name from DEPT D, EMP E WHERE d.dept_no = e.dept_no Select Dept_no,dept_name from DEPT D WH ERE Exists (SELECT ' X ' from EMP E WHERE e.dept_no = d.dept_no);

SQL statements are capitalized, because Oracle always resolves SQL statements first, and converts lowercase letters to uppercase executions.
Use the connector "+" connection string sparingly in Java code.
Avoid using not on indexed columns normally, we want to avoid using not on indexed columns, and not the same effect as using functions on indexed columns.
When Oracle "encounters" not, he stops using the index instead of performing a full table scan.
Avoid using calculations on indexed columns. In the WHERE clause, if the indexed column is part of the function. The optimizer uses a full table scan without indexing.

======================================

Http://database.51cto.com/art/200911/164017.htm