SourceCode ,Software & Life

간단한 쿼리인데 어떻게 해야 좋을지 감이 안와서 염치 무릅쓰고 질문 올립니다.
한 테이블에 2 개의 필드가 존재합니다. 한글이름과 한자이름인데요...

person_tbl 에
person_korname, person_chnname 라는 필드로 존재하고 있습니다.
그런데 한글 이름은 동일한데 한자 이름이 다른 경우가 많이 존재할 것입니다.

김종화 金宗和
김종화 金種華
김종화 金鍾華
김종화 金鍾化
김종환 金種桓
김중곤 金重坤
김중곤 金重坤
김중곤 金中坤

이런 식으로요...
이 상태에서 한글 이름은 같은데 한자 이름이 다른 경우의 수나 그 결과를 어떻게 뽑을 수 있을까요?
예를 들면 아래와 같이 말입니다.

김종화 金宗和 김종화 金種華
김종화 金宗和 김종화 金鍾華
김종화 金宗和 김종화 金鍾化
김중곤 金重坤 김중곤 金中坤

혹은 count(*) 의 결과가 4 가 되도록요...(김중곤의 1, 2 번째는 한글이름과 한자 이름이 완전 동일합니다)

조언 부탁드립니다.
이 글에 대한 댓글이 총 6건 있습니다.

만약에 위의 예에서,
김중곤 金中坤 ..... 항목이 한 번 더 나온다면,

쿼리 결과는
김종화 金宗和 김종화 金種華
김종화 金宗和 김종화 金鍾華
김종화 金宗和 김종화 金鍾化
김중곤 金重坤 김중곤 金中坤

인가요? 아니면,

김종화 金宗和 김종화 金種華
김종화 金宗和 김종화 金鍾華
김종화 金宗和 김종화 金鍾化
김중곤 金重坤 김중곤 金中坤
김중곤 金重坤 김중곤 金中坤

인가요?

SoQooL(김홍선)님이 2006-06-27 10:36:18에 작성한 댓글입니다.

김종화 金宗和 김종화 金種華
김종화 金宗和 김종화 金鍾華
김종화 金宗和 김종화 金鍾化
김중곤 金重坤 김중곤 金中坤
김중곤 金重坤 김중곤 金中坤

이 아니라

김종화 金宗和 김종화 金種華
김종화 金宗和 김종화 金鍾華
김종화 金宗和 김종화 金鍾化
김중곤 金重坤 김중곤 金中坤


였으면 좋겠습니다. 둘 다 표현이 가능하다면 그것도 좋겠지만요. ^^;;;
김종화(zepinos)님이 2006-06-27 11:03:21에 작성한 댓글입니다.

CREATE TABLE TAB38(
KONAME VARCHAR2(10),
CHNAME VARCHAR2(10)
)

INSERT INTO TAB38 VALUES(`김종화`,`金宗和`);
INSERT INTO TAB38 VALUES(`김종화`,`金種華`);
INSERT INTO TAB38 VALUES(`김종화`,`金鍾華`);
INSERT INTO TAB38 VALUES(`김종화`,`金鍾化`);
INSERT INTO TAB38 VALUES(`김종환`,`金種桓`);
INSERT INTO TAB38 VALUES(`김중곤`,`金重坤`);
INSERT INTO TAB38 VALUES(`김중곤`,`金重坤`);
INSERT INTO TAB38 VALUES(`김중곤`,`金中坤`);
INSERT INTO TAB38 VALUES(`김중곤`,`金中坤`);
INSERT INTO TAB38 VALUES(`김종화`,`金宗和`);
INSERT INTO TAB38 VALUES(`김종화`,`金種華`);

==============================================

1) 중복완전 배제

SELECT B.KONAME BKONAME,B.CHNAME BCHNAME,A.KONAME AKONAME,A.CHNAME ACHNAME
FROM
(SELECT DISTINCT KONAME,CHNAME FROM TAB38) A,
(
SELECT RM,KONAME,CHNAME
FROM
(
  SELECT ROW_NUMBER() OVER(PARTITION BY KONAME ORDER BY 1) RM
  ,KONAME,CHNAME
  FROM TAB38
)
WHERE RM=1
) B
WHERE A.KONAME=B.KONAME
AND A.CHNAME<> B.CHNAME

-------------------------------------------

2)중복허용

SELECT B.KONAME BKONAME,B.CHNAME BCHNAME,A.KONAME AKONAME,A.CHNAME ACHNAME
FROM

TAB38 A,
(
SELECT RM,KONAME,CHNAME
FROM
(
  SELECT ROW_NUMBER() OVER(PARTITION BY KONAME ORDER BY 1) RM
  ,KONAME,CHNAME
  FROM TAB38
)
WHERE RM=1
) B
WHERE A.KONAME=B.KONAME
AND A.CHNAME<> B.CHNAME


