分类 Oracle 下的文章

Oracle正则表达式

正则表达式简介

正则表达式,又称规则表达式。(英语:Regular Expression,在代码中常简写为regex、regexp或RE),计算机科学的一个概念。正则表通常被用来检索、替换那些符合某个模式(规则)的文本。

正则表达式是对字符串(包括普通字符(例如,a 到 z 之间的字母)和特殊字符(称为“元字符”))操作的一种逻辑公式,就是用事先定义好的一些特定字符、及这些特定字符的组合,组成一个“规则字符串”,这个“规则字符串”用来表达对字符串的一种过滤逻辑。正则表达式是一种文本模式,模式描述在搜索文本时要匹配的一个或多个字符串。

正则表达式具体描述

QQ图片20170922221302.png

- 阅读剩余部分 -

EXP-00091:Exporting questionable statistics

今天在oracle上做exp导出的时候报exp-00091;
查了下是服务端跟客户端的字符集不一致导致的

服务端字符集查询SQL:

select userenv('language') from dual;

执行过程如下:

E:\data>exp userid=mig/mig tables=T_CUSTOMER,T_PHONE,T_CUSTOMER_PHONE
 file=e:\data\pub.dump  log=e:\data\pub.log buffer=40960000 grants=n indexes=n t
riggers=n constraints=n

Export: Release 11.2.0.4.0 - Production on Tue Aug 29 15:50:24 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                   T_CUSTOMER    1320672 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                      T_PHONE    1869331 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table             T_CUSTOMER_PHONE    1869331 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

E:\data>echo $NLS_LANG
$NLS_LANG

E:\data>echo %NLS_LANG%
AMERICAN_AMERICA.UTF8

E:\data>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

E:\data>exp userid=mig/mig tables=T_CUSTOMER,T_PHONE,T_CUSTOMER_PHONE
 file=e:\data\pub.dump  log=e:\data\pub.log buffer=40960000 grants=n indexes=n t
riggers=n constraints=n

Export: Release 11.2.0.4.0 - Production on Tue Aug 29 15:55:25 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                   T_CUSTOMER    1320672 rows exported
. . exporting table                      T_PHONE    1869331 rows exported
. . exporting table             T_CUSTOMER_PHONE    1869331 rows exported
Export terminated successfully without warnings.

E:\data>

最后提一下,utf8跟al32utf8字符集是不一样的,好吧,mark一下!

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

昨天同事遇到一个问题,jdbc连接数据库的时候报ora-12505错误。原始写法如下:

jdbc:oracle:thin:@192.168.10.11:1521:ivldb

了解到这台数据库是11gRAC,jdbc连接的是rac的scan ip。经过查找发现jdbc连接scan ip的格式如下:

jdbc:oracle:thin:@{scan-listener-hostname}:{port}/{service-name}

顾把原始写法改成如下即可:

jdbc:oracle:thin:@192.168.10.11:1521/ivldb

参考MOS文档:Using SCAN With Oracle JDBC 11g Thin Driver (文档 ID 1290193.1)
mark一下,留作记录!

SQL优化之first_value

好久没写blog了,最近又忙着学java,然后就更没有时间了。刚好今天同事有个sql需要优化,我就看了一下,然后做了一个记录。

原sql如下:

