select a.table_name, a.constraint_name, c.column_name 
from user_constraints a, user_constraints b, user_cons_columns c
where a.r_constraint_name = b.constraint_name
and b.table_name = '<원하는 table명>'
and c.constraint_name = b.constraint_name

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

10g RAC의 Load Balancing과 Failover  (1) 2009.12.30
Backup and Recover  (0) 2009.12.30
Admin Workshop 1 - 구조  (0) 2009.12.15
Query 실행 과정  (1) 2009.12.15
힌트 종류  (0) 2009.12.15

업무 요건에 의해 많은 곳에서 SQL에 정렬을 수행하게 된다. 대부분의 SQL이 ORDER BY 절에 의한 정렬이다. 이와 같은 정렬이 SQL의 성능을 저하시킨다는 것은 누구나 아는 사실일 것이다. 하지만, 개발에 시간이 없어서인지 아니면 정확한 지식이 없어서인지 실제 업무에서 정렬을 제거하고자 하는 사람은 거의 없는 것 같다. 더욱 아쉬운 것은 성능을 향상시키고자 하는 튜너들 중 많은 튜너들이 정렬을 제거하는 방법을 모른다는 것이다.

정렬의 제거는 해당 시스템의 성능을 몇 단계 향상시킬 수 있는 방법이다. 그럼에도 불구하고 아직 많은 사이트에서는 이러한 것이 이뤄지지 않고 있다. 왜 그런 것일까? 개발에 식간이 없어서인가? 그것은 아닌 것 같다. 필자의 의견으로는 바빠서라기보다는 몰라서가 더 맞는 답인 것 같다. 이제 우리는 이와 같이 정렬을 제거할 수 있는 아키텍처를 이해해 최적의 성능을 보장 받아야 할 것이다. 이것이야말로 대용량 데이터베이스로 변하는 지금 우리에게 반드시 필요한 기술이다. 이번 호에서 SQL의 정렬을 제거하는 방법을 하나하나 알아보도록 하자.

정렬을 제거하기 위해 필요한 요소는 무엇인가?

정렬을 제거하기 위해 필요한 것은 무엇일까? 필자는 정렬을 제거하기 위해 필요한 요소는 세 가지라고 생각한다.

- 인덱스
- 기술력
- 논리적인 사고

정렬을 제거하기 위해서는 위와 같은 요소들이 융합되어야만 한다. 첫 번째로 인덱스에 대해 확인해 보자. ORDER BY 절을 가지고 있는 SQL에 대해 정렬을 제거하려면 어떻게 해야 하는가? ORDER BY 절을 제거하고 정렬을 제거하기 위해서는 이미 정렬된 데이터가 존재해야 할 것이다. 정렬된 데이터가 존재하지 않는데 자동으로 정렬된 데이터를 추출할 수는 없다. 그렇다면 어디에 정렬된 데이터가 존재하는가? 테이블을 먼저 확인해 보자. 테이블에는 데이터가 저장되는 순서대로 저장되므로 실제 정렬된 데이터가 존재할 수 없게 된다. 만약 테이블에 원하는 형태의 정렬된 데이터가 존재한다면 이는 운이 좋은 경우이다. 이와 같기 때문에 테이블에서 정렬된 데이터를 원할 수는 없게 된다. 그렇다면 또 무엇을 확인해야 하는가? 바로 인덱스이다. 인덱스는 어떠한가? 인덱스는 인덱스를 구성하는 컬럼으로 정렬되어 있게 된다. 그렇기 때문에 인덱스를 COL1 컬럼으로 생성한다면 해당 인덱스는 COL1 컬럼의 값으로 정렬되어 디스크에 저장된다. 이처럼 인덱스를 정확히 이해하고 효과적으로 사용해야만 우리는 정렬을 제거할 수 있다.

두 번째로 기술력에 대해 확인해 보자. 여기서 말하는 기술력은 데이터베이스 및 SQL에 대한 기술력이다. SQL을 작성하다 보면 조인을 많이 이용하게 된다. 이와 같은 조인은 2개 이상의 테이블에서 원하는 데이터를 추출하는 경우에 해당한다. 오히려 하나의 테이블에서 원하는 데이터를 추출하는 경우보다는 2개 이상의 테이블에서 원하는 데이터를 추출하는 경우가 더 많을 것이다. 이와 같은 경우에는 조인에 대한 성격을 정확히 이해해야만 정렬을 제거하고 정렬된 데이터를 추출할 수 있게 된다. 예를 들어 중첩 루프 조인(Nested Loops Join)의 경우에는 먼저 액세스되는 테이블이 이용하는 인덱스에 의해 정렬된 데이터가 추출될 것이다. 이처럼 인덱스만으로 정렬을 제거할 수 없으며 이에 따르는 전반적인 지식이 있어야만 우리는 정렬을 제거할 수 있다.

