SQL Optimization (Oracle)-Part II common SQL usage and considerations

Part two common SQL usage and considerations
1. Exists and in
2. Union and UNION ALL
3. With AS
4. ORDER BY
5. GROUP BY
6. Where and having
7. Case and Decode

1.exits and in usage
1) Description:
1. Exists first to the appearance of the loop, each cycle of internal table query; In the inner table and the appearance of the hash connection
2. Using exists Oracle checks the main query first, uses in, executes the subquery first, and stores the result in a temporary table

1.  uses exists and not exists
 select name, and Classno from student where exists (SELECT * from class where student.　　Classno= Class.classno);
 select name, Classno from student where isn't exists (SELECT * from class where student.classno= class.classno);
2.  uses in and not in
 select name, classno  from student where classno  in (select Classno from Class);
 select name, classno  from student where Classno isn't in (the Select Classno from Class);
 
2) Compare
1. If two table sizes are equal, in and exists differ significantly
2. If the size of the two table is large, the subquery table is large with the exists, and the subquery table is small in
3. Try not to use not in

2.union and UNION All
1) Description:
1. Usage scenario: When you need to display the results of two SELECT statements as a whole, you can use the Union and UNION all
2. Union sets the order of the default rules for the two result set unions that do not contain duplicate results, and union all sets the assembly to two result set, including repeating rows, without sorting
3. Union requires a duplicate value scan, is inefficient, and if no duplicate rows are to be deleted, you should use the Union ALL
4. Insersect and minus respectively intersection and difference sets, both do not include duplicate rows, and the default rules are sorted
2) Precautions for use
1. Multiple results can be set and
2. You must ensure that the result of the Select collection has the same number of columns, and that the type of each column is the same (the column name does not have to be the same, the column name of the first result is used as the column name of the result set by default)

3.with as
1) Description:
1. With table as can create temporary tables, one analysis, multiple use
2. For complex queries, use the with table as to extract the public query section and increase efficiency when querying multiple times
3. Enhanced legibility
2) Syntax:
With TabName as (select ... ）

4. ORDER BY
1) Description:
1. Order BY determines how Oracle will sort the results of the query
2. Default ASC when ASC or DESC is not specified
2) Use:
1. Single row ascending (can remove ASC)
SELECT * FROM student order by score ASC;
2. Multi-column Ascending
SELECT * FROM student order by Score,deptno;
3. Multi-column Descending
SELECT * FROM student ORDER by score Desc,deptno desc;
4. Mixing
SELECT * FROM student ORDER by score Asc,deptno desc;
3) Handling of NULL
1. Oracle considers NULL to be the maximum value at order BY, ASC at the end, and Desc at the top
2. Use nulls first (regardless of ASC or desc,null record) or Nulls last to control the null position
4) Attach a row of data to the top (decode)
SELECT * FROM Student ORDER by Decode (score,100,1,2);
SELECT * FROM Student ORDER by Decode (score,100,1,2), score; (one line is pinned, the other ascending)

5) Precautions
1. Any non-indexed entries in the ORDER BY statement will slow down the query
2. Avoid using an expression in an ORDER BY clause

5. Group by
1) Description:
1. Use to group the where execution results
 eg1:select sum (score), Deptno from student group by DEPTNO;
 eg2:select Deptno,sum (score) from student where deptno>1  group by deptno; 
 
 
6.where and having
1) Description:
1. where and having are used to filter the data, but the order of execution is different where--group by--having (that is, the where statement is calculated before the grouping calculation, and the group computes the having ' statement)
2. Having is generally used to filter the data after grouping
3. You cannot use a clustered function such as Sum,count,max
2) Example
EG1:
 select deptno,sum (score) from student where deptno>1   GROUP BY DEPTNO have sum (score) >100;

7. Case and Decode
1) Description:
1. Decode More concise
2. Decode can only do the equivalence of the conditional distinction, case when the use of interval to make judgments
2) Syntax:
Decode (condition, value 1, return value 1, value 2, return value 2,... Value N, return value N, default value)

--Equivalent to:

IF condition = value 1 Then
RETURN (translation value 1)
elsif condition = value 2 Then
RETURN (translation value 2)
......
elsif condition = value n Then
RETURN (translated value N)
ELSE
RETURN (default value)
END IF


Case expr when Comparison_expr1 then RETURN_EXPR1
[When COMPARISON_EXPR2 then RETURN_EXPR2
When Comparison_exprn then RETURN_EXPRN
ELSE else_expr]
END

  Case
     when Comparison_expr1 and RETURN_EXPR1
     [when Comparison_e XPR2 then RETURN_EXPR2
      When comparison_exprn then RETURN_EXPRN
    & nbsp ELSE else_expr]
  END
 
3) Example:
EG1:
  mode one:
 select name, Score,gender,
   case Gender if ' 1 ' then ' female '
        when ' 2 ' then ' Men '
  &nb　　Sp      Else ' not stated '
   end gender_t
 from student; 
 
  mode two:   select Name, Score,gender,
   case  when gender= ' 1 ' Then ' Women '
    when& nbsp Gender= ' 2 ' then ' Male '
                    Else ' does not specify '
   end gender_t
  from student;

Way three:
Select Name,gender,decode (Gender, ' 1 ', ' female ', ' 2 ', ' Male ', ' not described ') gender_t from student;

Results:


EG2:
Select Name,score,
Case where score >80 then ' excellent '
When score>=60 and score <=80 then ' good '
When score<60 then ' failed '
End Evalution
from student;
Results:

Set the default value to place null as no score:
Select Name,score,
Case where score >80 then ' excellent '
When score>=60 and score <=80 then ' good '
When score<60 then ' failed '
Else ' no grades '
End Evalution
from student;
Results:

4) Note:
1.case has two forms, where the case expression is then more efficient than the case-when expression
2. Use the Decode function to avoid repeatedly scanning the same record or repeatedly connecting the same table, so some cases can reduce processing time

SQL Optimization (Oracle)-Part II common SQL usage and considerations