이렇게 하시면 될 듯 합니다.
김강환(hashtable)님이 2006-06-27 11:20:42에 작성한 댓글입니다.

답변 감사드립니다.
rownum 을 이렇게 이용하는 방법이 있었네요. 내공부족을 여실히 느낍니다. ^^;;;
김종화(zepinos)님이 2006-06-27 11:32:54에 작성한 댓글입니다.

중복배제는 아래와 같이 만들어 봤습니다.

SELECT   pk1, pc2, pk1 pk2, pc1
    FROM (SELECT person_korname pk1, person_chnname pc1,
                 FIRST_VALUE (person_chnname) OVER
     (PARTITION BY person_korname ORDER BY ROWNUM) pc2
            FROM person_tbl)
   WHERE pc1 <> pc2
GROUP BY pk1, pc1, pc2

SoQooL(김홍선)님이 2006-06-27 11:52:10에 작성한 댓글입니다.

GROUP BY를 사용하여 부하가 많은 DISTINCT를 피할 수 있군요. 굳 아이디어내요.
FIRST_VALUE 쓰임새도 그렇고. 이런 함수가 있다는 것은 알지만 막상 쿼리문에 응용한다는 것이 쉽지 않은데...
쿼리문이 깔끔하네요 ^^

저자: Jonathan Gennick

새로운 SQL/XML 함수는 관계형 데이타와 XML을 표준에 기반하여 연결합니다.

수 십년 동안 기업들은 관계형 데이타베이스에 데이타를 쏟아 부어 왔습니다. 그러나 다른 기업과 데이타를 교환하는 기업들은 관계형 데이타베이스에서 데이타를 꺼내 이를 XML 포맷으로 변경한 후 비즈니스 파트너에게 전송해야 하는 경우가 갈수록 늘어나고 있습니다.

SQL/XML 표준

Oracle9i Database에는 관계형 데이타를 쿼리하고 XML 문서를 반환할 수 있는 여러 표준 기반 함수들이 들어 있습니다. 이 함수들을 통칭하여 SQL/XML이라 하며 때때로 SQLX라고 불리기도 합니다. SQL/XML은 현재 ANSI/ISO SQL 표준의 일부(정확히 말해 Part 14)로 등장하고 있으며 올해 후반 ISO/IEC 표준으로 발표될 예정입니다. SQL/XML 표준은 업계의 폭 넓은 지원을 받고 있으며, SQL/XML 표준 정의 작업과 관련된 주요 데이타베이스 업체들로는 IBM, Microsoft, Oracle, Sybase 등이 있습니다.

SQL/XML 국제 표준 최종안(The Final Draft International Standard for SQL/XML)에서는 다음과 같은 요소들을 정의하고 있습니다.


XML: XML 데이타를 담는 데이타 유형

XMLAgg: GROUP BY 질의에서 XML 데이타를 그룹으로 분류 또는 집계하는 함수

XMLAttributes: SQL 질의에 의해 반환된 XML 요소에 특성을 설정하는 데 사용되는 함수

XMLConcat: 둘 이상의 XML 값을 연결하는 함수

XMLElement: 관계형 값을 XML 요소로 변형시키는 함수(형식: <elementName>value</elementName>)

XMLForest: 관계형 값 목록으로부터 XML 요소의 목록(일명: `포리스트(forest)`)을 생성하는 함수

XMLNamespaces: XML 요소에서 네임스페이스를 선언하는 함수

XMLSerialize: XML 값을 문자열로 직렬화하는 함수

이 목록으로부터 Oracle9i Database는 XML 데이타 유형(XMLType), XMLAgg, XMLConcat, XMLElement 및 XMLForest 등을 구현합니다. 향후 출시되는 버전에서는 추가로 다른 함수를 지원할 계획입니다.

함수와 데이타 유형 외에도 SQL/XML 표준에서는 열 이름을 XML 요소 이름으로 변환하고 SQL 데이타 유형을 XML 데이타 유형으로 변환하는 규칙을 정의합니다. 이 규칙들은 XMLElement 및 기타 SQL/XML 함수들에 의해 자동으로 적용됩니다.


XML 문서 생성

여러분이 관광 안내소에 근무하고 있으며, 관광객들의 관심을 끌만한 관광 명소에 대한 정보를 다음 테이블과 같이 관계형으로 저장하고 있다고 가정해 보십시오.


SQL> describe COUNTY


Name                 Null? Type
--------------       ----- --------------
COUNTY_NAME               VARCHAR2(10)
STATE                     VARCHAR2(2)
SQL> describe ATTRACTION

Name                 Null? Type
--------------       ----- --------------
COUNTY_NAME               VARCHAR2(10)
ATTRACTION_NAME            VARCHAR2(30)
ATTRACTION_URL             VARCHAR2(40)

GOVERNMENT_OWNED           CHAR(1)
LOCATION                  VARCHAR2(20)

