꿈꾸는 DB 전문가

https://cafe.naver.com/dbian/4272

drop table t purge ;
create table t ( a number, b varchar2(100) ) pctfree 0 initrans 1;

insert into t 
select rownum, lpad(rownum, 10) from dual connect by level <= 1000;

commit;

select t.*, dbms_rowid.rowid_block_number(rowid) block_no from t;

select dbms_rowid.rowid_block_number(rowid) block_no, min(a), max(b)
from t
group by dbms_rowid.rowid_block_number(rowid)
order by 1;

접수기간이 매우 짧아졌어요! 응시하실 분들 어여 접수하셔야 겠습니다.

-- 아래는 실기2번과 비슷한 유형으로 중요하다고 생각되는 부분만 재현한 쿼리임 

/*

SALES_P  -> 월별 파티션테이블 

ORDER_DATE VARCHAR(8)

*/

CREATE TABLE SALES_P(
    ORDER_DATE VARCHAR2(8),
    ORDER_MODE VARCHAR2(6),
    CUSTOMER_ID VARCHAR2(21),
    EMPLOYEE_ID VARCHAR2(13),
    PRODUCT_ID VARCHAR2(13),
    ORDER_QTY NUMBER,
    ORDER_AMT NUMBER
)
PARTITION BY RANGE(ORDER_DATE)  (
    PARTITION P1 VALUES LESS THAN (DATE '2012-02-01') ,
    PARTITION P2 VALUES LESS THAN (DATE '2012-03-01') ,
    PARTITION P3 VALUES LESS THAN (DATE '2012-04-01') ,
    PARTITION P4 VALUES LESS THAN (DATE '2012-05-01') ,
    PARTITION P5 VALUES LESS THAN (DATE '2012-06-01') ,
    PARTITION P6 VALUES LESS THAN (DATE '2012-07-01') ,
    PARTITION P7 VALUES LESS THAN (DATE '2012-08-01') ,
    PARTITION P8 VALUES LESS THAN (DATE '2012-09-01') ,
    PARTITION P9 VALUES LESS THAN (DATE '2012-10-01') ,
    PARTITION P10 VALUES LESS THAN (DATE '2012-11-01') ,
    PARTITION P11 VALUES LESS THAN (DATE '2012-12-01') ,
    PARTITION PMAX VALUES LESS THAN (MAXVALUE) 
    
)  ;

SELECT '일별' 구분 
      , ORDER_DATE ORDATE
      , SUM(ORDER_AMT) ORAMT 
FROM SALES_P 
WHERE ORDER_DATE BETWEEN 20120301 AND 20120331
GROUP BY ORDER_DATE
UNION ALL
SELECT '월별' 구분 
      , SUBSTR(ORDER_DATE, 1, 6) ORDATE
      , SUM(ORDER_AMT) ORAMT 
FROM SALES_P
WHERE ORDER_DATE BETWEEN 20120301 AND 20120331
GROUP BY SUBSTR(ORDER_DATE, 1, 6) 
ORDER BY 1, 2
; -- 32 

 

SQL_ID  21ndvm228700r, child number 1
-------------------------------------
Plan hash value: 230622046
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |       |  4392 (100)|          |       |       |     32 |00:00:00.30 |   15652 |       |       |          |
|   1 |  SORT ORDER BY         |         |      1 |     32 |   608 |  4391   (1)| 00:00:01 |       |       |     32 |00:00:00.30 |   15652 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL            |         |      1 |        |       |            |          |       |       |     32 |00:00:00.30 |   15652 |       |       |          |
|   3 |    PARTITION RANGE ALL |         |      1 |     31 |   589 |  2196   (1)| 00:00:01 |     1 |    12 |     31 |00:00:00.15 |    7826 |       |       |          |
|   4 |     HASH GROUP BY      |         |     12 |     31 |   589 |  2196   (1)| 00:00:01 |       |       |     31 |00:00:00.15 |    7826 |  1214K|  1214K| 1374K (0)|
|*  5 |      TABLE ACCESS FULL | SALES_P |     12 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
|   6 |    HASH GROUP BY       |         |      1 |      1 |    19 |  2196   (1)| 00:00:01 |       |       |      1 |00:00:00.15 |    7826 |  1160K|  1160K|  504K (0)|
|   7 |     PARTITION RANGE ALL|         |      1 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
|*  8 |      TABLE ACCESS FULL | SALES_P |     12 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter((TO_NUMBER("ORDER_DATE")>=20120301 AND TO_NUMBER("ORDER_DATE")<=20120331))
   8 - filter((TO_NUMBER("ORDER_DATE")>=20120301 AND TO_NUMBER("ORDER_DATE")<=20120331))
 
  SELECT DECODE(B.NO, 1 ,'일별', 2, '월별') 구분
     , DECODE(B.NO, 1, ORDER_DATE, 2, SUBSTR(ORDER_DATE, 1, 6) ) ORDATE 
     , SUM(ORDER_AMT) ORDER_AMT 
