꾸러기

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

Autotrace is a very useful feature that used to trace the cost of a sql quarry and execution plane oracle used for that quarry.

Any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. But you will get the following error if it is not enabled.

sql :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

To solve this do the followings:

Run plustrce.sql through SYS schema (user: system) if it did not run before. the location of plustrce.sql is: ON NT :- @$ORACLE_HOME\sqlplus\admin\plustrce.sql . ON UNIX :- @$ORACLE_HOME/sqlplus/admin/plustrce.sql sql> @ .../plustrce.sql

Grant PLUSTRACE to the user : Sql> GRANT PLUSTRACE to user_name;
Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table , execure UTLXPLAN.sql location of the file is:

ON NT :- $ORACLE_HOME\rdbms\admin\utlxplan.sql
ON UNIX :-$ORACLE_HOME/rdbms/admin/utlxplan.sql
sql> @../utlxplan.sql

A User can use the AUTOTRACE options as follows:

sql> SET AUTOTRACE OFF ;
- No AUTOTRACE report is generated. This is the default.
sql> SET AUTOTRACE ON EXPLAIN ;
- The AUTOTRACE report shows only the optimizer execution path.
sql> SET AUTOTRACE ON STATISTICS;
- The AUTOTRACE report shows only the SQL statement execution statistics.
sql> SET AUTOTRACE ON;
- The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
sql> SET AUTOTRACE TRACEONLY;
- Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

source : http://shaharear.blogspot.com/2007/04/enable-autotrace.html


※ Autotrace linesize

column plan_plus_exp format a131
set lines 150

- raw device ; 성능 향상을 위해 OS 대신 Oracle이 직접 디스크를 관리함
=> 일반 OS 명령어로는 사용이 불가 / RMAN으로만 백업 복구 가능

- 11g부터 RAC는 ASM만 지원함

- 메모리 관리를 자동으로 해주는 기능으로 10g에 소개됨 (SGA 관리) / 11g에서는 PGA까지 관리
=> 설치시 기본으로 사용되도록 설정되어 있음 / ASMM을 끄고 수동으로 전환할 수 있음

- spfile을 사용하는 것이 편리 => 변경된 최적값이 재부팅 후에도 유지됨 (pfile은 초기값으로 돌아감)
=> pfile도 ASSM 사용은 가능하나 재부팅 후에 초기값으로 돌아가서 변경된 값이 취소됨

- MMON ; 통계 정보를 AWR에 저장 / MMAN ; AWR의 정보를 토대로 메모리를 조정

- redo log는 동적(dynamic) 변환이 안됨

- Granule ; 메모리를 할당할 때 사용하는 단위 (기본 단위의 배수로 증가함)
=> 9i ; SGA가 256MB 이상일 경우 16MB, 256MB 이하일 경우 4MB
=> 10g ; SGA가 1GB 이상일 경우 16MB, 1GB 이하일 경우 4MB

- 자동으로 시간에 알맞게 메모리를 재배열할 수 있음 / redo log는 재배열이 안됨
=> SGA 최대값을 정할 때 redo log 용량을 고려하여 계산할 것

- MMAN ; SGA 크기를 Broker로 부터 받아서 메모리를 크기를 조절해 줌
=> Granule 단위로 메모리의 크기가 변경됨

- SGA_MAX_SIZE ; 최대값을 지정하고 다른 프로세스의 최소값을 지정하면 최대값 한도에서 메모리 분할
=> 각 메모리들은 사용하지 않더라도 최소값 이상의 용량이 유지됨 (redo log는 dynamic한 변경 불가능)

- SGA Advisor ; SGA의 설정값이 궁금할 경우 사용 / ASMM을 사용하지 않을 경우에 활용

- AWR과 ADDM은 실시간 지원이 안됨 / ASH는 실시간 지원 + 내역(history)도 볼 수 있음

- ASH는 1초 단위로 체크를 함 => MMON이 60분 분량을 모아서 저장함

- 데이터 입력으로 과부하 발생 EM을 열어서 보고서 열람 http://ip주소:1158/em
=> dump 파일로 저장할 수 있지만, 분석하기가 힘듬

- archive log가 가득 차서 에러가 날 경우
장시간 에러 후 새로운 공간을 지정해도 자동 저장이 안될 경우 멈췄다가 다시 시작
alter system archive log stop; => archive log 생성 중지
alter system archive log start; => archive log 생성 재개

- AWR ; 전체 통계 => MMON이 자동으로 통계를 모아 sysaux에 저장
- ADDR ; 문제가 있다고 보는 것만 보고
- ASH ; 현재 상태를 보고함

- 서버 ; 메모리(SGA)와 디스크(Database) 튜닝으로 나눔

- 모든 가능성을 고려하여 최적의 경로를 계산해야 함

- 일반적으로 =, in, like 'A%', between, <>, like '%A', 전체 스캔 순서

from a
where a.no like '%AB' ; 4
and a.name = '홍길동' ; 1
and a.sal between ; 3
and a.loc in ("서울", "부산") ; 2

- 운영중에 인덱스를 생성할 경우 hang in 이 되어 DB가 정지될 수 있음

> create index idx_student_name
on student name);

> select table_name.index_name
from user_ind_columns
where thable_name='STUDENT';

> select name from student;

> select name from student
where name > '0'; => 인덱스를 사용하게 만드는 줄

> select /=+ index_desc(s_idx_student_name) */ name
from student
where name > '0';

> select /*+ index_desc(s_idx_student_name) */ name
from student s
where name > '0'
and rownum=1;