DBデータ実習(3)

DBデータ実習(3)

select * from dba_tab_comments where table_name LIKE upper('emp%');
select * from dba_objects where object_name LIKE upper('%comment%');
select * from all_objects where _name LIKE upper('emp%');
select * from dba_tables where table_name LIKE upper('emp%');
select * from all_tables where table_name LIKE upper('emp%');
select * from user_tables where table_name LIKE upper('emp%');
select * from EMPLOYEES;
-----------------
select last_name, salary, salary*12, sysdate, sysdate+(1/24), sysdate from employees;
desc employees;
select 123+123 as 결과 from dual;
select '결과', 123+123 as " 결과*" from dual;

select 123+123 as "결과", '' as col1 from dual;

create table test1 (col1 number, col2 varchar2(10));
select * from TEST1;

insert into test1 (col1, col2)
select 123+123 as col1, '' as col2 from dual;
select * from TEST1;

create table TB_x3 (
a varchar2(10)
, b varchar2(20)
);
insert into TB_X3 (a) values ('1');
insert into TB_X3 (a) values ('2');
select * from TB_X3;
commit;
select b from TB_X3 where b is null;
select b from TB_X3 where b is not null;



update tb_x3 set b = 'idb' where a=1;
select b from TB_X3 where nvl(b, 'x') = 'idb';
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last -rows')); 
-- ★★★ 해석할 수 있어야함, Result - Text Output - SQL실행시간 확인

dbms_xplan.display_cursor
https://argolee.tistory.com/8

alter session set statistics_level = all;
--select a, b from TB_X3 where nvl(b, 'x') = 'idb';
select a, b from TB_X3 where b = 'idb'; 

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); 
-------------------------------------------------------------------------------------                                                  
--| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                       
-------------------------------------------------------------------------------------                                                   
--|   0 | SELECT STATEMENT  |       |      1 |        |      1 |00:00:00.01 |       8 |                                             
--|*  1 |  TABLE ACCESS FULL| TB_X3 |      1 |      1 |      1 |00:00:00.01 |       8 |                                             
-------------------------------------------------------------------------------------

||接続演算子を使用したSQL文の生成

