Oracle 9i에서 소개된 Multitable insert는 한개의 테이블에 여러 row를 넣거나, 여러개의 테이블에 데이터를 한 insert 명령으로 넣을 수 있습니다.  이전 버전에서는 이런 기능은 PL/SQL로 구현을 해야 했으나 9i 이후로는 한 명령으로 수행할 수 있습니다.

INSERT ALL
INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)
VALUES (ord_nbr, cust_nbr, emp_id,ord_dt, ord_dt + 7, status)
INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)
VALUES (ord_nbr + 1, cust_nbr, emp_id,add_months(ord_dt, 1), add_months(ord_dt, 1) + 7, status)
INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)
VALUES (ord_nbr + 2, cust_nbr, emp_id,add_months(ord_dt, 2), add_months(ord_dt, 2) + 7, status)
INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)
VALUES (ord_nbr + 3, cust_nbr, emp_id,add_months(ord_dt, 3), add_months(ord_dt, 3) + 7, status)
INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)
VALUES (ord_nbr + 4, cust_nbr, emp_id,add_months(ord_dt, 4), add_months(ord_dt, 4) + 7, status)
INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)
VALUES (ord_nbr + 5, cust_nbr, emp_id,add_months(ord_dt, 5), add_months(ord_dt, 5) + 7, status)
SELECT 99990 ord_nbr, c.cust_nbr cust_nbr, e.emp_id emp_id,last_day(SYSDATE) ord_dt, 'PENDING' status
FROM customer c CROSS JOIN employee e
WHERE e.fname = 'MARY' and e.lname = 'TURNER'
and c.name = 'Gentech Industries';

INSERT ALL
INTO employee (emp_id, fname, lname, dept_id, hire_date) VALUES (eid, fnm, lnm, did, TRUNC(SYSDATE))
INTO salesperson (salesperson_id, name, primary_region_id) VALUES (eid, fnm || ' ' || lnm, rid)
SELECT 1001 eid, 'JAMES' fnm, 'GOULD' lnm,d.dept_id did, r.region_id rid
 FROM department d, region r
 WHERE d.name = 'SALES' and r.name = 'Southeast US';

위의 예와 같이 대상이 되는 모든 데이터를 같은/다른 테이블에 multi-row의 insert 가 가능하지만 특정 condition에 따라 insert도 가능합니다. 이 경우 INSERT FIRST를 사용하는데 이는 한개의 조건에 만족할 경우 나머지 조건은 skip되어 수행됩니다. 반대로 INSERT ALL의 경우 모든 조건을 판단하게 됩니다. 

INSERT FIRST
 WHEN order_dt < TO_DATE('2001-01-01', 'YYYY-MM-DD') THEN
  INTO cust_order_2000 (order_nbr, cust_nbr, sales_emp_id,sale_price, order_dt)
  VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)
 WHEN order_dt < TO_DATE('2002-01-01', 'YYYY-MM-DD') THEN
  INTO cust_order_2001 (order_nbr, cust_nbr, sales_emp_id,sale_price, order_dt)
  VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)
 WHEN order_dt < TO_DATE('2003-01-01', 'YYYY-MM-DD') THEN
  INTO cust_order_2002 (order_nbr, cust_nbr, sales_emp_id,sale_price, order_dt)
  VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)
SELECT co.order_nbr, co.cust_nbr, co.sales_emp_id,co.sale_price, co.order_dt
 FROM cust_order co
 WHERE co.cancelled_dt IS NULL
 AND co.ship_dt IS NOT NULL;



'Oracle Database' 카테고리의 다른 글

Oracle 11g의 새로운 diag tool, The SQL Test Case Builder  (0) 2013.11.14
Oracle 11g로 upgrade 해야 하는 이유..  (0) 2012.05.26
Oracle V$LOCK을 query할때는 ordered hint를 사용하자?  (0) 2011.08.31
oracle index_stats view의 del_lf_rows column값을 이용한 index rebuild 방안  (0) 2011.08.30
Oracle alert log의 날짜와 로그 내용을 한줄로 보기  (0) 2011.07.29

아래 blog 내용 처럼 v$lock을 조회할때 그리 시간이 많이 걸리는 경험은 없었던것 같지만..
v$lock을 조회하면 내부 fixed table join시 'MERGE JOIN CARTESIAN'이 사용되며 여기에 비용이 많이 든다는 군요.
혹시 v$lock query 결과가 늦으시면 ordered hint를 써보심이...


SQL statement for V$LOCK!!! 
select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance'); 



'Oracle Database' 카테고리의 다른 글

Oracle 11g로 upgrade 해야 하는 이유..  (0) 2012.05.26
Oracle Multitable inserts - INSERT ALL & INSERT FIRST  (0) 2011.10.21
oracle index_stats view의 del_lf_rows column값을 이용한 index rebuild 방안  (0) 2011.08.30
Oracle alert log의 날짜와 로그 내용을 한줄로 보기  (0) 2011.07.29
ORACLE /usr/lib/pa20_64/dld.sl: Unable to find library 'libskgxn2.sl'.  (0) 2011.05.31

오라클 11G R2 이전 버전에서는 BUG으로 인해 v$SQL_BIND_CAPTURE에서 TIMESTAMP 형태의 bind 값이 NULL로 보입니다. 그러나 ANYDATA.AccessTimestamp(value_anydata) 를 이용해 볼수 있는 workaround 가 있군요.

참고: V$SQL_BIND_CAPTURE Does Not Show The Value For Binds Of Type TIMESTAMP (Doc ID 444551.1)

SQL> declare 
bindts timestamp; 
begin 
bindts := systimestamp(); 
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is 
not null' using bindts; 
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is 
not null' using bindts; 
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is 
not null' using bindts; 
end; 
/ 

PL/SQL procedure successfully completed. 

SQL> select sql_id from v$sql where sql_fulltext like '%BIND_CAPTURE_TEST%' 
and sql_fulltext not like '%xxx%' and command_type = 3; 

SQL_ID 
------------- 
1mf1ch9vsr06a 

SQL> select name, position, datatype_string, was_captured, value_string, 
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = 
'1mf1ch9vsr06a'; 

NAME POSITION DATATYPE_STRING WAS 
------------------------------ ---------- --------------- --- 
VALUE_STRING 
------------------------------------------------------------------------------ 
-- 
ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA) 
--------------------------------------------------------------------------- 
:B1 1 TIMESTAMP YES 
05-JUL-07 12.20.23.311417000 PM

'Oracle Database' 카테고리의 다른 글

ORACLE Active Session History DUMP 뜨기.  (0) 2011.05.06
ORACLE CRS Resource의 상태가 UNKNOW이 되는 일반적인 이유.  (0) 2011.04.22
Oracle client program의 모듈명, Action명 정의 방법  (0) 2011.02.08
Oracle 11g에서 특정 SQL에 대해서 sql_trace 걸기  (0) 2011.01.27
Oracle의 특정 process가 CPU를 많이 먹을때: kill 대신 suspend  (0) 2011.01.05

시스템을 운영하면 가끔 특정 프로세스가 CPU를 점유해서 다른 프로세스에 영향을 끼치곤 합니다. 
이런 경우 kill -9 <process id>로 해당 프로세스를 정리하면 문제가 해결되곤 하죠. 
물론 CPU를 99%를 소모하는 프로세스는 거의 정상적이지 않은 프로세스이므로 kill 하는 게 맞지만, 
가끔 특정 batch 작업 등 많은 시간동안 수행되어 kill 시키기 아까운 프로세스들도 존재합니다. 

아래의 아이디어는 자원을 많이 먹는 프로세스를 kill 명령으로 죽이지 않고 잠시 멈춰두는 방법입니다. 

If that oracle process uses high CPU%, and don't want to kill it yet. we may use "oradebug" to suspend it.

$ ps -e -o pcpu,user,pid,args | sort -k 1 | grep 2904
62.8 oracle 2904 oracleorcl (LOCAL=NO)

SQL> oradebug suspend
Statement processed.

$ ps -e -o pcpu,user,pid,args | sort -k 1 | grep 2904
6.0 oracle 2904 oracleorcl (LOCAL=NO)

참고: http://surachartopun.com/2011/01/oradebug-man.html

물론 kill 명령도 signum (24/26)을 주면 동일하게 suspend를 시킬 수 있습니다. 


   signum   signame   Name            Description
   ___________________________________________________________________________
      0     SIGNULL   Null            Check access to pid
      1     SIGHUP    Hangup          Terminate; can be trapped
      2     SIGINT    Interrupt       Terminate; can be trapped
      3     SIGQUIT   Quit            Terminate with core dump; can be trapped
      9     SIGKILL   Kill            Forced termination; cannot be trapped
     15     SIGTERM   Terminate       Terminate; can be trapped
     24     SIGSTOP   Stop            Pause the process; cannot be trapped
     25     SIGTSTP   Terminal stop   Pause the process; can be trapped
     26     SIGCONT   Continue        Run a stopped process

이런 기능을 운영 서버에 사용하시려면 먼저 정상적으로 수행되는지는 테스트 해봐야 겠죠? 
대부분 요런 기능은 hang 같은 테스트 환경 만들 때 주로 쓰긴 합니다..만 알아서 하시길... ^^;

P.S. 
OS kill 명령에 의한 stop과 oradebug를 이용한 suspend의 차이는 뭘까요? 
suspend 된 세션은 v$session 등에서 어떤 상태로 보여질까요? 


'Oracle Database' 카테고리의 다른 글

Oracle client program의 모듈명, Action명 정의 방법  (0) 2011.02.08
Oracle 11g에서 특정 SQL에 대해서 sql_trace 걸기  (0) 2011.01.27
Oracle UTL_FILE을 이용한 데이터 Unloading의 간단한 예제  (0) 2010.12.28
Oracle 11g의 새로운 HINT : IGNORE_ROW_ON_DUPKEY_INDEX  (0) 2010.12.23
Oracle Interval Partition drop할때 ORA-14758 발생  (0) 2010.12.08

간단히 UTL_FILE을 이용한 scott.emp 테이블 데이터 unloading 하기
create or replace procedure utl_file_test_write (
  filename   in varchar2
  )
is
   v_file            UTL_FILE.FILE_TYPE;
   v_text            VARCHAR2 (20);
   v_filename        VARCHAR2 (200);
   v_delimitedchar   CHAR (1);
BEGIN
   v_delimitedchar := CHR (124);
   v_file := UTL_FILE.FOPEN ('DATA_PUMP_DIR', filename, 'W');
   FOR rec IN
      (SELECT   empno,ename,job,mgr,hiredate,sal,comm,deptno FROM scott.emp)
   LOOP
      UTL_FILE.PUT_LINE (v_file,rec.empno|| v_delimitedchar|| rec.ename||v_delimitedchar|| 
                         rec.job||v_delimitedchar|| rec.mgr||v_delimitedchar||rec.hiredate||
                         v_delimitedchar|| rec.sal ||v_delimitedchar|| rec.comm|| v_delimitedchar|| 
                         rec.deptno  );
   END LOOP; 
   UTL_FILE.FCLOSE (v_file);
END;
/

수행 및 결과
SQL> exec utl_file_test_write ('TEST01');
SQL> !cat /oracle_home/TEST01
7369|SMITH|CLERK|7902|1980-12-17|800||20
7499|ALLEN|SALESMAN|7698|1981-02-20|1600|300|30
7521|WARD|SALESMAN|7698|1981-02-22|1250|500|30
7566|JONES|MANAGER|7839|1981-04-02|2975||20
7654|MARTIN|SALESMAN|7698|1981-09-28|1250|1400|30
7698|BLAKE|MANAGER|7839|1981-05-01|2850||30
7782|CLARK|MANAGER|7839|1981-06-09|2450||10
7788|SCOTT|ANALYST|7566|1982-12-09|3000||20
7839|KING|PRESIDENT||1981-11-17|5000||10
7844|TURNER|SALESMAN|7698|1981-09-08|1500|0|30
7876|ADAMS|CLERK|7788|1983-01-12|1100||20
7900|JAMES|CLERK|7698|1981-12-03|950||30
7902|FORD|ANALYST|7566|1981-12-03|3000||20

