레이필's 행복한 세상

아.. 9i에서부터 지원해준다는 이쁜넘

FIELD1을 FIELD2로 변경처리해줌.

ALTER TABLE [테이블명]
RENAME COLUMN [FIELD1] TO [FIELD2];

이걸 몰라서 삽질이라니.. ^^;

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

select text from user_source

where name = upper('&spl_name')

  and type in( 'PROCEDURE', 'FUNCTION')

 
프로시져와 펑션문 보기.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

날짜와 날짜사이 시분초 구하기.
2008.02.01 oracle 7.3.4.5.0

＃ 날짜 - 날짜는 number 형으로 변환됨.

  1  select trunc(a) "일",
  2         trunc(a * 24) "시" ,
  3         trunc(((a*24)  - trunc(a *24))*60)  "분",
  4         ((((a*24*60)  - trunc(a *24*60))*60)  - (trunc(((a*24*60)  - trunc(a *24*60)))*60))  "초"
  5   from (select (to_date('20080131124759','yyyymmddhh24miss') -
  6                 to_date('20080131100709','yyyymmddhh24miss')) a
  7          from dual
  8*       )
SQL> /

        일         시         분         초
---------- ---------- ---------- ----------
         0          2         40         50

다른 방법은??

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

자료출처
http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=57&page=1&position=1

문제)

다중의 결과값을 하나의 행으로 컴마로 분리해 출력하는 방법을 알고싶습니다.

return
=========
홍길동
김길동     --> 홍길동, 김길동, 이길동
이길동
==========

답은...... 링크걸린곳으로 ^^:
원저작자 홈피정도는 가봐야.. ㅋ

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

분명 인덱스 컬럼순으로 조건절을 태웠는데...
인덱스를 타지 않는다..
인덱스 컬럼순이다..
GJNO1
ACCTKDCD
ACCTDATE
BUSDATE
FINICD
PARTCD
ACCTCD
CHITNO

INDEX 시스템에 맞기기-----------------------------

select acctcd,count(distinct gjno1),to_char(sum(exchamt),'999,999,999,999,990')
  from (
       select acctcd,gjno1,exchamt
         from test
        where gjno1 in(select code2 from test where code1 = 'GJNO1')
          and acctkdcd = '7102'
          and acctdate = to_date('&&yymmdd','yyyymmdd')
          and busdate = to_date('&yymmdd','yyyymmdd')
          and finicd in('1','2')
          and partcd = '3101'
          and acctcd in('551010301','551010303')
          and jukyo = 'TEST'
        )
group by acctcd

PLAN------------------------------------

SELECT STATEMENT     Cost =
  SORT GROUP BY
    MERGE JOIN
      SORT JOIN
        VIEW
          SORT UNIQUE
            REMOTE
      SORT JOIN
        REMOTE
------------------------------------------
Elapsed: 00:14:46.42

INDEX 강제로 태우기-----------------------

select /*+ INDEX_FFS(IMSI_N320) +*/ acctcd,count(distinct gjno1),to_char(sum(exchamt),'999,999,999,999,990')
  from (
       select acctcd,gjno1,exchamt
         from test
        where gjno1 in(select code2 from test where code1 = 'GJNO1')
          and acctkdcd = '7102'
          and acctdate = to_date('&&yymmdd','yyyymmdd')
          and busdate = to_date('&yymmdd','yyyymmdd')
          and finicd in('1','2')
          and partcd = '3101'
          and acctcd in('551010301','551010303')
          and jukyo = 'TEST'
        )
group by acctcd

PLAN------------------------------------
SELECT STATEMENT     Cost = 198
  SORT GROUP BY
    NESTED LOOPS
      VIEW
        SORT UNIQUE
          REMOTE
      REMOTE
------------------------------------------
Elapsed: 00:00:04.07

차이가 나도 너무 난다... 근데 의문인점이... 왜 인덱스를 타지 않을까??
강제로 인덱스를 태워야한다면..
처음부터 명시하라고 하든가.. 왜 옵션처럼 해놓구는..

너무어렵당.. 공부가 필요해 ??? 보내줘~~~ ㅋ

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

EXPORT 란,

오라클의 논리적 백업을 말하며 바이너리로 저장된다.
디비의 전체,특정사용자,특정테이블,권한,인덱스,제약조건등을 백업받을 수 있다.

EXPORT Parameter

userid : 백업실행할 사용자명/패스워드( userid=SCOTT/TAGER )
buffer : 데이터 행의 버퍼 크기
file : 백업시 저장되는 파일명 ( file=exp.dmp )
compress : 백업 데이터를 하나의 extent로 압축하여 import 시 사용 여부 ( compress=y/n )
grants : 권한설정 백업 여부 ( grants=y/n )
indexes : 인덱스설정 백업 여부 ( indexes=y/n )
rows : 데이터 행 백업 여부. 'N' 일땐 테이블 스키마만 백업됨. ( rows=y/n )
constraints : 제약조건 백업 여부 ( constraints=y/n )
log : 백업시 로그파일생성 ( log=exp.log )
ful : 전체 백업 여부 ( full=y )
owner : 특정 소유자의 데이터베이스 백업 ( owner=test ) || ( owner=test1,test2,test3... )
tables : 특정 테이블 리스트 ( tables=table1 ) || ( tables=table1,table2,table3,table4... )