FROM (
        SELECT /*+ FULL(S)*/ORDER_DATE 
             , ORDER_AMT 
        FROM SALES_P S
        WHERE ORDER_DATE BETWEEN '20120301' AND '20120331' --> 형변환되지 않게 문자열처리 (날짜가 varchar2 타입이므로) -> 파티션푸루닝 유도 
        ) A , ( SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2) B --> 같은테이블을 반복으로 읽지않기 위해 복제이용) 
GROUP BY DECODE(B.NO, 1 ,'일별', 2, '월별')
       , DECODE(B.NO, 1, ORDER_DATE, 2, SUBSTR(ORDER_DATE, 1, 6) ) 
ORDER BY 1, 2
; -- 32

SQL_ID  21ndvm228700r, child number 1
-------------------------------------
Plan hash value: 230622046
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |       |  4392 (100)|          |       |       |     32 |00:00:00.30 |   15652 |       |       |          |
|   1 |  SORT ORDER BY         |         |      1 |     32 |   608 |  4391   (1)| 00:00:01 |       |       |     32 |00:00:00.30 |   15652 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL            |         |      1 |        |       |            |          |       |       |     32 |00:00:00.30 |   15652 |       |       |          |
|   3 |    PARTITION RANGE ALL |         |      1 |     31 |   589 |  2196   (1)| 00:00:01 |     1 |    12 |     31 |00:00:00.15 |    7826 |       |       |          |
|   4 |     HASH GROUP BY      |         |     12 |     31 |   589 |  2196   (1)| 00:00:01 |       |       |     31 |00:00:00.15 |    7826 |  1214K|  1214K| 1374K (0)|
|*  5 |      TABLE ACCESS FULL | SALES_P |     12 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
|   6 |    HASH GROUP BY       |         |      1 |      1 |    19 |  2196   (1)| 00:00:01 |       |       |      1 |00:00:00.15 |    7826 |  1160K|  1160K|  504K (0)|
|   7 |     PARTITION RANGE ALL|         |      1 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
|*  8 |      TABLE ACCESS FULL | SALES_P |     12 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

국가공인 SQLD 자격증을 준비한다면 디비안에서 이번에 출간된 SQLD 자격검정 핵심노트를 적극 추천합니다.

책소개 

초보자들이 SQL을 효과적으로 공부할 수 있도록 돕는 방법이 무엇일지 고민하던 끝에 국가공인 'SQL 전문가 가이드' 집필진이 직접 본서를 출간하게 됐다. 자격증 응시자들이 선호하는 문제 풀이 형식을 취했지만, 정확히 학습할 수 있도록 돕는 데 더 큰 목표를 두고 성심껏 실습 스크립트를 개발했다.

순서대로 직접 실습해 가는 과정을 통해 자연스럽게 원리를 이해하도록 문제를 구성했고, 이해가 부족한 초보 독자를 위해 해설도 충실히 달았다. 집필진이 집필 의도에 맞는 예상 문제를 직접 출제하고 해설하였으므로 콘텐츠의 정확성은 그 어느 책보다 신뢰할만하다.

▶ 교보문고

http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9791196395773&orderClick=LEa&Kc=

​

▶ 예스24

http://www.yes24.com/Product/Goods/96272868

