190416(2)

-- 인덱스
-- rowid 조회하기
SELECT department_id, rowid
from EMPLOYEES;
 
-- 인덱스 생성하기
create index sample_pro_name_ix
on sample_products (product_name);
 
insert into sample_products ()
values ();
 
SELECT *
FROM SAMPLE_PRODUCTS
where PRODUCT_NAME = 'tv';
 
SELECT *
FROM SAMPLE_PRODUCTS
where PRODUCT_MAKER = '애플';
 
-- 함수 기반 인덱스 생성하기
create index emp_hire_year_ix
on employees (to_char(hire_date, 'yyyy'));
 
select *
from EMPLOYEES
where to_char(HIRE_DATE, 'yyyy') = '2008';
 
 
 
SELECT first_name
FROM EMPLOYEES
where TO_CHAR(HIRE_DATE, 'yyyy') = (SELECT TO_CHAR(HIRE_DATE, 'yyyy')
                                                 FROM EMPLOYEES
                                                 GROUP by TO_CHAR(hire_date, 'yyyy')
                                                 having count(*) = (SELECT max(개수)
                                                                         from (SELECT TO_CHAR(hire_date, 'yyyy'), count(*) 개수
                                                                                 FROM EMPLOYEES
                                                                                 GROUP by TO_CHAR(hire_date, 'yyyy'))));
 
create SEQUENCE name;