SeekU

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

set autotrace traceonly;

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;

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,

select first_name, last_name, job_id

from employ a

where exists (select 1 from dual b

where a.job_id = 'IT_PROG');

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

EXPLAIN PLAN FOR

SELECT DEPARTMENT_ID, MANAGER_ID FROM EMPLOYEES

GROUP BY DEPARTMENT_ID, MANAGER_ID ;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

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;