레이블이 오라클12C강좌인 게시물을 표시합니다. 모든 게시물 표시
레이블이 오라클12C강좌인 게시물을 표시합니다. 모든 게시물 표시

2013년 10월 24일 목요일

ORACLE ROWNUM을 이용한 테스트 대량 데이터를 가진 테이블 만들기

ORACLE ROWNUM을 이용한 테스트 대량 데이터를 가진 테이블 만들기 

connect by 를 잘 이용하시면 됩니다.

CREATE TABLE emptest
    AS
      SELECT ROWNUM                    AS id
      ,      MOD(ROWNUM,100)            AS grp  --2000개씩 그룹핑
      ,      DBMS_RANDOM.STRING('u',5)  AS val  --랜덤 문자5개
      ,      DBMS_RANDOM.STRING('u',30) AS pad  --랜덤문자 30개
      FROM  dual
      CONNECT BY ROWNUM <= 2000000  --200만건 만들자... 

2013년 10월 23일 수요일

오라클 테이블 단편화(행이행, 행연쇄) 체크 (Oracle Table chain )

오라클 테이블 단편화(행이행, 행연쇄) 체크 (Oracle Table chain )



블록사이즈가 4K인데 레코드의 한행이 4K이상이라면 여러 블럭에 나누어 저장되게 되는데 이를
행연쇄 라고 하구요, 처음 insert문에서 입력되는 데이터는 작았는데 추후 많은 량의 데이터로 update
하게되어 해당 불록에 다 기록할 수 없어 다른 블록에 기록할 수 있는 데 이를 행이동 이라고 합니다.

행이행의 경우 원래의 블록에 새로운 블록을 가리키는 포인터를 두며 갱신전 데이터가 있는 영역은 사용하지 못하게 됩니다. 이 처럼 재이용되지 못하는 영역이 생기는 것을 단편화라고 하며 이를 확인하는 방법은 다음과 같습니다.

먼저 Analyze를 이용하여 통계 데이터를 추출 합니다.

SQL>analyze table emp compute statistics


행이행과 행연쇄가 있는지 조사 합니다. 아래에서 chain_cnt 값은 행이행이나 행연쇄로 인해
여러 블록으로 쪼개져 있는 행의 수를 의미합니다.

SQL> SELECT table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt
        FROM    dba_table
        WHERE chain_cnt > 0; 

2013년 10월 13일 일요일

Spring3.x, Hibernate4연동하기]스프링3.2,하이버네이트4.2.3

1. 하이버네이트 다운로드
 다운로드
 www.hibernate.org (hibernate-4.2.3.Final.zip 사용)
 lib : 하이버네이트를 실행 시 필요한 JAR 파일
 project : 각종 소스 코드
 documentation : 문서들
 하이버네이트를 실행하기 위해서는 hibernate4.2.3.jar 파일 뿐 아니라 lib 폴더의 각종 jar 파일이 필요하다.
 물론 이클립스 하이버네이트 플러그인을 설치해도 된다. 현재 Eclipse INDIGO 까지 나와 있다.

2. 준비
 이클립스 ? Spring Project
hibernate4 및 spring 관련 라이브러리 추가
(하이버네이트4 의 경우 스프링 3,1 이상이 필요)
주) 스프링의 Template Project에서 hhibernate Template을 이용하여 하이버네이트 APP 작성가능하나 라이브러리 버전이 맞지 않아 hibernate4 예제와 연동 어려움
    

예제 테이블 작성
  create table myemp (
      empno number ,
      ename varchar2(10)
  )

 
 

3. MyEmp.java
package edu.onj.hibernate;
public class MyEmp {
private int empno;
private String ename;
 public MyEmp() {}
 public MyEmp(int empno, String ename) {
  this.empno = empno;    this.ename = ename;
 }
 public int getEmpno() { return empno;  }
 public void setEmpno(int empno) {this.empno = empno;}
 public String getEname() {
  return ename;
 }
 public void setEname(String ename) {
  this.ename = ename;
 }
}
 
 
4. MyempDao.java