EXPORT USING

>exp userid=SCOTT/TAGER
; 소유자의 모든 테이블을 자동 백업한다.
>exp userid=SCOTT/TAGER file=abc.dmp tables=table1,table2,table3 log=log.dmp
; tables 에 명시되어 있는 테이블만 백업한다.
; tables=(table1,table2,table3..) 이렇게 괄호를 넣으면 에러가 난다.

자 사용하러 가보자 ~~

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

☞How to Use Hints

●  응용설계자는 특정 데이터에 대하여 optimizer가 알 수 없는 정보를 가질 수 있음.
●  응용설계자는 optimizer보다 더 좋은 execution plan을 선택할 수 있음.
●  응용설계자는 hint를 사용하여 강제적으로 사용자가 선택한 execution plan을 생성하도록 optimizer에게 지시 가능.

♣ Hints의 사용

●  Hints의 사용범위
▶  SQL문을 위한 Optimization approach
▶  SQL문에 대한 Cost-based approach의 goal(best throughput, best response time)
▶  SQL문에 의해 접근되는 테이블에 대한 access path
▶  join문에 대한 join순서
▶  join문을 처리하기 위한 join operation

●  Statement Block
▶  간단한 SELECT, UPDATE, DELETE SQL문
▶  한 parent SQL문나 complex SQL문 내의 subquery
▶  한 compound query중의 한 부분
· UNION operator에 의해 조합된 두 개의 component query로 구성한 한 compound query는 두 개의 SQL문 block을 가짐. (각 component query를 위해 하나의 SQL문 block이 생김.)
· 첫 번째 component query에 있는 hint는 첫 번째 component의 optimizer에만 적용
· 두 번째 component query에 대한 optimizer에는 적용되지 않는다.

● hint를 포함하는 Syntax

·DELETE, SELECT, UPDATE : SQL문 block을 시작하는 DELETE, SELECT, UPDATE 키워드이다. Hint를 포함하는 comments는 이 키워드들이 나타난 후에 써야한다.
·+ : 오라클이 그 comment를 해석할 수 있게 해주는 기능을 한다. 이 ' + '는 comment 표시 후에 빈칸(blank)이 없이 즉시 따라와야만 한다.
·hint : 이절에서 언급되는 hint중의 하나이다. 만약 comment가 여러 hint를 가진다면 그 hint들은 최소한 하나의 공간으로서 분리되어 있어야만 한다.
·text : hint에 대한 설명

●  hint를 무시하는 경우
▶  hint가 쓰인 comment가 DELETE, SELECT, UPDATE 키워드 뒤에 오지 않고, 다른 곳에 쓰여 있는 경우
▶  hint가 Syntax error를 가진 경우
     (동일한 comment내에 error를 가지지 않고 올바로 표현된 다른 hint들은 인정)
▶  충돌한 hint의 조합들은 무시(한 comment내에 두 개 이상의 hint가 있을 경우, Optimizer가 어떤 hint를 먼저 써야하는지 모르는 경우)
      (동일한 comment내에 충돌한 hint외에 다른 hint는 인정.)
· 오라클은 SQL*Forms Version 3 Trigger와 같은 PL/SQL Version1을 사용하는 환경에서는 모든 SQL 문에 있는 hint들을 무시.
· Optimizer는 cost-based approach를 사용하는 경우에 hint를 인식.
· 한 SQL문 block에 RULE hint를 제외한 어떤 hint들이 포함되어 있으면, optimizer 는 자동적으로 cost-based approach를 사용.

☞ Hint for Optimization Approaches and Goals

▶  hint는 cost-based approach와 rule-based optimization approach 중에 하나를 선택
▶  cost-based approach를 선택한 경우에는 best throughput와 best reponse time사이에 하나를 선택
▶  SQL문이 optimization approach 와 goal을 기술한 한개의 hint를 가진다면, optimizer는 statistics의 존재여부와 ALTER SESSSION명령에 있는 OPTIMIZER_GOAL와 OPIMIZER_MODE 초기 파라메타의 값과는 상관없이 기술된 approach를 사용.

♣ ALL_ROWS

▶ ALL_ROWS hint는 best throughput(minimum total resource consumption)을 목적으로 SQL문 block을 최적화하기 위해 cost-based approach를 선택
▶  optimizer는 best throughput을 목적으로 SQL문를 최적화하려면
·SELECT /*+ ALL_ROWS */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

♣ FIRST_ROWS

