오라클에서 글로벌 임시 테이블을 피하는 방법

오라클에서 글로벌 임시 테이블을 피하는 방법

우리는 방금 SQL 서버 저장 프로시저를 오라클 프로시저로 변환했습니다.테이블에 크게 의존했습니다(SQL Server SP의 경우).INSERT INTO #table1...이 테이블은 오라클에서 글로벌 임시 테이블로 변환되었습니다.의 400 SP, 400 SP, 500 GTT의 약 했습니다.

이제 성능 및 기타 문제로 인해 오라클에서 GTT로 작업하는 것이 마지막 옵션으로 간주된다는 것을 알게 되었습니다.

다른 대안들은 무엇이 있습니까?컬렉션?커서?

GTT의 일반적인 사용법은 다음과 같습니다.

GTT에 삽입

INSERT INTO some_gtt_1
  (column_a,
   column_b,
   column_c)
  (SELECT someA,
      someB,
      someC
     FROM TABLE_A
    WHERE condition_1 = 'YN756'
      AND type_cd = 'P'
      AND TO_NUMBER(TO_CHAR(m_date, 'MM')) = '12'
      AND (lname LIKE (v_LnameUpper || '%') OR
      lname LIKE (v_searchLnameLower || '%'))
      AND (e_flag = 'Y' OR
      it_flag = 'Y' OR
      fit_flag = 'Y'));

GTT 업데이트

UPDATE some_gtt_1 a
SET column_a = (SELECT b.data_a FROM some_table_b b 
               WHERE a.column_b = b.data_b AND a.column_c = 'C')
WHERE column_a IS NULL OR column_a = ' ';

그리고 나중에 GTT에서 데이터를 꺼냅니다.이것들은 샘플 쿼리일 뿐입니다. 실제로 쿼리는 많은 조인과 하위 쿼리로 매우 복잡합니다.

세 가지 질문이 있습니다.

1. 누가 위의 샘플 쿼리를 컬렉션 및/또는 커서로 변환하는 방법을 보여줄 수 있습니까?
2. GTT를 사용하면 기본적으로 SQL로 작업할 수 있습니다. GTT를 사용하지 않는 이유는 무엇입니까?그들이 정말 그렇게 나쁜가요?
3. GTT의 사용 시기와 사용 시기에 대한 지침은 무엇이어야 합니까?

두 번째 질문에 먼저 답하겠습니다.

"왜 GTT에서 멀어집니까?"그들은 정말 그렇게 나쁜가요."

며칠 전 저는 대형 XML 파일(~18MB)을 XMLType에 로드하는 개념 증명을 작성하고 있었습니다.XMLType을 영구적으로 저장하고 싶지 않았기 때문에 PL/SQL 변수(세션 메모리)와 임시 테이블에 로드하려고 했습니다.임시 테이블에 로드하는 데는 XMLType 변수에 로드하는 것보다 5배 더 오래 걸렸습니다(1초에 비해 5초).차이점은 임시 테이블이 메모리 구조가 아니기 때문입니다. 임시 테이블은 디스크(특히 지정된 임시 테이블 공간)에 기록됩니다.

만약 당신이 많은 데이터를 캐시하고 싶다면, 메모리에 저장하는 것은 PGA에 스트레스를 줄 것이고, 세션이 많은 경우에는 좋지 않습니다.즉, RAM과 시간의 균형입니다.

첫 번째 질문으로:

"누가 위의 샘플 쿼리를 컬렉션 및/또는 커서로 변환하는 방법을 보여줄 수 있습니까?"

게시한 쿼리를 단일 문으로 병합할 수 있습니다.

SELECT case when a.column_a IS NULL OR a.column_a = ' ' 
           then b.data_a
           else  column_a end AS someA,
       a.someB,
       a.someC
FROM TABLE_A a
      left outer join TABLE_B b
          on ( a.column_b = b.data_b AND a.column_c = 'C' )
WHERE condition_1 = 'YN756'
  AND type_cd = 'P'
  AND TO_NUMBER(TO_CHAR(m_date, 'MM')) = '12'
  AND (lname LIKE (v_LnameUpper || '%') OR
  lname LIKE (v_searchLnameLower || '%'))
  AND (e_flag = 'Y' OR
  it_flag = 'Y' OR
  fit_flag = 'Y'));

(단순히 당신의 논리를 바꿔놓았지만, 그것은.case() 로니대수있습니다로 될 수 .nvl2(trim(a.column_a), a.column_a, b.data_a)).

당신이 질문이 더 복잡하다고 말하는 것을 알지만, 당신의 첫 번째 상담 창구는 그것들을 다시 쓰는 것을 고려하는 것입니다.비정상적인 쿼리를 PL/SQL과 함께 연결된 많은 아기 SQL로 나누는 것이 얼마나 매력적인지 알고 있지만 순수한 SQL이 훨씬 더 효율적입니다.

컬렉션을 사용하려면 SQL에서 유형을 정의하는 것이 가장 좋습니다. 이는 PL/SQL뿐만 아니라 SQL 문에서도 유형을 사용할 수 있는 유연성을 제공하기 때문입니다.