package edu.onj.hibernate;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional(propagation=Propagation.REQUIRED)
public class MyEmpDao {
private SessionFactory sessionFactory;
private Session session;
public MyEmpDao() {}
//constructor injection(생성자 주입)
@Autowired
public MyEmpDao(SessionFactory sessionFactory) {
this.sessionFactory = sessionFactory;
}
public Session getSession() {
return sessionFactory.getCurrentSession();
}
public void insertMyEmp(MyEmp myemp) {
getSession().save(myemp);
}
public void deleteMyEmp(int empno) {
getSession().delete(getMyEmpByEmpno(empno));
}
public MyEmp getMyEmpByEmpno(int empno) {
return (MyEmp)getSession().get(MyEmp.class, empno);
}
public void saveMyEmp(MyEmp myemp) {
getSession().update(myemp);
}
}
 
5. myemp.hbm.xml
<?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
    <hibernate-mapping>
    <class    name="edu.onj.hibernate.MyEmp"    table="MyEmp"    lazy="false">
        <id name="empno"  type="java.lang.Integer"   column="empno"  />
        <property  name="ename" type="java.lang.String"  column="ENAME" length="10"/>
    </class>
    </hibernate-mapping>
 
6. spring-hibernate.xml
 
<?xml version="1.0" encoding="UTF-8"?>
    <beans
        xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:util="http://www.springframework.org/schema/util"
        xmlns:aop="http://www.springframework.org/schema/aop"
        xmlns:context="http://www.springframework.org/schema/context"
        xmlns:tx="http://www.springframework.org/schema/tx"
        xsi:schemaLocation=
        "http://www.springframework.org/schema/beans
         http://www.springframework.org/schema/beans/spring-beans.xsd
         http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
         http://www.springframework.org/schema/aop
         http://www.springframework.org/schema/context
         http://www.springframework.org/schema/context/spring-context-3.0.xsd
         http://www.springframework.org/schema/tx
         http://www.springframework.org/schema/tx/spring-tx.xsd
         http://www.springframework.org/schema/util
         http://www.springframework.org/schema/util/spring-util.xsd" >

<!-- Annotation 쓰기 위해서 -->
      <context:annotation-config />
      <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
        <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
        <property name="url"><value>jdbc:oracle:thin:@localhost:1521:onj</value></property>
        <property name="username"><value>scott</value></property>
        <property name="password"><value>tiger</value></property>
      </bean>
      <!-- Hibernate Session Factory 설정 -->
      <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="dataSource"><ref bean="dataSource"/></property>
        <property name="mappingResources">
          <list>
            <value>myemp.hbm.xml</value>
          </list>
        </property>
        <property name="hibernateProperties">
          <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
          </props>
        </property>
      </bean>
      <!-- 트랜잭션 -->
      <tx:annotation-driven transaction-manager="transactionManager" />
      <bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
          <property name="sessionFactory" ref="sessionFactory" />
      </bean>
      <!-- MyEmpDAO autowiring  -->
      <bean id="myEmpDao" class="edu.onj.hibernate.MyEmpDao"> </bean>
   </beans>
  
  
7.    SpringHibernateExam.java

public class SpringHibernateExam {
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext("spring-hibernate.xml");
MyEmpDao myEmpDao = (MyEmpDao)ctx.getBean("myEmpDao");
try {
MyEmp myemp1 = new MyEmp(1, "1길동");
MyEmp myemp2 = new MyEmp(2, "2길동");
MyEmp myemp3 = new MyEmp(3, "3길동");
myEmpDao.insertMyEmp(myemp1);
myEmpDao.insertMyEmp(myemp2);
myEmpDao.deleteMyEmp(1);
myEmpDao.insertMyEmp(myemp3);
MyEmp emp1 = (MyEmp)myEmpDao.getMyEmpByEmpno(3);
System.out.println(emp1.getEmpno() + "::" + emp1.getEname());
myemp2.setEname("2가아니고4");
myEmpDao.saveMyEmp(myemp2);
MyEmp emp2 = (MyEmp)myEmpDao.getMyEmpByEmpno(2);
System.out.println(emp2.getEmpno() + "::" + emp2.getEname());
}
catch (HibernateException e)
        {             e.printStackTrace();         }
        finally        {                }
}
}
 
