오라클 교육 4일차

create table addrbook1 (        
    name varchar2(10) not null,       
    address varchar2(50),       
    age number default 0     ); 
    
-- addrbook1 을 tab, user_tables, user_catalog  딕셔너리 뷰로 확인할 수 있다.

select * from tab where table_name = 'addrbook1';
select * from user_tables where table_name = 'addrbook1';
select * from user_catalog where table_name = 'addrbook1';

--테이블 구조 확인

desc addrbook1

create table emp_job_info as select ename, job from emp where 1 > 100;  --100 은 1 보다 클 수 없음 (항상 false)
create table emp_sal_info as select ename, sal from emp where 1 > 100;  

insert all
    when deptno = 10 then
        into emp_job_info values (ename, job)
    when deptno= 20 then
        into emp_sal_infovalues (ename, sal);
        
select ename, sal , job, deptno from emp;

merge into emp_20 e20
    USING emp e
    ON(e20.empno = e.empno)
    WHEN MATCHED THEN
        UPDATE SET e20.sal = e.sal*1.1
    WHEN NOT MATCHED THEN
        INSERT(empno, ename, sal) VALUES(e.empno, e.ename, e.sal);
        
select * from emp_20;

create table t2 (      
    sale_date date,      
    id number references t1(id) on delete cascade,      
    amount number      ); 

CREATE INDEX index_name
ON table_name (Column);

-- ename 칼럼에 단일 인덱스 생성

CREATE INDEX idx_emp_ename on emp(ename)

-- emp 테이블에서 ename, sal 로 복합 인덱스 구성

create index idx_emp_ename_sal on emp(ename, sal);

select table_name, index_name, visibility from user_indexes where table_name = 'EMP';

-- 만약 ename 칼럼에 중복되는 값이 있다면 아래 create index 구문은 오류가 발생한다. 

create unique index idx_emp_ename on emp(ename); 

SELECT a.index_name
    from USER_IND_COLUMNS a, USER_INDEXES b
    where a.table_name = 'EMP'
      and a.table_name = b.index_name
      and a.column_name = 'ENAME'

create index idx_emp_job_sal on emp(job, sal desc) 

-- 1. EMP 테이블의 ENAME 칼럼에 대해 생성된 인덱스를 재구성 하세요. 
 
Alter index idx_emp_ename rebuild; 
 
-- 2. EMP 테이블의 ENAME 칼럼에 대해 생성된 인덱스를 삭제 하시요. 
 
Drop index idx_emp_ename; 

select count(*) from emp where sal> 1500;

-- 위 쿼리는 sal column에 인덱스가 걸려있다. plan 에 태울 때 INDEX RAGE SCAN을 한다.

ALTER INDEX IDX_EMP_SAL invisible

select count(*) from emp where sal> 1500;

-- 위 쿼리는 SAL COLUMN 에 걸린 인덱스를 숨겼기 때문에 TABLE ACCESS FULL 하게 된다.

create or replace view v_emp20
as
select empno, ename, sal fromemp
where deptno = 20;

CREATE SEQUENCE sequence_name
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE n| NOMAXVALUE}]
    [{MINVALUE n| MINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE n | NOCACHE}]

-- 보통 실무에서는 DEFAULT 옵션을 쓴다.

set autotrace on

select empno, ename, dname 
from emp e, dept d 
where e.deptno = d.deptno;

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    13 |   338 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    13 |   338 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    13 |   169 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP     |    13 |   169 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

select count(*) from emp;



Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------


> pl/sql ==> stored function, stored procedure

execute dbms_stats.create_stat_table(USER, 'stat_tab', 'users');

select table_name, last_analyzed from user_tables where table_name in ('EMP', 'DEPT');

TABLE_NAME                                                   LAST_ANALYZE
------------------------------------------------------------ ------------
DEPT                                                         11-FEB-19
EMP                                                          11-FEB-19

SELECT * FROM USER_TABLES WHERE TABLE_NAME = 'EMP';

begin
dbms_stats.gather_table_stas(
        USER, 'myemp1',
        method_opt => 'for all indexed columns',
        cascade => true);

select column_name, num_distinct, num_nulls
    from user_tab_col_statistics
    where table_name ='MYEMP1';

COLUMN_NAME                                                  NUM_DISTINCT  NUM_NULLS
------------------------------------------------------------ ------------ ----------
EMPNO                                                            10000002          0
ENAME                                                             9982976          0
DEPTNO                                                                  4          0
ADDR                                                              9976832          0
SAL                                                               6039040          0
JOB                                                                     6          0
COMM                                                               989888    8333335
SUNGBYUL                                                                2          0
HIREDATE                                                          3574016          0
OUTDATE                                                                 1    8333335
MGR                                                                     0   10000002

execute dbms_stats.gather_schema_stats(ownname => USER, estimate_percen => dbms_stats.auto_sample_size);

show parameter DB_FILE_MULTIBLOCK_READ_COUNT --(외워야됨) 

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_file_multiblock_read_count        integer                111

111개씩 읽어온다. (1개씩 읽는거보다 훨씬 빠를 수 밖에 없다.)

access full emp, idx ==> (block 단위로 읽고 쓴다) index fast full scan

SELECT * FROM EMP WHERE DEPTNO = 10;        
SELECT * FROM EMP WHERE DEPTNO = 20;        
SELECT * FROM EMP WHERE DEPTNO = 30;        

DECLAR
 v_sql VARCHAR2(500);
 v_cnt NUMBER;
 BEGIN
 FOR I IN 1..5 LOOP
 v_sql :='SELECT /*+ LITERAL_SHJ */ COUNT(*) FROM MYEMP1 WHERE EMPNO ='|| i;
 DBMS_OUTPUT.PUT_LINE(v_sql);
 EXECUTE IMMEDIATE v_sql INTO v_cnt;
END LOOP;
END;

SELECT /*+ 힌트 */ EMPNO, ENAME, SAL, DEPTNO
FROM EMP, DEPT -- 오라클은 뒤에서부터 읽는다 (DEPT 부터)
WHERE ~