'Oracle Database' 카테고리의 다른 글

Oracle 11g에서 특정 SQL에 대해서 sql_trace 걸기  (0) 2011.01.27
Oracle의 특정 process가 CPU를 많이 먹을때: kill 대신 suspend  (0) 2011.01.05
Oracle 11g의 새로운 HINT : IGNORE_ROW_ON_DUPKEY_INDEX  (0) 2010.12.23
Oracle Interval Partition drop할때 ORA-14758 발생  (0) 2010.12.08
Oracle Global Temporary Table  (0) 2010.12.08

블로그 구경다니다가 11g에서 쓸만한 hint 하나 확인해서 공유합니다. 
구구절절한 설명 보다 아래의 예를 보는게 가장 이해하기 쉽겠네요. 


However, Oracle11g allows us to use the IGNORE_ROW_ON_DUPKEY_INDEX hint, which will silently deal with the unique constraint violation errors by simply ignoring and not inserting any row in which the unique values already exist in the table.



SQL> insert into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 12;
insert into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 12
*
ERROR at line 1:
ORA-00001: unique constraint (BOWIE.RADIOHEAD_PK_I) violated

SQL> insert /*+ ignore_row_on_dupkey_index(radiohead,radiohead_pk_i) */ into radiohead select rownum, 
'OK COMPUTER' from dual connect by level <= 12;
2 rows created.

SQL> commit;
Commit complete.

위의 예를 보면 알겠지만 unique key 값이 없는 row에 대해서만 insert가 발생합니다. 에러는 생략되고요.. 
즉 새로 입력되는 값이 기존의 unique 값과 constraint violation이 발생한다면 무시되는 거죠.. 

이 hint는 update에는 사용할 수 없습니다. 또 parallel DML과 APPEND mode일 경우에는 무시됩니다. 
몇몇 요구사항에 맞지 않는다면 ORA-38912, ORA-38915, ORA-38913이 발생합니다. 

자세한 내용은 아래 메뉴얼에서...

'Oracle Database' 카테고리의 다른 글

Oracle의 특정 process가 CPU를 많이 먹을때: kill 대신 suspend  (0) 2011.01.05
Oracle UTL_FILE을 이용한 데이터 Unloading의 간단한 예제  (0) 2010.12.28
Oracle Interval Partition drop할때 ORA-14758 발생  (0) 2010.12.08
Oracle Global Temporary Table  (0) 2010.12.08
오라클 테스트 환경 만들기  (0) 2010.11.29

interval partition을 drop할 때 ORA-14758 에러가 발생하는 군요. 
메세지로만 봐서는 마지막 partition은 drop될 수 없다라는데...
분명 마지막 range의 partition도 아니고.

아래 노트를 보면 명확히 설명되어 있네요. 
마지막 partition이라는 게 처음 table을 생성할 때 만들어진 마지막 partition이며 
이후 DB에서 자동으로 interval partition을 만들때 이 partition을 참조하기 때문에 삭제하면 안된답니다. 

그럼 처음 interval partition을 만들때 마지막 partition은 안지워지니까 
주기적인 데이터 삭제용도로 사용하시려면 마지막 partition의 range에 대해 고려를 좀 해보셔야 겠죠? 

ORA-14758: Last Partition In The Range Section Cannot Be Dropped (Doc ID 882681.1)

Symptoms

You drop a partition and receive the following error:
ORA-14758: Last partition in the range section cannot be dropped

Cause

In case of interval partitioning the partitions which are created automatically depends on the last partition created as a reference while creating a table. So the last partition can not be dropped.

Example:

create table test.orders
(order_id number(12),
order_date date
)
partition by range(order_date)
interval(numtoyminterval(1, 'month'))
(partition p1 values less than (to_date('2000-01-01', 'YYYY-MM-DD')),
partition p2 values less than (to_date('2000-04-01', 'YYYY-MM-DD')),
partition p3 values less than (to_date('2000-07-01', 'YYYY-MM-DD')));

insert into test.orders values(1, to_date('2000-02-15', 'YYYY-MM-DD')) ;
insert into test.orders values(1, to_date('2000-03-15', 'YYYY-MM-DD')) ;
insert into test.orders values(1, to_date('2000-05-15', 'YYYY-MM-DD')) ;
insert into test.orders values(1, to_date('2000-06-15', 'YYYY-MM-DD')) ;
insert into test.orders values(1, to_date('2000-08-15', 'YYYY-MM-DD')) ;
insert into test.orders values(1, to_date('2000-09-15', 'YYYY-MM-DD')) ;

commit;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name='ORDERS';

TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ORDERS P1 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS P2 TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS P3 TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS SYS_P115 TO_DATE(' 2000-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS SYS_P116 TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> alter table test.orders drop partition SYS_P116;

Table altered.

SQL> alter table test.orders drop partition P2;

Table altered.

SQL> alter table test.orders drop partition P3;
alter table test.orders drop partition P3
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

Solution

This is expected behaviour.
Do not drop the last partition on which the interval partitions are based.

오류에 대해 좀 찾아 보니 interval을 임시로 바꿔 지우는 방법이 나와 있습니다. 
하지만 오라클쪽 자료(metalink)에는 그런 내용은 없는 것 같습니다. 
요 방법 써도 괜찮을라나? 

'Oracle Database' 카테고리의 다른 글

Oracle UTL_FILE을 이용한 데이터 Unloading의 간단한 예제  (0) 2010.12.28
Oracle 11g의 새로운 HINT : IGNORE_ROW_ON_DUPKEY_INDEX  (0) 2010.12.23
Oracle Global Temporary Table  (0) 2010.12.08
오라클 테스트 환경 만들기  (0) 2010.11.29
Oracle Begin backup에 대한 몇몇 궁금한 사항.  (0) 2010.11.25

임시 테이블은 사용자가 DML(update, insert, delete) 문을 실행한 후, 트랜젝션을 종료(commit)하면 변경된 데이터들이 테이블에 저장되지 않는 테이블이다. 어떤 응용을 개발할 때 데이터를 잠시 저장하는 변수와 같은 유형이다.

temporary table은 특정 session 또는 transaction 동안만 data를 저장한다. 

on commit delete rows : 
트랜젝션을 발생시킨 후, commit문을 실행할 때 없어지는 방법. 한 transaction 동안만 data를 저장(default임)

on commit preserve rows : 
트랜젝션을 종료하면(commit) 테이블 내에 데이터가 저장되었다가 세션을 종료하면 임시 테이블에 저장되었던 데이터들이 모두 없어지는 방법. 한 session 동안만 data를 저장

temporary table은 다음과 같은 성격을 갖는다. 

TABLE의 definition 정보는 data dictionary에 영구적으로 저장되지만, data는 일시적으로 저장된다.
CREATE로 TABLE 생성시 SEGMENT가 할당되는 것이 아니라 첫 번째 INSERT시 할당된다.
DML 작업시 해당 data에 대해 LOCK을 걸지 않는다.
data 변경시 redo log에 기록이 남지 않는다.

다음은 간단한 테스트 이다. 

1. global temporary table 생성 

SQL> create global temporary table gtt ( x int );

Table created.

2. object 생성 확인 

SQL> select dbms_metadata.get_ddl( 'TABLE', 'GTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','GTT')
--------------------------------------------------------------------------------
  CREATE GLOBAL TEMPORARY TABLE "SCOTT"."GTT"
   (    "X" NUMBER(*,0)
   ) ON COMMIT DELETE ROWS

SQL> select object_name,object_type from user_objects where object_name = 'GTT';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------
GTT                            TABLE

SQL> select table_name from user_tables where table_name = 'GTT';

TABLE_NAME
------------
GTT

SQL> select table_name,temporary,duration from user_tables where table_name = 'GTT';

TABLE_NAME           TEM DURATION
-------------------- --- ---------------------------------------------
GTT                  Y   SYS$TRANSACTION

SQL> select segment_name from user_segments where segment_name = 'GTT';

no rows selected

SQL> drop table gtt;

Table dropped.

3. global temporary table 생성 

SQL> create global temporary table gtt ( x int ) on commit preserve rows;

Table created.

4. object 생성 확인 

SQL> select dbms_metadata.get_ddl( 'TABLE', 'GTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','GTT')
--------------------------------------------------------------------------------
  CREATE GLOBAL TEMPORARY TABLE "SCOTT"."GTT"
   (    "X" NUMBER(*,0)
   ) ON COMMIT PRESERVE ROWS


SQL> select object_name,object_type from user_objects where object_name = 'GTT';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------- 
GTT                            TABLE

SQL> select table_name from user_tables where table_name = 'GTT';

TABLE_NAME
-------------------- 
GTT

SQL> select table_name,temporary,duration from user_tables where table_name = 'GTT';

TABLE_NAME           TEM DURATION
-------------------- --- ---------------------------------------------
GTT                  Y   SYS$SESSION

SQL> select segment_name from user_segments where segment_name = 'GTT';

no rows selected

SQL> drop table gtt;

Table dropped.


'Oracle Database' 카테고리의 다른 글

Oracle 11g의 새로운 HINT : IGNORE_ROW_ON_DUPKEY_INDEX  (0) 2010.12.23
Oracle Interval Partition drop할때 ORA-14758 발생  (0) 2010.12.08
오라클 테스트 환경 만들기  (0) 2010.11.29
Oracle Begin backup에 대한 몇몇 궁금한 사항.  (0) 2010.11.25
Oracle LOCAL INDEX DROP 하는 방법  (0) 2010.11.23

Oracle LOCAL INDEX를 drop하는 방법은 없습니다. ㅋ
아래 테스트를 보시면 local partition index로 만들어진 index는 drop이 되지 않는 군요.. 

LOCAL partition index는 partition table의 구조를 참조해서 만들어 지기 때문에 drop이 되지 않습니다. 
그러나 Global partition index는 자신만의 구조를 갖기 때문에 drop이 가능합니다.. 

+ LOCAL INDEX

SQL> create table p_emp (sal number(10))
 2  partition by range (sal)
 3   (partition emp_p1 values less than (2000),
 4    partition emp_p2 values less than (3000),
 5    partition emp_max values less than (maxvalue));

Table created.

SQL> create index p_emp_i on p_emp (sal) local;

Index created.

SQL> alter index p_emp_i drop partition emp_p1;
alter index p_emp_i drop partition emp_p1
           *
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index

+ GLOBAL INDEX

 1  create table p_emp (empno number(4), sal number(10))
 2  partition by range (sal)
 3   (partition emp_p1 values less than (2000),
 4    partition emp_p2 values less than (3000),
 5*   partition emp_pmax values less than (maxvalue))
SQL> /

Table created.

 1  create index p_emp_i on p_emp (sal)
 2  global  partition by range (sal)
 3   (partition emp_p1 values less than (2000),
 4    partition emp_p2 values less than (3000),
 5*   partition emp_max values less than (maxvalue))
SQL> /

Index created.

SQL>
SQL> alter index p_emp_i drop partition emp_p2;

Index altered.

+ 참고

'Oracle Database' 카테고리의 다른 글

오라클 테스트 환경 만들기  (0) 2010.11.29
Oracle Begin backup에 대한 몇몇 궁금한 사항.  (0) 2010.11.25
TRUSS 명령과 SQLNET tracing 설정 방법  (0) 2010.11.04
Oracle UTL_FILE을 이용한 데이터 loading의 간단한 예제  (0) 2010.11.03
DBMS_JOB으로 만들어진 JOB의 interval을 1초로 지정해도 5초 정도의 interval을 갖는다?  (0) 2010.10.12

UTL_FILE을 이용해 file을 loading 하는 아주 간단한 example. 

declare
   fp utl_file.file_type;
   str varchar2(48);
   VAL SCOTT.TEST%ROWTYPE;   
begin
   fp := utl_file.fopen('DATA_PUMP_DIR','sample_data.txt','r');
   loop
    utl_file.get_line(fp,str);
    VAL.CUST_ID := TRIM(SUBSTR(str,1,9));
    VAL.FC_ID := TRIM(SUBSTR(str,11,20));
    insert into SCOTT.TEST values(VAL.CUST_ID,VAL.FC_ID);
   end loop;
exception
when no_data_found then
   commit;
end;
/


'Oracle Database' 카테고리의 다른 글

Oracle LOCAL INDEX DROP 하는 방법  (0) 2010.11.23
TRUSS 명령과 SQLNET tracing 설정 방법  (0) 2010.11.04
DBMS_JOB으로 만들어진 JOB의 interval을 1초로 지정해도 5초 정도의 interval을 갖는다?  (0) 2010.10.12
DBMS_TRANSACTOIN.STEP_ID로 내 세션의 TX 존재여부파악하기  (0) 2010.10.11
SGA - library cache lock/pin II  (1) 2010.09.20
네이버 지식인에 DBMS_JOB으로 만들어진 JOB의 interval을 1초로 지정해도 
5초 정도의 interval을 갖는다는 질문이 있어 간단히 테스트 해 봤습니다. 

DBMS_JOB package로 job을 생성합니다 
SQL> variable jobno number;
SQL> begin
SQL> dbms_job.submit(:jobno,'insert into test values (sysdate);',
SQL> sysdate,'sysdate+1/60/24/50',FALSE);
SQL> end;
SQL> /

PL/SQL procedure successfully completed.

SQL> print jobno
     JOBNO
----------
        21

SQL> exec dbms_job.run(21);
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

SQL> select * from user_jobs;

       JOB LOG_USER                       PRIV_USER                      SCHEMA_USER                    LAST_DATE LAST_SEC         THIS_DATE THIS_SEC         NEXT_DATE NEXT_SEC         TOTAL_TIME B
---------- ------------------------------ ------------------------------ ------------------------------ --------- ---------------- --------- ---------------- --------- ---------------- ---------- -
INTERVAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  FAILURES
----------
WHAT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MISC_ENV                                                           INSTANCE
---------------------------------------------------------------- ----------
        21 SYS                            SYS                            SYS                            12-OCT-10 16:50:28                                    12-OCT-10 16:50:29                  0 N
sysdate+1/60/24/50
         0
insert into test values (sysdate);
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000                                                          0

JOB 내역을 보면 정상적으로 NEXT_DATE는 LAST_DATE + 1초로 나옵니다 

SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
Session altered.

SQL> select * from test;
COMMIT_DATE
-------------------
2010/10/12 16:50:04
2010/10/12 16:50:08
2010/10/12 16:50:13
2010/10/12 16:50:18
2010/10/12 16:50:23
2010/10/12 16:50:28
2010/10/12 16:50:33
2010/10/12 16:50:38
2010/10/12 16:50:43
2010/10/12 16:50:48
2010/10/12 16:50:53
2010/10/12 16:50:58
2010/10/12 16:51:03
2010/10/12 16:51:08

14 rows selected.

그러나 table에 들어간 시간을 보니 4초 차이가 발생하는 군요...

SQL> exec dbms_job.broken(21,TRUE);
PL/SQL procedure successfully completed.

SQL> exec dbms_job.remove(21);
PL/SQL procedure successfully completed.

SQL> delete from test;
25 rows deleted.

SQL> commit;
Commit complete.

이번엔 DBMS_SCHEDULER로 테스트 해봤습니다. 

SQL> begin
  2  dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_1_SECONDS',
  start_date    => trunc(sysdate),
  repeat_interval => 'freq=SECONDLY;interval=1',
  comments     => 'Runtime: Every day all 1 SECONDS');  3    4    5    6  
  7  end;
  8  /