8. 결과
/*
SQL> select * from myemp;
EMPNO ENAME
---------- ----------
    2 2가아니고4
    3 3길동
*/
  
  
 
 

2013년 8월 3일 토요일

(oracle hint)-실행계획 SQL연산(INDEX RANGE SCAN DESCENDING, INDEX UNIQUE SCAN)

실행계획 SQL연산
(INDEX RANGE SCAN DESCENDING, INDEX UNIQUE SCAN)

구로디지털 오엔제이프로그래밍실무교육센터


인덱스 영역에서 데이터를 찾은 후 역순으로 인덱스 블록을 Scan 하므로 당연히 데이터는 역순으로 정렬되어 있습니다. 흔히 이렇게 역순으로 출력하기 위해(인덱스가 있음에도 불구하고) ORDER BY를 사용하기도 하는데 아래의 예를 잘 보시고 이 방법을 이용하도록 하자구요~

SQL> SELECT
               ENAME,
               SAL
      FROM   EMP E
      WHERE SAL > 0;

Execution Plan
--------------------------------------------------------------------
0       SELECT STATEMENT Optimizer=CHOOSE
1       0   TABLE ACCESS (BY INDEX ROWID) OF EMP
2       1      INDEX (RANGE SCAN DESCENDING) OF idx_emp_sal (NON-UNIQUE)


위에서 보인 INDEX_DESC는 오라클의 힌트 구문으로 idx_emp_sal 인덱스에서 역순으로 SCAN 하라는 의미를 가집니다.

아래와 같은 방법은 좋은 방법이 아닙니다. 위의 SQL 문장과 비교하여 보세요~

SQL> SELECT ENAME,
               SAL
      FROM   EMP E
      ORDER  BY SAL DESC;


한편 INDEX UNIQUE SCAN Unique한 인덱스에서 Unique한 값을 추출하는 연산인데 하나의 ROW를 추출하는데 있어 가장 좋은 방법입니다.

아래는 Primary Key 생성시 만들어진 Unique 인덱스를 이용하여 로우를 추출하는 예입니다.
SQL> SELECT ENAME,
               SAL
      FROM   EMP E
      WHERE EMonO =1004;

Execution Plan
--------------------------------------------------------------------
0       SELECT STATEMENT Optimizer=CHOOSE
1      0   TABLE ACCESS (BY INDEX ROWID) OF EMP
2      1      INDEX (UNIQUE SCAN) OF pk_emp (UNIQUE) 

2013년 8월 2일 금요일

조인 방법 변경(LEADING), 오라클 힌트 강좌

조인 방법 변경(LEADING):namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
 
구로디지털 오엔제이프로그래밍실무교육센터
 
오라클 힌트 구문 중 하나인 LEADING 힌트는 두 테이블 조인 시 드라이빙 테이블을 인자로 사용하며,  ORDERED와 같이 FROM절 뒤에 오는 테이블의 위치가 중요 합니다.
 
참고로 ORDERED 힌트는 주로 USE_NL/USE_MERGE/USE_HASH 힌트와 같이 사용되는데 USE_NL/USE_MERGE/USE_HASH 인자로 사용되는 테이블은 FROM절에서 두 번째로 나타나는 테이블 이어야 하며 FROM절에서 처음 나타나는 테이블이 드라이빙 테이블(OUTER/DRIVING TABLE)이 되고 나중에 나타나는 테이블이 PROBED TABLE(INNER TABLE)이 됩니다.
 
[9i]
SQL>SELECT E.ENAME, D.DNAME
     FROM EMP E, DEPT D
     WHERE E.DEPTNO = D.DEPTNO;
 
Execution Plan
--------------------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE
TABLE ACCESS (BY INDEX ROWID) OF DEPT
  NESTED :namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />LOOP
    TABLE ACCESS (FULL) OF EMP
    INDEX (RANGE SCAN) OF idx_dept_deptno
 
 
위 힌트는 ORDERED를 이용하면 다음과 같이 바꿀 수 있습니다.
 