create or replace type tab_a_row as object
    (col_a number
     , col_b varchar2(23)
     , col_c date);
/
create or replace type tab_a_nt as table of tab_a_row;
/

다음은 결과 집합을 반환하는 샘플 함수입니다.

create or replace function get_table_a 
      (p_arg in number) 
      return sys_refcursor 
is 
    tab_a_recs tab_a_nt; 
    rv sys_refcursor; 
begin 
    select tab_a_row(col_a, col_b, col_c)  
    bulk collect into tab_a_recs 
    from table_a 
    where col_a = p_arg; 

    for i in tab_a_recs.first()..tab_a_recs.last() 
    loop 
        if tab_a_recs(i).col_b is null 
        then 
            tab_a_recs(i).col_b :=  'something'; 
        end if; 
    end loop;  

    open rv for select * from table(tab_a_recs); 
    return rv; 
end; 
/ 

그리고 여기 그것이 작동하고 있습니다.

SQL> select * from table_a
  2  /

     COL_A COL_B                   COL_C
---------- ----------------------- ---------
         1 whatever                13-JUN-10
         1                         12-JUN-10

SQL> var rc refcursor
SQL> exec :rc := get_table_a(1)

PL/SQL procedure successfully completed.

SQL> print rc

     COL_A COL_B                   COL_C
---------- ----------------------- ---------
         1 whatever                13-JUN-10
         1 something               12-JUN-10

SQL>

기능에서는 ORA-00947 예외를 방지하기 위해 열로 유형을 인스턴스화해야 합니다.PL/SQL 테이블 유형을 채울 때는 이 작업이 필요하지 않습니다.

SQL> create or replace procedure pop_table_a
  2        (p_arg in number)
  3  is
  4      type table_a_nt is table of table_a%rowtype;
  5      tab_a_recs table_a_nt;
  6  begin
  7      select *
  8      bulk collect into tab_a_recs
  9      from table_a
 10      where col_a = p_arg;
 11  end;
 12  /

Procedure created.

SQL> 

마지막으로, 가이드라인

"GTT를 사용할 때와 피해야 할 때에 대한 지침은 무엇이어야 합니까?"

글로벌 임시 테이블은 동일한 세션의 서로 다른 프로그램 단위 간에 캐시된 데이터를 공유해야 할 때 매우 유용합니다.예를 들어, 여러 절차 중 하나에 의해 채워진 GTT를 공급하는 단일 함수에 의해 생성된 일반 보고서 구조가 있는 경우. (비록 동적 참조 커서로도 구현될 수 있지만...)

단일 SQL 쿼리로 해결하기에는 너무 복잡한 중간 처리가 많은 경우에도 글로벌 임시 테이블이 좋습니다.특히 해당 처리가 검색된 행의 하위 집합에 적용되어야 하는 경우에는 더욱 그렇습니다.

그러나 일반적으로 임시 테이블을 사용할 필요가 없다고 가정해야 합니다.그렇게

1. 너무 힘들지 않다면 SQL로 하세요. 어떤 경우...
2. PL/SQL 변수(일반적으로 컬렉션)에서 수행합니다. 메모리가 너무 많이 필요하지 않으면...
3. 글로벌 임시 테이블로 수행

일반적으로 소량의 데이터(1,000개의 행)를 저장하는 데 PL/SQL 컬렉션을 사용합니다.데이터 볼륨이 훨씬 더 크면 프로세스 메모리에 과부하가 걸리지 않도록 GTT를 사용할 것입니다.

따라서 데이터베이스에서 PL/SQL 컬렉션으로 수백 개의 행을 선택한 다음 해당 행을 반복하여 계산을 수행하거나 몇 개의 행을 삭제한 다음 해당 컬렉션을 다른 테이블에 삽입할 수 있습니다.

수십만 개의 행을 처리할 경우 '헤비 리프팅' 처리의 상당 부분을 대형 SQL 문으로 밀어 넣으려고 합니다.그것은 GTT를 필요로 할 수도 있고 필요하지 않을 수도 있습니다.

SQL 수준 컬렉션 개체를 SQL과 PL/SQL 간에 쉽게 변환할 수 있습니다.

create type typ_car is object (make varchar2(10), model varchar2(20), year number(4));
/

create type typ_coll_car is table of typ_car;
/

select * from table (typ_coll_car(typ_car('a','b',1999), typ_car('A','Z',2000)));
MAKE       MODEL                           YEAR
---------- -------------------- ---------------
a          b                           1,999.00
A          Z                           2,000.00

declare
  v_car1 typ_car := typ_car('a','b',1999);
  v_car2 typ_car := typ_car('A','Z',2000);
  t_car  typ_coll_car := typ_coll_car();
begin
  t_car := typ_coll_car(v_car1, v_car2);
  FOR i in (SELECT * from table(t_car)) LOOP
    dbms_output.put_line(i.year);
    END LOOP;
end;
/

언급URL : https://stackoverflow.com/questions/2918466/ways-to-avoid-global-temp-tables-in-oracle