▶ FIRST_ROWS hint는 best response time을 목적으로 SQL문 block을 최적화하기 위해 cost-based approach를 선택.(minimum resource usage to return first row)
▶  이 hint는 아래 내용을 이행할 수 있는 optimizer를 생성
· Index scan을 쓸수 있다면, optimizer는 full table scan보다는 Index scan을 사용.
· Index scan을 쓸수 있다면, optimizer는 연관된 table이 nested loop의 inner table일 때마다, sort-merge join보다는 nested loops join을 선택.
· Index scan이 ORDER BY절에 의해 쓰여지면, optimizer는 sort operation을 피하기 위해 index scan을 선택.
▶  optimizer는 best response time을 목적으로 아래 SQL문를 최적화하려면.
·SELECT /*+ FIRST_ROWS */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;
▶  Optimizer는 아래 Syntax를 가지는 DELETE와 UPDATE SQL문 blocks과 SELECT SQL문 blocls에 있는 hint는 무시.
· set operators(UNION, INTERSECT, MINUS, UNION ALL)
· GROUP BY절
·FOR UPDATE 절
·group functions
·DISTINCT operator
▶  이들 SQL문는 best response를 목적으로 최적화될 수 없다.
▶  위의 경우 첫 번째 row를 반환하기 전에 SQL문에 접근되는 모든 행들을 retrieve해야만 함.
▶  이런 SQL문에 대해 hint를 쓰면 optimizer는 cost-based approach를 사용하고, best throughput을 목적으로 최적화.
▶  SQL문에 ALL_ROWS나 FIRST_ROWS hint를 기술하고, data dictionary가 그 SQL문에 의해 접근되는 table에 대해 어떤 statistics도 포함하지 않는다면, optimizer는 내부적으로 execution plan을 선택하고, missing statistics를 추정할 default statistics 값을 사용.
▶  이 추정치(estimates)는 ANALYZE명령에 의해 생성되어진 것만큼 정확하지는 않음.
▶  ANALYZE명령을 사용하여 Cost-based optimization을 사용하는 SQL문에 의해 접근되는 모든 table에 대한 statistics를 생성해야 함.
▶  Access path를 위한 hint 또는 ALL_ROWS나 FIRST_ROWS hint를 가지는 join operation을 기술한다면, optimizer는 hint에 의해 기술된 join operation들과 access paths를 우선적으로 취함.

♣ CHOOSE

▶ CHOOSE hint는 statistics가 존재하고, SQL문에 의해 접근된 테이블에 대해 rule-based approach나 cost-based approach중 어떤 것을 쓸 것인지 optimizer가 선택.
▶  data dictionary가 이 table들 중 최소한 하나에 대한 statistics를 가진다면 optimizer는 cost-based approach를 사용.
▶  data dictionary가 이 table들 중 어떤 것에 대해서도 statistics를 가지지 않는다면 optimizer는 rule-based approach를 사용.
▶  아래 SQL문에서 만약 EMP 테이블에 대해 statistics가 있다면 optimizer는 cost-based approach를 사용.
▶  data dictionary에 EMP 테이블에 대해 어떤 statistics도 존재하지 않는다면 optimizer는 rule-based approach를 사용.
·SELECT /*+ CHOOSE */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

♣ RULE

▶ RULE hint는 한 SQL문 block에 대해 rule-based optimization을 선택.
▶  이 hint는 optimizer가 SQL문 block에 대해 기술한 다른 hint들을 무시.
▶  Optimizer는 아래 SQL문를 위해서 rule-based approach를 사용.
·SELECT --+ RULE
               empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;
▶  rule-based approach를 쓰는 RULE hint는 오라클의 다음 버전에서는 사용하지 않는다.

☞ Hint for Access Methods

▶  각 hint들은 table에 대한 access method를 제안
▶  hint중의 하나를 기술하는 것은 access path가 인덱스나 클러스터와 SQL문의 의미구조의 존재를 기본적으로 이용할수 있다면 기술된 access path를 선택
▶  힌트가 access path를 이용할수 없다면 optimizer는 그것을 무시.
▶  SQL문에서 정확하게 access되는 테이블을 기술
▶  SQL문이 table에 대한 alias를 사용하면, hint에서 table의 이름보다는 alias를 사용.
▶  테이블의 이름이나 alias 는 local database에 있는 한 테이블에 대한 하나의 synonym이나 하나의 table을 의미함.

♣ FULL

▶ FULL hint는 테이블에 대해 full table scan을 선택
▶  FULL hint의 문법은 FULL(table)
▶ (table)에는 full table scan을 수행하는 table의 alias나 name을 기술한다.
▶  예 : ACCOUNT 테이블에 WHERE절의 조건에 의해 사용가능한 ACCNO 칼럼에 대한 인덱스가 있음에도 불구하고, 오라클은 이 SQL 문을 실행할 ACCOUNTS 테이블에 full table scan을 수행.
·SELECT /*+ FULL(a) Don't use the index on ACCNO */ accno, bal
              FROM accounts a
              WHERE accno = 7086854;