PL/SQL procedure successfully completed.

1초에 한번씩 수행할 Schedule을 생성합니다 

SQL> begin
dbms_scheduler.create_program
(program_name=> 'TEST_PROC01',
 program_type=> 'STORED_PROCEDURE',
 program_action=> 'SYS.TEST_PROC',
 enabled=>true,
 comments=>'job interval test'
 );
end;
/ 
PL/SQL procedure successfully completed.

수행할 Program을 생성합니다. 
TEST_PROC는 test table에 sysdate를 입력하는 procedure 입니다.

SQL> begin
dbms_scheduler.create_job
 (job_name => 'JOB_TEST',
  program_name=> 'TEST_PROC01',
  schedule_name=>'INTERVAL_EVERY_1_SECONDS',
  enabled=>true,
  auto_drop=>false,
  comments=>'job test');
end;
/  
PL/SQL procedure successfully completed.

이제 먼저 생성한 schedule과 program을 이용해 JOB을 생성합니다 

SQL> begin
dbms_scheduler.run_job('JOB_TEST',TRUE);
end; 
/
PL/SQL procedure successfully completed.



SQL> select * from test;

COMMIT_DATE
-------------------
2010/10/12 17:12:38
2010/10/12 17:12:39
2010/10/12 17:12:40
2010/10/12 17:12:41
2010/10/12 17:12:42
2010/10/12 17:12:43
2010/10/12 17:12:44
2010/10/12 17:12:45
2010/10/12 17:12:46
2010/10/12 17:12:47
2010/10/12 17:12:48
2010/10/12 17:12:49
2010/10/12 17:12:50
2010/10/12 17:12:51
2010/10/12 17:12:52
2010/10/12 17:12:53
2010/10/12 17:12:54
2010/10/12 17:12:55
2010/10/12 17:12:55
2010/10/12 17:12:56
2010/10/12 17:12:57
2010/10/12 17:12:58
2010/10/12 17:12:59
2010/10/12 17:13:00
2010/10/12 17:13:01
...

table에 들어간 데이터를 보니 1초에 한번씩 수행하고 있군요.. 

DBMS_JOB을 이용한 job은 interval에 제한이 있는 걸까요? 그런 자료는 못 본것 같은데..

예전 oracle 8에서 job_queue_interval parameter가 있을때
수행 시간이 job_queue_interval 주기때문에 job interval이 정확히 계산되지 않았던 것 같은데... 
혹시 job_queue_process가 wake-up 하는 시간때문일까요?  





'Oracle Database' 카테고리의 다른 글

TRUSS 명령과 SQLNET tracing 설정 방법  (0) 2010.11.04
Oracle UTL_FILE을 이용한 데이터 loading의 간단한 예제  (0) 2010.11.03
DBMS_TRANSACTOIN.STEP_ID로 내 세션의 TX 존재여부파악하기  (0) 2010.10.11
SGA - library cache lock/pin II  (1) 2010.09.20
Oracle 11g SQLPLUS의 errorlogging 기능  (0) 2010.09.17

10. 10. 6 작성자: Laurent Schneider의 Laurent Schneider

Pretty straightforward, check if dbms_transaction.step_id is null!

SQL> select dbms_transaction.step_id from dual;
        STEP_ID
---------------

SQL> insert into t values (1);
1 row created.

SQL> select dbms_transaction.step_id from dual;
        STEP_ID
---------------
114352430549782

SQL> commit;
Commit complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------


STEP_ID가 어떤의미일까요?
메뉴얼엔 step_id function에 대해 아래와 같이 정의도어 있는데, 무슨 말인지 잘 모르겠네요. 

"This function returns local (to local transaction) unique positive integer that orders the DML operations of a transaction."

테스트를 해보자면...

SQL> delete from yhcho_temp where col1 = 'TURNER';
1 row deleted.

SQL> select dbms_transaction.step_id from dual;
                   STEP_ID
--------------------------
         37783619732783637

SQL> savepoint A ;
Savepoint created.

SQL> delete from yhcho_temp where col1 = 'KING';
1 row deleted.

SQL> select dbms_transaction.step_id from dual;
                   STEP_ID
--------------------------
         37783619732783639

SQL> rollback to savepoint A;
Rollback complete.

SQL> select dbms_transaction.step_id from dual;
                   STEP_ID
--------------------------
         37783619732783639

savepoint A까지 rollback 해도 step_id는 바뀌지 않는 군요.. 
단순히 transaction step에 대한 sequence 일까요? 

SQL> delete from yhcho_temp where col1 = 'KING';
1 row deleted.

SQL> select dbms_transaction.step_id from dual;
                   STEP_ID
--------------------------
         37783619732783641

특정 transaction을 가리키는 것 같진 않고 
위의 post 처럼 step_id로는 단순히 transaction이 있는지 여부만 확인할 수 있을 것 같네요..

SQL> desc dbms_transaction        
PROCEDURE ADVISE_COMMIT
PROCEDURE ADVISE_NOTHING
PROCEDURE ADVISE_ROLLBACK
PROCEDURE BEGIN_DISCRETE_TRANSACTION
PROCEDURE COMMIT
PROCEDURE COMMIT_COMMENT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CMNT                           VARCHAR2                IN
PROCEDURE COMMIT_FORCE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 XID                            VARCHAR2                IN
 SCN                            VARCHAR2                IN     DEFAULT
FUNCTION LOCAL_TRANSACTION_ID RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CREATE_TRANSACTION             BOOLEAN                 IN     DEFAULT
PROCEDURE PURGE_LOST_DB_ENTRY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 XID                            VARCHAR2                IN
PROCEDURE PURGE_MIXED
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 XID                            VARCHAR2                IN
PROCEDURE READ_ONLY
PROCEDURE READ_WRITE
PROCEDURE ROLLBACK
PROCEDURE ROLLBACK_FORCE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 XID                            VARCHAR2                IN
PROCEDURE ROLLBACK_SAVEPOINT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SAVEPT                         VARCHAR2                IN
PROCEDURE SAVEPOINT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SAVEPT                         VARCHAR2                IN
FUNCTION STEP_ID RETURNS NUMBER
PROCEDURE USE_ROLLBACK_SEGMENT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RB_NAME                        VARCHAR2                IN


'Oracle Database' 카테고리의 다른 글

Oracle UTL_FILE을 이용한 데이터 loading의 간단한 예제  (0) 2010.11.03
DBMS_JOB으로 만들어진 JOB의 interval을 1초로 지정해도 5초 정도의 interval을 갖는다?  (0) 2010.10.12
SGA - library cache lock/pin II  (1) 2010.09.20
Oracle 11g SQLPLUS의 errorlogging 기능  (0) 2010.09.17
Oracle Trigger를 이용해 특정 column에 대한 변경 막기  (0) 2010.08.11

10. 8. 16 작성자: April C Sims의 Oracle High Availability

New error logging facility in 11g…spool errors from a table that traps them. Table is automatically created after using the SET command in SQLPLUS.

Data stored in the sperrorlog persists between sessions and whether it is interactive or non-interactive such as a script.

Should you turn on errorlogging for SYS?  Sort of a catchall auditing for errors not ordinarily trapped? I am turning on sperrorlog for SYS in a non-production 11gR2 database to see what happens.

New 11g SQLPLUS Parameter ERRORLOGGING [ID 471066.1]


11g에서는 sqlplus에서 발생하는 에러를 logging 할 수 있다는 군요. 
(이 사람들은 새로운 버전 나올때 마다 모든 메뉴얼을 다 보는 것일까요? 존경스럽습니다.. )