select LAST_NAME || salary as "이름 월급" from EMPLOYEES;
select LAST_NAME || ' ' || salary as "이름 월급" from EMPLOYEES;
select '이름: ' || ' ' || LAST_NAME || ' ' || '급여: ' || salary as " " from EMPLOYEES;
select '''이름: ''' || LAST_NAME || ''' 급여: ''' || salary as " " from EMPLOYEES;
select 'insert into employees(' || LAST_NAME || ',' || SALARY || ') values ('''|| LAST_NAME || ''', ''' || SALARY || '''' || ');' as from EMPLOYEES;

SELECT法

select JOB_ID from EMPLOYEES;
select distinct job_id from EMPLOYEES;
select distinct job_id from EMPLOYEES order by JOB_ID;
select JOB_ID, count(*) from EMPLOYEES group by JOB_ID;
select JOB_ID, count(*) as "CNT" from EMPLOYEES group by JOB_ID;
select JOB_ID as "직렬군", count(*) as "인원수" from EMPLOYEES group by JOB_ID;
select JOB_ID as "직렬군", count(*) as "인원수" from EMPLOYEES group by JOB_ID order by JOB_ID;
select JOB_ID as "직렬군", count(*) as "인원수" from EMPLOYEES group by JOB_ID order by count(*);
select JOB_ID as "직렬군", count(*) as "인원수" from EMPLOYEES group by JOB_ID order by "인원수";
select JOB_ID as "직렬군", count(*) as "인원수" from EMPLOYEES group by JOB_ID order by 2;
select JOB_ID as "직렬군", count(*) as "인원수" from EMPLOYEES group by JOB_ID order by 2 DESC;
select JOB_ID as "직렬군", count(*) as "인원수" from EMPLOYEES group by JOB_ID 
                                                               having count(*) >= 3 and count(*) < 8
                                                               order by 2 DESC;
select JOB_ID as "직렬군", count(*) as "인원수" from EMPLOYEES group by JOB_ID 
                                                               having count(*) between 3 and 8
                                                               order by 2 DESC; 
                                                               
select      e.JOB_ID as "직렬군",
            count(e.job_id) as "인원수"
from        EMPLOYEES e
group by    e.JOB_ID
order by    2 DESC;

GATHER PLAN STATISTICSの使い方
http://jmkjb.blogspot.com/2015/06/gatherplanstatistics.html
???

select /*+ GATHER_PLAN_STATISTICS */ distinct job_id from EMPLOYEES;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); 

??

alter session set statistics_level = all;
select EMPLOYEE_ID,
       FIRST_NAME,
       JOB_ID,
       SALARY
  from EMPLOYEES
 where SALARY >= '10000';
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last -rows'));

---------------------------------------------------------------
- IN을 사용하고, Group by 절이 함께 사용시... Union 연산으로 자동변환 발생
이에 대한 대응 방안은 힌트 /*+ no_expand*/ 를 사용

alter session set statistics_level = all;
select *
  from EMPLOYEES
 where EMPLOYEE_ID IN (100,
               200,
               300);
 select *
  from EMPLOYEES
 where 1=1
   and (EMPLOYEE_ID = 100
            or EMPLOYEE_ID = 200
            or EMPLOYEE_ID = 300);
 
select *
  from EMPLOYEES
 where EMPLOYEE_ID = 100
 union
select *
  from EMPLOYEES
 where EMPLOYEE_ID = 200
 union
select *
  from EMPLOYEES
 where EMPLOYEE_ID = 300;
 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last -rows'));

印刷計画

alter session set statistics_level = all;


select *
  from EMPLOYEES
 where EMPLOYEE_ID IN (100,
               200,
               300);
-----------------------------------------------------------------------------------------------                                         
--| Id  | Operation                    | Name          | Starts | A-Rows |   A-Time   | Buffers |                                       
-----------------------------------------------------------------------------------------------                                         
--|   0 | SELECT STATEMENT             |               |      1 |      2 |00:00:00.01 |       4 |                                       
--|   1 |  INLIST ITERATOR             |               |      1 |      2 |00:00:00.01 |       4 |                                       
--|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      3 |      2 |00:00:00.01 |       4 |                                       
--|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |      3 |      2 |00:00:00.01 |       2 |                                       
-----------------------------------------------------------------------------------------------                               


select *
  from EMPLOYEES
 where 1=1
   and (EMPLOYEE_ID = 100
            or EMPLOYEE_ID = 200
            or EMPLOYEE_ID = 300);
-----------------------------------------------------------------------------------------------                                         
--| Id  | Operation                    | Name          | Starts | A-Rows |   A-Time   | Buffers |                                       
-----------------------------------------------------------------------------------------------                                         
--|   0 | SELECT STATEMENT             |               |      1 |      2 |00:00:00.01 |       4 |                                       
--|   1 |  INLIST ITERATOR             |               |      1 |      2 |00:00:00.01 |       4 |                                         
--|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      3 |      2 |00:00:00.01 |       4 |                                         
--|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |      3 |      2 |00:00:00.01 |       2 |                                       
---------------------------------------------------------------------------------------------------            
            
select *
  from EMPLOYEES
 where EMPLOYEE_ID = 100
 union
select *
  from EMPLOYEES
 where EMPLOYEE_ID = 200
 union
select *
  from EMPLOYEES
 where EMPLOYEE_ID = 300;
 
 ---------------------------------------------------------------------------------------------------------------------------             
--| Id  | Operation                     | Name          | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |           
---------------------------------------------------------------------------------------------------------------------------             
--|   0 | SELECT STATEMENT              |               |      1 |      2 |00:00:00.01 |       5 |       |       |          |             
--|   1 |  SORT UNIQUE                  |               |      1 |      2 |00:00:00.01 |       5 |  2048 |  2048 | 2048  (0)|             
--|   2 |   UNION-ALL                   |               |      1 |      2 |00:00:00.01 |       5 |       |       |          |             
--|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      1 |      1 |00:00:00.01 |       2 |       |       |          |             
--|*  4 |     INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |      1 |      1 |00:00:00.01 |       1 |       |       |          |             
--|   5 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      1 |      1 |00:00:00.01 |       2 |       |       |          |             
--|*  6 |     INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |      1 |      1 |00:00:00.01 |       1 |       |       |          |             
--|   7 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      1 |      0 |00:00:00.01 |       1 |       |       |          |             
--|*  8 |     INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |      1 |      0 |00:00:00.01 |       1 |       |       |          |             
---------------------------------------------------------------------------------------------------------------------------
 
 
 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last -rows'));

検索時に%の位置で発生した問題

--% 는  LIKE와 결합하여 사용하고, 사용 시 %는 문자열 뒤에 배치.
WHERE EMPLOYEE_NAME LIKE '홍%'; -->이렇게 사용
WHERE EMPLOYEE_NAME LIKE '%홍%'; -->이러면 성능에 문제가 생긴다.(안되는건 아님)
WHERE EMPLOYEE_NAME NOT LIKE '홍%'; --> 해도 됨
WHERE EMPLOYEE_NAME NOT LIKE '%홍%'; --> 절대 하면 안됨