■ NOTE
·  ACCONTS테이블이 alias A를 가지기 때문에 hint는 테이블의 이름이 아닌 alias로 테이블을 표현.
·  FROM 절에 테이블의 이름이 기술되었음에도 불구하고, hint에서 는 schema names을 기술하지 않는다.

♣ ROWID

▶ ROWID hint는 테이블에 대해 ROWID에 의한 table scan을 선택
▶  ROWID hint의 문법은 ROWID(table)
▶ (table)에는 ROWID에 의한 table scan이 이행되어지는 table의 alias나 이름을 기술.

♣ CLUSTER

▶ CLUSTER hint는 테이블에 대해 cluster scan을 선택
▶  CLUSTER hint의 문법은 CLUSTER(table)
▶ (table)에는 cluster scan에 의해 접근되는 테이블의 이름이나 alias를 기술.
·SELECT --+ CLUSTER emp, ename, deptno
              FROM emp, dept
              WHERE deptno = 10 AND emp.deptno = dept.deptno;

♣ HASH

▶ HASH hint는 테이블에 대해 HASH scan을 선택
▶  HASH hint의 문법은 HASH(table)
▶ (table)에는 hash scan에 의해 접근되는 테이블의 이름이나 alias를 기술

♣ INDEX

▶ INDEX hint는 테이블에 대해 index scan을 선택
▶  INDEX hint의 문법은  INDEX(table index)

table : scan될 index와 관련있는 테이블의 이름이나 alias를 기술
index : index scan이 수행될 index를 기술

▶  hint는 하나이상의 indexes들을 기술
· hint가 하나의 사용가능한 index를 기술한다면, optimizer는 index에서 한개의 scan을 수행.
· optimizer는 full table scan이나 테이블에 있는 다른 index에 대한 scan은 수행하지 않음.
· hint가 사용가능한 index의 리스트를 기술한다면, optimizer는 리스트에 있는 각각의 인덱스에 대한 scan을 하는데 드는 비용을 고려한 후에 가장 적은 비용이 드는 index scan을 이행
· 이 access path가 최저의 비용을 가진다면 optimizer는 이 리스트로부터 여러 인덱스를 scan하고 그 결과들을 merge.
· optimizer는 full table scan이나 hint에 있지 않은 index scan은 고려하지 않음.
· hint가 어떤 index도 기술하지 않았다면, optimizer는 테이블에 있는 사용가능한 index를 각각 scan한 비용을 고려한 후에 lowest cost를 가진 index scan을 수행.
· 이 access path가 최저비용을 가진다면 optimizer는 muliple index를 scan하고 그 결과값을 merge.
▶ SELECT name, height, weight
              FROM patients
              WHERE sex='M'
·  sex의 열은 index되어 있고, 이 칼럼은 'M'과 'F'의 값을 가짐.
·  병원에 남자의 수와 여자의 수가 동일하다면, 이 질의는 연관된 테이블의 행의 최다 퍼센트를 반환하고, full table scan이 index scan보다는 더 빠르게 된다.
·  병원의 환자 중 남자의 비율이 매우 적다면, 질의는 관련된 테이블의 행에 대해 적은 비율을 반환하고, 이 경우에는 index scan이 full table scan보다 더 빠르다.
▶  각 disinct column value의 발생수는 optimizer에게 별로 유용하지 않다(도움이 되지 않는다.)
▶ cost-based approach는 각각의 값들이 각각의 행에서 나타나는 빈도수가 동일하다고 가정을 한다.
▶  한 칼럼이 단 2개의 다른 값들(distinct values)을 가진다면 optimizer는 그 두 값들이 각각 row의 50%로정도 나타난다고 가정한다 그래서 cost-based approcah는 index scan보다는 full table scan을 선택하곤 한다.
▶  WHERE절에 있는 값이 모든 row에 대해 매우 적은 퍼센트를 가진다면, hint에 index scan을 사용하여 optimizer가 강제로 index scan을 사용하게 할수 있다.
▶  아래 문장에서 INDEX hint는 SEX_INDEX에 대해 index scan을 선택한다.
· SELECT /*+ INDEX(patients sex_index) Use SEX_INDEX, since there are few male patients */
              name, height, weight
              FROM patients
              WHERE sex = 'M';

♣ FULL hint와 INDEX hint의 비교 예제

▶  1. EMP테이블의 총건수는 10,000건, DEPT테이블의 총건수는 2400건.
▶  2. EMP테이블에서 empno, ename, sal을 select
▶  3. WHERE절의 조건은 JOB 필드의 'SALESMAN'값을 검사
▶  4. JOB_INDEX 존재.
▶  5. JOB필드에는 2개의 값이 존재(MANAGER-(9,751건/10,000건), SALESMAN-(249건/10,000건))
·  EMP, DEPT 테이블을 ANALYZE함.
·  sql_trace를 true로
·  OPTIMIZER_GOAL 은 ALL_ROWS로