위의 내용보고 따라해 봤습니다..

$ sqlplus system
SQL> set errorlogging on
SQL> select * from sperrorlog;

no rows selected

SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> set pagesize 200
SQL> select * from sperrorlog;

USERNAME
--------------------------------------------------------------------------------
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
IDENTIFIER
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
SYSTEM
2010-09-17-13.53.55.000000


ORA-00942: table or view does not exist
select * from scott.emp

잘 되는 군요..
sqlplus를 재 접속해도 위의 에러정보는 계속 남습니다. 

그러면 purge는 어떻게 할까요? 
따로 나와있는 내용이 없는 거 같으니, 아마 delete나 truncate로 해야하는 거 같긴한데..
요건 좀 나중에 확인해 보고..

user가 table을 만들고 이걸 지정할 수 도 있답니다. 

SQL>  SET ERRORLOGGING ON TABLE enduser_sperrorlog;

위의 블로그 한번 들러 보시지요~ ^^

'Oracle Database' 카테고리의 다른 글

DBMS_TRANSACTOIN.STEP_ID로 내 세션의 TX 존재여부파악하기  (0) 2010.10.11
SGA - library cache lock/pin II  (1) 2010.09.20
Oracle Trigger를 이용해 특정 column에 대한 변경 막기  (0) 2010.08.11
Oracle 10g에서 11g로 upgrade 할때, 기존의 hint를 삭제해야 할까요?  (0) 2010.08.09
ORACLE GROUP BY와 함께 쓰이는 HAVING 절  (1) 2010.08.06

사용자 table 데이터 중에서 꼭 변경되면 안되는 데이터는 반드시 있기마련입니다. 
예를 들자면, 이름과 주민등록 번호, 학생과 학번, 등등..

위의 데이터 처럼 입력은 가능해야 하나 한번 입력된 특정 column을 변경되지 않게 하기 위해서는 어떤 방법이 있을까요? 
가장 쉽게 생각할 수 있는게 trigger 입니다. 
다음은 trigger를 이용해 특정 column을 변경하지 못하게 설정하는 trigger sample 입니다. 

(사실 trigger 이외에 딱히 생각나는 방법이 없네요 --;)

1. SCOTT user의 EMP table에 trigger를 설정합니다. 

CREATE TRIGGER emp_upd_trigger
BEFORE UPDATE on emp
declare
    v_error VARCHAR2(256);
begin
    if (updating('EMPNO') or updating('ENAME'))
     then
         v_error:='You cannot update the empno';
         raise_application_error (-20999,v_error);
     end if;
end;
/

2. trigger가 만들어 졌으면 간단히 테스트를 해볼까요? 

SQL> select * from emp;

     EMPNO ENAME                          JOB                                MGR HIREDATE          SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ---------- ---------- ---------- ----------
      7900 JAMES                          CLERK                             7698 1981-12-03        950                    30
      7902 FORD                           ANALYST                           7566 1981-12-03       3000                    20
      7934 MILLER                         CLERK                             7782 1982-01-23       1300                    10

SQL> update emp set empno = 9999 where ename = 'MILLER';
update emp set empno = 9999 where ename = 'MILLER'
       *
ERROR at line 1:
ORA-20999: You cannot update the empno or ename
ORA-06512: at "SCOTT.EMP_UPD_TRIGGER", line 7
ORA-04088: error during execution of trigger 'SCOTT.EMP_UPD_TRIGGER'

SQL> update emp set ename = 'ORACLE' where empno = 7934;
update emp set ename = 'ORACLE' where empno = 7934
       *
ERROR at line 1:
ORA-20999: You cannot update the empno or ename
ORA-06512: at "SCOTT.EMP_UPD_TRIGGER", line 7
ORA-04088: error during execution of trigger 'SCOTT.EMP_UPD_TRIGGER'

이 방법 이외에 다른 방법 아시는 분.. 
좀 알려주세요.. ^^


* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

SGA - library cache lock/pin II  (1) 2010.09.20
Oracle 11g SQLPLUS의 errorlogging 기능  (0) 2010.09.17
Oracle 10g에서 11g로 upgrade 할때, 기존의 hint를 삭제해야 할까요?  (0) 2010.08.09
ORACLE GROUP BY와 함께 쓰이는 HAVING 절  (1) 2010.08.06
ORACLE Nested Group Operations  (0) 2010.08.06


We promised in an earlier post to cover some of the questions from the Optimizer round table discussion at Oracle Open World. Here's our first in a series of posts that will address these questions. 

Q: When moving from 10g to 11g, should hints in existing SQL be removed? 

A: I was glad to see this question at the round table, since I think this is something people often overlook when adding hints to an application. Over time, hints can become stale. You may have added a hint to a query because of a weakness in an old version of the optimizer, but the hint might no longer be necessary to get the best plan. Even worse, the hint may force a plan which is suboptimal, for a couple of reasons:

이 질문은 오라클 데이터베이스 관계자라면 매우 자주 듣는 질문이죠. 
이전 버전의 SQL에 적용된 hint를 제거하는게 맞을까요? 아님 계속 사용하는게 맞을까요? 
물론 답은 당연히 제거하라 겠죠? 

새로운 버전에서, 그리고 변경되는 데이터 상에서 해당 hint가 적정할지,
또 hint를 사용한 사람이 선택한 plan이 과연 적정할지.. 
그 hint는 새로운 버전에서 계속 존재하는지..
이러한 판단이 가능한 사람이 있을까 모르겠네요.. 

위의 포스트는 오라클 optimizer 팀 blog의 post 입니다. open world에서 질문 나온걸 정리한다네요. 
한번 읽어 보세요 ~
baseline을 이용한 plan 고정하는 방법도 나오네요..

그나저나 _optimizer_ignore_hints parameter는 언제 생긴걸까? 
이거 쓸만할 거 같은데, 확인 좀 해 봐야겠네요~


* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

Oracle 11g SQLPLUS의 errorlogging 기능  (0) 2010.09.17
Oracle Trigger를 이용해 특정 column에 대한 변경 막기  (0) 2010.08.11
ORACLE GROUP BY와 함께 쓰이는 HAVING 절  (1) 2010.08.06
ORACLE Nested Group Operations  (0) 2010.08.06
Oracle Table의 통계정보를 옮기는 방법.  (1) 2010.08.03

이 자료는 O'Reilly에서 나온 " Mastering Oracle SQL, 2nd Edition"을 공부하면서 몰랐던 부분을 정리하고 있습니다. 
"Mastering Oracle SQL, 2nd Edition"의 저자는 Alan Beaulieu, Sanjay Mishra 이며 2004년에 나온 책이네요. 
알라딘에서 구해 볼 수 있습니다. 
HAVING 절은 항상 쓰면서 따로 메뉴얼을 읽어본적은 없다.
그래서 아마 group 함수의 결과에 대해 조건을 주는거? 이렇게 생각하고 있었는데, 
역시 메뉴얼을 읽어야해 ~ 

HAVING 절은 GROUP BY 절에 의해 나오는 나올수 있는 결과에 대해 filtering 해주는 역활을 해준다. 
즉 내가 지금까지 생각하던 거와는 달리 
group 함수의 결과 컬럼에 대해서만 조건을 주는 게 아니라 
결과 SET group by 절에 의해 나올 수 있는 결과 set 에 대해 조건을 줄 수 있다. 

다음의 예를 보면 .. 
부서별 인원수를 구하는 SQL이다.
SQL> select deptno,count(*) from emp group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

이 중인 인원수가 4이상인 부서를 출력한다. 즉 group 함수에 의해 계산된 값에 대한 조건이다. 
SQL> select deptno,count(*) from emp group by deptno having count(*) > 4;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5

아래의 SQL은 group by 에 의해 만들어진 결과에서 deptno가 20 이상인 부서만 출력한다. 
SQL> select deptno,count(*) from emp group by deptno having deptno > 20;

    DEPTNO   COUNT(*)
---------- ----------
        30          6

그러면 group by 절에 의한 결과에 대한 having과 select 절의 where 절은 바꿔 쓸 수 있을까?
아래의 결과를 보면 having deptno > 20 과 where deptno > 20의 결과는 같다. (당연한가?)

SQL> select deptno,count(*) from emp where deptno > 20 group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6

그러나 having 절에 의한 조건은 filter 처리로 계산된 결과에서 특정 조건만을 걸러내고 있으나, 
where 절의 경우 access 처리로 scan의 범위를 줄이고 있음을 알 수 있다. 

SQL> select deptno,count(*) from emp group by deptno having deptno > 20;

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     2 |     6 |     3  (34)| 00:00:01 |
|*  1 |  FILTER                |           |       |       |            |          |
|   2 |   HASH GROUP BY        |           |     2 |     6 |     3  (34)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| EMP_IDX04 |   449 |  1347 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO">20)

SQL> select deptno,count(*) from emp where deptno > 20 group by deptno;

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     2 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|           |     2 |     6 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | EMP_IDX03 |   193 |   579 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO">20)

queyr의 결과는 동일하나 access 하는 범위 자체가 틀리니 이에 대한 주의가 필요하다. 
당연히 access 범위를 줄일 수 있는 조건은 where 절에 기술하는 것이 유리할 듯 !!

근데, 테스트 하다보니 아래처럼 수행해도 결과가 잘 나오더군요. 
select는 count를 했으나 having에는 sum으로 조건을 주어도 결과가 잘 나오네요. 

SQL> select deptno,count(*) from emp group by deptno having sum(sal) > 10000;

    DEPTNO   COUNT(*)
---------- ----------
        20          5

Execution Plan
----------------------------------------------------------
Plan hash value: 3110987654

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |    28 |    13   (8)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |     4 |    28 |    13   (8)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |   449 |  3143 |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUM("SAL")>10000)

그럼 group by에 의한 수행 결과에 대해 조건을 주는 것이 아니라 
group by에 의해 나올 수 있는 결과에 대해 조건을 줄 수 있다는 거군요..

count(*)와 sum(sal)은 operation 자체가 다른데, 이걸 다 계산하고 있다는 건가? 
아니면 having에 있는 sum()이 query 수행시 참조되는 건가? 

"The HAVING clause is closely associated with the GROUP BY clause. The HAVING clause is used to put a filter on the groups created by the GROUP BY clause. If a query has a HAVING clause along with a GROUP BY clause, the result set will include only the groups that satisfy the condition specified in the HAVING clause."

네, having 절에 있는 group operation도 참조되는 게 맞습니다.. ^^

SQL> select deptno from emp group by deptno having sum(sal) > 10000;

    DEPTNO
----------
        20



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

Oracle Trigger를 이용해 특정 column에 대한 변경 막기  (0) 2010.08.11
Oracle 10g에서 11g로 upgrade 할때, 기존의 hint를 삭제해야 할까요?  (0) 2010.08.09
ORACLE Nested Group Operations  (0) 2010.08.06
Oracle Table의 통계정보를 옮기는 방법.  (1) 2010.08.03
Oracle datafile 생성 이후 모든 archive log는 있으나 datafile의 backup이 없는 경우 recovery  (0) 2010.07.30

이 자료는 O'Reilly에서 나온 " Mastering Oracle SQL, 2nd Edition"을 공부하면서 몰랐던 부분만을 정리하고 있습니다.
"Mastering Oracle SQL, 2nd Edition"의 저자는 Alan Beaulieu, Sanjay Mishra 이며 2004년에 나온 책이네요. 
알라딘에서 구해 볼 수 있습니다. 

Oracle GROUP 함수는 다중 사용을 지원한다. 몰랐다...
간단히 말하면 GROUP 함수 안에 GROUP 함수를 사용할 수 있다. 

예를 들자면 ..