SELECT E.ENAME, D.DNAME
     FROM EMP E, DEPT D
     WHERE E.DEPTNO = D.DEPTNO;
 
 
 
[실습]
 
-      실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.
 
myemp1 : 1000만건
myemp1_old : 100만건
mydept : 5
 
테스트환경 : oracle 11g
 
 
 
n  Mydept1이 드라이빙 테이블, myemp1이 내부 테이블(비 드라이빙 테이블)
SQL> select
  2         e.ename,
  3         d.dname
  4  from   mydept1 d, myemp1 e
  5  where  e.deptno = d.deptno ;
 
20000000 개의 행이 선택되었습니다.
 
   : 00:02:11.23
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    20M|   476M|   169K  (1)| 00:33:53 |
|   1 |  NESTED LOOPS      |         |    20M|   476M|   169K  (1)| 00:33:53 |
|   2 |   TABLE ACCESS FULL| MYDEPT1 |    10 |   100 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| MYEMP1  |  2000K|    28M| 16939   (1)| 00:03:24 |
------------------------------------------------------------------------------
 
 
 
이번에는 mydept1이 드라이빙, myemp1이 비드라이빙
 
SQL> select
  2         e.ename,
  3         d.dname
  4  from   mydept1 d, myemp1 e
  5  where  e.deptno = d.deptno ;
 
20000000 개의 행이 선택되었습니다.
 
   : 00:01:47.94
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    20M|   476M| 17043   (2)| 00:03:25 |
|*  1 |  HASH JOIN         |         |    20M|   476M| 17043   (2)| 00:03:25 |
|   2 |   TABLE ACCESS FULL| MYDEPT1 |    10 |   100 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| MYEMP1  |    10M|   143M| 16941   (1)| 00:03:24 |
------------------------------------------------------------------------------
 

[Oracle Hint]조인 방법 변경(DRIVING_SITE), 오라클 힌트

조인 방법 변경(DRIVING_SITE)

구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr


분산환경(DB Link를 이용하는 경우)에서 쿼리를 사용하는 경우 일반적으로 SQL 실행의 주체는 해당 Query를 실행시킨 로컬데이터 베이스가 됩니다. 즉 원격지의 DEPT 테이블의 데이터를 로컬로 가져와서 조인을 하는 것은 로컬에서 하게 되구요… 참고로 이 힌트의 경우 Cost-Based Optimizer 또는 Rule-Based Optimizer환경 모두에서 사용 가능 합니다.

[형식]
/*+ DRIVING_SITE ( table ) */


SQL>select e.empno, e.ename, e.sal, d.dname, d.loc
      from emp e, dept@remote_db d   요기 DB Link 사용했습니다.
      where e.deptno = d.deptno;

Execution plan
-------------------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE
NESTED LOOPS
  REMOTE          remote_db
  TABLE ACCESS (BY INDEX ROWID) of ‘EMP’
    INDEX (RANGE SCAN) OF ‘idx_emp_deptno’ (NON-UNIQUE)
 
SERIAL_FROM_REMOTE SELECT “DEPTNO”, “DNAME”, “LOC” FROM “DEPT” “D……

DRIVING_SITE 힌트를 사용하면 인자로 취한 테이블이 위치한 원격지에서 조인이 일어나게 할 수 있는데 즉 아래의 실행 계획을 보면 위에서와는 달리 SELECT STATEMENT 부분에 REMOTE라는 것이 보일 겁니다. 즉 다음의 쿼리는 원격지에서 주도해서 그곳에서 조인이 일어났음을 알 수 있습니다.


SQL>select /*+ driving_site(d) */ e.empno, e.ename, e.sal, d.dname, d.loc
      From emp e, dept@remote_db d
      Where e.deptno = d.deptno;

Execution plan
-------------------------------------------------------------------
SELECT STATEMENT(REMOTE) Optimizer=CHOOSE
MERGE JOIN
  SORT (JOIN)
      TABLE ACCESS (FULL) of ‘DEPT’
    SORT (JOIN)
      REMOTE*
 
SERIAL_FROM_REMOTE SELECT “EMPNO”, “ENAME”, “SAL” FROM ……