여러분은 방금 이 데이타를 다른 지역의 유사한 사무소로 보내 줄 것을 요청 받았습니다. 이 데이타를 보낼 때는 반드시 XML로 보내야 합니다. 우선 XMLElement를 활용하여 각 관광 명소에 대한 XML 요소를 다음과 같이 생성합니다.


SELECT XMLElement("Attraction",
                 attraction_name)
FROM attraction;

XMLELEMENT("ATTRACTION",ATTRACTION_NAME)
------------------------------------------

<Attraction>Pictured Rocks</Attraction>
<Attraction>Da Yoopers ... </Attraction>
<Attraction>Valley Camp ...</Attraction>
...

XMLElement는 확실히 이해해야 할 가장 중요한 SQL/XML 기능 중의 하나인 것은 XML 요소 생성이야말로 SQL/XML이 존재하는 근본적인 이유이기 때문입니다. XMLElement의 첫 번째 인수는 문자열 인수가 아니라 식별자로서 테이블 이름 또는 열 이름이 식별자인 것과 같은 의미입니다. 그래서 위의 XMLElement 질의에서 이중 따옴표를 사용했습니다. 테이블에서 소문자 열 이름을 사용하려면 테이블을 생성할 때 그 열 이름에 이중 따옴표를 사용하십시오. 마찬가지로 요소 이름을 소문자로 하는 경우에도 이중 따옴표로 묶습니다. XMLElement에 대한 두 번째 인수는 현재 생성하고 있는 요소에 값을 제공하는 열 이름입니다.

이번 데이타 전달에는 관광 명소 이름 뿐만 아니라 다른 정보도 전달하려 합니다. 목록 1과 같이 XMLElement에 호출을 중첩하여 각각의 관광 명소에 하위 요소를 생성할 수 있습니다. XMLElement에 대한 외부 호출은 <Attraction> 요소를 생성합니다. XMLElement에 대한 내부 호출은 중첩된 <Name>, <Location>, <URL> 요소를 생성합니다. XMLAttributes를 외부 XMLElement 함수 호출의 둘째 인수로 사용하는 것에 유의하십시오. XMLAttributes의 호출은 XMLElements에 선택적으로 쓸 수 있는 둘째 인수이며 이 경우에는 각 <Attraction> 태그에서 볼 수 있는 GOV 특성이 만들어집니다.

XMLElement 함수가 문자열 값이 아니라 XMLType 값을 반환한다는 점을 이해하는 것이 중요합니다. 목록에 문자가 표시되는 것은 SQL*Plus(Oracle9i Release 2)가 여러분이 선택한 모든 XMLType 값에 대해 XML 텍스트를 암묵적으로 추출하여 표시하기 때문입니다. SQL*Plus를 사용하여 이 문서에서 그 예제를 재현해 보려면, 여기에서 테이블 생성 스크립트를 다운로드하십시오. SQL*Plus에서는 XML 출력이 절단되지 않도록 SET LONG 2000 명령을 내리고, 선택적으로 SET PAGESIZE 80 명령을 내려 XML 출력에 성가신 페이지 구분이 생기지 않도록 합니다.


Null 요소의 처리

관계형 데이타를 다룰 때는 null 값이 나타날 가능성을 반드시 고려해야 합니다. 예를 들어 LOCATION 열이 일부 관광 명소에 대해 null인 경우에는 어떨까요? XMLElement을 사용하는 경우 null 열 값은 목록 2의 첫번째 질의와 결과에 나타난 바와 같이 빈 요소로 나타납니다. XML에 이러한 빈 요소를 두고 싶지 않다면 최소한 두 가지 해결책이 있습니다.

빈 XML 요소 생성을 방지하는 데 사용할 수 있는 방법 중 하나는 SQL CASE 표현식을 사용하여 열의 null 값을 테스트하고 null 또는 XMLElement의 결과를 반환하는 것입니다. 목록 2의 둘째 질의가 이 방법을 구현한 것입니다. 문제의 열이 null인 경우 각 CASE 표현식 결과는 항상 null이며, 내부 XMLElement 함수에 null이 아닌 값을 전달합니다. 외부 XMLElement은 null이 아닌 모든 값을 끌어모으며 빈 XML 요소가 될 모든 null은 무시됩니다.

빈 XML 요소 생성을 방지하는 또 다른 방법은 XMLForest 함수를 사용하는 것입니다. XML에서는 <Attraction> 아래 중첩된 요소들을 "포리스트(forest)"라 부릅니다. XMLForest를 이용하면 단 하나의 함수 호출을 가진 요소의 포리스트(forest)를 생성할 수 있습니다. 이러한 요소를 생성할 때 XMLForest는 null 값을 가진 요소들은 생략합니다. 이러한 결과를 다음 질의의 출력에서 볼 수 있습니다. 여기서 XMLForest는 질의 결과에서 null <location> 요소를 산출하지 않습니다.



