preface

Before writing, this blog will be updated constantly to sort out and summarize the sql optimization problems in the development work (actual combat process); Because the author works in the big data Department, the amount of data is basically very large, so he pays more attention to the performance under big data (but for some operations, it may have higher efficiency under the condition of less data). At the same time, the department uses mybatis, so here we will also share some useful but may not be commonly used sentences. You are also welcome to discuss and supplement..

1.insert dates

Into all

insert into expression

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual
`

insert into mybatis application

INSERT INTO table(
			colum1,colum2,...
		) 
		(
		<foreach collection="list" item="keyData" index="" separator="union all">
			select 
				#{keyData.colum1},#{keyData.colum2},...
		 	from dual
		</foreach>
		)

merge into

When inserting multiple pieces of data, if you want to judge at the same time, update the existing data, and add new data if there is no data, you can eat merge into;

merge into expression

MERGE INTO [Target table] A USING [Source table] B ON([conditional expression] and [...]...) 
WHEN MATCHED THEN
  [UPDATE sql] 
WHEN NOT MATCHED THEN
  [INSERT sql]

merge into mybatis application

merge into table1 a
		using (
		<foreach collection="list" item="keyData" index="" separator="union all">
			select 
				#{keyData.seqId} AS SEQ_ID,#{keyData.dataDate} AS DATA_DATE,'1' AS STATUS
			 from dual
		</foreach>
		) b
		on(a.SEQ_ID=b.SEQ_ID)  
		 
		when matched then update set a.STATUS='0'
 
		when  not matched then insert  
			(a.SEQ_ID,a.DATA_DATE,STATUS)
		values
			(b.SEQ_ID,b.DATA_DATE,b.STATUS)

2.case when

case when expression is a general conditional expression, which can be used wherever the expression is valid.

Primitive sentence is present

SELECT * FROM( 
	SELECT t.*,
  (case --1
        when t.flag='01' then 'BankCode1' 
        when t.flag='02' then 'BankCode2'
        else 
              (--2
              select BANK_CODE from table1 b 
              where t.BANK_NO = b.BANK_NO)
        end ) col1,
        (case 
        when t.flag='01' then 'BankType1' 
        when t.flag='02' then 'BankType2'
        when t.flag='04' and t.BANK_NO IN 
          (SELECT BANK_NO from table1
              WHERE BANK_NAME in ('item')
              ) then 'BankType3'
            else 'BankType4' 
        end )  col2,
	row_number()over(partition by CON_NUM order by SIGN_DATE desc) cn 
	FROM table2 t --3
	WHERE t.CREATE_TIME = '201912' 
	AND t.CODE IN ( 'item' ) 
	) t 
WHERE cn = 1 

decode function

When the amount of data is very large, the decode function is more than 40% faster than the case when rate. However, decode can only judge the value of the field, not the expression. As follows:
decode (condition, value 1, return value 1, value 2, return value 2,..., default value)
Use the decode function to implement the case:

decode(flag, 10, '01', 20, '02', 30, '04', 'other')  from table2 

The result is the same as using case when, but decode looks much simpler

Comparison between DECODE and CASE WHEN

1. DECODE is available only in Oracle, but not in other databases;
2. The usage of CASE WHEN is supported by Oracle, SQL Server and MySQL;
3. DECODE can only be used to judge equality, but it can be used to judge greater than, less than, and equal with the sign function. CASE when can be used to judge =, > =, <, < =, < >, is null, is not null, etc;
4. DECODE uses it to be more concise, and CASE is complex but more flexible;
5. In addition, in decode, null and null are equal, but in case when, it can only be judged by is null.