부서별 연봉 합계이다. 
SQL> select deptno,sum(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

이 연봉 합계가 가장 높은 연봉 합은 다음처럼 구할 수 있다..

SQL> select max(salary) from (
SQL> select deptno,sum(sal) salary from emp group by deptno;

MAX(SALARY)
-----------
      10875

이걸 중첩 GROUP operation을 쓰면 다음과 같이 쓰일 수 있다. 

SQL> select max(sum(sal)) from emp group by deptno;

MAX(SUM(SAL))
-------------
        10875

SQL> select max(sum(sal)),min(sum(sal)),avg(sum(sal)) from emp group by deptno;

MAX(SUM(SAL)) MIN(SUM(SAL)) AVG(SUM(SAL))
------------- ------------- -------------
        10875          8750          9675

그럼 중첩 GROUP operation은 몇개까지 중첩될 수 있을까? 
아래 SQL이 어떤 의미인지는 모르겠지만..

일단 3개 중첩!!

SQL> select max(sum(count(sal))) from emp group by deptno;
select max(sum(count(sal))) from emp group by deptno
               *
ERROR at line 1:
ORA-00935: group function is nested too deeply

SQL> !oerr ora 935
00935, 00000, "group function is nested too deeply"
// *Cause:
// *Action:

GROUP function에 dept의 제한이 있는게 확인 된다. 
그럼 SUBQUERY를 사용하게 되면.. 

SQL> select max(a) from (
SQL>   select sum(b) a from  (
SQL>    select count(sal) b from emp group by deptno ));

    MAX(A)
----------
        14

잘 되는 군요.

다들 아시는 내용이겠지만, 저는 몰랐기 때문에 정리합니다 
-,.-



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

Oracle 10g에서 11g로 upgrade 할때, 기존의 hint를 삭제해야 할까요?  (0) 2010.08.09
ORACLE GROUP BY와 함께 쓰이는 HAVING 절  (1) 2010.08.06
Oracle Table의 통계정보를 옮기는 방법.  (1) 2010.08.03
Oracle datafile 생성 이후 모든 archive log는 있으나 datafile의 backup이 없는 경우 recovery  (0) 2010.07.30
간단히 테스트해본 oracle 11g new feature invisible index  (0) 2010.07.19

보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우가 있읍니다. 이러한 경우,데이타는 쉽게 옮겨도 통계치는 딱히 옮기는 방법이 없습니다. 이때 아래의 방법을 사용하시면 쉽게 올길수 있읍니다.

dbms_stats.export_table_stats(
ownname   VARCHAR2,                -- schema name
tabname   VARCHAR2,                -- table name
partname  VARCHAR2 DEFAULT NULL,   -- partition name
stattab   VARCHAR2,                -- stat table name
statid    VARCHAR2 DEFAULT NULL,   -- optional identifier
cascade   BOOLEAN  DEFAULT TRUE,   -- TRUE = indexes too
statown   VARCHAR2  DEFAULT NULL); -- stat table schema

exec dbms_stats.export_table_stats(USER, 'servers', NULL, 'STAT_TAB', NULL, TRUE);

dbms_stats.import_table_stats(
ownname       VARCHAR2,
tabname       VARCHAR2,
partname      VARCHAR2 DEFAULT NULL,
stattab       VARCHAR2,
statid        VARCHAR2 DEFAULT NULL,
cascade       BOOLEAN  DEFAULT TRUE,
statown       VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         BOOLEAN DEFAULT FALSE);

exec dbms_stats.import_table_stats(USER, 'servers', stattab=>'STAT_TAB');

예 : SCOTT의 EMP  통계를 SCOTT2의 EMP2로 이관

1. 통계정보를 임시로 저장할 Table생성.
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');

2. 원하는 table의 통계정보를 임시 table로 이관.
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);

* 참고 (updated: 2010/08/03)
"emp stats"로 중간에 space가 들어가니 에러가 나는 군요.. 이래서 직접 테스트를해봐야 한다니까...
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP_STATS',TRUE,'SCOTT');
BEGIN dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP STATS',TRUE,'SCOTT'); END;
*
ERROR at line 1:
ORA-20001: EMP STATS is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 8240
ORA-06512: at "SYS.DBMS_STATS", line 9188
ORA-06512: at line 1

* 주의 (updated: 2010/08/03)
STATS table의 데이터를 qery 해보면 EMP table의 원래 소유자인 SCOTT이 C5 column에 명시되어 있습니다. 만약 다른 계정으로, 즉 SCOTT2 계정으로 통계정보를 옮기려면 STATS table의 SCOTT값을 SCOTT2로 update 해주어야 합니다. 
두개의 DB의 계정이 동일하다면 변경해 줄 필요는 없겠죠~

3. 임시 table을 export, 그리고 target db로 import.
 $exp scott/tiger tables=STATS file=expstat.dmp
 $imp scott/tiger file=expstat.dmp full=y log=implog.txt
(만약 동일 DB라면 이 과정은 생략 가능합니다.)

4. 임시 Table로 부터 통계치를 원하는 table에 넣는다.
SQL> exec dbms_stats.import_table_stats('SCOTT2','EMP2',NULL,'STATS');




'Oracle Database' 카테고리의 다른 글

ORACLE GROUP BY와 함께 쓰이는 HAVING 절  (1) 2010.08.06
ORACLE Nested Group Operations  (0) 2010.08.06
Oracle datafile 생성 이후 모든 archive log는 있으나 datafile의 backup이 없는 경우 recovery  (0) 2010.07.30
간단히 테스트해본 oracle 11g new feature invisible index  (0) 2010.07.19
Database에 savepoint를 찍어보자 !!  (0) 2010.07.13

기본적으로 recovery는 backup된 datafile을 가져다 놓고 backup 시점 이후의 archive log를 적용하게 됩니다. 
complete recovery의 경우 모든 archive log가 적용된후 undo가 다시 적용되는데요..

아래의 시나리오는 backup된 datafile은 없으나, 손상된 datafile 생성 시점이후 모든 archive log가 있을 경우 recovery하는 시나리오입니다. 
예전 data guard에서는 primary DB에서 datafile 생성한게 standby에 생성되지 않아 수작업으로 'alter database create datafile,..'을 수행했던거 같은데, 요즘 version에서는 되는지 모르겠네요..

Re-Creating Datafiles When Backups Are Unavailable: Scenario


If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:

  • All archived log files written after the creation of the original datafile are available

  • The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database)

    Note:

    You cannot re-create any of the datafiles for the SYSTEM tablespace by using the CREATE DATAFILE clause of the ALTER DATABASE statement because the necessary redo is not available.

To re-create a datafile for recovery:

Create a new, empty datafile to replace a damaged datafile that has no corresponding backup. For example, assume that the datafile ?/oradata/trgt/users01.dbf has been damaged, and no backup is available. The following statement re-creates the original datafile (same size) on disk2:


ALTER DATABASE CREATE DATAFILE '?/oradata/trgt/users01.dbf' AS
                               '/disk2/users01.dbf';

손상된 '?/oradata/trgt/users01.dbf'를 '/disk2/users01.dbf'로 이름을 바꿔 빈 datafile을 생성합니다.

This statement creates an empty file that is the same size as the lost file. The database looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.

Perform media recovery on the empty datafile. For example, enter:


RECOVER DATAFILE '/disk2/users01.dbf';

새로 만들어진 빈 datafile에 대해 archive log를 complete로 적용합니다.

All archived logs written after the original datafile was created must be applied to the new, empty version of the lost datafile during recovery.




* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

ORACLE Nested Group Operations  (0) 2010.08.06
Oracle Table의 통계정보를 옮기는 방법.  (1) 2010.08.03
간단히 테스트해본 oracle 11g new feature invisible index  (0) 2010.07.19
Database에 savepoint를 찍어보자 !!  (0) 2010.07.13
Oracle Join 종류: CROSS, INNER, OUTER, NATURAL,EQUI, SELF join  (0) 2010.07.13

+ INVISIBLE index 생성 
SQL> create index emp_idx01 on emp(empno) invisible;

Index created.

+ 일반 세션에서 SQL 수행 
SQL> set autot on exp
SQL> 
SQL> select empno from emp where empno = 7839;

     EMPNO
----------
      7839

Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |    12   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     4 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=7839)

+ INVISIBLE INDEX 사용 정의
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select empno from emp where empno = 7839;

     EMPNO
----------
      7839


Execution Plan
----------------------------------------------------------
Plan hash value: 3956893595

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_IDX01 |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPNO"=7839)

+ INVISIBLE INDEX 사용 해제
SQL> alter session set optimizer_use_invisible_indexes=false;

Session altered.

SQL> select empno from emp where empno = 7839;

     EMPNO
----------
      7839


Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |    12   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     4 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=7839)

+ OPT_PARAM hint를 통한 INVISIBLE index 사용 권한 부여 -> 실패 ?
SQL> select /*+ opt_param('optimizer_use_invisible_indexes','true') */ empno from emp where empno = 7839;

     EMPNO
----------
      7839


Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |    12   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     4 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=7839)

'Oracle Database' 카테고리의 다른 글

Oracle Table의 통계정보를 옮기는 방법.  (1) 2010.08.03
Oracle datafile 생성 이후 모든 archive log는 있으나 datafile의 backup이 없는 경우 recovery  (0) 2010.07.30
Database에 savepoint를 찍어보자 !!  (0) 2010.07.13
Oracle Join 종류: CROSS, INNER, OUTER, NATURAL,EQUI, SELF join  (0) 2010.07.13
오라클 옵티마이져의 비용 산정 모듈의 세가지 예상치 (Selectivity, cardinality, cost)  (0) 2010.07.07

oracle 10g R2 new feature를 보다 보니까.. (나온지가 언젠데.!!)
"restore point"라는 개념이 나오더군요.. (아래..)

테스트 함 해봤습니다.

원문 : Restore Point in Flashback Database

SQL의 savepoint라는 개념을 기억하십니까? 트랜잭션에서 savepoint를 생성한 후, 데이터를 일부 수정한 다음, 다시 savepoint를 생성하는 식으로 작업합니다. 그리고 변경된 내용을 다시 되돌리고 싶다면, 해당되는 savepoint로 롤백하면 됩니다.

Oracle Database 10g의 Flashback Database는 데이터베이스를 과거의 특정 시점으로 되돌리는 기능을 제공합니다. 그렇다면 여기에도 savepoint와 유사한 개념을 도입하면 유용하지 않을까요? 시간을 기준으로 하지 않고, 명명된 시점(named point)으로 되돌릴 수 있게 하면 좋지 않을까요?

Oracle Database 10g Release 2에 추가된 “restore point”라는 기능이 바로 이러한 역할을 담당합니다. 그 작동 원리가 다음과 같습니다.


위의 내용은 OTN의 Oracle Database 10g Release 2의 신기능에서 가져왔습니다.

1. RESTORE POINT 생성.

SQL> create restore point before_fb_test guarantee flashback database;

ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_FB_TEST'.
ORA-38786: Flash recovery area is not enabled.

SQL> alter system set db_recovery_file_dest='/..' scope=both;
SQL> alter system set log_archive_dest_1='' scope=spfile;
SQL> shutdown immediate;
SQL> startup

SQL> archive log list
Database log mode   Archive Mode
Automatic archival  Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
            :


SQL> create restore point before_fb_test guarantee flashback database;

ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_FB_TEST'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when flashback database is off.

ORA-38787: Creating the first guaranteed restore point requires mount mode when flashback database is off.
Cause: While flashback database is not on, an attempt was made to create the first guaranteed restore point while the database is open.
Action: Mount the database and retry.

SQL> shutdown immediate;
SQL> STARTUP MOUNT
SQL> ALTER DATABASE FLASHBACK ON;

ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: Not enough space for first flashback database log file