▶  예제 1
analyze table emp estimate statistics;
analyze table dept estimate statistics;

alter session set sql_trace = true;
alter session set optimizer_goal = all_rows;

select empno, ename, sal
from emp
where job = 'SALESMAN';

select /*+ full(emp) */ empno, ename, sal
from emp
where job = 'SALESMAN';

select /*+ index(emp job_index) */ empno, ename, sal
from emp
where job = 'SALESMAN';

·  OPTIMIZER_GOAL 은 FIRST_ROWS로

▶  예제 2
analyze table emp estimate statistics;
analyze table dept estimate statistics;

alter session set sql_trace = true;
alter session set optimizer_goal = first_rows;

select empno, ename, sal
from emp
where job = 'SALESMAN';

select /*+ full(emp) */ empno, ename, sal
from emp
where job = 'SALESMAN';

select /*+ index(emp job_index) */ empno, ename, sal
from emp
where job = 'SALESMAN';

♣ INDEX_ASC

▶ INDEX_ASC hint는 테이블에 대한 index scan을 선택
▶  이 SQL문이 index range scan을 사용한다면, Oracle은 index된 값들을 오름차순으로 정렬한 index entry들을 scan.
▶  INDEX_ASC hint의 문법은 INDEX_ASC(table index)

▶  각 파라메타는 INDEX hint에서와 같은 목적을 의미.
▶  range scan에 대해서 오라클의 default behavior는 index된 값에 대해 오름차순으로 정렬하고 그 index entry들을 scan하는 것이므로 이 hint는 일반적으로 index hint보다 더 나은 점은 없다.

♣ INDEX_DESC

▶ INDEX_DESC hint는 테이블에 대해 index scan을 선택
▶  만약 SQL 문이 index range scan을 사용한다면, 오라클은 index된 값들을 내림차순으로 정렬한 index entry들을 scan.
▶  INDEX_desc hint의 문법은 INDEX_DESC(table index)

▶  각 파라메타는 INDEX hint에서와 같은 목적을 의미.
▶  이 hint는 테이블보다 INDEX를 더 많이 접근하므로 SQL문에 영향을 주지 않음.
▶  아래의 SQL문은 index된 값에 대해서 항상 오름차순으로 정렬된 index range scan을 수행
·CREATE TABLE tank_readings (
              time DATE CONSTAINT un_time UNIQUE,
               temperature NUMBER);
▶  테이블의 각 행들은 한 시점에서 시간과 온도를 저장.
▶  TIME칼럼에 대해 UNIQUE 제약을 주면 테이블이 동일한 시점에서 한번만 내용을 읽도록 한다.
▶  오라클은 TIME 칼럼에 강제로 인덱스를 수행.
▶  특별한 T시간에 대해서 읽은 가장 최근의 온도를 SELECT하는 complex query를 생각해보자.
▶  Subquery는 온도를 읽는 시점 T나 T이전의 가장 최근시간의 값을 반환.
▶  Parent query는 그 시간에 대한 온도를 찾는다.
·SELECT temperature
              FROM tank_readings
              WHERE time = (SELECT MAX(time)

              FROM tank_readings
              WEHRE time <= TO_DATE(:t) );

▶  위 SQL문에 대한 execution plan은 아래그림과 같다.

▶  위 SQL문장을 실행시키면, 오라클은 아래와 같은 operations를 실행.
· step 4와 3은 subquery를 실행.

- step 4는 시간 T와 같거나 더 적은 모든 TIME 값들을 반환할 UN_TIME 인덱스의 range scan을 수행.
- step 3는 step 4로부터 최대 TIME값을 선택하고 그 값을 반환.

·step 2 와 1은 parent query를 실행.

- step 2는 step 3에 의해 반환된 TIME 값에 맞는 UN_TIME index의 unique scan을 수행하고 관련된 ROWID를 반환.
- step 1은 step 2에 의해 반환된 ROWID를 사용하여 TANK_READING 테이블을 접근하고 TEMPERATURE 값을 반환.

▶ Step 4에서 오라클은 오름차순으로 정렬된 인덱스에 있는 TIME 값을 scan.
▶  오라클은 첫 번째 TIME값이 T보다 더 큰 경우에 scaning을 중지하고 그후에 step 3에서 T값과 같거나 더 적은 모든 값을 반환.
▶  INDEX_DESC hint를 사용하면 index로부터 단 하나의 TIME값을 읽어오는 질의 사용 가능.
·SELECT /*+ INDEX_DESC(tank_readings un_time) */ temperature
              FROM tank_readings
              WHERE time <= TO_DATE(:t)
              AND ROWNUM = 1;
              ORDER BY time DESC;
▶  이것의 execution plan을 아래 그림과 같다.