SELECT XMLElement("Attraction",
  XMLAttributes(government_owned AS GOV),
     XMLForest(attraction_name AS "Name",
              Location AS "Location",
              attraction_url AS "URL"))
FROM attraction
WHERE attraction_name=`Mackinac Bridge`;

XMLELEMENT("ATTRACTION",XMLATTRIBUTES(GOV
-------------------------------------------------
<Attraction GOV="Y">
  <Name>Mackinac Bridge</Name>
<URL>http://www.mackinacbridge.org/</URL>

</Attraction>

이 질의는 XMLForest에 한 번 호출하므로 목록 2에서와 같이 XMLElement에 세 번 호출하는 경우보다 입력하기 쉽고 입력 오류의 여지도 적습니다. 두 경우 모두, 질의 결과에서 null 요소가 제거됩니다. XMLForest 사용의 단점은 요소 특성을 지정할 수 없다는 점입니다. 요소에 특성을 지정해야 한다면 반드시 XMLElement를 XMLAttributes와 함께 사용해야 합니다.

XML 요소의 집계

앞의 질의는 각 관광 명소마다 별도의 XML 문서를 생성했습니다. 이는 그다지 현실적인 시나리오는 아닙니다. 관광 명소에 대한 데이타를 비즈니스 파트너에게 제공하는 경우에는 어떤 식으로든 데이타를 집계하고자 할 것입니다. 예를 들어 한 나라의 모든 관광 명소를 한 데 모아 한 문서로 전송하고자 할 수도 있습니다. 이 경우에는 XMLAgg 함수를 GROUP BY 질의와 함께 사용하면 그렇게 할 수 있습니다.

XMLAgg는 MIN, MAX, AVG 등과 같은 집계 함수입니다. 이 함수 사용의 핵심은 어떤 같은 값에 따라 데이타를 그룹으로 묶는 것입니다. 목록 3에서 질의는 데이타를 나라 이름으로 묶습니다. 그러면 XMLAgg 함수는 해당 나라의 모든 개별 <Attraction> 요소들을 가져와 한 데 연결하여 단일한 XMLType 값으로 반환하고, 그 값은 <County> 요소를 생성하는 새로운 포함 XMLElement 함수 호출로 입력됩니다. 그 결과는 목록 3< county. per document XML one returns> 에서 확인 하실 수 있습니다. 목록 3에서 XMLAttributes이 여러 특성 값을 생성하는 데 사용되는 것을 보게 됩니다.

XMLAttributes에 대한 가장 바깥 쪽의 호출은 세 개의 특성 즉, 나라 이름에 대한 것 하나, 그리고 해당 문서가 따르는 XML 스키마를 가리키는 것 둘을 생성합니다. 여기서 질의는 GROUP BY이므로 가장 바깥 쪽의 XMLElement 함수 호출과 그와 연관된 XMLAttributes 호출은 요약된 열만을 참조할 수 있습니다. c.county_name 대신 a.county_name을 사용하면 오류가 나타나는데 이는 a.county_name이 GROUP BY 표현식이 아니기 때문입니다.

기타 아래의 내용도 한번 참고 해 보세요...

SQL/XML 표준 학습
http://www.sqlx.org

Oracle SQL/XML에 대한 상세 정보
http://www.oracle.com/otn.oracle.com/tech/xml/
http://www.oracle.com/xmldb/htdocs/sql_xml.html

이 문서의 테이블 생성 코드 다운로드
http://www.oracle.com/otn.oracle.com/oramag/oracle/03-may/o33xml_tablecreate.zip

XML DB 데모
http://www.oracle.com/otn.oracle.com/tech/xml/xmldb

관련 자료 읽기
http://www.oracle.com//kr/magazine/webcolumns/2003/o13xml.html

XML DB에 대하여
http://otn.oracle.co.kr/tech/xml

technet.oracle.co.kr에 있는 내용 입니다.
참고 하세요~

자바의 객체직렬화를 이용하면 직렬화된 객체를 파일, 데이터베이스 또는 원격으로 전송 하는 것이 가능 합니다.

물론 객체직렬화가 되기 위해선 Object는 자바의 Seriablizable 이라는 Interface를 구현해야 합니다. 그래서 이 Object는 Byte Stream 형태로 저장 되어 파일이나 DB에 저장 된 후 나중에 Load되어서 복원이 가능 하게 되는 것입니다.

아래의 예제는 ObjectSerTest 라는 클래스를 직렬화 가능하게 만든 후 오라클의 BLOB에  입력 한 후 읽어내는 예제 입니다.

=====================================================

SQL>conn scott/tiger
SQL> create sequence object_ser_seq increment by 1 start with 1;

주문번호가 생성되었습니다.

SQL> create table object_table (
  2  no number,
  3  obj_name varchar2(2000),
  4  obj_value blob default empty_blob()
  5  );

테이블이 생성되었습니다.

=================================================


/*
* Created on 2005. 2. 11
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package jdbc;

import java.io.*;
import java.sql.*;
import oracle.sql.*;

class ObjectSerTest implements java.io.Serializable{

  static final String driver_class = "oracle.jdbc.driver.OracleDriver";
  static final String connectionURL = "jdbc:oracle:thin:@localhost:1521:wink";
  static final String userID = "scott";
  static final String userPassword = "tiger";
  static final String getSequenceSQL = "SELECT object_ser_seq.nextval FROM dual";
  static final String writeObjSQL    = "BEGIN " +
                                       "  INSERT INTO object_table(no, obj_name, obj_value) " +
                                       "  VALUES (?, ?, empty_blob()) " +
                                       "  RETURN obj_value INTO ?; " +
                                       "END;";
  static final String readObjSQL     = "SELECT obj_value FROM object_table WHERE no = ?";

  /*
   ** +--------------------------------------------------+
   ** | METHOD: writeObj                                 |
   ** +--------------------------------------------------+
  */
  public static long writeObj(Connection conn, Object obj) throws Exception {

    long id = getNextSeqVal(conn);
   
    String className = obj.getClass().getName();
    CallableStatement stmt = conn.prepareCall(writeObjSQL);
   
    stmt.setLong(1, id);
    stmt.setString(2, className);    
    stmt.registerOutParameter(3, java.sql.Types.BLOB);
    stmt.executeUpdate();
   
    BLOB blob = (BLOB) stmt.getBlob(3);
    OutputStream os = blob.getBinaryOutputStream();
    ObjectOutputStream oop = new ObjectOutputStream(os);
    oop.writeObject(obj);
    oop.flush();
    oop.close();
    os.close();
    stmt.close();
    System.out.println("Done serializing: " + className);
    return id;

  } // END: writeObj


  /*
   ** +--------------------------------------------------+
   ** | METHOD: readObj                                  |
   ** +--------------------------------------------------+
  */
  public static Object readObj(Connection conn, long id) throws Exception {

    PreparedStatement stmt = conn.prepareStatement(readObjSQL);
    stmt.setLong(1, id);
    ResultSet rs = stmt.executeQuery();
    rs.next();
    InputStream is = rs.getBlob(1).getBinaryStream();
    ObjectInputStream oip = new ObjectInputStream(is);
    Object obj = oip.readObject();
    String className = obj.getClass().getName();
    oip.close();
    is.close();
    stmt.close();
    System.out.println("Done de-serializing: " + className);
    return obj;

  } // END: readObj

  /*
   ** +--------------------------------------------------+
   ** | METHOD: getNextSeqVal                            |
   ** +--------------------------------------------------+
  */
  private static long getNextSeqVal (Connection conn) throws SQLException {

    Statement stmt = conn.createStatement();
    ResultSet rs   = stmt.executeQuery(getSequenceSQL);
    rs.next();
    long id = rs.getLong(1);
    rs.close();
    stmt.close();
    return id;

  } // END: getNextSeqVal

  /*
   ** +--------------------------------------------------+
   ** | METHOD: main                                     |
   ** +--------------------------------------------------+
  */
  public static void main (String args[]) throws SQLException {

    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    int insertResults;
    int deleteResults;

    try {
    
      System.out.print("\n");
      System.out.print("Loading JDBC Driver  -> " + driver_class + "\n");
      Class.forName (driver_class).newInstance();

      /*
      ** CONNECT TO THE DATABASE
      */
      System.out.print("Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL, userID, userPassword);
      System.out.print("Connected as         -> " + userID + "\n\n");

      /*
      ** TURN OFF AutoCommit
      */
      conn.setAutoCommit(false);

      ObjectSerTest obj = new ObjectSerTest();      

      long no = writeObj(conn, obj);
      conn.commit();

      System.out.print("Serialized OBJECT_ID => " + no + "\n\n");

      System.out.print("OBJECT VALUES  => " + readObj(conn, no) + "\n\n");
      conn.close();

    }  // TRY:

    catch (Exception e) {
      e.printStackTrace();
    }

    finally {
      if (conn != null) {
        try {
          System.out.print("Closing down all connections...\n\n");
          conn.close();
        }
        catch (SQLException e) {
          e.printStackTrace();
        }
      }
    } // FINALLY:

  } // METHOD: main

} // CLASS: ObjectSerTest



[결과]


Loading JDBC Driver  -> oracle.jdbc.driver.OracleDriver
Connecting to        -> jdbc:oracle:thin:@localhost:1521:wink
Connected as         -> scott

Done serializing: jdbc.ObjectSerTest
Serialized OBJECT_ID => 2

Done de-serializing: jdbc.ObjectSerTest
OBJECT VALUES  => jdbc.ObjectSerTest@601bb1

Closing down all connections...

개발을 하다보면 흔히 어떤 테이블에 원하는 데이터가 있는지 세어보는 경우가 가끔 있습니다. 이러한 경우 COUNT(*)를 사용하는데 좀더 빠르게 할 수 있는 방법이 있어 소개 드립니다.

COUNT를 할 때 전체 테이블을 FULL SCAN 할 수도 있고 INDEX 영역을 FULL SCAN할 수도 있습니다. TABLE을 전체 FULL SCAN 할 때와 INDEX영역을 FULL SCAN할 때 어디가 빠르다고는 장담하지 못합니다. 이러한 경우 테이블 이나 인덱스의 단일 블록을 SCAN하기 때문에 예측하기가 애매할 수 있는 것이죠... 만약 인덱스를 SCAN하는데 하나의  블록이 아니라 MULTI BLOCK을 SCAN한다면 어떨까요? 당연히 SINGLE BLOCK을 SCAN하는 것보다 빠를 수 있습니다. 이때 사용하는 것이 INDEX FAST SCAN이라는 것인데 COUNT하는 경우 당연히 속도가 빨라 집니다.힌트 INDEX_FFS를 이용하면 되는데 아래의 예를 참고하세요~

SQL)SELECT COUNT(*) FROM EMP;

Execution Plan
------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=CHOOSE
1        0  SORT  (AGGREGATE)
2        1    TABLE ACCESS (FULL) OF ‘EMP’


SQL)SELECT /*+ INDEX(EMP pk_emp */ COUNT(*) FROM EMP;