SQL> alter system set db_recovery_file_dest_size=5G scope=both;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

( flash backup area에 flb라는 확장자로 파일이 하나 생기는 군요.)

SQL> create restore point before_fb_test guarantee flashback database;

2. 테스트

SQL> delete from scott.emp;
SQL> commit;

* flash backup area에 flb file이 추가로 1개 더 생기는 ..

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO RESTORE POINT before_update_of_emp;
SQL> ALTER DATABASE OPEN RESETLOGS;

기본 설정만 해놓으면 잘 되는 거 같네요..



'Oracle Database' 카테고리의 다른 글

Oracle datafile 생성 이후 모든 archive log는 있으나 datafile의 backup이 없는 경우 recovery  (0) 2010.07.30
간단히 테스트해본 oracle 11g new feature invisible index  (0) 2010.07.19
Oracle Join 종류: CROSS, INNER, OUTER, NATURAL,EQUI, SELF join  (0) 2010.07.13
오라클 옵티마이져의 비용 산정 모듈의 세가지 예상치 (Selectivity, cardinality, cost)  (0) 2010.07.07
oracle oradebug를 이용해 삭제된 trace file을 다시 만드는 방법  (0) 2010.07.06

CROSS JOIN/ Cartesian Products

 - Cartesian product 값을 얻을때 사용 합니다. 
 - 즉 join을 통해 생성가능한 모든 row를 return 합니다. 

SQL>SELECT ename FROM emp CROSS JOIN dept;
SQL>SELECT ename FROM emp, dept;

참고: CROSS가 생략되어 INNER join으로 수행되는 경우(join condition이 없는 경우) 나 CROSS join에서 join condition이 기술된 경우 에러발생합니다. 

SQL>SELECT e.lname, d.name FROM employee e JOIN department d;
ORA-00905: missing keyword

SQL>SELECT e.lname, d.name FROM employee e CROSS JOIN department d ON e.dept_id = d.dept_id;
ORA-00933: SQL command not properly ended


INNER JOIN 

 - 일반 조인시 Table간의 ','를 생략하고 'INNER JOIN'을 추가하고 WHERE절대신 ON절을 사용해야 합니다.
 - INNER는 생략 가능합니다.

SQL>SELECT e.empno, e.ename FROM dept d INNER JOIN emp e ON d.deptno=e.deptno;
SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno=e.deptno;

EQUI-JOIN/ NON-EQUI-JOIN 
 - Join condition에 equal operation(=)을 사용하는 JOIN 
 - Join condition에 equal operation 외의 다른 operation을 사용하는 JOIN

SQL> SELECT s.name supplier_name, p.name part_name FROM supplier s JOIN part p ON s.supplier_id = p.supplier_id;
SQL> SELECT p.name part_name, c.inv_class inv_class FROM part p JOIN inventory_class c ON p.unit_cost BETWEEN c.low_cost AND c.high_cost;

SELF JOIN
 - 동일 TABLE에 대한 JOIN

SQL> SELECT e.lname employee, m.lname manager FROM employee e JOIN employee m ON e.manager_emp_id = m.emp_id;

OUTER JOIN 

 - Join시 특정 table의 join 조건에 일치하지 않은 row를 추가할때 사용해야 합니다.
 - { LEFT | RIGHT | FULL } [OUTER]

SQL>SELECT e.empno, e.ename FROM dept d LEFT OUTER JOIN emp e ON d.deptno=e.deptno;
SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno=e.deptno(+);

SQL>SELECT e.empno, e.ename FROM dept d RIGHT OUTER JOIN emp e ON d.deptno=e.deptno;
SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno(+)=e.deptno;

SQL>SELECT e.empno, e.ename FROM dept d FULL OUTER JOIN emp e ON d.deptno=e.deptno;

참고: full outer join의 비밀 [Science of DataBase]


PARTITION OUTER JOIN 

 - PARTITION OUTER JOIN을 사용하여 분석 대상이 되는 디멘션의 densification을 수행할 수 있으며, 
 - 파티션이나 테이블 내부에서 OUTER JOIN을 적용할 수 있습니다.

select hiredate,d.dname,  nvl(sum_sal,0) sum_sal
from dept d
left outer join (select deptno,to_char(hiredate,'YYYY') hiredate, sum(sal) sum_sal from emp group by deptno,to_char(hiredate,'YYYY')) e
partition by (e.hiredate)
on d.deptno = e.deptno
order by 1,2
/

HIREDATE     DNAME                                         SUM_SAL
------------ ------------------------------------------ ----------
1980         ACCOUNTING                                          0
1980         OPERATIONS                                          0
1980         RESEARCH                                          800
1980         SALES                                               0
1981         ACCOUNTING                                       7450
1981         OPERATIONS                                          0
1981         RESEARCH                                         5975
1981         SALES                                            9400
1982         ACCOUNTING                                       1300
1982         OPERATIONS                                          0
1982         RESEARCH                                         3000
1982         SALES                                               0
1983         ACCOUNTING                                          0
1983         OPERATIONS                                          0
1983         RESEARCH                                         1100
1983         SALES                                               0



NATURAL JOIN 

 - Equijoin과 동일하다고 보시면 됩니다.
 - 두 테이블의 동일한 이름을 가지는 칼럼은 모두 조인이 됩니다. (oracle forum : HR유저에서 NATURAL JOIN 결과가 다릅니다 )
 - 동일한 컬럼을 내부적으로 찾게 되므로 테이블 Alias를 주면 오류가 발생 합니다. 
    (ORA-25155: column used in NATURAL join cannot have qualifier)
 - 동일한 컬럼이 두개 이상일 경우 JOIN~USING문장으로 조인되는 컬럼을 제어 할 수 있습니다.
 
SQL>SELECT empno, ename, deptno FROM emp NATURAL JOIN dept 
SQL>SELECT e.empno, e.ename, d.deptno FROM emp e, dept d WHERE e.deptno=d.deptno


JOIN ~ USING 

 - NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되었는데 USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있습니다.
 - USING절 안에 포함되는 컬럼에 Alias를 지정하면 오류가 발생 합니다.
  
SQL>SELECT e.empno, e.ename, deptno FROM emp e JOIN dept d USING(deptno)


ON 구문

 - 조인 조건을 지정 할 수 있습니다. 
 - 모든 논리 연산 및 서브쿼리를 지정할 수 있습니다.

SQL>SELECT e.empno, e.ename, e.sal
       FROM emp e JOIN dept    d  ON (e.deptno=d.deptno)
                           JOIN bonus  b ON (b.ename = e.ename)
       WHERE e.sal IS NOT NULL




* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

간단히 테스트해본 oracle 11g new feature invisible index  (0) 2010.07.19
Database에 savepoint를 찍어보자 !!  (0) 2010.07.13
오라클 옵티마이져의 비용 산정 모듈의 세가지 예상치 (Selectivity, cardinality, cost)  (0) 2010.07.07
oracle oradebug를 이용해 삭제된 trace file을 다시 만드는 방법  (0) 2010.07.06
Oracle optimizer statistic gathering 대상 Table 확인하는 script  (0) 2010.07.05

옵티마이저는 실행 계획의 비용을 계산하기 위한 비용 모델(Cost Model)을 갖고 있고, 이 비용 모델은 Oracle Data Dictionary에서 관리하는 다양한 통계정보를 기반으로 크게 다음과 같은 세 가지 값(measure)의 예상치를 계산한다.

Q1:
select ename, sal
from emp e, dept d
where e.deptno = d.deptno and d.loc = ‘SEOUL’

선택도

우선 선택도(selectivity)의 개념을 예로 들자. 앞에서 예로 든 질의 Q1에서 d.loc = ‘SEOUL’이라는 조건의 선택도는 dept 테이블 전체 중에서 loc의 값이 ‘SEOUL’인 레코드의 비율을 일컫는다. 옵티마이저는 선택도 계산을 통해서 해당 조건을 만족하는 레코드가 몇 건 정도가 되는지를 예측하게 된다. 옵티마이저는 만일 DBA_TABLES에 dept 테이블의 loc 칼럼의 distinct column values가 10이라면 옵티마이저는 선택도가 0.1이라고 판단하게 된다. 이때 선택도를 이와 같이 정하는 이유는 dept 테이블이 loc 칼럼들에 골고루 분포되어 있다고 가정할 때 성립한다. 그러나, 실제로 loc 칼럼의 값들이 skew되어서 분포할 수도 있다.

예를 들어, 전체 레코드의 50%가 loc 값으로‘SEOUL’을 갖는다면 잘못된 선택도 값을 얻게 된다. 이와 같이 데이타 분포가 skew되어 있는 경우, 해당 칼럼에 대한 히스토그램 정보를 DBA_HISTOGRAM 테이블에 만들어 주어야 정확한 선택도 값을 계산할 수 있다(이 경우는 0.5). 오라클 옵티마이저는 다양한 조건식의 종류에 대해 선택도를 통계정보에 기반해서 계산하는 수식을 내부적으로 갖고 있다. 그렇지만, 만일 dept 테이블이 아직 분석되지 않아서 통계정보가 없는 경우, 옵티마이저는 내부적으로 갖고 있는 디폴트 값을 선택도로 지정한다(예를 들어, 0.01).

카디널러티

앞의 dept 테이블의 전체 레코드 건수가 1000일 때, 앞에서 설명한 loc = ‘SEOUL’의 선택도가 0.1로 계산되었을 때, 조건을 만족하는 레코드 건수는 1000 x 0.1, 즉 100개로 예상할 수 있다. 이와 같이 어떤 연산을 수행한 결과로 나오는 레코드 건수를‘카디널러티(cardinality)’라 하는데, 정확한 카디널러티를 계산하는  것은 좋은 실행 계획을 만드는 데 굉장히 중요하다.

예를 들어, (T1§_T2)§_T3 순서로 테이블을 조인할 때 (T1§_T2)의 결과와 T3를 조인할 때 어떤 조인 방법을 선택하는 것이 좋을지를 결정하기 위해서는 (T1§_T2)의 크기를 정확하게 알아야 한다. 이를 위해서는 (T1§_T2) 조인의 결과 레코드가 몇 개인지를 예상할 수 있어야 한다. 이를 위해 오라클 옵티마이저는 다양한 연산의 결과 레코드의 카디널러티를 통계정보와 수식에 의해서 계산한다. T1과 T2의 조인 조건이 T1.c1 = T2.c2(이를‘P’라
표기)라 했을 때, 앞에서 설명한 선택도 계산 공식에 의해 이 조건식의 선택도 Sel(P)를 먼저 계산한 후, 이 조인의 결과 카디널러티는 Card(T1) x Card(T2) x Sel(P)가 된다.

예를 들어, T1, T2의 튜플 수가 각각 1000, 5000이고 Sel(P)가 0.01이면, 조인의 결과로 생기는 튜플 수는 1000 x 5000 x 0.01 = 5000이 된다. 그런데, Sel(P)가 조금이라도 틀리면 이후의 전체적인 비용 산정이 잘못되게 된다. 오라클 옵티마이저는 다양한 종류의 연산에 대해 내부 공식을 사용해 카디널러티를 계산한다.

비용

비용(cost)은 테이블 액세스, 조인 등을 수행하는 데 걸리는 시간을 의미하는데, 시간은 주로 디스크 I/O 수와 CPU 사용시간을 고려한다. 비용은 앞에서 계산한 통계 정보와 내부 계산식에 의해 계산된다.

예를 들어, T1§_T2를 Nested Loop 방식으로 조인할 경우 조인비용은 (T1의 데이타 블록수) + ((T1의 레코드 건수)*(T2의 액세스 비용))이 된다. 이처럼 오라클 옵티마이저는 모든 연산에 대해 소요되는 비용을 계산하는 수식을 갖고 있다. 오라클 옵티마이저는 이 세 가지 예상 값(measure)을 기반으로, 현재의 실행 계획의 예상 비용을 구한다.

