꾸러기

SQL> select empno, ename, sal,
  2    rank() over (order by sal desc) as rk
  3  from emp;

     EMPNO ENAME             SAL         RK
---------- ---------- ---------- ----------
      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          2
      7566 JONES            2975          4
      7698 BLAKE            2850          5
      7782 CLARK            2450          6
      7499 ALLEN            1600          7
      7844 TURNER           1500          8
      7934 MILLER           1300          9
      7521 WARD             1250         10
      7654 MARTIN           1250         10
      7876 ADAMS            1100         12
      7900 JAMES             950         13
      7369 SMITH             800         14

14 rows selected.

SQL> select empno, ename, sal,
  2    dense_rank() over (order by sal desc) as rk
  3  from emp;

     EMPNO ENAME             SAL         RK
---------- ---------- ---------- ----------
      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          2
      7566 JONES            2975          3
      7698 BLAKE            2850          4
      7782 CLARK            2450          5
      7499 ALLEN            1600          6
      7844 TURNER           1500          7
      7934 MILLER           1300          8
      7521 WARD             1250          9
      7654 MARTIN           1250          9
      7876 ADAMS            1100         10
      7900 JAMES             950         11
      7369 SMITH             800         12

14 rows selected.

SQL> select empno, ename, sal, rownum
  2  from emp;

     EMPNO ENAME             SAL     ROWNUM
---------- ---------- ---------- ----------
      7369 SMITH             800          1
      7499 ALLEN            1600          2
      7521 WARD             1250          3
      7566 JONES            2975          4
      7654 MARTIN           1250          5
      7698 BLAKE            2850          6
      7782 CLARK            2450          7
      7788 SCOTT            3000          8
      7839 KING             5000          9
      7844 TURNER           1500         10
      7876 ADAMS            1100         11
      7900 JAMES             950         12
      7902 FORD             3000         13
      7934 MILLER           1300         14

14 rows selected.

SQL> select a.*, rownum
  2  from (select empno, ename, sal from emp order by sal desc) a;

     EMPNO ENAME             SAL     ROWNUM
---------- ---------- ---------- ----------
      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          3
      7566 JONES            2975          4
      7698 BLAKE            2850          5
      7782 CLARK            2450          6
      7499 ALLEN            1600          7
      7844 TURNER           1500          8
      7934 MILLER           1300          9
      7521 WARD             1250         10
      7654 MARTIN           1250         11
      7876 ADAMS            1100         12
      7900 JAMES             950         13
      7369 SMITH             800         14

14 rows selected.

SQL> select a.*, rownum
  2  from (select empno, ename, sal from emp order by sal desc) a
  3  where rownum = 1;

     EMPNO ENAME             SAL     ROWNUM
---------- ---------- ---------- ----------
      7839 KING             5000          1

SQL> select a.*, rownum
  2  from (select empno, ename, sal from emp order by sal desc) a
  3  where rownum = 5;

no rows selected

SQL> select empno, ename, sal,
  2  rank() over (order by sal desc) as rk
  3  from emp
  4  where rk = 5;
where rk = 5
      *
ERROR at line 4:
ORA-00904: "RK": invalid identifier

SQL> select *
  2  from (select empno, ename, sal,
  3  rank() over (order by sal desc) as rk
  4  from emp)
  5  where rk = 5;

     EMPNO ENAME             SAL         RK
---------- ---------- ---------- ----------
      7698 BLAKE            2850          5

SQL> select empno, ename, sal,
  2  dense_rank() over (order by sal desc) as rk
  3  from emp
  4  where rk = 5;
where rk = 5
      *
ERROR at line 4:
ORA-00904: "RK": invalid identifier

SQL> select *
  2  from (select empno, ename, sal,
  3  dense_rank() over (order by sal desc) as rk
  4  from emp)
  5  where rk = 5;

     EMPNO ENAME             SAL         RK
---------- ---------- ---------- ----------
      7782 CLARK            2450          5

SQL> select a.*, rownum
  2  from (select empno, ename, sal from emp order by sal desc) a
  3  where rownum <= 5;

     EMPNO ENAME             SAL     ROWNUM
---------- ---------- ---------- ----------
      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          3
      7566 JONES            2975          4
      7698 BLAKE            2850          5

5 rows selected.

SQL> select *
  2  from (select empno, ename, sal,
  3    rank() over (order by sal desc) as rk
  4      from emp)
  5  where rk <=5;

     EMPNO ENAME             SAL         RK
---------- ---------- ---------- ----------
      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          2
      7566 JONES            2975          4
      7698 BLAKE            2850          5