​

▶ 알라딘

https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=258709220

​

▶ 인터파크

http://book.interpark.com/product/BookDisplay.do?_method=detail&sc.shopNo=0000400000&sc.prdNo=344617300&sc.saNo=003002001&bid1=search&bid2=product&bid3=title&bid4=001

oracle-base.com/articles/10g/index-monitoring

select * from dba_virus
where view_name like 'DBA%IND';

create table t5 (id varchar2(5) primary key ,
                 ord_dt date ,
                 c3 varchar2(20) ,
                 c4 varchar2(200) ) ;


drop table t5 purge;   
truncate table t5 ;

insert into t5
select * from (
  select object_id -1 , last_ddl_time, object_type, object_name
  from all_objects
  order by object_id )
where rownum <= 50000 ;

commit;
create index t5_x1 on t5(ord_dt) ;

select * from t5;

update t5 set ord_dt = to_date('2017/05/08', 'YY/MM/DD')
where id between 48000 and 49000 ;

select * from t5;
update t5 set ord_dt = to_date('2017/05/09', 'YY/MM/DD')
where id between 49001 and 50000 ;

commit;

select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4
from t5
where ord_dt > '2017/05/08'
and ord_dt <= '2017/05/09'
order by ord_dt, id, c3, c4 ;

select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4
from t5
where ord_dt > '2017/05/08'
and ord_dt <= '2017/05/09'
order by ord_dt, id;



select * from bulk_emp
where empno = 7369 ;


create table bulk_emp
as
select rownum rn, empno, ename, job, to_date('20100101', 'yyyymmdd')+lv as hiredate, sal, deptno
from emp
, (select mod(level-1, 5000) lv from dual connect by level <= 100000) ;

alter table bulk_emp add (constraint pk_bulk_emp primary key(rn));
create index ix1_bulk_emp on bulk_emp(hiredate);




select /*+ gather_plan_statistics */
hiredate, empno, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, empno, ename, job ;

select /*+ gather_plan_statistics */
hiredate, empno, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, empno ;



select /*+ gather_plan_statistics */
hiredate, rn, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, rn, ename, job ;

select /*+ gather_plan_statistics */
hiredate, rn, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, rn ;





select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));



 

SQL_ID  7r72vumqu8n7n, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4 from t5 where 
ord_dt > '2017/05/08' and ord_dt <= '2017/05/09' order by ord_dt, id, 
c3, c4
 
Plan hash value: 1711542383
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT ORDER BY                        |       |      1 |   1000 |   1000 |00:00:00.01 |      16 |   106K|   106K|96256  (0)|
|*  2 |   FILTER                              |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T5    |      1 |   1000 |   1000 |00:00:00.01 |      16 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | T5_X1 |      1 |   1000 |   1000 |00:00:00.01 |       6 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_DATE('2017/05/09')>TO_DATE('2017/05/08'))
   4 - access("ORD_DT">'2017/05/08' AND "ORD_DT"<='2017/05/09')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

SQL_ID  3pjr8972swmvm, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4 from t5 where 
ord_dt > '2017/05/08' and ord_dt <= '2017/05/09' order by ord_dt, id
 
Plan hash value: 1711542383
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT ORDER BY                        |       |      1 |   1000 |   1000 |00:00:00.01 |      16 | 99328 | 99328 |88064  (0)|
|*  2 |   FILTER                              |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T5    |      1 |   1000 |   1000 |00:00:00.01 |      16 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | T5_X1 |      1 |   1000 |   1000 |00:00:00.01 |       6 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_DATE('2017/05/09')>TO_DATE('2017/05/08'))
   4 - access("ORD_DT">'2017/05/08' AND "ORD_DT"<='2017/05/09')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |      8 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 |      8 | 52224 | 52224 |47104  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |      8 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |      8 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 52224 | 52224 |47104  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("HIREDATE">=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HIREDATE"<TO_DATE(' 2015-01-04 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
              
              
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 46080 | 46080 |40960  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------   

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 46080 | 46080 |40960  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

www.oracle.com/technetwork/database/manageability/owp-sql-monitoring-128746.pdf