SELECT M.TORD_ID,
       '101',
       M.BIZ_UNT_CD,
       DECODE(M.BIZ_UNT_CD, '1001', '2001', '5001', '52001') L_chan_id,
       (SELECT PRD.DLR_ID
          FROM shunzi.PRD_PRD_M PRD, shunzi.PRD_UNT_PRD_D UNT
         WHERE UNT.PRD_ID = PRD.PRD_ID
           AND UNT.UNT_PRD_ID = M.UNIT_SEQ) DLR_ID,
       (SELECT UNT.PRD_ID
          FROM shunzi.PRD_UNT_PRD_D UNT
         WHERE 1 = 1
           AND UNT.UNT_PRD_ID = M.UNIT_SEQ) PRD_ID,
       M.UNIT_SEQ,
       M.BARCODE UNIT_CODE,
       NVL((SELECT RGN.STKO_WH_NO
             FROM shunzi.CMM_ZIP_NO_RGN_WH_D RGN
            WHERE RGN.WH_PTR_CD = '10'
              AND RGN.MDA_CD = '10'
              AND RGN.BIZ_UNT_CD = M.BIZ_UNT_CD
              AND RGN.LRGN_ID = M.ACC_PRV_ID
              AND RGN.MRGN_ID = M.ACC_CITY_ID
              AND RGN.SRGN_ID = M.ACC_CONTR_ID
              AND ROWNUM = 1),
           'C01') WH_CD,
       DECODE(NVL((SELECT RGN.STKO_WH_NO
                    FROM shunzi.CMM_ZIP_NO_RGN_WH_D RGN
                   WHERE RGN.WH_PTR_CD = '10'
                     AND RGN.MDA_CD = '10'
                     AND RGN.BIZ_UNT_CD = M.BIZ_UNT_CD
                     AND RGN.LRGN_ID = M.ACC_PRV_ID
                     AND RGN.MRGN_ID = M.ACC_CITY_ID
                     AND RGN.SRGN_ID = M.ACC_CONTR_ID
                     AND ROWNUM = 1),
                  'C01'),
              'C01',
              1,
              'C03',
              8,
              'C04',
              3,
              'C06',
              55,
              'C07',
              60,
              'C05',
              48,
              1) DLV_ID,
       0,
       PC.SL_PC sl_pc,
       PC.SL_PC acc_sls_amt,
       PC.PRS_PC prs_pc,
       1,
       20161124,
       M.FD_DATE,
       M.CUSER_ID,
       M.FD_DATE,
       M.CUSER_ID,
       M.FD_DATE
  FROM shunzi.PRD_TICK_ORD_M M,
       (SELECT A.PRD_ID,
               A.UNT_SEQ,
               (SELECT D.BARCODE
                  FROM shunzi.PRD_UNT_PRD_D D
                 WHERE D.PRD_ID = A.PRD_ID
                   AND D.UNT_SEQ = A.UNT_SEQ) UNIT_CODE,
               (SELECT D.UNT_PRD_ID
                  FROM shunzi.PRD_UNT_PRD_D D
                 WHERE D.PRD_ID = A.PRD_ID
                   AND D.UNT_SEQ = A.UNT_SEQ) UNT_PRD_ID,
               A.SL_PC SL_PC,
               A.PRS_PC PRS_PC
          FROM shunzi.PRD_UNT_PRD_PC_CHG_D A
         WHERE (PRD_ID, UNT_SEQ, PC_SEQ) IN
               (SELECT A.PRD_ID, A.UNT_SEQ, MAX(PC_SEQ)
                  FROM shunzi.PRD_UNT_PRD_PC_CHG_D A,
                       shunzi.PRD_PRD_M            B,
                       shunzi.PRD_UNT_PRD_D        C
                 WHERE B.PRD_ID = A.PRD_ID
                   AND B.PRD_ID = C.PRD_ID
                   AND A.UNT_SEQ = C.UNT_SEQ
                   AND C.UNT_PRD_SL_STS_CD != '10'
                   AND ((B.PRD_APP_STS_CD = '210' AND A.PC_APP_STS_CD = '250') OR
                       (B.PRD_APP_STS_CD != '210'))
                   AND SYSDATE BETWEEN A.PC_BGN_DATE AND A.PC_CL_DATE
                --AND B.PRD_PTR_CD = '50' 
                 GROUP BY A.PRD_ID, A.UNT_SEQ)) PC
 WHERE 1 = 1
   AND M.UNIT_SEQ = PC.UNT_PRD_ID
   AND M.TICKET_TYPE = '30'
   AND M.FD_DATE BETWEEN TO_DATE('2016-11-01', 'yyyy-mm-dd hh24:mi:ss') AND
       TO_DATE('2016-11-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 0.001
   AND NVL((SELECT 1
             FROM shunzi.ORD_ORD_DTL_D G
            WHERE G.ORD_ID = M.ORD_ID
              AND G.ORD_SEQ = M.ORD_SEQ
              AND G.UNT_PRD_ID = M.UNIT_SEQ
              and (((G.ord_sts_cd = '70' and G.ord_supp_sts_cd is null) or
                  G.ord_sts_cd = '96') and G.rjt_rtn_yn = 'N')),
           '0') = '0'

- 阅读剩余部分 -

记一次标量子查询的优化

今天一个同事扔来一条SQL,反映说11s才出结果,前台有个10s的超时设置,问能不能优化下,降到10s以内。SQL Code如下:

SELECT /* system-mng-mapper | selectPtnDispatchbillInsert  */
            STKI_CMD_ID         --采购单号
            ,WH_NO              --仓库号
            ,WH_NM              --仓库名
            ,STKI_CMD_PGS_STS_NM    --执行状态
            ,PRD_ID             --商品号
            ,PRD_NM             --商品名
            ,MD_ID              --MD号
            ,MD_NM              --MD名
            ,STKI_CMD_PTR_NM    --采购类型
            ,PRS_SP_NM          --合作方式
            ,PRD_PTR_NM         --商品类型
            ,STKI_CMD_DATE      --采购指示日
            ,STKI_DDL_DATE      --采购到期日
            ,ORG_STKI_CMD_ID    --原采购单号
            ,STKI_CMD_REQ_ID    --采购申请号
            ,STOCK_MDA_NM       --库存渠道
            ,WH_PTR_NM          --入库属性
            ,STKI_CMD_QTY                                                          AS STKI_CMD_QTY         --采购数量
            ,(A.STKI_CMD_SO_ARR_QTY - A.STKI_CMD_CNFM_QTY - A.STKI_CMD_QA_FLR_QTY) AS STKI_SO_PGS_QTY      --发货在途数量
            ,STKI_CMD_CNFM_QTY                                                     AS STKI_CNFM_QTY        --入库数量            
            ,(A.STKI_CMD_QTY - (A.STKI_CMD_SO_ARR_QTY - A.STKI_CMD_QA_FLR_QTY))    AS STKI_RMN_QTY         --剩余数量   
        FROM
        (SELECT
             PG_FUL_UTIL.FN_FUL_CMD_MASK(FSCM.STKI_CMD_ID) AS STKI_CMD_ID
            ,FSCM.WH_NO
            ,(SELECT WH_NM FROM FUL_WH_M WHERE FUL_WH_M.WH_NO = FSCM.WH_NO) WH_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'FUL005' AND CMM_CODE_D.CODE = FSCM.STKI_CMD_PGS_STS_CD) STKI_CMD_PGS_STS_NM
            ,FSCM.PRD_ID
            ,(SELECT PRD_NM FROM PRD_PRD_M WHERE PRD_PRD_M.PRD_ID = FSCM.PRD_ID) AS PRD_NM
            ,FSCM.MD_ID
            ,(SELECT MD_NM FROM PRD_MD_M WHERE PRD_MD_M.MD_ID = FSCM.MD_ID) AS MD_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'FUL006' AND CMM_CODE_D.CODE = FSCM.STKI_CMD_PTR_CD) STKI_CMD_PTR_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'PRD024' AND CMM_CODE_D.CODE = PPM.PRS_SP_CD) PRS_SP_NM
            ,(SELECT Z.CODE_NM FROM CMM_CODE_D Z WHERE Z.CODE_GRP_ID = 'PRD010' AND Z.CODE = PPM.PRD_PTR_CD) AS PRD_PTR_NM
            ,FSCM.STKI_CMD_DATE
            ,FSCM.STKI_DDL_DATE
            ,FSCM.ORG_STKI_CMD_ID
            ,FSCM.STKI_CMD_REQ_ID
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'PRD103' AND CMM_CODE_D.CODE = FSCM.STOCK_MDA_CD) AS STOCK_MDA_NM
            ,(SELECT Z.CODE_NM FROM CMM_CODE_D Z WHERE Z.CODE_GRP_ID = 'PRD014' AND Z.CODE = PPM.WH_PTR_CD) AS WH_PTR_NM
            ,NVL((SELECT SUM(STKI_CMD_QTY) STKI_CMD_QTY FROM FUL_STKI_CMD_D WHERE STKI_CMD_ID = FSCM.STKI_CMD_ID AND FSCM.WH_NO = WH_NO),0) AS STKI_CMD_QTY
            ,NVL((SELECT SUM(FSND1.STKI_CNFM_QTY)
                  FROM FUL_STKI_SO_D   FSSD1
                      ,FUL_STKI_CNFM_D FSND1
                  WHERE FSSD1.WH_NO           = FSND1.WH_NO
                    AND FSSD1.STKI_SO_ID      = FSND1.STKI_SO_ID           
                    AND FSSD1.STKI_SO_DTL_SEQ = FSND1.STKI_SO_DTL_SEQ
                    AND FSSD1.WH_NO           = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID     = FSCM.STKI_CMD_ID),0) AS STKI_CMD_CNFM_QTY
            ,NVL((SELECT SUM(
                           CASE WHEN FSSD1.STKI_SO_PGS_STS_CD = '91' THEN 0
                                 WHEN FSSD1.STKI_SO_PGS_STS_CD = '92' THEN FSSD1.STKI_SO_ARRIV_QTY
                                ELSE FSSD1.STKI_SO_ARR_QTY  END )
                  FROM FUL_STKI_SO_D FSSD1
                 WHERE FSSD1.WH_NO         = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID   = FSCM.STKI_CMD_ID ),0)  AS STKI_CMD_SO_ARR_QTY
            ,NVL((SELECT SUM(FSQD1.QA_FLR_QTY)
                  FROM FUL_STKI_SO_D   FSSD1
                      ,FUL_STKI_QA_D   FSQD1
                  WHERE FSSD1.WH_NO           = FSQD1.WH_NO
                    AND FSSD1.STKI_SO_ID      = FSQD1.STKI_SO_ID
                    AND FSSD1.STKI_SO_DTL_SEQ = FSQD1.STKI_SO_DTL_SEQ                  
                    AND FSSD1.WH_NO           = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID     = FSCM.STKI_CMD_ID),0) AS STKI_CMD_QA_FLR_QTY                                       
        FROM FUL_STKI_CMD_M FSCM
            ,PRD_PRD_M PPM
       WHERE FSCM.PRD_ID = PPM.PRD_ID
         AND FSCM.DLR_ID = '803'
         AND FSCM.STKI_CMD_PGS_STS_CD IN ('16', '20', '30')                                                                            
         AND FSCM.STKI_CMD_PGS_STS_CD NOT IN ('90','95')                                                                                         
         ) A                                                                                                                                       
        ORDER BY A.STKI_CMD_DATE DESC

- 阅读剩余部分 -

Title - Artist
0:00