Some query statements in Oracle and their execution sequence

Some query statements in Oracle and their execution sequence

Some query statements in Oracle and their execution sequence

Query condition:

1) LIKE: fuzzy query. Two Wildcards are required. %: represents 0 to multiple characters; _: identifies a single character.

2) IN (list): used to retrieve data that matches the list range.

3) not in (list): retrieve data records that do NOT match the list.

4)... AND... : Used to query data that meets the conditions of a value range. The most common method is to use data range of the numeric type, but also applies to data of the character type and date type. For example: SELECT ename, sal FROM emp WHERE sal BETWEEN 1500 AND 3000;

(5) is null: null is a special value. "=" cannot be used for comparison. "is null" must be used; otherwise, no correct result can be obtained.

6) is not null: opposite to 5.

7)> ANY: greater than the minimum <ANY: less than the maximum> ALL: greater than the maximum <ALL: less than the minimum

For example: SELECT empno, ename, job, sal, deptno FROM emp WHERE sal> ANY (345, 3333, 123)

8) DISTINCT: duplicate filter. For example: select distinct deptno FROM emp

9) order by: sort the queried data according to certain rules. ASC specifies the Ascending ORDER and DESC specifies the descending ORDER.

10) group by: GROUP the data according to the given fields. For example, divide the entire data table into groups BY department.

11) HAVING: The HAVING clause is used to further limit the results after grouping. For example, if you get the highest salary for each department after grouping by department, you can continue to limit the output results. It must be followed by group by and cannot exist independently. Example: SELECT deptno, MAX (sal) max_sal FROM emp group by deptno having max (sal)> 4000;

Query statement execution sequence:

1. FROM clause: The execution sequence is FROM backward to backward and FROM right to left. Tables with a small amount of data should be placed behind them as much as possible.

2. WHERE clause: The execution sequence is bottom-up, from right to left. Write the conditions that can filter out the maximum number of records in the rightmost of the WHERE clause.

3. group by: the execution sequence is grouped from left to right. It is best to use WHERE before group by to filter out unnecessary records before group.

4. HAVING clause: consumes resources. Avoid using it whenever possible. HAVING filters the result set only after all records are retrieved, and requires sorting and other operations.

5. SELECT clause: Use less "*" and use the field name whenever possible. During ORACLE parsing, the * number is converted into all column names by querying the data dictionary, which consumes time.

6. order by clause: the execution ORDER is from left to right, consuming resources.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Install Oracle 11.2.0.4 x64 in Oracle Linux 6.5