5 rows selected.

SQL> select *
  2  from (select empno, ename, sal,
  3    dense_rank() over (order by sal desc) as rk
  4      from emp)
  5  where rk <=5;

     EMPNO ENAME             SAL         RK
---------- ---------- ---------- ----------
      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          2
      7566 JONES            2975          3
      7698 BLAKE            2850          4
      7782 CLARK            2450          5

6 rows selected.

SQL> select *
  2  from (select a.*, rownum rnum
  3    from (select empno, ename, sal
  4      from emp order by sal desc) a
  5    where rownum <= 10)
  6  where rnum >= 6;

     EMPNO ENAME             SAL       RNUM
---------- ---------- ---------- ----------
      7782 CLARK            2450          6
      7499 ALLEN            1600          7
      7844 TURNER           1500          8
      7934 MILLER           1300          9
      7521 WARD             1250         10

5 rows selected.

- 주의 사항
Where 절에는 Alias를 사용할 수 없다. 이유는?
The values in the select list do not "exist" before the where clause is executed.
The order of execution semantically is:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY




* TOP-N 분석

   ㄴ 최대값 , 최소값 => N(갯수)

   ㄴ rownum => insert할때 자동으로 삽입 + inline view

   ㄴ 참고 사이트 : http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html

   ㄴ 형식

SELECT 컬럼명,..., ROWNUM FROM (SELECT 컬럼명,... from 테이블명  ORDER BY top_n_컬럼명)  WHERE ROWNUM <= n;

예제1> 연봉 가장 많은 5명

SELECT empno, sal FROM ( SELECT * FROM emp ORDER BY sal DESC) WHERE rownum <= 5;

         예제2> 입사일 기준으로 4 번째 부터 10번째까지 출력? ( 7 명 )
         select  empno , ename
                  from   (select  a.* , rownum rnum  from  (select *  from emp order by hiredate ) a
                  where rownum <= 10 ) where rnum >= 4;

http://blog.naver.com/iyearbook/150047915439

Overview
Subqueries can be used to answer queries such as "who has a salary more than Tom's". For such query, two queries have to be executed: the first query finds Tom's salary and the second finds those whose salary is greater than Tom's. Subquery is an approach provides the capability of embedding the first query into the other:  Oracle executes the subquery first, making the result of the sub query available to the main query and then executing the main query.

The syntax of subquery is

SELECT <column, ...>

FROM <table>

WHERE expression operator

    ( SELECT <column, ...>

       FROM <table>

      WHERE <condition>)

For example, the following statement answers the described query above.

    SQL> SELECT * FROM employee

         WHERE sal > (SELECT sal WHERE name='TOM');

Note that:

a subquery must be enclosed in the parenthesis.
a subquery must be put in the right hand of the comparison operator, and
a subquery cannot contain a ORDER-BY clause.
a query can contain more than one sub-queries.
In general, there are three types subqueries:

single-row subquery, where the subquery returns only one row.
multiple-row subquery, where the subquery returns multiple rows,.and
multiple column subquery, where the subquery returns multiple columns.
Single-row subqueries can only be used with single-row comparison operators, and multiple-row subqueries can be used only with multiple-row operators.  They are to be described separately in the following.

--------------------------------------------------------------------------------

Single-Row Subquery
The operators that can be used with single-row subqueires are =, >, >=, <, <=, and <>.

Group functions can be used in the subquery. For example, the following statement retrieve the details of the employee holding the highest salary.

SQL> SELECT * FROM employee

     WHERE sal = (SELECT MIN(sal) FROM employee);

Having-clause can also be used with the single-row subquery. For example, the following statement returns all departments in where the minimum salary is more than the minimum salary in the department 5.

SQL> SELECT dept_no, MIN(sal) FROM employee

     GROUP BY dept_no

  HAVING MIN(sal) > (

      SELECT MIN(sal)

        FROM employee

        WHERE dept_no = 5);

--------------------------------------------------------------------------------

Multiple-Row Subquery
Note the following statement is illegal, because the operator = cannot be used with subquery returns multiple rows.

SQL> SELECT name, sal FROM employee

  WHERE sal > (

      SELECT MIN(sal) FROM employee GROUP BY dept_no);

Some operators that can be used with multipe-row subqueries are:

IN, equal to any member in the list,
ANY, compare values to each value returned by the subquery.
For example, the following statement find the employees whose salary is the same as the minimum salary of the employees in some department.

SQL>  SELECT name, sal FROM employee

  WHERE sal IN (

      SELECT MIN(sal)

        FROM employee

        GROUP BY dept_no);