세 번째로 논리적인 사고이다. 여기서 말하는 논리적인 사고는 ORDER BY 절을 제거하기 위해 반드시 필요하다. ORDER BY 절을 제거하지 못하더라도 최적의 성능을 보장하기 위해 반드시 필요한 사항이다. 예를 들어 UNION ALL이 사용되었다면 어떻게 될까? 두 집합을 연결하는 연산자가 UNION ALL 집합 연산자이다. 이와 같은 경우 정렬을 수행해 해당 집합에서 20건의 데이터만을 추출한다면 쉽게 ORDER BY 절을 제거할 수 없을 것이다. 이런 경우에는 각각의 집합에서 정렬된 20건의 데이터를 추출한 후 40건에 대해 실제 ORDER BY 절을 수행해 정렬을 최적화할 수 있다. 이처럼 논리적인 사고는 ORDER BY 절을 제거하거나 최적화하기 위해 반드시 필요하다.

정렬을 제거한다는 것은 결코 쉬운 것이 아니다. 또한, 모든 정렬은 위에서 언급한 세 가지 요소를 정확히 구사한다면 모두 제거할 수 있다. 하지만 모든 정렬을 제거하는 것 또한 의미는 없으며 힘든 작업이 될 것이다. 그렇기 때문에 해당 시스템에서 중요한 SQL에 대해서만 정렬을 제거하는 것이 필요하다.

기본적인 정렬을 제거해 보자.

우선적으로 기본적인 정렬을 제거해 봄으로써 정렬을 제거하는 기본 개념을 이해하길 바란다.


위의 SQL에서 사용된 ORDER BY 절을 제거하기 위해서는 어떻게 해야 할까? 앞서 언급했듯이 ORDER BY 절을 제거하기 위해서는 인덱스를 이용해야 할 것이다. 그렇다면 어떻게 인덱스를 생성해서 이용해야 정렬을 제거하고 효과적인 SQL을 작성할 수 있겠는가?

REG_DATE로 인덱스를 생성했다고 가정하자. 그렇다면 인덱스에 BOARD_ID 컬럼이 존재하지 않으므로 모든 인덱스 값을 액세스해야 할 것이다. 모든 인덱스 값을 액세스한 후 BOARD_ID 값이 ‘111’인 데이터를 확인해 결과로 추출하게 된다. 이와 같이 수행한다면 인덱스 FULL SCAN 실행 계획이 생성될 것은 너무나도 자명한 일이다. 인덱스 FULL SCAN이 발생한다면 인덱스의 모든 값을 액세스한 후 BOARD_ID 컬럼의 값을 확인해 조건을 만족하지 않는 경우 버리게 되므로 비효율이 발생하게 되며 그 양이 많다면 성능 저하도 당연할 것이다.

인덱스를 BOARD_ID+REG_DATE로 생성하는 경우는 어떠한가? 이와 같이 인덱스를 생성한다면 BOARD_ID 컬럼을 만족하는 데이터에 대해 차례대로 값을 액세스하게 된다. 인덱스가 BOARD_ID+REG_DATE로 구성되어 있으므로 동일한 BOARD_ID 값에 대해서는 REG_DATE 컬럼의 값으로 정렬되어 있다. 그러므로 이와 같이 인덱스를 생성한다면 처리 범위도 감소시키면서 인덱스를 이용해 정렬을 제거할 수 있게 된다. 아래의 예제를 확인해 보자.

위와 같이 SQL을 수행한다면 BOARD_ID+REG_DATE 인덱스로 정렬을 제거할 수 있겠는가?
해당 SQL은 BOARD_ID +REG_DATE 인덱스를 이용해서는 정렬된 데이터를 추출할 수 없다. 이는 BOARD_ID 컬럼의 값이 하나가 아니며 여러 개의 값이기 때문이다. BOARD_ID+REG_DATE 인덱스는 동일한 BOARD_ID 컬럼의 값에 대해서는 REG_DATE 컬럼의 값으로 정렬된다. 하지만 해당 SQL은 BOARD_ID 컬럼의 값이 여러 개이므로 조건을 만족하는 BOARD_ID 컬럼의 값에 대해 REG_DATE 컬럼의 값으로 정렬되어 있다고 할 수 없다.

