Oracle query operation considerations

Oracle query operation considerations

1. Fuzzy search
To use the wildcard % and _ as character values, you must use the Escape Character After escape. For example:
-- Query records starting with 'pt _ 'in the g3e_username Field
Select * From g3e_dialogtab where g3e_username like 'pta _ % 'escape 'A'

2. Use of the order by clause
When a SELECT statement contains multiple clauses (where, group by, having, and order by), order by must be the last clause.

3. Questions about multi-Table insertion
Use the all operator to insert multiple tables
Insert all
When deptno = 10 then into dept10
When deptno = 20 then into dept20
When deptno = 30 then into dept30
When job = 'cler' then into cler'
Else into other
Select * from EMP;

Use first to insert multiple tables
Use the first multi-table insert method. If the data already meets the previous conditions and has been inserted into a table, the modified data will not be used again in subsequent inserts.
Insert first
When deptno = 10 then into dept10
When deptno = 20 then into dept20
When deptno = 30 then into dept30
When job = 'cler' then into cler'
Else into other
Select * from EMP;

4. Use of group by and having
When you use the group by clause for grouping statistics, columns in the selected list must appear in the group by clause. You can only use having to restrict the display results of groups (you cannot use the WHERE clause ).

5. Use rollup or cube in the group by clause
Select operator, district, sum (cur_conduit_len) from n9_rpt_userdata group by rollup (operator, district); -- perform a total operation for operator by district
Select operator, district, sum (cur_conduit_len) from n9_rpt_userdata group by cube (operator, district); -- vertical statistics not only total operator, but also District

6. Use of the grouping Function
This function is used to determine whether the statistical result uses a specific column. If it is used, it is represented by 1. Otherwise, it is 0.
Select operator, district, sum (cur_conduit_len), grouping (operator), grouping (district) from n9_rpt_userdata group by rollup (operator, district );

7. Use of the grouping sets Operator
Used to display group statistics
Select operator, district, sum (cur_conduit_len) from n9_rpt_userdata group by grouping sets (operator, district );

8. Use of the (+) Operator
1) the (+) operator can only appear in the WHERE clause and cannot be used with the outerjoin syntax.
2) When an external join is executed using the (+) operator, if the WHERE clause contains multiple conditions, the (+) operator must be included in all conditions.
The second error impressed me a lot. I wrote a statement before, because the WHERE clause contains multiple conditions, but the (+) operator is not specified, result errors.

Select B. district sub-region,
Sum (decode (A. Ownership, 'user-created ', 1, 0) number of self-built CIDR blocks,
Sum (decode (A. Ownership, 'user-created ', 0, 1) number of other CIDR blocks,
Sum (decode (A. Ownership, 'user-created ', A. length, 0) self-built CIDR Block length,
Sum (decode (A. Ownership, 'user-created ', 0, A. Length) other CIDR Block lengths
From B $ I _hangline_info A, B $ gc_netelem B
Where a. g3e_fid (+) = B. g3e_fid
And a. feature_state <> 'delete' -- this condition was added later. It is the addition of this condition that leads to a statistical result error.
And B. district in ('baoshan ', 'changning', 'chongming ', 'fengxian', 'hongkou ', 'huangpu', 'jiading ', 'jinshan', 'jing'an ', 'luwan ', 'putuo', 'pudong ', 'qingpu', 'songjiang', 'xuhui ', 'yangpu', 'zhabei ', 'minhang ')
Group by B. District;

The query results of the preceding statements are incomplete because an external connection is used. the condition feature_state <> 'delete' does not have an external join operator. As a result, the statistical value of the first column in the statistics is 0, although the result after this column is not 0. you only need to modify the following to display the query results:
Select B. district sub-region,
Sum (decode (A. Ownership, 'user-created ', 1, 0) number of self-built CIDR blocks,
Sum (decode (A. Ownership, 'user-created ', 0, 1) number of other CIDR blocks,
Sum (decode (A. Ownership, 'user-created ', A. length, 0) self-built CIDR Block length,
Sum (decode (A. Ownership, 'user-created ', 0, A. Length) other CIDR Block lengths
From B $ I _hangline_info A, B $ gc_netelem B
Where a. g3e_fid (+) = B. g3e_fid
And a. feature_state (+) <> 'delete'
And B. district in ('baoshan ', 'changning', 'chongming ', 'fengxian', 'hongkou ', 'huangpu', 'jiading ', 'jinshan', 'jing'an ', 'luwan ', 'putuo', 'pudong ', 'qingpu', 'songjiang', 'xuhui ', 'yangpu', 'zhabei ', 'minhang ')
Group by B. District;

3) the (+) operator can only be applied to columns, but cannot be used in expressions.
4) the (+) operator cannot be used together with the OR and in operators.
5) the (+) operator can only be used to implement left Outer Join or right outer join, but not to implement full outer join.

9. Use all or any in multi-row subqueries
Single-Column single-row or single-column multi-row subquery
1) Use the all operator
The all operator must be used in combination with the single-row operator, and the returned row must match the results of all subqueries.
Select * From g3e_attribute where g3e_ano> All (select g3e_ano from g3e_tabattribute where g3e_dtno = 247303381 );

2) use the any operator
The all operator must be used in combination with the single-line operator, and the returned row only needs to match any result of the subquery.
Select * From g3e_attribute where g3e_ano> Any (select g3e_ano from g3e_tabattribute where g3e_dtno = 247303381 );

Multi-column multi-row subquery
Paired comparison
Select * From I _exch_info where (gwm_fid, gwm_fno) in (select gwm_fid, gwm_fno from gc_netelem where gwm_fno = 25057 );

Non-paired comparison:
Select * From I _exch_info where gwm_fid in (select gwm_fid from gc_netelem where gwm_fno = 25057) and gwm_fno in (select gwm_fno from gc_netelem where gwm_fno = 25057 );

10. Pay attention to using minus and intersect in queries.
Minus can replace not in or not exists; Intersect can replace and