For example, the following statement find the employees whose salary is more than the minimum salary of the employees in any department.

SQL> SELECT name, sal FROM employee

  WHERE sal > ANY (

      SELECT MIN(sal)

        FROM employee

        GROUP BY dept_no);

--------------------------------------------------------------------------------

Multiple-Column Subquery
In multiple-column  subqueries, rows in the subquery results are evaluated in the main query in pair-wise comparison. That is, column-to-column comparison and row-to-row comparison.

For example, the following statement lists all items whose quantity and product id match to an item of order id 200.

SQL> SELECT order_id,  product_id, quantity

    FROM item

    WHERE (product_id, quantity) IN (

        SELECT  product_id, quantity FROM item WHERE order_it = 200)

    AND order_id = 200;

Note that you can put a subquery in the FROM clause in the main query.

For example, the following statement finds all employees in each department where their salary is above the average.

    SQL> SELECT a.name, a.sal, a.dept_no, b.salary_ave

        FROM employee a,

               (SELECT dept_no, AVE(sal) salary_ave

              FROM employee 

                GROUP BY dept_no)

        WHERE  a.dept_no = b.dept_no;

        AND a.sal > b.salary_ave;

--------------------------------------------------------------------------------

from ; http://www.comp.nus.edu.sg/~ooibc/courses/sql/dml_query_subquery.htm

- index = 주소록 (보조 기억장치에 데이터가 위치한 곳을 정리해 놓은 데이터의 주소록)

- 데이터를 관리하기 위한 목적으로 index를 만듦 (SQL 처리가 빨라지는 것은 부수적인 이득)

- where 조건절에 나오는 컬럼을 위주로 index를 작성한다.

- index를 생성하기 위해 먼저 시스템이 데이터 보호를 위해 lock을 하고 full scan을 한다.
이 때, 시스템에 부하가 높아지므로 데이터를 활용하지 않는 시간에 index를 작성하는게 바람직하다.

- index를 생성하는 과정 ; lock -> full scan -> sort -> block 기록 -> unlock

- 데이터 삽입시 index split으로 DML의 속도 저하
데이터의 추가시에 index도 업데이트를 해야 하며 이 과정에 index split이 발생
따라서 index가 많을 경우, 데이터 변경시에 index 업데이트로 인해 시스템에 부하가 많아짐
 
- 데이터 삭제시 테이블의 데이터는 지워지지만 index에는 데이터가 지워지지 않고
사용하지 않는 데이터로 표시되어 그대로 남아서 성능이 저하됨 => index rebuild를 해서 정리해줘야함

- index는 검색된 데이터의 블록이 많더라도 한번에 한 블록씩만 메모리로 불러들인다.
=> 총 데이터의 5% 미만일 경우에만 index의 성능이 좋다.

- index를 이용할 때, 보조 기억장치에서 한번에 읽어들이는 데이터의 양보다
데이터의 블록을 읽어들이기 위해 보조 기억장치를 액세스하는 횟수가 쿼리의 성능을 좌우한다.

- 따라서 전체 데이터 블록의 5% 이상의 횟수로 보조 기억장치의 블록을 액세스할 경우,
오라클의 optimizer는 인덱스를 사용하는 것이 비효율적이라고 판단하여 바로 보조기억장치를 검색함

- unique index는 내용이 중복되지 않으므로 사용하기 좋음 ; unique한 컬럼만 unique index로 지정할 수 있음

- 결합 index의 배합에 따라 index의 성능이 좌우됨

- descending index는 내림차순으로 정리함 ; max함수나 최근의 내용을 우선으로 검색할 때 좋음 (큰 값, 날짜별)

- index가 설정된 상태에서는 where로 검색할 때 index가 된 컬럼에 다른 함수는 지정하지 않는다.
대신 함수를 지정해야할 경우에는 함수 기반 인덱스를 지정한다.

-temp
emp_id,  emp_name,  birth_date,  dept_code,  emp_type,  use_yn,  tel,  holly,  salary,  lev

-tdept
dept_code,  dept_name,  paren_dept,  use_yn,  area,  boss_id

-tcom
work_year,  emp_id,  bonus_rate,  commle

-emp_level
lev,  from_sal,  to_sal,  from_age,  to_age

-student
studno,  name,  userid,  grade,  idnum,  birthdate,  tel,  height,  weight,  deptno,  profno

-professor
profno,  names,  userid,  position,  sal,  hiredate,  comm,  deptno

-department
deptno,  dname,  college,  loc