그렇기 때문에 위의 예제는 BOARD_ID+REG_DATE 인덱스를 이용해 정렬된 데이터를 추출할 수 없게 되며 인덱스를 이용해 정렬을 수행하고자 한다면 REG_DATE 인덱스 또는 REG_DATE +BOARD_ID 인덱스를 이용해야 할 것이다. 이와 같다면 BOARD_ID 컬럼에 의해 처리 범위가 감소하지 않게 된다. 이 경우에는 정렬을 제거하고 전체를 액세스하는 것이 유리한지 아니면 정렬은 수행하되 BOARD_ID 컬럼에 의한 처리 범위를 감소시키는 것이 유리한지를 고려해 선택해야 할 것이다.
정렬을 제거하는 가장 기본적인 예제들을 확인해 보았다.

해당 예제들은 인덱스만을 이용해 정렬을 제거하는 것이며 인덱스 구성만 최적화한다면 손쉽게 정렬을 제거할 수 있다. 이처럼 정렬을 제거하고자 한다면 정렬을 제거할 수는 있다. 하지만, 정렬을 제거할 경우의 상황을 정확히 파악해 최적의 경우를 선택해야 한다는 것이 더 중요하다. 다음 호에서는 정렬을 제거하는 SQL에 대해 복잡한 경우를 확인해 보도록 하자.

필자소개

권순용 kwontra@hanmail.net|Data Consulting 업무를 수행하는 ㈜엑시엄의 대표이사이며 DBA로 시작해 SQL 튜닝, 데이터베이스 아키텍처 및 모델링 업무를 주로 수행했다. 데이터베이스 교육에도 많은 관심을 가지고 있으며 저서로는 『Perfect! 오라클 실전 튜닝, 『초보자를 위한 오라클 10g』 및 『INSIDE SQL』이 있다. 또한, 데이터 액세스 최적화에 대한 특허를 출원했다.

출처 : 한국 마이크로 소프트웨어 [2009년 11월호]
제공 : DB포탈사이트 DBguide.net

'Database' 카테고리의 다른 글

DBMS별 날짜 포멧  (0) 2010.01.27
오픈소스 DBMS 라이센스의 이해  (0) 2009.12.30
DBMS별 날짜 포멧  (0) 2009.12.30
Triple DES Security Java 소스이다.
다른 소스와 다른 점은 NoSuchAlgorithm이 날 경우에 SunJCE를 설치하여 Exception을 없앤 소스라는 것...


/*

 * Copyright (c) 2000 David Flanagan.  All rights reserved.
 * This code is from the book Java Examples in a Nutshell, 2nd Edition.
 * It is provided AS-IS, WITHOUT ANY WARRANTY either expressed or implied.
 * You may study, use, and modify it for any non-commercial purpose.
 * You may distribute it non-commercially as long as you retain this notice.
 * For a commercial use license, or to purchase the book (recommended),
 * visit http://www.davidflanagan.com/javaexamples2.
 */

import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.security.Provider;
import java.security.Security;
import java.security.spec.InvalidKeySpecException;

import javax.crypto.BadPaddingException;
import javax.crypto.Cipher;
import javax.crypto.CipherOutputStream;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.KeyGenerator;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.SecretKey;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.DESedeKeySpec;

/**
 * This class defines methods for encrypting and decrypting using the Triple DES
 * algorithm and for generating, reading and writing Triple DES keys. It also
 * defines a main() method that allows these methods to be used from the command
 * line.
 */