· step3 은 T와 동일하거나 더 적은 TIME값을 찾기위해 UN_TIME 인덱스를 range scan하고 그와 관련된 ROWID를 반환.
·step 2는 step 3에 의해 반환된 ROWID값들로서 TANK_READING 테이블에 접근.
· step 1은 step 2로부터 단 하나의 행을 요구함으로 ROWNUM=1이라는 조건을 수행.
▶  INDEX_DESC hint 때문에 step 3은 T값부터 시작하는 내림차순으로 정렬된 index에서 TIME값을 scan.
▶  scan된 첫 번째 TIME값은 T이거나 T값보다는 적은 최대 TIME값이다.
▶  step 1은 단 하나의 행을 요구한 후부터는 , step 3은 첫 번째 TIME값 이후의 index entry에 대해 더 이상 scan하지 않는다.
▶  default 행동이 오름차순 index scan이므로 INDEX_DESC hint없이 이 질의를 수행하면 오라클은 테이블에서 T와 같거나 그보자 적은 최대 시간을 처음 scaning하는 것보다 최초의 시간을 scaning함으로서 시작하게 됨. step1은 최초의 시간에대한 온도를 반환하게 된다.
▶  위 질의에서 좀 더 빨리 이 복합 질의에서 요구하는 온도를 반환하려면 INDEX_DESC hint를 사용해야만 한다.

♣ AND_EQUAL

▶ AND_EQUAL hint는 몇몇의 single-column index에대한 scan을 merge하는 access path를 사용하는 execution plan을 선택
▶  이 AND_EQUAL hint의 문법은

table : merge할 인덱스와 연관된 테이블의 이름이나 alias를 기술.
index : index scan을 수행하는 index를 기술

▶ 최소 2개 이상 최대 5개 이하의 index를 기술해야 한다.

♣ USE_CONCAT

▶  USE_CONCAT hint는 OR 조건을 UNION ALL set operator를 사용하는 compound query로 변환
▶  이 변환은 UNION ALL set operations을 사용하는 질의가 이를 사용하지 않을 때보다 비용이 더 적을 경우에만 발생

☞Hint for join Orders

ORDERED hint는 join order를 제안

♣ ORDERED

▶  ORDERED hint는 FROM절에 table이 나타나는 순서대로 테이블을 join시킨다.
▶  예를들어, 아래 SQL문은 테이블 TAB1과 테이블 TAB2를 조인한 후에 그 결과와 테이블 TAB3을 조인한다.
·SELECT /*+ ORDERED */ tab1.col1, tab2.col2, tab3.col3
              FROM tab1, tab2, tab3
              WHERE tab1.col1 = tab2.col1 AND tab2.col1 = tab3.col1;
▶  SQL문에서 ORDERED hint를 생략하고 join를 수행하면, optimizer가 table을 join할 순서를 선택
▶  각 테이블에서 select해 오는 행의 수에대해 알고 있다면 join 순서를 기술하는 ORDERED hint를 사용하는 것이 좋다.
▶  사용자가 inner 와 outer table을 선택하는 것이 optimizer가 할수 있는 것보다 나을 수도 있다.

☞ Hint for Join Operations

● 이절에서의 hint는 테이블을 위한 join operation을 언급
● SQL문에서 나타나는 조인된 table을 정확하게 기술해야만 한다.
● SQL문이 테이블의 alias를 사용한다면 hint에서도 테이블의 이름보다는 alias를 사용해야만 한다.
● 테이블의 이름이나 alias는 local database에 테이블의 synonym이나 테이블을 가지고 있어야만 한다.
● USE_NL과 USE_MERGE hint는 ORDERED hint를 사용해야만 한다.
● 오라클은 참조된 테이블이 조인에서 강제로 inner테이블이 될 때 이 hint를 사용해야만 한다.
● 참조된 테이블이 outer table이라면 이 hint들을 무시한다.

♣ USE_NL

▶  USE_NL hint는 테이블을 적는 부분에 테이블 기술된 table은 inner table로서 사용하여 nested loops로서 다른 테이블의 row source와 기술된 table을 join하게 한다.
▶  USE_NL hint의 문법은 USE_NL(inner_table)

table : nested loops join의 inner table로서 사용될 테이블의 이름과 alias이다.

▶  예를 들어서, ACCOUNTS와 CUSTOMERS 테이블을 조인하는 SQL문이 있다고 생각해보자. 이들 테이블들은 cluster에 함께 저장되지 않았다고 가정한다.
·SELECT accounts.balance, customers.last_name, customers.first_name
              FROM accounts, customers
              WHERE accounts.custno = customers.custo;