Execution Plan
------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=CHOOSE
1        0  SORT  (AGGREGATE)
2        1    INDEX FULL SCAN OF ‘pk_emp’ (UNIAUE) (cost=30)

---------------------------------------------
[아래처럼 인덱스 패스트 스캔을 이용하여 count 하세요]
---------------------------------------------
SQL)SELECT /*+ INDEX_FFS(EMP pk_emp) */ COUNT(*) FROM EMP;

Execution Plan
------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=CHOOSE
1        0  SORT  (AGGREGATE)
2        1    INDEX (FAST FULL SCAN) OF ‘pk_emp’ (UNIQUE) (cost=2)

아래는 어떤 분이 50000건의 데이터로 간단히 인덱스를 이용하는것으로 count를 했을때의 결과라고
올려 놓은신건데 참고하세요...

-----------------------------------------------------------
힌트를 사용한 성능향상 테스트(http://cafe.naver.com/projectprogramming.cafe?frame_url=/ArticleRead.nhn%3Farticleid=118)
-----------------------------------------------------------

50000만 건을 입력하고 전체 카운터를 가져오는 테스트를 해보겠다.

여기서의 소요시간은 서버환경이나 측정하는 방법에 따라 달라 질 수 있다. 하지만 상대적으로 비교해 볼 수는 있는 것이니 어떤 효과가 있는지를 알기에는 충분하다고 생각한다.

1. select count(idx) idx from 테이블명
- 소요시간 : 203ms

2. select /*+ index(테이블명 인덱스명) */ count(idx) idx from 테이블명
- 소요시간 : 15ms

Oracle 9i 부터는 대부분의 인덱스 타입에 대해 다른 사용자의 작업에 영향을 주지 않으며 index를 재구성하는 방법인 online index rebuild를 지원 합니다.

인덱스 rebuild 절차는 다음과 같습니다.

1.        rebuild 하려는 index의 대상 table에 발생하는 변경 대상을 index가 생성되는 tablespace에 “temporary index organized journal table”을 만들어 기록
2.        다른 사용자의 select는 rebuild 하기 전의 인덱스를 사용
3.        다른 사용자가 rebuil 되기 전의 인덱스에 변경을 가하면 journal table에 기록
4.        rebuild 작업이 완료되면 journal table에 있던 변경 내용과 merge
5.        merge 작업은 20 rows 마다 commit 됨

인덱스 rebuild 방법에 대해 예를 통해 보도록 하죠…

SQL> conn scott/tiger
연결되었습니다.

SQL> create table test_emp (
  2  emp_id varchar2(10) not null,
  3  emp_name varchar2(10) not null,
  4  salary number
  5  );

테이블이 생성되었습니다.

SQL> );
SP2-0042: 알 수 없는 명령어 ")" - 나머지 줄 무시.
SQL> insert into test_emp values ('A0000001','1길동',5000);