public class TripleDES {
  /**
   * The program. The first argument must be -e, -d, or -g to encrypt,
   * decrypt, or generate a key. The second argument is the name of a file
   * from which the key is read or to which it is written for -g. The -e and
   * -d arguments cause the program to read from standard input and encrypt or
   * decrypt to standard output.
   */
  public static void main(String[] args) {
    try {
      // Check to see whether there is a provider that can do TripleDES
      // encryption. If not, explicitly install the SunJCE provider.
      try {
        Cipher c = Cipher.getInstance("DESede");
      } catch (Exception e) {
        // An exception here probably means the JCE provider hasn't
        // been permanently installed on this system by listing it
        // in the $JAVA_HOME/jre/lib/security/java.security file.
        // Therefore, we have to install the JCE provider explicitly.
        System.err.println("Installing SunJCE provider.");
        Provider sunjce = new com.sun.crypto.provider.SunJCE();
        Security.addProvider(sunjce);
      }

      // This is where we'll read the key from or write it to
      File keyfile = new File(args[1]);

      // Now check the first arg to see what we're going to do
      if (args[0].equals("-g")) { // Generate a key
        System.out.print("Generating key. This may take some time...");
        System.out.flush();
        SecretKey key = generateKey();
        writeKey(key, keyfile);
        System.out.println("done.");
        System.out.println("Secret key written to " + args[1]
            + ". Protect that file carefully!");
      } else if (args[0].equals("-e")) { // Encrypt stdin to stdout
        SecretKey key = readKey(keyfile);
        encrypt(key, System.in, System.out);
      } else if (args[0].equals("-d")) { // Decrypt stdin to stdout
        SecretKey key = readKey(keyfile);
        decrypt(key, System.in, System.out);
      }
    } catch (Exception e) {
      System.err.println(e);
      System.err.println("Usage: java " + TripleDES.class.getName()
          + " -d|-e|-g <keyfile>");
    }
  }

  /** Generate a secret TripleDES encryption/decryption key */
  public static SecretKey generateKey() throws NoSuchAlgorithmException {
    // Get a key generator for Triple DES (a.k.a DESede)
    KeyGenerator keygen = KeyGenerator.getInstance("DESede");
    // Use it to generate a key
    return keygen.generateKey();
  }

  /** Save the specified TripleDES SecretKey to the specified file */
  public static void writeKey(SecretKey key, File f) throws IOException,
      NoSuchAlgorithmException, InvalidKeySpecException {
    // Convert the secret key to an array of bytes like this
    SecretKeyFactory keyfactory = SecretKeyFactory.getInstance("DESede");
    DESedeKeySpec keyspec = (DESedeKeySpec) keyfactory.getKeySpec(key,
        DESedeKeySpec.class);
    byte[] rawkey = keyspec.getKey();

    // Write the raw key to the file
    FileOutputStream out = new FileOutputStream(f);
    out.write(rawkey);
    out.close();
  }

  /** Read a TripleDES secret key from the specified file */
  public static SecretKey readKey(File f) throws IOException,
      NoSuchAlgorithmException, InvalidKeyException,
      InvalidKeySpecException {
    // Read the raw bytes from the keyfile
    DataInputStream in = new DataInputStream(new FileInputStream(f));
    byte[] rawkey = new byte[(int) f.length()];
    in.readFully(rawkey);
    in.close();

    // Convert the raw bytes to a secret key like this
    DESedeKeySpec keyspec = new DESedeKeySpec(rawkey);
    SecretKeyFactory keyfactory = SecretKeyFactory.getInstance("DESede");
    SecretKey key = keyfactory.generateSecret(keyspec);
    return key;
  }

  /**
   * Use the specified TripleDES key to encrypt bytes from the input stream
   * and write them to the output stream. This method uses CipherOutputStream
   * to perform the encryption and write bytes at the same time.
   */
  public static void encrypt(SecretKey key, InputStream in, OutputStream out)
      throws NoSuchAlgorithmException, InvalidKeyException,
      NoSuchPaddingException, IOException {
    // Create and initialize the encryption engine
    Cipher cipher = Cipher.getInstance("DESede");
    cipher.init(Cipher.ENCRYPT_MODE, key);

    // Create a special output stream to do the work for us
    CipherOutputStream cos = new CipherOutputStream(out, cipher);

    // Read from the input and write to the encrypting output stream
    byte[] buffer = new byte[2048];
    int bytesRead;
    while ((bytesRead = in.read(buffer)) != -1) {
      cos.write(buffer, 0, bytesRead);
    }
    cos.close();

    // For extra security, don't leave any plaintext hanging around memory.
    java.util.Arrays.fill(buffer, (byte) 0);
  }

