구동테이블이 작은 Nested Loops + 내부테이블의 결합키에 인덱스


'Oracle' 카테고리의 다른 글

튜닝 기본  (0) 2017.03.23
실행계획  (0) 2017.03.06
START WITH CONNECT BY  (0) 2017.02.23
OVER PARTITION BY  (0) 2017.02.22
EXISTS  (0) 2017.02.21
EXPLAIN PLAN FOR  (0) 2017.02.20

set autotrace traceonly;

'Oracle' 카테고리의 다른 글

튜닝 기본  (0) 2017.03.23
실행계획  (0) 2017.03.06
START WITH CONNECT BY  (0) 2017.02.23
OVER PARTITION BY  (0) 2017.02.22
EXISTS  (0) 2017.02.21
EXPLAIN PLAN FOR  (0) 2017.02.20

SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.MANAGER_ID,

B.EMPLOYEE_ID, B.FIRST_NAME, B.MANAGER_ID

FROM EMPLOYEES A, EMPLOYEES B

WHERE A.MANAGER_ID = B.EMPLOYEE_ID;



SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.MANAGER_ID

FROM EMPLOYEES A

START WITH A.MANAGER_ID IS NOT NULL

CONNECT BY PRIOR A.MANAGER_ID = B.EMPLOYEE_ID;

'Oracle' 카테고리의 다른 글

튜닝 기본  (0) 2017.03.23
실행계획  (0) 2017.03.06
START WITH CONNECT BY  (0) 2017.02.23
OVER PARTITION BY  (0) 2017.02.22
EXISTS  (0) 2017.02.21
EXPLAIN PLAN FOR  (0) 2017.02.20

SELECT DEPARTMENT_ID, 

JOB_ID, 

COUNT(SALARY), 

SUM(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID, JOB_ID,




OVER PARTITION BY 를 쓰면 GROUP BY 를 안써도 됨

SELECT DEPARTMENT_ID, 

JOB_ID, 

COUNT(SALARY) OVER ( PARTITION BY SALARY )

SUM(SALARY) OVER ( PARTITION BY SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID, JOB_ID,



'Oracle' 카테고리의 다른 글

실행계획  (0) 2017.03.06
START WITH CONNECT BY  (0) 2017.02.23
OVER PARTITION BY  (0) 2017.02.22
EXISTS  (0) 2017.02.21
EXPLAIN PLAN FOR  (0) 2017.02.20
rowid  (0) 2017.02.14

select first_name, last_name, job_id

from employ a

where exists (select 1 from dual b

where a.job_id = 'IT_PROG');


EXISTS 다음 서브쿼리가 결과값을 가지면 보여진다.

'Oracle' 카테고리의 다른 글

실행계획  (0) 2017.03.06
START WITH CONNECT BY  (0) 2017.02.23
OVER PARTITION BY  (0) 2017.02.22
EXISTS  (0) 2017.02.21
EXPLAIN PLAN FOR  (0) 2017.02.20
rowid  (0) 2017.02.14

EXPLAIN PLAN FOR

SELECT DEPARTMENT_ID, MANAGER_ID FROM EMPLOYEES

GROUP BY DEPARTMENT_ID, MANAGER_ID ;


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

'Oracle' 카테고리의 다른 글

실행계획  (0) 2017.03.06
START WITH CONNECT BY  (0) 2017.02.23
OVER PARTITION BY  (0) 2017.02.22
EXISTS  (0) 2017.02.21
EXPLAIN PLAN FOR  (0) 2017.02.20
rowid  (0) 2017.02.14

DECLARE

V_ROWID ROWID;

V_SALARY NUMBER;

BEGIN

SELECT ROWID, SALARY

INTO V_ROWID, V_SALARY

FROM EMPLOYEES

WHERE FIRST_NAME = 'Steven'

AND LAST_NAME = 'Kind'


UPDATE EMPLOYEES SET SALARY = V_SALARY * 1.1

WHERE ROWID = V_ROWID;

END;

'Oracle' 카테고리의 다른 글

실행계획  (0) 2017.03.06
START WITH CONNECT BY  (0) 2017.02.23
OVER PARTITION BY  (0) 2017.02.22
EXISTS  (0) 2017.02.21
EXPLAIN PLAN FOR  (0) 2017.02.20
rowid  (0) 2017.02.14

+ Recent posts