Efficient SQL Statements in Oracle

1. not in
A: SELECT col1,col2,col3 FROM table1 a WHERE a.col1 not in (SELECT col1 FROM table2)
B：SELECT col1,col2,col3 FROM table1 a WHERE not exists (SELECT 'x' FROM table2 b WHERE a.col1=b.col1)
B:  is better than A.
a<>0  to->  a>0 or a<0
a<>''  to->  a>''

2. not null
a>0 or a>'' is better than a is not null.

3.>= replace >
A: SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
B: SELECT * FROM EMP WHERE DEPTNO >3
A is better than B.

4.LIKE
name LIKE '%jeck%'  should be name LIKE 'Xjeck%' OR YY_BH LIKE 'Bjack%'

5.EXISTS v DISTINCT

A: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO
B: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
B is better than A

6. page

A:
select *
  from (select a.*, rownum as rnum
          from (select * from table1) a
         where rownum <=5300)
 where rnum >= 5000

B: is better. 
select * from ( select t.*, row_number() OVER(ORDER BY null) AS row_number  from table1 t ) p where p.row_number <5300 and p.row_number >=5000

--OVER and GROUP BY
OVER first shows Oracle 8.1.6

    A   B   C                     
    -- -- ----------------------
    m   a   2                     
    n   a   3                     
    m   a   2                     
    n   b   2                     
    n   b   1                     
    x   b   3                     
    x   b   2                     
    x   b   4                     
    h   b   3
SQL>   select a,b,c,sum(c)over(partition by a) from t2               
   Result:
   A   B   C        SUM(C)OVER(PARTITIONBYA)     
   -- -- ------- ------------------------
   h   b   3        3                       
   m   a   2        4                       
   m   a   2        4                       
   n   a   3        6                       
   n   b   2        6                       
   n   b   1        6                       
   x   b   3        9                       
   x   b   2        9                       
   x   b   4        9                       
 
 sum，GROUP BY 
SQL> SELECT a, sum(c) FROM t2 GROUP BY a;
   A   SUM(C)                           
   -- ----------------------
   h   3                     
   m   4                     
   n   6                     
   x   9                     
ww w.blog java. net/loocky /archive/2007 /11/13/160213.html