▶  cost-based approach의 default 목적은 best throughput이므로 이 optimizer는 좀 더 빨리 질의에 의해 select된 모든 행들을 반환하기위해 nested loops operation이나 sort-merge operation중 하나를 선택한다.
▶  그러나 질의에 의해 선택된 첫 번째 행만 반환할 때 필요시간이 매우 적어야 할 경우에는 best throughput보다 best response time으로 SQL문을 최적화하는 것이 더 낫다.
▶  그렇게 하려면 USE_NL hint를 사용함으로서 optimizer가 nested loops join을 강제로 선택하게 할 수 있다.
▶  SQL문에서 USE_NL hint는 CUSTOMERS테이블을 inner table로 가지는 nested loop를 선택
·SELECT /*+ ORDERED USE_NL(customers) Use N-L to get first row faster */
              accounts.balance, customers.last_name, customers.first_name
              FROM accounts, customers
              WHERE accounts.custno = customers.custno;
▶  많은 경우에 nested loops join은 sort-merge join보다는 더빨리 첫 번째 행을 반환한다.
▶  Nested loop join은 한 테이블로부터 첫 번빼 select한 행을 읽은 후에 첫 번째 행을 반환할 수 있고, 다른 테이블에서 첫 번째로 일치하는 행을 찾고 그것들을 결합한다.
▶  반면에 sort-merge join은 양 테이블에서 select한 모든 row들을 읽고 정렬한 후 각각의 저장된 row source의 첫 번째 행들을 결합할 때까지 첫 번째 행을 반환할 수 없다.

♣ USE_MERGE

▶  USE_MERGE hint는 오라클이 sort-merge join으로 각 테이블을 조인하게끔 하는 방법이다.
▶  USE_MERGE hint의 문법은 USE_MERGE(table)

☞ Hints for Parallel Query Execution

♣ CACHE

▶  CACHE hint는 full table scan이 수행됐을 때 hint에 있는 테이블에 대해 retrieve된 블록들은 버퍼 캐시에 있는 LRU list의 가장최근(most recently)에 사용되어진 것의 끝에 위치
▶  이 option은 small lookup table에 유용하다. CACHE hint는 테이블의 default caching specification을 무시
·SELECT/*+ FULL (scoot_emp) CACHE(scott_emp) */ ename
              FROM scott.emp scott_emp;

♣ NOCACHE

▶  NOCACHE hint는 full table scan이 수행됐을 때 hint에 있는 테이블에 대해 retrieve된 블록들은 버퍼 캐시에 있는 LRU list의 가장 오래전(least recently)에 사용되어진 것의 끝에 위치
▶  버퍼캐수에 blocks의 일반적인 행동
·SELECT/*+ FULL (scoot_emp) NOCACHE(scott_emp) */ ename
              FROM scott.emp scott_emp;

☞ Considering Alternative Syntax

●  SQL이 융통성이 있는 언어이기 때문에 하나 이상의 SQL문이 Application을 필요로 할 것이다.
●  2개의 SQL문이 동일한 결과를 산출함에도 불구하고, 오라클은 2중의 하나가 더 처리속도가 빠르다.
●  execution plans을 비교하기 위해 EXPLAIN PLAN SQL문의 결과와 두 SQL문의 비용들을 사용할 수 있다.

♣ 두 SQL문에 대한 execution plan 비교

● 첫 번째 SQL문과 그것에 대한 execution plan
·SELECT dname, deptno
              FROM dept
              WHERE deptno NOT IN
              (SELECT deptno FROM emp);
▶ Execution Plan with Two Full Table Scans

▶ step3을 통해 오라클은 DEPTNO 칼럼에 인덱스가 있음에도 불구하고 EMP테이블을 full table scan함으로서 위 SQL문을 실행한다는 것을 알수 있다.
▶ full table scan은 time-consuming operation을 할 수 있다.
▶ EMP테이블을 찾는 subquery에 index사용이 가능한 WHERE절이 없기 때문에 오라클은 index를 사용할 수 없다.
▶  그러나, 아래의 SQL문은 인덱스에 접근함으로서 동일한 행을 SELECT한다.
·SELECT dname, deptno
              FROM dept
              WHERE NOT EXISTS
              (SELECT deptno
              FROM emp
              WHERE dept.deptno = emp.deptno);
▶ Execution Plan with a Full Table Scan and an Index Scan

▶ subquery의 WHERE절이 EMP테이블의 DEPTNO칼럼을 사용하므로 DEPTNO_INDEX를 사용할 수 있다.
▶ 인덱스의 사용은 execution plan의 step3에서 하게된다
▶ DEPTNO_INDEX의 index range scan은 첫 번째 문장에서 EMP테이블의 full scan하는 것보다 시간이 더 적게 걸린다.
▶  첫 번째 query는 DEPT 테이블에서 모든 DEPTNO를 가져오기 위해 EMP테이블을 한번 full scan한다.
▶ 이런 이유로 두 번째 SQL 문은 첫 번째보다는 더 빠르다.
▶ Application에 NOT IN operator를 사용하는 SQL문를 가진다면 NOT EXISTS operator를 사용해서 그것들을 다시 써라. 이것은 인덱스가 있다면 그 인덱스를 사용할 수 있게 해줄 것이다.

출처 : http://blog.naver.com/flydrago.do?Redirect=Log&logNo=40001931099

