Oracle database optimization and LNNVL function usage

A: First talk about the optimization of the database

1. Max functions are slow to run; now replace them in the following way

A; the use of the Max function; Get the latest operation time of a row of employee records-----------slow;

SELECT *
From [email protected] Jer
WHERE Jer. TYPE = ' Employee bank card information record '
and Jer. CreateDate = (SELECTMAX(CreateDate)
From [email protected]
WHERE TYPE = ' Employee bank card information record '
and Emplid = Jer. Emplid
)

B; How to use the Max function:---------------

SELECT *
From [email protected] Jer
WHERE Jer.type = ' Employee bank card information record '
And not EXISTS(SELECT' X '//x Here is random can be any value here the logic is that there is no creation date than the temp created date greater than Jer; This is the date of the Jer .
From [email protected] Temp
WHERE Temp.type = Jer.type
and temp.emplid = Jer.emplid
and Temp.createdate > Jer.createdate)

Two: The new writing of the left connection

SELECT Jer.emplid,
Pn.name,
To_char (jer.createdate, ' yyyy-mm-dd ') CreateDate,
PB.ACCOUNT_EC_ID,
ppn.national_id nid
From [email protected] 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 (+)//LEFT JOIN connection
and Jer.emplid = pn.emplid (+)//LEFT JOIN connection
And not EXISTS
(SELECT ' X '
From [email protected] Temp
WHERE Temp.type = Jer.type
and temp.emplid = Jer.emplid
and Temp.createdate > Jer.createdate)
and Jer.type = ' Employee bank card information record '
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

Three: Lnnvl function

LNNVL official explanation translation
LNNVL the condition in the WHERE clause of a statement, returns False if the condition is true, or true if the condition is unknown or false. The function cannot be used in compound conditions such as and, or, or between.

Test instructions for several situations

--年份小于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

Oracle database optimization and LNNVL function usage