위 내용은 아래 첨부된 "Oracle Technical Note : 오라클 옵티마이저의 기본 원리"의 일부를 정리한 내용입니다.
02winter_optimizer_1.pdf



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

Database에 savepoint를 찍어보자 !!  (0) 2010.07.13
Oracle Join 종류: CROSS, INNER, OUTER, NATURAL,EQUI, SELF join  (0) 2010.07.13
oracle oradebug를 이용해 삭제된 trace file을 다시 만드는 방법  (0) 2010.07.06
Oracle optimizer statistic gathering 대상 Table 확인하는 script  (0) 2010.07.05
Listener hang/slowdown 현상 원인 찾기  (0) 2010.07.05

가끔 문제가 있어 alert log file이나 event 설정해 놓은 걸 보면 trace file이 있을거로 예상 되어지는데, 실제 trace 파일 등이 없는 경우가 있습니다. 대부분 부족한 filesystem 때문에 oracle home 내의 trace file을 주기적으로 정리하느라 없어지는 경우가 대부분이죠. 
 
이럴 경우, unix 상에서 trace 파일을 지우면 그 trace 파일을 오픈하고 있는 process는 계속해서 write를 할 수 있기때문에, 결국은 trace file에 열심히 기록을 해 놓아도 볼 수 없게 됩니다.  결국은 내렸다 올리면 process들이 trace file을 새로 생성하게되니 문제가 해결되지만, oradebug를 사용하면 process를 내리지 않고 trace file을 다시 생성하게 할 수 있습니다. 
 
oradebug setospid <ospid>
oradebug close_trace

위의 명령을 수행하면 현재까지 process가 잡고 있던 trace file을 close 하게 되고 이 후 process가 trace file에 기록할 내용이 발생하면 trace file을 새로 생성하고 기록하게 됩니다. 물론 이미 존재하는 trace file이 있다면 마지막 부분에 file의 header를 모두 기록하고 시작하게 됩니다.



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

Oracle Join 종류: CROSS, INNER, OUTER, NATURAL,EQUI, SELF join  (0) 2010.07.13
오라클 옵티마이져의 비용 산정 모듈의 세가지 예상치 (Selectivity, cardinality, cost)  (0) 2010.07.07
Oracle optimizer statistic gathering 대상 Table 확인하는 script  (0) 2010.07.05
Listener hang/slowdown 현상 원인 찾기  (0) 2010.07.05
Oracle Datafile 사용 공간 줄이기  (0) 2010.06.29

10g 부터는 Oracle에서 자동으로 optimizer statistic 통계정보를 수집하게 됩니다.
11g에는 scheduler job에서 automated task로 변경된 거 같은데,
모두 마찬가지로 10% 이상의 변경을 대상으로 수집합니다. (11g 두? .. 음)

다음의 SQL은 dba_tab_modifications table에서 DML에 의해 변경된 건수와 dba_tables의 num_rows를 비교해
10% 이상 변경된 table을 조회하는 SQL 문입니다.

select TABLE_OWNER,m.TABLE_NAME,num_rows,round(num_rows/10,0) "NUM_ROWS/10",inserts+updates+deletes "tot_changed",
INSERTS,UPDATES,DELETES,TIMESTAMP,LAST_ANALYZED,round(LAST_ANALYZED-TIMESTAMP,0) GAP
from dba_tab_modifications  m, dba_tables t
where table_owner not in ('SYS')
and t.table_name = m.table_name
and t.owner = m.table_owner
and round(num_rows/10,0) - (inserts+updates+deletes) < 0
order by LAST_ANALYZED-TIMESTAMP
/

UPDATE (2010/07/05)

아래 블로그는 제가 구독하는 Oracle 관련 blog 입니다.
dba_tab_modifications에 대해 자세한 설명과 test 결과를 post 해 놓았네요. dba_tab_modifications의 데이터가 즉각적으로 보이지 않던데, 
flush를 해줘야 하는 군요.. 움~

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO







'Oracle Database' 카테고리의 다른 글

오라클 옵티마이져의 비용 산정 모듈의 세가지 예상치 (Selectivity, cardinality, cost)  (0) 2010.07.07
oracle oradebug를 이용해 삭제된 trace file을 다시 만드는 방법  (0) 2010.07.06
Listener hang/slowdown 현상 원인 찾기  (0) 2010.07.05
Oracle Datafile 사용 공간 줄이기  (0) 2010.06.29
Oracle 11gR2 Grid Infrastructure Single Client Access Name (SCAN) .. 이건 또 뭐야?  (3) 2010.06.24

제가 구독하는 블로그들 중 MWIDLAKE님이 포스트 한 내용입니다. 
한번 읽어 보고 정리해 봅니다 ~

10. 6. 17 작성자: mwidlake의 Martin Widlake's Yet Another Oracle Blog

If you look at v$session you sid SID and SERIAL#, which most of us DBA-types know uniquely identify a session and allow you to kill it (*with the relevant permissions and knowledge you are not about to compromise a business process).

But what is AUDSID?

desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 SADDR                                              RAW(8)
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 AUDSID                                             NUMBER
 PADDR                                              RAW(8)
 USER#                                              NUMBER
 USERNAME                                           VARCHAR2(30)
 COMMAND                                            NUMBER
 OWNERID                                            NUMBER
 TADDR                                              VARCHAR2(16)
 LOCKWAIT                                           VARCHAR2(16)
 STATUS                                             VARCHAR2(8)
 SERVER                                             VARCHAR2(9)
 SCHEMA#                                            NUMBER
 SCHEMANAME                                         VARCHAR2(30)
 OSUSER                                             VARCHAR2(30)
...

AUDSID is a unique identifier for the session and is used in sys.aud$ , as the SESSIONID column.


정리해 보면...

audsid는 auditing 기능을 위한 identifier 이며
sys.audses$ trigger에 의해 발생하는 unique value 이며
sys 계정의 audsid는 0 값을 가진다.. 

auditing 기능을 사용하고 있지 않아 더이상 깊이는 무리.. 


* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

Oracle Datafile 사용 공간 줄이기  (0) 2010.06.29
Oracle 11gR2 Grid Infrastructure Single Client Access Name (SCAN) .. 이건 또 뭐야?  (3) 2010.06.24
IT 스페셜리스트. Oracle Certified Master (OCM)가 되고 싶다면?  (0) 2010.06.16
Oracle Goldengate : 업무중단없이 Upgrade/migration 하세요.  (2) 2010.06.16
ORACLE shared pool내의 특정 cursor purge 하기  (0) 2010.06.15
 
오라클은 사용자 작업을 위해 많은 Package/Fuction 등 을 지원합니다. 
이런 놈들을 다 알필요는 없지만 아는 많큼 손발이 고생할 여지가 조금씩 줄어 듭니다. 그러니 기회가 있을 때마다 조금씩 알아 놓는게 좋겠죠 ^^;
얼마전 작업하다기 DBMS_SHARED_POOL.PURGE procedure를 유용하게 사용할 수 있는 기회가 있었어 간략하게 정리합니다. 
 
주말의 몇몇 table의 re-org 작업이 있었는데, 
시간 계산을 잘못해서 마지막 즈음에 기다리던 작업자를 위해 사용자 세션을 풀어 주었습니다. 주말이라 얼마나 들어오겠나 싶었죠.. 
많은 세션들이 들어오진 않았지만, 몇몇 세션들이 주로 수행되는 SQL을 이것 저것 수행하고 나갔습니다. 
 
근데 작업 전보다 성능이 않좋다고 하더군요. 
지금 re-org 작업이 있으니 조금 느릴 수도 있겠다 싶었죠. 근데, 작업 후에도 느리다고 하더군요. 
Plan이 바뀌었습니다. 통계정보도 같이 넣어 주었는데 말이죠.. 
 
import로 data를 loading 하면 마지막에 해당 segment의 통계정보를 같이 적제하게 됩니다. 근데 통계정보가 다 들어가기 전에 수행된 SQL은 통계정보가 없는 상태에서 cost를 계산해서 sql plan을 만들어 버리죠. 또 이렇게 만들어진 plan 정보는 해당 cursor가 purge 되기 전까지는 계속 사용하게 됩니다. 
 
이러한 비슷한 경우는 bind peeking 기능에 의해 일반적이지 않은 bind 값이 들어가도 발생할 수가 있습니다. 
 
이러한 경우 shared pool flush를 시켜 해당 cursor를 purge 시킬 수 도 있겠지만 11g 이후 부터는 단일 cursor 별로 purge가 가능합니다. 10.2.0.4 version에서도 사용가능한데, 이 버전의 경우 event 설정이 필요합니다. 
 
event="5614566 trace name context forever"
다음의 내용은 참조 노트의 사용 방법에 대한 간단한 예 입니다. 
 
SESSION 1 
 
sqlplus scott/tiger 
SQL> select ename from emp where empno=7900;
 
SESSION 2 
 
sqlplus / as sysdba 
 
SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
     from v$sqlarea 
     where sql_text = 'select ename from emp where empno=7900';
 
ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS 
---------------- ---------- ---------- ---------- ------------- ------------- ----------- 
000000007A6CF430 1052545619          1          1             1             0           1
 
SQL> exec dbms_shared_pool.purge ('000000007A6CF430,1052545619 ','C'); 
 
SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
     from v$sqlarea 
     where sql_text = 'select ename from emp where empno=7900';
 
no rows selected
 
 
이 내용은 아래의 문서를 참조했습니다. 
 
Note 457309.1 How To Flush an Object out the Library Cache 
Note 751876.1 DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4
 

'Oracle Database' 카테고리의 다른 글

IT 스페셜리스트. Oracle Certified Master (OCM)가 되고 싶다면?  (0) 2010.06.16
Oracle Goldengate : 업무중단없이 Upgrade/migration 하세요.  (2) 2010.06.16
TABLE COMPRESS  (0) 2010.06.04
Oracle 10g new feature - 여태 몰랐던..  (0) 2010.05.20
Oracle AWR 데이터를 편히 볼 수 있는 PIVOT 기능 - 11g new feature  (0) 2010.05.18

Faster Startup


The days when dinosaurs ruled the earth and 2GB memory was considered large are gone. Now, it's not uncommon to see large buffer caches to the tune of 100GB. When the instance is started, it might take several minutes, or even hours, to initialize a buffer cache of this size.

If you look deeper into the situation, you will notice that the entire buffer cache need not be up when the database instance starts. After the instance starts, the buffer cache is empty, which gradually fills up when users select data from tables. So, there is no need to initialize the entire buffer cache when the instance starts.

In Oracle Database 10g Release 2, this behavior is accounted for in the startup logic. When you start the instance, only 10% of the buffer cache is initialized; the rest is initialized after the database is opened by the checkpoint process. This new approach reduces instance startup time significantly.

Bear in mind, however, that until the entire buffer cache is initialized, automatic buffer cache sizing is not available.

아시다 시피 SGA memory 영역은 Database가 nomount 단계에서 할당됩니다. 
이 new feature는 nomount 단계에서는 10%의 buffer pool만 할당하고 나머지는 instance가 open 된 후에 할당한다는 의미입니다.


Drop Empty Datafiles


Imagine that you just added a datafile to the wrong directory or tablespace—a fairly common error. All is not lost; the datafile doesn't contain any data yet, so you can easily drop it, right?

Unfortunately, you can't. Prior to Oracle Database 10g Release 2, your only clean option for removing a datafile is to drop the entire tablespace and then rebuild it without that particular file. If the tablespace contains data, you have to go through the time-consuming and laborious process of storing the data on a separate location and reinstating it. In addition to its inconvenience, this process makes the tablespace unavailable.