1 개의 행이 만들어졌습니다.

SQL> insert into test_emp values ('A0000003','3길동',3000);

1 개의 행이 만들어졌습니다.

SQL> insert into test_emp values ('A0000002','2길동',2000);

1 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select * from test_emp;

EMP_ID     EMP_NAME       SALARY
---------- ---------- ----------
A0000001   1길동            5000
A0000003   3길동            3000
A0000002   2길동            2000

SQL> create index idx_emp_id_reverse on emp(emp_id) reverse;
create index idx_emp_id_reverse on emp(emp_id) reverse
                                       *
1행에 오류:
ORA-00904: 열명이 부적합합니다


SQL> create index idx_emp_id_reverse on test_emp(emp_id) reverse;

인덱스가 생성되었습니다.

SQL> alter index idx_emp_id_reverse rebuild online;

인덱스가 변경되었습니다.

SQL> alter index idx_emp_id_reverse rebuild compute statistics online;

인덱스가 변경되었습니다.

SQL> -- oracle8i에서는 general index라고 할지라도 compute statistics와 online을
같이 사용하면 오류가 발생

SQL> create index idx_ename_sal_fnc
  2  on test_emp (emp_name desc, (salary / 10)*100);
on test_emp (emp_name desc, (salary / 10)*100)
                                          *
