2016.9.9 "Oracle query optimization rewriting techniques and cases" skills in the Book of Electronics Industry Publishers

1, COALESCE (C1,C2,C3,C4,...) is similar to NVL but can return the first non-null value from multiple expressions

2. To refer to the alias of a column in the Where condition, you can nest a layer of queries again

SELECT * FROM (select salary gz from person) where gz>100

3. The wildcard character of the like () function in addition to the% extra, and _ represents a character, to be escaped in the like expression _ Symbol:

The like (' \_bcd ') escape ' \ ' escape is used to define the escape character, at which point the ' \ \ ' represents the true ' \ ' symbol

4. Numbers such as order by 1 or 2 indicate sort by number of columns (count (1) is it similar? ）

5, substr (aa,-3) to take the last 3 digits, but not to specify the starting position, for example, can not use substr (aa,3,-2)

6, sorting when the control of the null record before or after: Order by xx nulls first (nulls last)

7. With. As ... Create a temporary view that exists only when the statement executes

With e as (select ad_hp_id ID, code_id code, txt_name name from ad_hp where code_id like ' zb% ')

Select Id,code, name from E

8. The WHERE and order statements of the LEFT join can be written after the on statement

9, the internal connection is equivalent to the direct where two table fields are equal, only two tables matching data appears

Fully connected two tables all records appear, mismatched items are displayed as null

The left and right connections can be written in the where association, just like the normal two tables, but with the (+) in front of the table condition:

Select a.code_id,b.* from AD_HP A, Bureau b

where a.code_id like ' zb% '

and a.txt_name_admin = B.bureau_code (+)

ORDER by a.code_id

10, 11g after the addition of a number of characters in the statistical string function Regexp_count (' ABABCABCD ', ' a '), the result =3

11, after sorting by a column, take its maximum or minimum value corresponding to the other column max (col1) Keep (Dense_rank first or last order by col2)

If you want to group Intrastat, you can use over (partition by col3). Group by group is also available

Select Max (code_id) Keep (Dense_rank First order by Geo_lat) from AD_HP t where code_id like ' zb% '

12. In the tree data structure, enumerate all the descendants of a node (chapter 12th)

Select Empno as employee code, ename as name, Mgr as Supervisor code, (prior ename) as Supervisor name, Sys_connect_by_path (ename, ', ') as Path

From Scott.emp

Start with empno=7566

Connect by (prior empno) =mgr

2016.9.9 "Oracle query optimization rewriting techniques and cases" skills in the Book of Electronics Industry Publishers