  /**
   * Use the specified TripleDES key to decrypt bytes ready from the input
   * stream and write them to the output stream. This method uses uses Cipher
   * directly to show how it can be done without CipherInputStream and
   * CipherOutputStream.
   */
  public static void decrypt(SecretKey key, InputStream in, OutputStream out)
      throws NoSuchAlgorithmException, InvalidKeyException, IOException,
      IllegalBlockSizeException, NoSuchPaddingException,
      BadPaddingException {
    // Create and initialize the decryption engine
    Cipher cipher = Cipher.getInstance("DESede");
    cipher.init(Cipher.DECRYPT_MODE, key);

    // Read bytes, decrypt, and write them out.
    byte[] buffer = new byte[2048];
    int bytesRead;
    while ((bytesRead = in.read(buffer)) != -1) {
      out.write(cipher.update(buffer, 0, bytesRead));
    }

    // Write out the final bunch of decrypted bytes
    out.write(cipher.doFinal());
    out.flush();
  }
}

'Program > Java' 카테고리의 다른 글

Annotation (since tiger / 1.5)  (0) 2009.12.15
XML 파싱  (0) 2009.12.15
아파치 미나  (0) 2009.12.15
알아야 할 용어 정리  (0) 2009.11.30
Stuts2 설정 - struts.properties  (0) 2009.10.09
1. RMI : Remote Method Invocation
Java 환경에서 Computer 간 또는 Program 간에 통신을 할 수 있는 기능을 제공
http://www.javanuri.com/devforum/board.jsp?menuId=13

2. AOP : Aspect Oriented Programming [관점경향]
공통적으로 사용하는 것들에 대한 의존 관계의 복잡성과 코드의 중복을 해소 해 주는 프로그래밍 기법
  - Aspect : 여러 객체에 공통적으로 적용되는 공통 관심사항 [예:트랜잭션이나 보안]
  - Advice: Aspect를 핵심 로직에 적용할지 정의 [예 : 메소드 호출 전에 트랜잭션 시작]
  - Joinpoint : Advice 적용 가능한 지점 [예: 메소드 호출, 필드 값 변경]
  - Pointcut : Joinpoint 의 부분집합. Advice가 적용되는 Joinpoint
  - Weaving : Adivce를 핵심 로직 코드에 적용하는 것

3. DI :  Dependency Injection
의존성 주입
의존(Dependency)에 대한 주입(Injection)을 외부에서 처리하여 주는 것
http://www.sleepyon.com/219

 4. IOC : Inversion of Control
객체에 대한 제어권이 컨테이너에게 넘어가면서 객체의 생명주기를 관리하는 권한 또한 컨테이너들이 전담할 수 밖에 없게 되었다. 이처럼 객체의 생성에서부터 생명주기의 관리까지 모든 객체에 대한 제어권이 바뀐 것을 의미하는 것이 제어권의 역전, 즉 Ioc라는 개념
http://wiki.javajigi.net/pages/viewpage.action?pageId=3664

5. ORM : Object-relational mapping [프레임워크]
객체와의 관계를 맵핑시킨다는 일을 함

6. Hiernate
객체를 RDB에 매핑해서 저장하는 ORM(Object-Relational Mapping) Tool
http://wiki.javajigi.net/pages/viewpage.action?pageId=5415

7. DAO : Data Access Objects
실질적인 DB와의 연결을 담당하는 일을 가진 객체

8. MVC : Model, View, Controller [패턴]

9. Beans
jsp에서 객체를 가져다가 사용할 수 있도록 한 기법
기본적으로 데이터를 저장하기 위한, 멤버변수와, 데이터를 컨트롤하는 setter/getter 메소드를 가지고 있는 클래스를 일컫는 말
데이터를 담은 POJO Object

10. POJO : Plain Old Java Object
순수 자바 클래스들을 이르는 말로, 기본 자바 오브젝트를 말함

11. ORM : Object-Relation Mapping
http://www.javajigi.net/pages/viewpage.action?pageId=6560

12. Singleton pattern
heap 영역에 한개만 올려놓고 스택에선 같은 객체를 가르키도록 코딩
클래스의 멤버 변수 공유

13. BeanFactory
빈을 생성하고 소멸시키는 책임을 가진 클래스

14. Decoupling : 디커플링
탈동조화
결합을 약화시킨다

15.

'Program > Java' 카테고리의 다른 글

Annotation (since tiger / 1.5)  (0) 2009.12.15
XML 파싱  (0) 2009.12.15
아파치 미나  (0) 2009.12.15
Triple DES Java  (0) 2009.12.02
Stuts2 설정 - struts.properties  (0) 2009.10.09

+ Recent posts