【SQL(续)】

SQL(续)

1. 临时表空间(总大小、使用率、剩余空间)

select c.tablespace_name,
d.bytes_used "表空间使用大小(BYTE)",
c.bytes/(1024*1024*1024) "表空间大小(GB)",
(c.bytes-d.bytes_used)/(1024*1024*1024) "表空间剩余大小(GB)",
d.bytes_used/(1024*1024*1024) "表空间使用大小(GB)",
to_char(d.bytes_used*100/c.bytes,'99.99999') || '%' "使用率"
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name

2. KETTLE抽取数据配置优化

rewriteBatchedStatements   true  --是否批量提交
useServerPrepStmts  true  --压缩数据传输，优化客户端和MySQL服务器之间的通信性能
cachePrepStmts  true  --开启预编译结果的缓存

3. oracle数据更新

select * from A表 for update -- 解锁-修改-锁定-提交

4. 数据迁移：若数据量过大可考虑对数据进行切面拆分；先将历史数据按照创建时间进行数据拆分，再对业务数据进行已完结与未完结拆分，最后将基础数据进行全量迁移。
5. 索引

CREATE INDEX XXX ON AA(BB) TABLESPACE TACTINDEX01;  --XXX:索引名；AA：表名  BB: 字段名  

drop index XXX --索引名，创建索引时不允许重名，可放心删除

6. 连接串示例

mytable : mytable4DB_KENSYOU =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.44.39.198)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mytable4db)
    )
  )  

7. excel秒数转换为分钟

/*excel秒数转换为分钟*/
CONCATENATE(INT(A1/60),"分",MOD(A1,60)，"秒")

8. 截取 @ 符号前五位

/* 截取 @ 符号前五位* /
SELECT SUBSTR(CREUSER,0,INSTR(CREUSER,'@',1,1)-1) FROM 表名

9. mysql让空值转为0然后相加

--1
UPDATE biaoming A
JOIN ( select ifnull(suma,0)+ifnull(sumb,0) as sum,id
from biaoming)B ON A.id=B.id
SET A.sum= B.SUM where A.id like '2022%'
--2
UPDATE biaoming  A
JOIN ( SELECT CASE WHEN suma IS NULL THEN 0+sumb WHEN sumb IS NULLTHEN O+suma ELSE suma + sumb END AS SUM, ID FROM biaoming )B ON A.ID = B.ID SET A.SUM = B.SUM

10. 更新分表拼接语句

select 'update a_' || NUM_64 ||' set flag = '''|| a.flag ||  ''' WHERE ID = '|| ID || ';'
from Abiao A
join t_vin B
A.VIN = B.VIN