분산 환경의 쿼리를 실행하는 경우 그쪽의 DB사양이나 시스템 사양이 좋다면 DRIVING_SITE 힌트를 이용하여 그곳에서 드라이빙이 일어나게 할 수 있습니다.

[예]
SELECT /*+DRIVING_SITE(departments)*/ *
FROM employees, departments@rsite
WHERE employees.department_id = departments.department_id; 

2013년 8월 1일 목요일

Oracle Database Startup shut down

---------------
 DataBase 시작
 ---------------
 Server Manager를 기동한후 작업실시 svrmgrl(unix), svrmgr30(NT용 Oracle 8.x)

 1. DataBase를 마운트 하지않고 인스턴스 시작
 startup nomount;
 - DataBase 생성주에만 이러한 경우가 발생
 2. DataBase를 마운트한후 인스턴트 시작
 startup mount;
 - 데이터 파일의 이름변경,리두로그 파일추가,삭제,변경
 Redo Log Archive Option 활성화또는 비활성화
 전체 DataBase 복구작업 등의 경우에 사용
 3. 인스턴스 시작후 DB를 Mount하여 Open
 startup open;
 - 사용자들이 일반적인 데이타 Access 작업을 할 수 있다.
 4. DataBase 시작단계에서 Access제한
 startup restrict;
 - 인덱스 재구축이나, DB Export/Import 수행
 SQL*Loader등의 작업 수행
 create session권한이 있는 사용자는 DB에 접속 가능하며, Create session/restriced sesison권한이
 있는 사용자는 DB에 Access 할 수 있슴. 즉 DBA만이 restricted session권한이 있어야 함
 5. Instance 강제시작
 startup force;
 - Instance 시작시 문제가 발생한 경우나, shutdown normal이나 shutdown immediate로 현재의 Instance를
 종료할수 없는 경우에 사용
 6. 인스턴스를 시작하고, DB를 Mount한다음 자동으로 복구
 startup recover;

 ---------------
 DataBase 종료
 ---------------
 1. 정상종료
 shutdown normal;
 - 사용자들이 모든 Session을 끊을때까지 기다린후 shutdown
 다시 시작할때 인스턴스 복구가 필요없슴.
 2. 즉시종료
 shutdown immediate;
 - 현재 Client의 모든 SQL명령이 즉시종료
 Commit안된 Session은 RollBack됨
 현재 session을 RollBack한후 즉시 Connection을 끊어버림
 3. 인스턴스중지
 shutdown abort;
 - DB를 즉시 종료할 경우에 사용
 인스턴스를 시작할때 문제가 발생한 경우
 Client의 SQL명령이 즉시 종료되며 Commit안된 Transaction은 RollBack안되며, 즉시 종료됨 
 

Oracle 12c(오라클12c) Top-n, Fetch 사용하기, Row Limiting 예제

오라클12c(Oracle 12c) Top-n, Fetch 사용하기, Row Limiting 
 
오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터
(오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷  실무전문 강의) 
 
Top-n 구는 정렬된 데이터에서 Top에서 Bottom으로 정해진 숫자만큼 데이터를 추출 하는 것이다.  MySQL이라면 다음과 같이 Limit 구를 사용하여 Top-n을 구현했었다.
 
 
SELECT *
FROM   table
ORDER BY column
LIMIT 0 , 40
 
 
Oracle 12c의 Top-N 쿼리의 기본 문법은 다음과 같다.
 
 
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
 
 
위 MySQL의 Limit 구문처럼 정통적인 Top-N 쿼리 구현은 간단하다.
 
Select * from mytable order by num
 
num
----
1
1
2
2
3
3
4
4
.
.
10
10
 
 
Select num
From mytable
Order by num
Fetch first 3 rows only;
 
10
10
9
 
다음 쿼리를 보자
 
Select num
From mytable
Order by num
Fetch first 3 rows with ties;
 
10
10
9
9
 
위 SQL ties 구문에 의해 같은 9라는 값을 가진 다른 데이터도 같이 선택된다.
 
다음 예문을 보자
 
Select num
From mytable
Order by num
Fetch first 10 percent rows only;
 
1
1