2행에 오류:
ORA-01031: 권한이 불충분합니다

--------------------------------------------------------------
위의 함수 기반 인덱스를 만들기 위해서는 QUERY REWRITE 권한이 있어야 합니다.

함수 기반 인덱스란,,,,함수(function)이나 수식(expression)으로 계산된 결과에 대해 인덱스를 생성하여 사용할 수 있는 기능을 제공 합니다. 질의 수행 시 해당 함수, 수식을 처리하여 결과를 가져 오는 것이 아니라 인덱스 형태로 존재하는 미리 계산되어 있는 결과를 가지고 처리하므로 성능 향상을 기할 수 있습니다.
--------------------------------------------------------------

SQL> conn / as sysdba
연결되었습니다.

SQL> GRANT QUERY REWRITE TO SCOTT;

권한이 부여되었습니다.

SQL> conn scott/tiger
연결되었습니다.

SQL> create index idx_ename_sal_fnc
  2  on test_emp (emp_name desc, (salary / 10));

인덱스가 생성되었습니다.



함수 기반 인덱스에 대해 온라인 리빌드가 가능 합니다.

SQL> alter index idx_ename_sal_fnc rebuild online;

인덱스가 변경되었습니다.
-- Key Compressed Index 생성, 온라인 rebuild test

SQL> create index idx_salary_compress on test_emp(salary) compress 1;

인덱스가 생성되었습니다.

SQL> alter index idx_salary_compress rebuild online;

인덱스가 변경되었습니다.


이번에는 비트맵 인덱스를 만들고 온라인 rebuil 해 봅니다. 이 경우엔 온라인 rebuild 되지 않습니다.

SQL> create bitmap index idx_emp_name on test_emp(emp_name);

인덱스가 생성되었습니다.

SQL> alter index idx_emp_name rebuild online;
alter index idx_emp_name rebuild online
*
1행에 오류:
ORA-08108: 인덱스 온라인으로 된 유형을 구축하거나 재구축하지 말아야 합니다

1. Role이란?
- 사용자에게 허가할수 있는 권한들의 집합
- 이 롤을 이용하여 권한 부여와 회수를 쉽게할수 있다.
- 한 사용자가 여러롤을 엑세스할수 있고 다른 여러사용자에게
   같은 롤을 지정할수 있다.

2. 롤의 작성과 지정
- DBA가 role을 생성해야하며, 다음에 권한을 롤에 지정하고 사용자에게
   롤을 부여한다.

   SQL> create role myrole;

   Role created.

   SQL> grant create table, select any table to myrole;

   Grant succeeded.

- 위의 예에서 myrole이라는 롤을 생성하고 해당 롤에는 create table권한과
   select any table이란 권한을 할당했다.

   SQL> grant myrole to office;

   Grant succeeded.

- 그런다음 2개의 권한이 있는 Role을 office라는 사용자에게 부여했다.
   즉 office라는 사용자는 create table 권한과 select ant table 2개의
   권한을 부여받은 것이다.

오라클은 정보를 메모리와 디스크등에 저장 합니다. 가능하면 메모리에 원하는 정보가 있는 것이 훨씬 빠르고 효율적 입니다. 오라클의 메모리 구조는 크게 아래와 같이 3가지 구조로 볼 수 있습니다.

SGA(System Global Area) – 오라클 프로세스들이 접근하는 하나의 큰 공유 메모리 세그먼트(Share Pool, DataBase Buffer Cache, Redo Log Buffer, Java Pool, Large Pool)

PGA(Process Global Area) – 한 프로세스 혹은 스레드의 개별적인 메모리 공간으로 다른 프로세스와 스레드는 접근 불가

UGA(User Global Area) – 사용자 세션과 관련된 메모리 공간, MTS 혹은 전용서버모드로 동작 하느냐에 따라 SGA 또는 PGA에 존재 합니다.

----------------------------------------------------

SGA(System Global Area)
SGA는 오라클이 시스템의 일정 자원을 정적으로 할당 받아 관리하는 오라클 시스템 영역으로 데이터베이스 인스턴스가 활성화 되면(Oracle이 Startup 될때) 시스템의 일정 부분을 할당 받고 인스턴스 종료 시(Shutdown 시) 반환하는 구조를 가집니다.

