si0

1. 시퀀스 작성 쿼리

CREATE SEQUENCE SEQUENCE_NAME

[    INCREMENT BY n  ]

[   START WITH  ]

[ { MAXVALUE n | NOMAXVALUE } ]

[ { MINVALUE n | NOMINVALUE } ]

[ { CYCLE | NOCYCLE } ]

[ { CACHE n | NOCACHE } ]

1. 집합 함수 (Aggregate Functions)

1) avg 함수

select avg(salary) from employees;

select employee_id, department_id, salary, 

RAOUND(AVG(salary) OVER (PARTITION BY department_id),0) "AvgByDeptid"

from employees

where department_id IN (10,20,30)

2) rank 함수

select rank(3000) whthin group(order by salary desc) "rank" from employees;

select emplyee_id, salary, rank() over (order by salary) "rank" from employees;

3) sum, min, max, count 함수

 2. 분석함수

1) first_value 함수

select employee_id, salary,

FIRST_VALUE(salary)

OVER(PARTITION BY department_id ORDER BY salary DESC)

"Highsal_Departmentid"

from employees;

2)count 함수

3) sum 함수

예전 카페에서 폐지 공지가 와서 자료를 옮겨 놓습니다.

이때도 플랜 테이블 생성안되어 있던 곳이 있었나 보네요.

create table PLAN_TABLE ( 
        statement_id    varchar2(30), 
        timestamp       date, 
        remarks         varchar2(80), 
        operation       varchar2(30), 
        options         varchar2(30), 
        object_node     varchar2(128), 
        object_owner    varchar2(30), 
        object_name     varchar2(30), 
        object_instance numeric, 
        object_type     varchar2(30), 
        optimizer       varchar2(255), 
        search_columns  number, 
        id              numeric, 
        parent_id       numeric, 
        position        numeric, 
        cost            numeric, 
        cardinality     numeric, 
        bytes           numeric, 
        other_tag       varchar2(255), 
        partition_start varchar2(255), 
        partition_stop  varchar2(255), 
        partition_id    numeric, 
        other           long, 
        distribution    varchar2(30)); 

create unique index plan_index 
on plan_table( statement_id, id ); 

table 생성후

view>option>oracle>general의 Explan Plan Table Name을 생성된 테이블 명과 동일하게 셋팅

SELECT * FROM RECYCLEBIN WHERE ORIGINAL_NAME LIKE 'E2%'

FLASHBACK TABLE E2T_EMPL_BAS_INFO TO BEFORE DROP;