oracle数据库优化与LNNVL函数使用

一：先谈谈数据库的优化

1. max 函数运行速率慢；现在用下面的方式替换

A;用max函数的方式；获取最新操作时间的一行员工记录-----------速度慢；

          select *
   from JCT_EMPLID_RECORD@JEHR JER
             WHERE JER.TYPE = '员工银行卡信息记录'
   AND JER.CREATEDATE = (SELECT MAX(CREATEDATE)
                               FROM JCT_EMPLID_RECORD@JEHR
                              WHERE TYPE = '员工银行卡信息记录'
                               AND EMPLID = JER.EMPLID
                           )

B;不用max函数的方式： ---------------

              SELECT *
FROM jct_emplid_record@jehr jer
WHERE jer.type = '员工银行卡信息记录'
AND NOT EXISTS (SELECT 'X'              //X这里是随意的可以是任何值   这里的逻辑就是不存在比 temp的创建日期大于jer的创建日期；这就是要 jer里的日期是最新的
           FROM jct_emplid_record@jehr temp
                          WHERE temp.type = jer.type
                            AND temp.emplid = jer.emplid
          AND temp.createdate > jer.createdate)

二：左连接的新写法

           SELECT jer.emplid,
       pn.name,
       to_char(jer.createdate, 'yyyy-mm-dd') createdate,
       pb.account_ec_id,
       ppn.national_id nid
FROM jct_emplid_record@jehr jer,
       sysadm.ps_pye_bankacct pb,
       sysadm.ps_pers_nid ppn,
       (SELECT ps.emplid, ps.name
          FROM sysadm.ps_names ps
         WHERE NOT EXISTS (SELECT 'X'
                  FROM sysadm.ps_names ps1
                 WHERE ps1.emplid = ps.emplid
                   AND ps1.effdt > ps.effdt)) pn
WHERE 1 = 1
   AND jer.emplid = ppn.emplid
   AND jer.emplid = pb.emplid(+)    //左连接
   AND jer.emplid = pn.emplid(+)   //左连接
   AND NOT EXISTS
(SELECT 'X'
          FROM jct_emplid_record@jehr temp
         WHERE temp.type = jer.type
           AND temp.emplid = jer.emplid
           AND temp.createdate > jer.createdate)
   AND jer.type = '员工银行卡信息记录'
   AND ppn.national_id_type = 'NID'
   AND jer.createdate >= to_date('2015-03-01', 'yyyy-mm-dd')
   AND jer.createdate <= to_date('2015-04-10', 'yyyy-mm-dd')
ORDER BY jer.emplid

三：LNNVL函数

LNNVL官方解释翻译
lnnvl用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。

几种情况测试说明

--年份小于2009(lnnvl表示年份大于或者2009包含null)
SQL> select * from xifenfei where lnnvl(year<2009);
 
NAME                       YEAR
-------------------- ----------
xifenfei2008
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011
 
--year不为null(lnnvl表示年份为null)
SQL> select * from xifenfei where lnnvl(year is not null);
 
NAME                       YEAR
-------------------- ----------
xifenfei2008
 
--年份为null(lnnvl表示年份不为null)
SQL> select * from xifenfei where lnnvl(year is  null);
 
NAME                       YEAR
-------------------- ----------
xifenfei2001               2001
xifenfei2002               2002
xifenfei2003               2003
xifenfei2004               2004
xifenfei2005               2005
xifenfei2006               2006
xifenfei2007               2007
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011
 
10 rows selected.
 
--年份为12345(lnnvl表示年份不为12345)
SQL> select * from xifenfei where lnnvl(year =12345);
 
NAME                       YEAR
-------------------- ----------
xifenfei2001               2001
xifenfei2002               2002
xifenfei2003               2003
xifenfei2004               2004
xifenfei2005               2005
xifenfei2006               2006
xifenfei2007               2007
xifenfei2008
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011
 
11 rows selected.
 
--年份不为12345(lnnvl表示年份为12345或者null)
SQL> select * from xifenfei where lnnvl(year !=12345);
 
NAME                       YEAR
-------------------- ----------
xifenfei2008