사용자가 테이블의 데이터를 엑세스 할 때 이러한 데이터는 메모리에 위치하며 즉 SGA안에 위치하게 되면 다른 사용자가 같은 데이터를 요구할  때는 오라클이 디스크에서 해당 내용을 읽어 오는 것이 아니라 이 메모리 영역에서 데이터를 가져다가 보여주게 됩니다.

이럴 경우 디스크에서 읽어 오는 것 보다 훨씬 빠르고 효율적 입니다. 이렇게 빠른 엑세스를 위해 SGA는 서버의 Main Memory에 위치하게 되며 만약 SGA 영역이 스와핑 된다면 SGA내의 데이터를 빠르게 접근 할 수 없게 됩니다.

모든 사용자는 데이터베이스 인스턴스에 접근하여 인스턴스의 SGA내의 정보를 읽을 수 있으며 오라클의 여러 프로세스는 SGA 영역을 읽고 쓸 수 있습니다. SGA의 데이터 구조와 그에 대한 간단한 설명 입니다.

데이터베이스 버퍼 캐시(Database Buffer Cache) – 실행 한 SQL문장의 대상이 되는 데이터 즉 디스크로부터 읽혀진 데이터의 복사본이 저장되는 메모리 영역, SQL이 실행되기 전의 Before 이미지와 변경된 후의 After Image가 저장 됩니다.

리두 로그 버퍼(Redo Log Buffer) – DataBase의 데이터 블록에 행하여 진 모든 변경 사항에 관한 정보를 기록하며 장애 발생시 복구에 이용됩니다.

공유 풀(Shared Pool) – DB에 접근하는 사용자들이 공유, 처리되는 SQL문장들이 저장되는 Library Cache와 SQL 문장의 실행을 위해 필요한 딕셔너리 정보를 간직하고 있는 Data
Dictionary Cache로 구성 됩니다. Library Cache는 가장 최근에 실행된 SQL 문과 그것에 관한 정보를 보관 합니다. Data Dictionary Cache는 가장 최근에 사용된 데이터베이스 정의(able, View이름, Table의 Column명, 사용자 정보)가 저장되어 있으며 이들 정의는 Data Dictionary로부터 읽혀 집니다.

자바 풀(Java Pool) – Java 프로그램을 사용하여 데이터베이스에 접근하여 일련의 작업을 하는 경우 사용되는 영영으로 데이터베이스 내에서 실행되는 Java Code를 저장 합니다.

대용량 풀(Large Pool, Optional) – 백업이나 복구 같은 작업시에 처리되는 대용량 데이터를 저장하는데 사용되는 대용량 메모리 영역으로 SQL문의 처리와는 직접적인 관련이 없습니다.

SGA 영역에는 데이터베이스의 상태와 인스턴스에 대한 일반적인 정보가 들어 있습니다. 이것은 백그라운드 프로세스가 필요한 정보로 Fixed SGA라고 하며, 사용자 정보는 이 영역에 저장되지 않습니다. SGA는 프로세스끼리의 정보(예를들면 Locking)를 주고 받는 중간 역할을 합니다. 만약 시스템이 공유 서버 (Shared Server) Architecture라면 사용자의 요구와 반응에 대한 큐와 PGA에 대한 약간의 정보를 SGA에 포함하고 있습니다.


참고로  SGA의 상태를 보기 위해서는 SQL*Plus등으로 로그인 한 후… 아래 처럼 하면 된다.

SQL> show sga
Total System Global Area   72123504 bytes
Fixed Size                   279664 bytes
Variable Size              67108864 bytes
Database Buffers            4194304 bytes
Redo Buffers                 540672 bytes


오라클 9i에서는, SGA가 이전 버전에서 처럼 정적으로 구성될 수도 있고,  동적으로 바뀔 수 있게 구성될 수 도 있습니다.

아래 문장은 1000 byte 이상을 차지하는 문장을 찾아 내는 SQL 입니다. 참고하세요~

SQL> select deptno, sum(sal)
  2  from emp
  3  group by deptno
  4  having sum(sal) > 1000;

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


SQL> conn / as sysdba
연결되었습니다.

SQL> select substr(sql_text,1,60) "Statement",
  2         count(*),
  3         sum(sharable_mem) " Memory",
  4         sum(users_opening) "Open",
  5         sum(executions)    "Exec"
  6  from v$sql
  7  where sql_text  like '%emp%'
  8  group by substr(sql_text,1,60)
  9  having sum(sharable_mem) > 1000
10  /

Statement
--------------------------------------------------------------------------------

COUNT(*)     Memory       Open       Exec
---------- ---------- ---------- ----------
select deptno, sum(sal) from emp group by deptno having sum(
         1       8636          0          1