아쉽게도 출처의 이미지가 다 깨졌다... 알아서 공부해보자..

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

 

※ 인덱스란?
  인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의
원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조입니다.  

자동 인덱스 : 프라이머리 키 또는 uinque 제한 규칙에 의해 자동적으로 생성되는 인덱스 입니다.
수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스들 입니다.  

※  Index를 생성하는 것이 좋은 Column
① WHERE절이나 join조건 안에서 자주 사용되는 컬럼
② null 값이 많이 포함되어 있는 컬럼
③ WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들
※  다음과 같은 경우에는 index 생성이 불필요 합니다.

① table이 작을 때
③ 테이블이 자주 갱신될 때

※  오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있습니다.
B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 냅니다.
이 알고리즘 원리는
 ① 주어진 값을 리스트의 중간점에 있는 값과 비교합니다.     
     만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다.
     만약 그 값이 더 작다면 위쪽 반을 버립니다.
 ② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도
     반복합니다.

※  인덱스는 B-tree 구조를 가지며 크게 다음 네 가지로 분류될수 있습니다.
① Bitmap 인덱스
  비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동합니다.
  그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킵니다.
  테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있습니다.

SQL>CREATE BITMAP INDEX emp_deptno_indx
ON emp(deptno);

② Unique 인덱스
  Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.
프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스입니다.

SQL>CREATE UNIQUE INDEX emp_ename_indx
ON  emp(ename);

③ Non-Unique 인덱스
   Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있습니다.

SQL>CREATE INDEX  dept_dname_indx
ON  dept(dname);

④ 결합 (Concatenated(=Composite)) 인덱스
   복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개입니다

SQL>CREATE UNIQUE INDEX emp_empno_ename_indx
ON  emp(empno, ename);

※  인덱스의 삭제
 - 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지
않습니다.
 - 인덱스를 삭제하려면 INDEX의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야 합니다.
 - INDEX는 ALTER를 할 수 없습니다.
SQL>DROP INDEX emp_empno_ename_indx ;

※  인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수
      있습니다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

상황)

 

1. 윈도우2003서버 부팅후 오라클에서 리스너가 죽었다.

 

2. Net Manager/Net Configuration Assistant 에서 리스너를 재 설정 해보지만  살아날 기미가 없다.

 

3. 인터넷으로 다방면으로 알아보지만... 시원한 답변이 없다.

 

http://blog.naver.com/paradog/100025279794 (우연히 다음 검색으로 알아냈다)

 

해결)

 

1. 관리도구>서비스에서 오라클 리스너 실행되어 있지 않음을 확인

 

2. 서비스 시작을 눌러보지만.. 뻗는다.

 

3. cmd>lsnrctl 실행(리스너 관련 컨트롤)

 

4. lsnrctl>help .. 몇가지가 있다..

 

5. lsnrctl>start  ... error 똑같은 소리만 나온다... (리스너가 아닙니다..)좬당..

 

6.  E:\ORACLE_SERVER\oracle\product\10.2.0\db\NETWORK\log\listener.log 에서 최초 실행시 정상 작동되었던 설정을 확인해봤다.

 

7. host <<< luciper-s ?? 어라.. host 에다가 (luciper.iptime.org) 이거 아니야????

 

8. 로컬(오라클10g server)에서는 호스트명이 컴터명이다(추측해본다).. 후후훗...

 

9. Net Manager > 리스너 > 호스트명 (luciper-s)로 변경

 

10. lsnrctl>start ... (OK)

 

잡담)

 

정말 우연찮게 찾은 블로그에서 이런 단비와 같은 것을 찾아냈다...

 

네이버가 검색엔 좋을찌 모르지만.. 내가 원하는 답을 하는 녀석은 네이버에 없다...

 

다음부터는 다음,네이버 등... 많은 검색싸이트를 뒤져봐야겠다..

 

오라클에 대해 오늘도 한발 다가선다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

숫자,한글,영문의 정렬순서를 정리해본다.

 

-- 숫자, 영문, 한글 순서
ORDER BY 컬럼명  

-- 숫자, 한글, 영문 순서
ORDER BY CONVERT (컬럼명, 'VN8VN3')

-- 한글, 영문, 숫자 순서
ORDER BY CONVERT (컬럼명, 'US8ICL')  

-- 한글, 숫자, 영문 순서
ORDER BY CONVERT (컬럼명, 'ISO2022-KR')

-- 영문, 한글, 숫자 순서
ORDER BY CONVERT (컬럼명, 'UTFE')

 

 

출처 : http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=15#1516

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

SQL 실행 순서입니다.
기본적인 내용인데 모르는 분이 많아서 글로 남깁니다.
(괄호가 실행 순서를 나타낸다.)
SELECT       - (5)
FROM         - (1)
WHERE        - (2)
GROUP BY     - (3)
HAVING       - (4)
ORDER BY     - (6)

 

출처 : http://www.soqool.com/

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.