Thankfully, in Oracle Database 10g Release 2 the process has been simplified: You can just drop the datafile. For example, the following command will remove the indicated datafile from the tablespace as well as from the server.
alter tablespace users drop datafile '/tmp/users01.dbf'
 /
There are a couple restrictions, however: The datafile must be empty to be dropped. You can't drop the last datafile in a tablespace; the tablespace itself must be dropped. And the tablespace must be online and in read-write status.

10g 이전 버전에서는 데이터파일을 잘 못 붙일 경우 이거 띄어 내려면 tablespace 전체를 재구성해야 하죠. 
예전 OPS 쓸때 raw device가 아닌 filesystem에 datafile을 추가하는 등의 황당한 경우도 있었습니다.. ㅋ
drop empty datafiles new feature는 이런 유저 실수로 인한 막대한 작업을 더이상 안해도 되게 해주겠군요. 


Catch the Error and Move On: Error Logging Clause


Suppose you are trying to insert the records of the table ACCOUNTS_NY to the table ACCOUNTS. The table ACCOUNTS has a primary key on ACC_NO column. It's possible that some rows in ACCOUNTS_NY may violate that primary key. Try using a conventional insert statement:
SQL> insert into accounts
  2  select * from accounts_ny;
insert into accounts
*
ERROR at line 1:
ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated
None of the records from the table ACCOUNTS_NY has been loaded. Now, try the same with error logging turned on. First, you need to create a table to hold the records rejected by the DML statement. Call that table ERR_ACCOUNTS.
exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS')
Next, execute the earlier statement with the error-logging clause.
SQL> insert into accounts
  2  select * from accounts_ny
  3  log errors into err_accounts
  4  reject limit 200
  5  /

6 rows created.
Note that the table ACCOUNTS_NY contains 10 rows yet only six rows were inserted; the other four rows were rejected due to some error. To find out what it was, query the ERR_ACCOUNTS table.
SQL> select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO
 2  from err_accounts;

ORA_ERR_NUMBER$   ORA_ERR_MESG$                                       ACC_NO
 ---------------  --------------------------------------------------  ------
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi  9997
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS)vi  9998
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 9999
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 10000
               olated
Note the columns ORA_ERR_NUMBER$, which show the Oracle error number encountered during the DML statement execution, and the ORA_ERR_MESG$, which shows the error message. In this case you can see that four records were rejected because they violated the primary key constraint PK_ACCOUNTS. The table also captures all the column of table ACCOUNTS, including the column ACC_NO. Looking at the rejected records, note that these account numbers already exist in the table; hence the records were rejected with ORA-00001 error. Without the error-logging clause, the whole statement would have failed, with no records rejected. Through this clause, only the invalid records were rejected; all others were successful.

이 기능은 sql*loader의 bad file 같은 기능입니다. 
특정 에러가 발생해도 이를 logging만 하고 나머지 데이터는 처리를 해주는 군요.  
PK constraint violation 발생하면 이거 처리하는 것도 좀 귀찮은 작업인데, 요 기능 쓰면 약간 편해지겠습니다. ^^

그나 저나 아직도 10g new feature를 모르고 있다니... 좌절이다~



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

ORACLE shared pool내의 특정 cursor purge 하기  (0) 2010.06.15
TABLE COMPRESS  (0) 2010.06.04
Oracle AWR 데이터를 편히 볼 수 있는 PIVOT 기능 - 11g new feature  (0) 2010.05.18
회사에서 Oracle DBA에게 요구하는 기술들  (0) 2010.04.29
Oracle index 생성 전에 index에 대한 size 예상하는 방법.  (0) 2010.04.28

11g에서 나온 PIVOT 구문입니다. 
아래 sample 처럼 동일 column 값에 대해 pivot 기능이 적용되어 display 됩니다. 

SQL> select job,deptno,avg(sal) from emp  group by job,deptno;

JOB                             DEPTNO   AVG(SAL)
--------------------------- ---------- ----------
PRESIDENT                           10       5000
MANAGER                             30       2850
CLERK                               30        950
CLERK                               10       1300
MANAGER                             20       2975
ANALYST                             20     6499.5
CLERK                               20        950
SALESMAN                            30       1400
MANAGER                             10       2450


SQL> select * from (select job,deptno,sal from emp) 
pivot (avg(sal) for deptno in (10,20,30)) order by job;

JOB                                 10         20         30
--------------------------- ---------- ---------- ----------
ANALYST                                    6499.5
CLERK                             1300        950        950
MANAGER                           2450       2975       2850
PRESIDENT                         5000
SALESMAN                                                1400


PIVOT 기능은 AWR 데이터를 보기편하게 변형하는 데 유용합니다. 

* 아래 SQL은 노드별 user call 값을 출력해본 간단한 SQL 입니다. 
select * from
(
 select to_char(end_interval_time,'HH24:MI:SS') end_interval_time
       ,a.instance_number,value from dba_hist_sysstat a, dba_hist_snapshot b
 where stat_name in ('user calls')
 and a.snap_id = b.snap_id
 and a.instance_number = b.instance_number
)
pivot (avg(value) for instance_number in (1 as "Node1",2 as "Node2",3 as "Node3"))
order by 1
/

END_INTERV                Node1      Node2      Node3
---------- -------------------- ---------- ----------
00:10:31          1,432,826,125 1278172133 1281672038
00:10:34          1,254,265,411 1153949372 1154503152
00:10:39            233,495,867  223300171  222547544
00:20:01          1,255,123,846 1154019492 1154577807
00:20:02          2,260,343,067 2050291466 2048154492
00:20:08            719,183,716  664321484  663506525
00:20:13            478,942,298  446272763  444528572


* V$SYSSTAT의 특정 statistic 값을 query하는 SQL 
select *
from (select to_char(sysdate,'MMDDHH24MISS') dat, a.name,a.value
        from v$sysstat a 
        where statistic# in (146,9,67,62,72,6,446,447,0,451,5,4)
        )
pivot (sum(value) for name in ('logons cumulative','user rollbacks','user commits','user calls','session logical reads','physical reads','db block changes', 'physical writes','redo size','parse count (total)','parse count (hard)','execute count'))
order by dat
/

* PIVOT을 decode로 바꿔본 SQL (11g 이전 버전)
select to_char(sysdate,'MMDDHH24MISS') dat,
       sum(decode(name,'logons cumulative',value)) "logons cumulative",
       sum(decode(name,'user rollbacks',value)) "user rollbacks",
       sum(decode(name,'user commits',value)) "user commits",
       sum(decode(name,'user calls',value)) "user calls",
       sum(decode(name,'session logical reads',value)) "session logical reads",
       sum(decode(name,'physical reads',value)) "physical reads",
       sum(decode(name,'db block changes',value)) "db block changes",
       sum(decode(name,'physical writes',value)) "physical writes",
       sum(decode(name,'redo size',value)) "redo size",
       sum(decode(name,'parse count (total)',value)) "parse count (total)",
       sum(decode(name,'parse count (hard)',value)) "parse count (hard)",
       sum(decode(name,'execute count',value)) "execute count"
from v$sysstat
where statistic# in (146,9,67,62,72,6,446,447,0,451,5,4)
group by to_char(sysdate,'MMDDHH24MISS')
/
(왠지 좀 무지해 보이는데.. 더 좋은 방법이 있으면 알려주세요 *^^*)


* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!


'Oracle Database' 카테고리의 다른 글

TABLE COMPRESS  (0) 2010.06.04
Oracle 10g new feature - 여태 몰랐던..  (0) 2010.05.20
회사에서 Oracle DBA에게 요구하는 기술들  (0) 2010.04.29
Oracle index 생성 전에 index에 대한 size 예상하는 방법.  (0) 2010.04.28
DISTRIBUTED QUERY ANALYSIS  (0) 2010.04.22

Oracle 관련된 전문 블로거들의 RSS를 구독하고 있습니다. 
좀 흥미로운 내용이 있어 소개해 드립니다. 

Did you know that it’s possible to use EXPLAIN PLAN FOR CREATE INDEX ON table(col1,col2,col3) syntax for explaining what exactly would be done when an index is created?

That’s useful for example for seeing the Oracle’s estimated index size without having to actually create the index.

You can also use EXPLAIN PLAN FOR ALTER INDEX i REBUILD to see whether this operation would use a FULL TABLE SCAN or a FAST FULL INDEX SCAN (offline index rebuilds of valid indexes can use this method).


위의 내용은 읽어 보시면 아시겠지만, EXPLAIN PLAN FOR 명령을 이용해 생성할 INDEX의 size를 추측해 보는 방법을 설명하고 있습니다. 
EXPLAIN PLAN FOR는 일반적으로 수행행되는 SQL의 PLAN이나 statistic 값들을 확인하기 위해 사용되는데, 
해당 SQL이 수행되면서 읽을 양으로 INDEX size를 판단할 수 있다는 내용입니다. 

위 포스트에서 설명한 내용을 그대로 한번 따라가 보겠습니다. 

SQL> show user
USER is "SCOTT"
SQL> explain plan set statement_id = 'cr_emp_idx01' for  create index emp_idx01 on emp(empno);   

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 188079254

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |           |    14 |    56 |    13   (0)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| EMP_IDX01 |       |       |            |          |
|   2 |   SORT CREATE INDEX    |           |    14 |    56 |            |          |
|   3 |    TABLE ACCESS FULL   | EMP       |    14 |    56 |    12   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 65536  bytes

14 rows selected.

위의 plan 결과에 따르면 해당 index는 65536 bytes가 되겠군요.
그럼 INDEX를 한번 만들어 보겠습니다.

SQL> create index emp_idx01 on emp(empno);

Index created.

SQL> column segment_name format a30
SQL> column segment_type format a30
SQL> select segment_name,segment_type,bytes from user_segments where segment_name= 'EMP_IDX01';

SEGMENT_NAME                   SEGMENT_TYPE                        BYTES
------------------------------ ------------------------------ ----------
EMP_IDX01                      INDEX                             1048576

SQL>  

SQL> connect system/manager
Connected.

SQL> select segment_name,segment_type,tablespace_name from dba_segments where segment_name = 'EMP_IDX01'; 

SEGMENT_NAME                   SEGMENT_TYPE                   TABLESPACE
------------------------------ ------------------------------ ----------
EMP_IDX01                      INDEX                          TS_TEST01


SQL> column tablespace_name format a10
SQL> select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT from dba_tablespaces where tablespace_name = 'TS_TEST01';

TABLESPACE INITIAL_EXTENT NEXT_EXTENT
---------- -------------- -----------
TS_TEST01         1048576     1048576

EMP_IDX01이 저장될 tablespace의 initial extent 값이 1048576이라
INDEX size 65536 bytes는 한개의 extent에 모두 저장되었군요.

PLAN에서 보여준 statistic 값은 Block scan 결과만 보여주는 것이니 PCTFREE 등의 값의 영향으로 약간의 가감은 필요할 것 같습니다만.
대충 얼마정도의 공간이 필요할 것이다라는 것에 대한 힌트는 될 것 같습니다.



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!




'Oracle Database' 카테고리의 다른 글

Oracle AWR 데이터를 편히 볼 수 있는 PIVOT 기능 - 11g new feature  (0) 2010.05.18
회사에서 Oracle DBA에게 요구하는 기술들  (0) 2010.04.29
DISTRIBUTED QUERY ANALYSIS  (0) 2010.04.22
Oracle 11g Trace file 정리하는 방법, ADRCI purge 기능  (0) 2010.04.21
Oracle Online re-org 기능을 이용해 일반 table을 interval partition으로 변경하는 방법  (0) 2010.04.21

+ Recent posts

티스토리툴바