First week of work Oracle function summary

1. Oracle Row-to-column and column-changing career

The most common row-to-column, the main principle is the use of the DECODE function, aggregate function (SUM () Max ()), combined with the group by group implementation

MAX (DECODE (SW. SBBL, ' 1 ', SW. SBBL, NULL)) as sbbl_01,

MAX (DECODE (SW. SBBL, ' 2 ', SW. SBBL, NULL)) as sbbl_02,

Description: SW. The value in SBBL is 1 or 2, which is now converted to 2 columns. If 1 is the column name in Sbbl_01, the value of this column is 1. If 2 is the column name in Sbbl_02, the value of this column is 2.

decode(condition, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)

DECODE (B.task_status, ' 1 ', ' in-process ', ' 2 ', ' handled ')

Biographies, mainly using the Union in SQL, the specific SQL

Select user_name, ' Cn_score ' COURSE, Cn_score as score from Test_tb_grade2
Union
Select user_name, ' Math_score ' COURSE, Math_score as score from Test_tb_grade2
Union
Select user_name, ' En_score ' COURSE, En_score as score from Test_tb_grade2
ORDER BY User_name,course

2. The data in the table is de-duplicated, and when the table data is small, you can use distinct (attribute) directly, but when the table data is large .

Replace distinct with exists
Low efficiency
SELECT DISTINCT Dept_no,dept_name
From Dept D,emp E
where D.dept_no=e.dept_no
Efficient
Select Dept_no,dept_name
From Dept D
where exists (select 1 from EMP E
where E.dept_no=d.dept_no)

3. Oracle parses the WHERE clause in a bottom-up order. When multiple table joins in where, returns a table with fewer rows, the clause with the filter condition should be placed at the end of the WHERE clause

4. When the table data is large, use truncate instead of delete for data delete operations .

5. Convert data of date type to string type operation.

To_char (B.receive_time, ' yyyy-mm-dd HH24:MI:SS ') receive_time

6.general principles of SQL optimization:
Rely on the Oracle optimizer as much as possible to establish an appropriate index.
Using indexes in coding, avoiding large tables, using temporary tables rationally, avoiding writing complex SQL

Several common optimization scenarios
When creating a table, you should try to establish a primary key, call the entire data table Pctfree and pctused parameters as needed
Big Data table Delete TRUNCATE TABLE
Use less * Number select COUNT (key) from tab where key>0 performance is better than select COUNT (*)
Try to use fewer nested subqueries
For comparison of multiple or operations, it is recommended to split into multiple queries, with union all joined together
The Oracle parser is right-to-left for table parsing, with less records on the right
Frequently accessed tables can reside in memory. ALTER TABLE. Cache
Avoid complex multi-table associations
Avoid using resource-intensive operations

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Summary of Oracle functions in the first week of work