쿼리 변환

1 쿼리 변환[편집]

최적화 프로그램은 비용을 기준으로 사용 가능한 변환을 사용할지 여부를 결정합니다.
힌트나 제약 조건 부족을 비롯한 다양한 이유로 최적화 프로그램에서 변환을 사용하지 못할 수 있습니다.
예를 들어 제약 조건을 지원하지 않는 외부 파티션이 포함된 하이브리드 파티션을 나눈 테이블에는 하위 쿼리 중첩 해제와 같은 변환을 사용할 수 없습니다.

쿼리변환시 사용되는 2가지 방식

휴리스틱기반 쿼리변환
* 사전적 의미는 ‘경험적인’ 뜻, 논리적추론 + 수학적 연산 + 경험

- 특정조건을 충족시킬때

- 대부분의 상황에서 더 나은 실행 계획을 생성한다.
비용기반 쿼리변환
- 비용 추정기가 산출한 비용에 근거하여 생성

- 원래 구문보다 더 낮은 비용의 실행계획이 생성될때 적용

1.1 OR Expansion[편집]

확장 시 OR최적화 프로그램은 최상위 분리가 포함된 쿼리 블록을 UNION ALL두 개 이상의 분기가 포함된 쿼리 형식으로 변환합니다.

최적화 프로그램은 분리를 해당 구성 요소로 분할한 다음 각 구성 요소를 쿼리 분기와 연결하여 이 목표를 달성합니다 UNION ALL. 옵티마이저는 OR다양한 이유로 확장을 선택할 수 있습니다. 예를 들어, 데카르트 곱을 피하는 보다 효율적인 액세스 경로나 대체 조인 방법을 사용할 수 있습니다. 항상 그렇듯이 최적화 프로그램은 변환된 문의 비용이 원래 문의 비용보다 낮은 경우에만 확장을 수행합니다.

이전 릴리스에서는 옵티마이저가 연산자를 사용하여 확장을 CONCATENATION수행했습니다 OR. Oracle Database 12c 릴리스 2(12.2) 부터 최적화 프로그램은 UNION-ALL대신 연산자를 사용합니다. 프레임워크는 다음과 같은 향상된 기능을 제공합니다.

다양한 변환 간의 상호 작용 가능

쿼리 구조 공유 방지

다양한 검색 전략 탐색 가능

비용 주석 재사용 제공

표준 SQL 구문 지원

예 5-1 변환된 쿼리: UNION ALL 조건

이 예를 준비하려면 데이터베이스에 관리자로 로그인하고 다음 문을 실행하여 열에 고유 제약 조건을 추가한 hr.departments.department_name다음 테이블에 100,000개의 행을 추가합니다 hr.employees.

ALTER TABLE hr.departments ADD CONSTRAINT department_name_uk UNIQUE (department_name);
DELETE FROM hr.employees WHERE employee_id > 999;
DECLARE
v_counter NUMBER(7) := 1000;
BEGIN
 FOR i IN 1..100000 LOOP    
 INSERT INTO hr.employees
    VALUES (v_counter,null,'Doe','Doe' || v_counter || '@example.com',null,'07-JUN-02','AC_ACCOUNT',null,null,null,50);
 v_counter := v_counter + 1;
 END LOOP;
END;
/
COMMIT; 
EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'hr', tabname => 'employees');

그런 다음 사용자로 연결하고 및 테이블 hr을 조인하는 다음 쿼리를 실행합니다 . employeesdepartments

SELECT *
FROM   employees e, departments d
WHERE  (e.email='SSTILES' OR d.department_name='Treasury')
AND    e.department_id = d.department_id;

확장이 없으면 OR옵티마이저는 e.email='SSTILES' OR d.department_name='Treasury'단일 단위로 처리됩니다. e.email결과적으로 옵티마이저는 또는 열의 인덱스를 사용할 수 없으므로 및 d.department_name의 전체 테이블 스캔을 수행합니다 . employeesdepartments

확장을 통해 OR최적화 프로그램은 다음 예에 표시된 것처럼 분리적 조건자를 두 개의 독립적인 조건자로 나눕니다.

SELECT *
FROM   employees e, departments d
WHERE  e.email = 'SSTILES'
AND    e.department_id = d.department_id
UNION ALL
SELECT *
FROM   employees e, departments d
WHERE  d.department_name = 'Treasury'
AND    e.department_id = d.department_id;

이 변환을 통해 e.email및 d.department_name열이 인덱스 키 역할을 할 수 있습니다. 다음 실행 계획에 표시된 것처럼 데이터베이스가 두 개의 전체 테이블 스캔 대신 두 개의 고유 인덱스를 사용하여 데이터를 필터링하므로 성능이 향상됩니다.

Plan hash value: 2512933241

-------------------------------------------------------------------------------------------
| Id| Operation                           | Name            |Rows|Bytes|Cost(%CPU)|Time   |
-------------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT                     |                  |    |     |122(100)|        |
| 1 | VIEW                                |VW_ORE_19FF4E3E   |9102|1679K|122 (5) |00:00:01|
| 2 |  UNION-ALL                          |                  |    |     |        |        |
| 3 |   NESTED LOOPS                      |                  |  1 |  78 |  4 (0) |00:00:01|
| 4 |    TABLE ACCESS BY INDEX ROWID      | EMPLOYEES        |  1 |  57 |  3 (0) |00:00:01|
|*5 |     INDEX UNIQUE SCAN               | EMP_EMAIL_UK     |  1 |     |  2 (0) |00:00:01|
| 6 |    TABLE ACCESS BY INDEX ROWID      | DEPARTMENTS      |  1 |  21 |  1 (0) |00:00:01|
|*7 |     INDEX UNIQUE SCAN               | DEPT_ID_PK       |  1 |     |  0 (0) |        |
| 8 |   NESTED LOOPS                      |                  |9101| 693K|118 (5) |00:00:01|
| 9 |    TABLE ACCESS BY INDEX ROWID      | DEPARTMENTS      |  1 |  21 |  1 (0) |00:00:01|
|*10|     INDEX UNIQUE SCAN               |DEPARTMENT_NAME_UK|  1 |     |  0 (0) |        |
|*11|    TABLE ACCESS BY INDEX ROWID BATCH| EMPLOYEES        |9101| 506K|117 (5) |00:00:01|
|*12|     INDEX RANGE SCAN                |EMP_DEPARTMENT_IX |9101|     | 35 (6) |00:00:01|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."EMAIL"='SSTILES')
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  10 - access("D"."DEPARTMENT_NAME"='Treasury')
  11 - filter(LNNVL("E"."EMAIL"='SSTILES'))
  12 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

35 rows selected.

1.2 뷰머징 (View Merging)[편집]

뷰와 인라인뷰로 인한 쿼리 블록의 수를 줄이기 위해 이들중 일부를 병합하는것이 뷰머징의 목적임
뷰머징 에서 옵티마이져는 뷰를 나타내는 쿼리 블록을 이를 포함하는 쿼리 블록에 병합 함.
뷰머징은 옵티마이져가 조인 순서 추가 , 액세스 방법 및 기타 변환을 고려할 수 있도록 하여 실행계획을 개선.
예를 들어, 뷰가 병합되고 여러 테이블이 하나의 쿼리 블록에 상주한 후 뷰 내부의 테이블은 옵티마이져가 조인 제거를 사용하여 뷰 외부의 테이블을 제거하도록 허용할 수 있음.
병합이 항상 더 나은 계획으로 이어지는 일부 단순뷰의 경우 옵티마이져는 비용을 고려하지 않고 자동으로 뷰를 병합합니다. 그렇지 않으면 옵티마이져는 비용을 기반으로 결정 합니다.
옵티마이져는 보안,유효성,제약조건등을 포함한 여러 가지 이유로 뷰를 병합하지 않도록 선택할 수도 있음.
OPTIMIZER_SECURE_VIEW_MERGING(기본값) 인 true 경우 Oracle 데이터베이스는 뷰머징 및 조건자 푸시(push predicate)가 뷰 작성자의 보안 의도를 위반하지 않는지 확인하기 위한 검사를 수행합니다.

메모:힌트를 사용하면 비용이나 경험적 방법으로 인해 거부된 뷰 병합을 무시할 수 있지만 유효성은 그렇지 않습니다.

1.2.1 뷰 병합의 쿼리 블록[편집]

옵티마이져는 별도의 쿼리 블록으로 중첩된 각 하위 쿼리 또는 병합되지 않은 뷰를 표시함
데이터베이스는 상향식 방법으로 각각의 쿼리 블록을 최적화합니다.
1. 따라서 데이터베이스는 가장 안쪽 쿼리 블록을 먼저 최적화하고 이에 대한 계획의 일부를 생성한 다음 전체 쿼리를 나타내는 외부 쿼리 블록에 대한 계획을 생성합니다.
파서는 쿼리에서 참조되는 각 뷰를 별도의 쿼리 블록으로 확장합니다.
1. 블록은 기본적으로 뷰 정의, 즉 뷰의 결과를 나타냅니다.
옵티마이져의 한 가지 옵션은 뷰 쿼리 블록을 별도로 분석하고, 뷰 하위 계획을 생성한 다음, 뷰 하위 계획을 사용하여 나머지 쿼리를 처리하여 전체 실행 계획을 생성하는 것입니다.
1. 그러나 이 기술은 뷰가 별도로 최적화되기 때문에 최적이 아닌 실행 계획으로 이어질 수 있습니다.
뷰 병합은 때때로 성능을 향상시킬 수 있습니다.
1. 뷰 병합은 뷰 테이블을 외부 쿼리 블록에 병합하고 내부 쿼리 블록을 제거합니다. 따라서 별도의 뷰 최적화가 필요하지 않습니다.

1.2.2 단순 뷰 병합[편집]

평범한 select-project-join 쿼리블록을 병합하기 위해 사용됨
휴리스틱에 기반을 둘 쿼리 변환
집계,집합연산자,계층형쿼리,MODEL절 ,SELECT절에 서브쿼리가 있는 뷰나 인라인뷰에는 적용할수 없음.

1.2.2.1 select-project-join 뷰 병합[편집]

단순뷰 병합 에서 옵티마이져는 select-project-join 뷰를 병합 합니다.
* select : 특정 조건을 만족하는 로우 추출

* project: 참조되는 테이블에서 특정 컬럼만을 추출

* join : 여러 테이블에서 추출되는 데이터를 하나로 합치는 조인
1. select-project_join 블록

select *
  from (select t1.*
          from t1,t2
         where t1.id = t2.t1_id) t12,
       (select *
          from t3
         where id > 6) t3
 where t12.id = t3.t1_id

====>>> 인라인뷰 끼리 병합하여 다음과 같이 쿼리 생성

select t1.*,t3.*
  from t1,t2,t3
 where t1.id = t3.t1_id 
   and t1.id = t2.t1_id
   and t3.id > 6

1.2.2.2 단순뷰 병합에 실패하는 경우[편집]

GROUP BY
DISTINCT
아우터 조인
MODEL
CONNECT BY
집합 연산자
집합

다음 쿼리는 hr.employees테이블을 dept_locs_v 뷰와 결합하여 각 부서의 거리 주소를 반환합니다.

SELECT e.first_name, e.last_name, dept_locs_v.street_address,
       dept_locs_v.postal_code
FROM   employees e,
      ( SELECT d.department_id, d.department_name, 
               l.street_address, l.postal_code
        FROM   departments d, locations l
        WHERE  d.location_id = l.location_id ) dept_locs_v
WHERE  dept_locs_v.department_id = e.department_id
AND    e.last_name = 'Smith';

데이터베이스는 뷰의 행을 departments하고 조인하여 생성한 다음에 결과를 조인하여 이전 쿼리를 실행할 수 있습니다 .
쿼리에는 뷰가 포함되어 있고 이 뷰에는 두 개의 테이블이 포함되어 있으므로 최적화 프로그램은 다음 조인 순서 중 하나를 사용해야 합니다.

locations,employees, dept_locs_v

employees, dept_locs_v( departments, locations)

employees, dept_locs_v( locations, departments)

dept_locs_v( departments, locations),employees

dept_locs_v( locations, departments),employees

조인 방법도 제한됩니다. employees이 뷰의 열에 인덱스가 없기 때문에 인덱스 기반 중첩 루프 조인은 다음으로 시작하는 조인 순서에 적합하지 않습니다 .

뷰 병합 없이 최적화 프로그램은 다음 실행 계획을 생성합니다.

-----------------------------------------------------------------
| Id  | Operation                    | Name        | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     7  (15)|
|*  1 |  HASH JOIN                   |             |     7  (15)|
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | EMP_NAME_IX |     1   (0)|
|   4 |   VIEW                       |             |     5  (20)|
|*  5 |    HASH JOIN                 |             |     5  (20)|
|   6 |     TABLE ACCESS FULL        | LOCATIONS   |     2   (0)|
|   7 |     TABLE ACCESS FULL        | DEPARTMENTS |     2   (0)|
-----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("E"."LAST_NAME"='Smith')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

뷰 병합은 뷰의 테이블을 외부 쿼리 블록으로 병합하여 내부 쿼리 블록을 제거합니다.

SELECT e.first_name, e.last_name, l.street_address, l.postal_code
FROM   employees e, departments d, locations l
WHERE  d.location_id = l.location_id
AND    d.department_id = e.department_id
AND    e.last_name = 'Smith';

세 개의 테이블이 모두 하나의 쿼리 블록에 나타나기 때문에 최적화 프로그램은 다음 6개의 조인 순서 중에서 선택할 수 있습니다.

employees, departments,locations

employees, locations,departments

departments, employees,locations

departments, locations,employees

locations, employees,departments

locations, departments,employees

이제 조인은 인덱스 기반이 될 수 있습니다
employees. departments뷰 병합 후 최적화 프로그램은 중첩 루프를 사용하는 다음과 같은 보다 효율적인 계획을 선택합니다.

-------------------------------------------------------------------
| Id  | Operation                      | Name        | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     4   (0)|
|   1 |  NESTED LOOPS                  |             |            |
|   2 |   NESTED LOOPS                 |             |     4   (0)|
|   3 |    NESTED LOOPS                |             |     3   (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     2   (0)|
|*  5 |      INDEX RANGE SCAN          | EMP_NAME_IX |     1   (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1   (0)|
|*  7 |      INDEX UNIQUE SCAN         | DEPT_ID_PK  |     0   (0)|
|*  8 |    INDEX UNIQUE SCAN           | LOC_ID_PK   |     0   (0)|
|   9 |   TABLE ACCESS BY INDEX ROWID  | LOCATIONS   |     1   (0)|
-------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 5 - access("E"."LAST_NAME"='Smith')
 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

참조:https://blogs.oracle.com/optimizer/단순 뷰 병합의 특별한 경우인 외부 조인 뷰 병합에 대해 알아보려면 Oracle Optimizer 블로그를 참조하세요.

1.2.3 복합뷰 머징 (Complex View Merging)[편집]

뷰 병합 에서 최적화 프로그램은 포함된 뷰 GROUP BY와 DISTINCT뷰를 병합합니다. 단순 뷰 병합과 마찬가지로 복합 병합을 통해 최적화 프로그램은 추가 조인 순서와 액세스 경로를 고려할 수 있습니다.

최적화 프로그램은 조인을 평가할 때까지 GROUP BY또는 작업 평가를 지연할 수 있습니다. DISTINCT이러한 작업을 지연하면 데이터 특성에 따라 성능이 향상되거나 저하될 수 있습니다. 조인에서 필터를 사용하는 경우 조인 이후까지 작업을 지연하면 작업을 수행할 데이터 세트가 줄어들 수 있습니다. 작업을 조기에 평가하면 후속 조인에서 처리할 데이터 양이 줄어들거나 조인으로 인해 작업에서 처리할 데이터 양이 늘어날 수 있습니다. 최적화 프로그램은 비용을 사용하여 뷰 병합을 평가하고 비용이 더 낮은 옵션인 경우에만 뷰를 병합합니다.

비용 외에도 최적화 프로그램은 다음과 같은 이유로 복잡한 뷰 병합을 수행하지 못할 수 있습니다.

외부 쿼리 테이블에는 rowid 또는 고유 열이 없습니다.

뷰가 CONNECT BY쿼리 블록에 나타납니다.

뷰에 GROUPING SETS, ROLLUP또는 PIVOT절이 포함되어 있습니다.

뷰 또는 외부 쿼리 블록에 MODEL절이 포함되어 있습니다.

예 5-3 GROUP BY를 사용한 복합 뷰 조인

다음 보기에서는 GROUP BY절을 사용합니다.

CREATE VIEW cust_prod_totals_v AS
SELECT SUM(s.quantity_sold) total, s.cust_id, s.prod_id
FROM   sales s
GROUP BY s.cust_id, s.prod_id;

다음 쿼리는 모피 트리밍 스웨터를 100개 이상 구매한 미국 고객을 모두 찾습니다.

SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM   customers c, products p, cust_prod_totals_v
WHERE  c.country_id = 52790
AND    c.cust_id = cust_prod_totals_v.cust_id
AND    cust_prod_totals_v.total > 100
AND    cust_prod_totals_v.prod_id = p.prod_id
AND    p.prod_name = 'T3 Faux Fur-Trimmed Sweater';

뷰 cust_prod_totals_v는 복합 뷰 병합에 적합합니다. 병합 후 쿼리는 다음과 같습니다.

SELECT c.cust_id, cust_first_name, cust_last_name, cust_email
FROM   customers c, products p, sales s
WHERE  c.country_id = 52790
AND    c.cust_id = s.cust_id
AND    s.prod_id = p.prod_id
AND    p.prod_name = 'T3 Faux Fur-Trimmed Sweater'
GROUP BY s.cust_id, s.prod_id, p.rowid, c.rowid, c.cust_email, c.cust_last_name, 
         c.cust_first_name, c.cust_id
HAVING SUM(s.quantity_sold) > 100;

변환된 쿼리는 변환되지 않은 쿼리보다 비용이 저렴하므로 최적화 프로그램은 뷰를 병합하도록 선택합니다. 변환되지 않은 쿼리에서 연산자는 뷰의 GROUP BY전체 테이블에 적용됩니다 . 변환된 쿼리에서는 테이블 행의 상당 부분을 sales조인 products하고 필터링하므로 작업 비용이 절감됩니다. 조인은 테이블이 줄어들지 않았기 때문에 비용이 더 많이 들지만, 원래 쿼리에서는 행 집합의 크기를 크게 줄이지 않기 때문에 비용이 많이 들지 않습니다. 위의 특성 중 하나라도 변경되면 뷰를 병합하는 것이 더 이상 비용이 절감되지 않을 수 있습니다. 뷰가 포함되지 않은 최종 계획은 다음과 같습니다. customerssalesGROUP BYsalesGROUP BY

--------------------------------------------------------
| Id  | Operation             | Name      | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT      |           |  2101  (18)|
|*  1 |  FILTER               |           |            |
|   2 |   HASH GROUP BY       |           |  2101  (18)|
|*  3 |    HASH JOIN          |           |  2099  (18)|
|*  4 |     HASH JOIN         |           |  1801  (19)|
|*  5 |      TABLE ACCESS FULL| PRODUCTS  |    96   (5)|
|   6 |      TABLE ACCESS FULL| SALES     |  1620  (15)|
|*  7 |     TABLE ACCESS FULL | CUSTOMERS |   296  (11)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("QUANTITY_SOLD")>100)
3 - access("C"."CUST_ID"="CUST_ID")
4 - access("PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')

예 5-4 DISTINCT를 사용한 복합 뷰 조인

다음 cust_prod_v뷰 쿼리에서는 연산자를 사용합니다 DISTINCT.

SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM   customers c, products p,
       ( SELECT DISTINCT s.cust_id, s.prod_id
         FROM   sales s) cust_prod_v
WHERE  c.country_id = 52790
AND    c.cust_id = cust_prod_v.cust_id
AND    cust_prod_v.prod_id = p.prod_id
AND    p.prod_name = 'T3 Faux Fur-Trimmed Sweater';

뷰 병합이 더 저렴한 계획을 생성한다고 판단한 후 최적화 프로그램은 쿼리를 다음과 같은 동등한 쿼리로 다시 작성합니다.

SELECT nwvw.cust_id, nwvw.cust_first_name, nwvw.cust_last_name, nwvw.cust_email
FROM   ( SELECT DISTINCT(c.rowid), p.rowid, s.prod_id, s.cust_id,
                c.cust_first_name, c.cust_last_name, c.cust_email
         FROM   customers c, products p, sales s
         WHERE  c.country_id = 52790
         AND    c.cust_id = s.cust_id
         AND    s.prod_id = p.prod_id
         AND    p.prod_name = 'T3 Faux Fur-Trimmed Sweater' ) nwvw;

이전 쿼리에 대한 계획은 다음과 같습니다.

-------------------------------------------
| Id  | Operation             | Name      |
-------------------------------------------
|   0 | SELECT STATEMENT      |           |
|   1 |  VIEW                 | VM_NWVW_1 |
|   2 |   HASH UNIQUE         |           |
|*  3 |    HASH JOIN          |           |
|*  4 |     HASH JOIN         |           |
|*  5 |      TABLE ACCESS FULL| PRODUCTS  |
|   6 |      TABLE ACCESS FULL| SALES     |
|*  7 |     TABLE ACCESS FULL | CUSTOMERS |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - access("C"."CUST_ID"="S"."CUST_ID")
  4 - access("S"."PROD_ID"="P"."PROD_ID")
  5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
  7 - filter("C"."COUNTRY_ID"='US')

이전 계획에는 뷰 병합이 발생한 후에도 투영 뷰vm_nwvw_1 라고 하는 뷰가 포함되어 있습니다. 투영 뷰는 뷰가 병합된 쿼리에 나타나 거나 뷰가 , 또는 집계 도 포함하는 외부 쿼리 블록에 병합됩니다 . 후자의 경우 투영 뷰에는 원래 외부 쿼리 블록의 , 및 집계가 포함됩니다.DISTINCTGROUP BYGROUP BYHAVINGGROUP BYHAVING

앞의 프로젝션 뷰 예에서 최적화 프로그램은 뷰를 병합할 때 연산자를 DISTINCT외부 쿼리 블록으로 이동한 다음 원래 쿼리와의 의미론적 동등성을 유지하기 위해 몇 가지 추가 열을 추가합니다. SELECT이후 쿼리는 외부 쿼리 블록 목록 에서 원하는 열만 선택할 수 있습니다 . 최적화는 뷰 병합의 모든 이점을 유지합니다. 모든 테이블은 하나의 쿼리 블록에 있고, 최적화 프로그램은 필요에 따라 최종 조인 순서에서 테이블을 변경할 수 있으며, DISTINCT모든 조인이 완료될 때까지 작업이 지연됩니다.

1.3 PUSH SUBQ (술어 푸시)[편집]

조건자 푸시 에서 최적화 프로그램은 포함 쿼리 블록의 관련 조건자를 보기 쿼리 블록으로 "푸시"합니다.

병합되지 않은 뷰의 경우 이 기술은 병합되지 않은 뷰의 하위 계획을 개선합니다. 데이터베이스는 푸시인 조건자를 사용하여 인덱스에 액세스하거나 필터로 사용할 수 있습니다.

예를 들어 다음과 같이 테이블을 생성한다고 가정합니다 hr.contract_workers.

DROP TABLE contract_workers;
CREATE TABLE contract_workers AS (SELECT * FROM employees where 1=2);
INSERT INTO contract_workers VALUES (306, 'Bill', 'Jones', 'BJONES',
  '555.555.2000', '07-JUN-02', 'AC_ACCOUNT', 8300, 0,205, 110);
INSERT INTO contract_workers VALUES (406, 'Jill', 'Ashworth', 'JASHWORTH', 
  '555.999.8181', '09-JUN-05', 'AC_ACCOUNT', 8300, 0,205, 50);
INSERT INTO contract_workers VALUES (506, 'Marcie', 'Lunsford', 
  'MLUNSFORD', '555.888.2233', '22-JUL-01', 'AC_ACCOUNT', 8300, 
  0, 205, 110);
COMMIT;
CREATE INDEX contract_workers_index ON contract_workers(department_id);

employees및 를 참조하는 뷰를 생성합니다 contract_workers. UNION뷰는 다음과 같이 집합 연산자를 사용하는 쿼리로 정의됩니다 .

CREATE VIEW all_employees_vw AS
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   employees )
  UNION
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   contract_workers );

그런 다음 다음과 같이 뷰를 쿼리합니다.

SELECT last_name
FROM   all_employees_vw
WHERE  department_id = 50;

뷰는 UNION설정된 쿼리이기 때문에 최적화 프로그램은 뷰의 쿼리를 액세스하는 쿼리 블록에 병합할 수 없습니다. WHERE대신, 최적화 프로그램은 조건부인 조건 절을 department_id=50뷰의 UNION설정 쿼리 에 푸시하여 액세스하는 명령문을 변환할 수 있습니다 . 동등한 변환 쿼리는 다음과 같습니다.

SELECT last_name
FROM   ( SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   employees
         WHERE  department_id=50
         UNION
         SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   contract_workers
         WHERE  department_id=50 );

이제 변환된 쿼리는 각 쿼리 블록의 인덱스 액세스를 고려할 수 있습니다.

5.4 하위 쿼리 중첩 해제 하위 쿼리 중첩 해제 에서 최적화 프로그램은 중첩 쿼리를 동등한 조인 문으로 변환한 다음 조인을 최적화합니다.

이 변환을 통해 최적화 프로그램은 액세스 경로, 조인 방법 및 조인 순서 선택 중에 하위 쿼리 테이블을 고려할 수 있습니다. 최적화 프로그램은 결과 조인 문이 원본 문과 동일한 행을 반환하도록 보장되고 하위 쿼리에 와 같은 집계 함수가 포함되지 않은 경우에만 이 변환을 수행할 수 있습니다 AVG.

예를 들어 사용자로 연결 sh하고 다음 쿼리를 실행한다고 가정합니다.

SELECT * 
FROM   sales
WHERE  cust_id IN ( SELECT cust_id 
                    FROM   customers );

열이 기본 키이기 때문에 customers.cust_id최적화 프로그램은 복잡한 쿼리를 동일한 데이터 반환이 보장되는 다음 조인 문으로 변환할 수 있습니다.

SELECT sales.* 
FROM   sales, customers
WHERE  sales.cust_id = customers.cust_id;

최적화 프로그램이 복잡한 문을 조인 문으로 변환할 수 없는 경우 상위 문과 하위 쿼리가 별도의 문인 것처럼 실행 계획을 선택합니다. 그런 다음 최적화 프로그램은 하위 쿼리를 실행하고 반환된 행을 사용하여 상위 쿼리를 실행합니다. 전체 실행 계획의 실행 속도를 향상시키기 위해 최적화 프로그램은 하위 계획을 효율적으로 정렬합니다.

1.4 구체화된 뷰를 사용한 쿼리 재작성[편집]

구체화 된 뷰는 테이블에 저장된 쿼리 결과입니다.

최적화 프로그램이 구체화된 뷰와 관련된 쿼리와 호환되는 사용자 쿼리를 찾으면 데이터베이스는 구체화된 뷰 측면에서 쿼리를 다시 작성할 수 있습니다. 이 기술은 데이터베이스가 대부분의 쿼리 결과를 미리 계산했기 때문에 쿼리 실행을 향상시킵니다.

최적화 프로그램은 사용자 쿼리와 호환되는 구체화된 뷰를 찾은 다음 비용 기반 알고리즘을 사용하여 구체화된 뷰를 선택하여 쿼리를 다시 작성합니다. 구체화된 뷰가 구체화된 뷰로 생성된 계획보다 비용이 낮지 않는 한 최적화 프로그램은 계획이 생성될 때 쿼리를 다시 작성하지 않습니다.

또한보십시오:

쿼리 재작성에 대해 자세히 알아보려면 Oracle Database Data Warehousing Guide를 참조하세요.

1.4.1 쿼리 재작성 및 최적화 프로그램 정보[편집]

쿼리는 쿼리 재작성 후보인지 여부를 결정하기 위해 여러 가지 검사를 거칩니다.

쿼리가 검사에 실패하면 구체화된 뷰가 아닌 세부 정보 테이블에 쿼리가 적용됩니다. 다시 쓸 수 없으면 응답 시간과 처리 능력 측면에서 비용이 많이 들 수 있습니다.

최적화 프로그램은 두 가지 다른 방법을 사용하여 구체화된 뷰 측면에서 쿼리를 다시 작성할 시기를 결정합니다. 첫 번째 방법은 쿼리의 SQL 텍스트를 구체화된 뷰 정의의 SQL 텍스트와 일치시킵니다. 첫 번째 방법이 실패하면 최적화 프로그램은 쿼리와 구체화된 뷰 간의 조인, 선택, 데이터 열, 그룹화 열 및 집계 함수를 비교하는 보다 일반적인 방법을 사용합니다.

쿼리 재작성은 다음 유형의 SQL 문의 쿼리 및 하위 쿼리에 대해 작동합니다.

SELECT

CREATE TABLE … AS SELECT

INSERT INTO … SELECT

또한 집합 연산자 UNION, UNION ALL, INTERSECT및 의 하위 쿼리 와 , 및 와 MINUS같은 DML 문의 하위 쿼리 에 대해서도 작동합니다 . INSERTDELETEUPDATE

차원, 제약 조건 및 다시 쓰기 무결성 수준은 구체화된 뷰를 사용하도록 쿼리를 다시 작성하는지 여부에 영향을 줍니다. REWRITE또한 쿼리 재작성은 힌트 NOREWRITE와 세션 매개변수를 통해 활성화하거나 비활성화할 수 있습니다 QUERY_REWRITE_ENABLED.

이 DBMS_MVIEW.EXPLAIN_REWRITE프로시저는 쿼리에서 쿼리 재작성이 가능한지 여부와 가능한 경우 어떤 구체화된 뷰가 사용되는지 조언합니다. 또한 쿼리를 다시 작성할 수 없는 이유도 설명합니다.

1.4.2 쿼리 재작성을 위한 초기화 매개변수 정보[편집]

쿼리 재작성 동작은 특정 데이터베이스 초기화 매개변수에 의해 제어됩니다.

표 5-1 쿼리 재작성 동작을 제어하는 초기화 매개변수

초기화 매개변수 이름 초기화 매개변수 값 쿼리 재작성 동작 OPTIMIZER_MODE ALL_ROWS(기본값), FIRST_ROWS또는FIRST_ROWS_n OPTIMIZER_MODE로 설정하면 최적화 FIRST_ROWS프로그램은 비용과 경험적 방법을 혼합하여 처음 몇 행의 빠른 전달을 위한 최상의 계획을 찾습니다. 로 설정하면 FIRST_ROWS_n최적화 프로그램은 비용 기반 접근 방식을 사용하고 최상의 응답 시간을 목표로 최적화하여 첫 번째 n행(여기서 n = 1, 10, 100, 1000)을 반환합니다.

QUERY_REWRITE_ENABLED TRUE(기본값), FALSE또는FORCE 이 옵션을 사용하면 최적화 프로그램의 쿼리 재작성 기능이 활성화되어 최적화 프로그램이 구체화된 뷰를 활용하여 성능을 향상할 수 있습니다. 로 설정되면 FALSE이 옵션은 최적화 프로그램의 쿼리 재작성 기능을 비활성화하고 재작성되지 않은 쿼리의 예상 쿼리 비용이 더 낮은 경우에도 구체화된 뷰를 사용하여 쿼리를 다시 작성하지 않도록 최적화 프로그램에 지시합니다.

로 설정하면 FORCE최적화 프로그램의 쿼리 재작성 기능이 활성화되고 재작성되지 않은 쿼리의 예상 쿼리 비용이 더 낮은 경우에도 구체화된 뷰를 사용하여 쿼리를 다시 작성하도록 최적화 프로그램에 지시합니다.

QUERY_REWRITE_INTEGRITY STALE_TOLERATED, TRUSTED또는 ENFORCED(기본값) 이 매개변수는 선택사항입니다. 그러나 설정된 경우 값은 초기화 매개변수 값 열에 지정된 값 중 하나여야 합니다.

기본적으로 무결성 수준은 으로 설정됩니다 ENFORCED. 이 모드에서는 모든 제약 조건을 검증해야 합니다. 따라서 를 사용하는 경우 ENABLE NOVALIDATE RELY특정 유형의 쿼리 재작성이 작동하지 않을 수 있습니다. 이 환경(제약조건이 검증되지 않은 환경)에서 쿼리 재작성을 활성화하려면 무결성 수준을 TRUSTED또는 와 같은 더 낮은 수준의 세분성으로 설정해야 합니다 STALE_TOLERATED.

1.4.3 쿼리 재작성의 정확성 정보[편집]

쿼리 재작성은 초기화 매개변수에 의해 제어되는 세 가지 수준의 재작성 무결성을 제공합니다 QUERY_REWRITE_INTEGRITY.

매개변수 에 설정할 수 있는 값은 QUERY_REWRITE_INTEGRITY다음과 같습니다.

ENFORCED

이것이 기본 모드입니다. ENABLED VALIDATED최적화 프로그램은 구체화된 뷰의 최신 데이터만 사용하고 기본 키, 고유 키 또는 외래 키 제약 조건을 기반으로 하는 관계만 사용합니다 .

TRUSTED

모드 에서 TRUSTED최적화 프로그램은 차원 및 제약 조건에 선언된 관계가 RELY올바른지 신뢰합니다. 이 모드에서 최적화 프로그램은 미리 작성된 구체화된 뷰 또는 뷰를 기반으로 하는 구체화된 뷰도 사용하며, 강제되는 관계뿐만 아니라 강제되지 않는 관계도 사용합니다. 또한 선언된 내용은 신뢰하지만 ENABLED VALIDATED기본 또는 고유 키 제약 조건과 차원을 사용하여 지정된 데이터 관계는 신뢰하지 않습니다. 이 모드는 더 뛰어난 쿼리 재작성 기능을 제공하지만 선언한 신뢰 관계가 올바르지 않은 경우 잘못된 결과가 발생할 위험도 있습니다.

STALE_TOLERATED

모드 에서 STALE_TOLERATED최적화 프로그램은 유효하지만 오래된 데이터와 새로운 데이터가 포함된 구체화된 뷰를 사용합니다. 이 모드는 최대 재작성 기능을 제공하지만 부정확한 결과가 생성될 위험이 있습니다.

재작성 무결성이 가장 안전한 수준인 으로 설정된 경우 ENFORCED최적화 프로그램은 적용된 기본 키 제약 조건과 참조 무결성 제약 조건만 사용하여 쿼리 결과가 세부 테이블에 직접 액세스할 때의 결과와 동일한지 확인합니다.

재작성 무결성이 이외의 수준으로 설정된 경우 ENFORCED재작성을 사용한 출력이 재작성이 없는 출력과 다를 수 있는 몇 가지 상황이 있습니다.

구체화된 뷰는 데이터의 마스터 복사본과 동기화되지 않을 수 있습니다. 이는 일반적으로 구체화된 뷰의 하나 이상의 세부 테이블에 대한 대량 로드 또는 DML 작업 이후 구체화된 뷰 새로 고침 절차가 보류 중이기 때문에 발생합니다. 일부 데이터 웨어하우스 사이트에서는 일부 구체화된 뷰가 특정 시간 간격으로 새로 고쳐지는 것이 일반적이므로 이러한 상황이 바람직합니다.

차원 개체에 의해 암시된 관계가 잘못되었습니다. 예를 들어 계층 구조의 특정 수준에 있는 값은 정확히 하나의 상위 값으로 롤업되지 않습니다.

미리 작성된 구체화된 뷰 테이블에 저장된 값이 올바르지 않을 수 있습니다.

적용되지 않은 테이블 또는 뷰 제약 조건으로 정의된 잘못된 데이터 관계로 인해 잘못된 대답이 발생할 수 있습니다.

QUERY_REWRITE_INTEGRITY초기화 매개변수 파일에서 설정하거나 또는 문을 사용하여 ALTER SYSTEM설정할 수 있습니다 ALTER SESSION.

1.4.4 쿼리 재작성 예[편집]

이 예에서는 구체화된 뷰를 사용한 쿼리 재작성의 강력한 기능을 보여줍니다.

cal_month_sales_mv매월 판매된 달러 금액의 집계를 제공하는 다음 구체화된 뷰를 고려하십시오 .

CREATE MATERIALIZED VIEW cal_month_sales_mv
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

보통 한 달 동안 매장의 판매량이 약 100만 건이라고 가정해 보겠습니다. 따라서 이 구체화된 집계 뷰에는 매월 판매된 달러 금액에 대해 미리 계산된 집계가 있습니다.

매달 매장에서 판매된 금액의 합계를 요청하는 다음 쿼리를 고려해 보세요.

SELECT t.calendar_month_desc, SUM(s.amount_sold)
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

이전의 구체화된 뷰 및 쿼리 재작성 기능이 없는 경우 Oracle Database는 테이블에 sales직접 액세스하여 판매 금액의 합계를 계산하여 결과를 반환해야 합니다. 여기에는 테이블에서 수백만 개의 행을 읽는 작업이 포함되며 sales, 이로 인해 디스크 액세스로 인해 쿼리 응답 시간이 항상 늘어납니다. 또한 쿼리의 조인은 수백만 행에서 조인을 계산해야 하므로 쿼리 응답 속도를 더욱 저하시킵니다.

구체화된 뷰가 있는 경우 cal_month_sales_mv쿼리 재작성은 이전 쿼리를 다음 쿼리로 투명하게 다시 작성합니다.

SELECT calendar_month, dollars
FROM cal_month_sales_mv;

구체화된 뷰에는 수십 개의 행만 cal_month_sales_mv있고 조인이 없기 때문에 Oracle 데이터베이스는 결과를 즉시 반환합니다.

1.5 STAR TRANSFORM (별 변환)[편집]

스타 변환은 스타 스키마에 있는 팩트 테이블의 전체 테이블 스캔을 방지하는 최적화 변환입니다.

1.5.1 스타 스키마 정보[편집]

스타 스키마는 데이터 를 FACT(사실)과 DEMESION(차원) 으로 나눕니다.
1. FACT 은 판매와 같은 이벤트의 측정값이며 일반적으로 숫자입니다.
2. DEMENSION(차원)은 날짜, 위치, 제품 등의 사실을 식별하는 범주입니다.
팩트 테이블에는 스키마 차원 테이블의 기본 키로 구성된 복합 키가 있습니다.
차원 테이블은 쿼리를 제한하는 값을 선택할 수 있는 조회 또는 참조 테이블 역할을 합니다.

다이어그램은 일반적으로 중앙 팩트 테이블을 차원 테이블에 연결하는 선으로 표시하여 별 모양을 제공합니다.

다음 그래픽은 sales사실 테이블과 products, times, customers및 channels차원 테이블로 표시됩니다.

그림 5-1 스타 스키마

그림 5-1에 대한 설명은 다음과 같습니다. "그림 5-1 스타 스키마"에 대한 설명 눈송이 스키마는 차원 테이블이 다른 테이블을 참조하는 스타 스키마입니다. 눈보라 스키마는 눈송이 스키마의 조합입니다.

또한보십시오:

스타 스키마에 대해 자세히 알아보려면 Oracle Database Data Warehousing Guide를 참조 하세요.

1.5.2 별 변환의 목적[편집]

팩트 테이블과 차원 테이블의 조인에서 별 변환은 팩트 테이블의 전체 스캔을 방지할 수 있습니다.
별형 변환은 제약 조건 차원 행에 조인되는 관련 팩트 행만 가져와서 성능을 향상시킵니다.
경우에 따라 쿼리에는 차원 테이블의 다른 열에 대한 제한적인 필터가 있습니다.
필터를 조합하면 데이터베이스가 팩트 테이블에서 처리하는 데이터 세트를 크게 줄일 수 있습니다.

1.5.3 별 변환이 작동하는 방식[편집]

스타 변환은 제약 조건 차원에 해당하는 비트맵 세미조인 술어 라고 하는 하위 쿼리 술어를 추가합니다 .

최적화 프로그램은 팩트 조인 열에 인덱스가 있을 때 변환을 수행합니다. AND하위 쿼리에서 제공하는 비트맵 및 키 값 작업을 구동함으로써 OR데이터베이스는 팩트 테이블에서 관련 행만 검색하면 됩니다. 차원 테이블의 조건자가 중요한 데이터를 필터링하는 경우 팩트 테이블의 전체 스캔보다 변환이 더 효율적일 수 있습니다.

데이터베이스가 팩트 테이블에서 관련 행을 검색한 후 데이터베이스는 원래 조건자를 사용하여 이러한 행을 차원 테이블에 다시 조인해야 할 수도 있습니다. 다음 조건이 충족되면 데이터베이스는 차원 테이블의 조인을 제거할 수 있습니다.

차원 테이블의 모든 조건자는 semijoin 하위 쿼리 조건자의 일부입니다.

하위 쿼리에서 선택한 열은 고유합니다.

SELECT차원 열은 목록, GROUP BY절 등에 없습니다 .

1.5.4 별 변환 제어[편집]

초기화 STAR_TRANSFORMATION_ENABLED매개변수는 별 변환을 제어합니다.

이 매개변수는 다음 값을 사용합니다.

true

옵티마이저는 사실 및 제약 차원 테이블을 자동으로 식별하여 별형 변환을 수행합니다. 옵티마이저는 변환된 계획의 비용이 대안보다 낮은 경우에만 별 변환을 수행합니다. 또한 최적화 프로그램은 구체화가 성능을 향상시킬 때마다 자동으로 임시 테이블 변환을 시도합니다( " 임시 테이블 변환: 시나리오 " 참조 ).

false(기본)

옵티마이저는 별 변환을 수행하지 않습니다.

TEMP_DISABLE

이 값은 true최적화 프로그램이 임시 테이블 변환을 시도하지 않는다는 점을 제외하면 와 동일합니다.

또한보십시오:

STAR_TRANSFORMATION_ENABLED초기화 매개변수 에 대해 알아보려면 Oracle Database Reference를 참조하세요.

1.5.5 별 변환: 시나리오[편집]

이 시나리오는 스타 쿼리의 스타 변환을 보여줍니다.

예 5-5 스타 쿼리

다음 쿼리는 1999년 1분기와 2분기 동안 캘리포니아의 모든 도시에서 총 인터넷 판매량을 찾습니다.

SELECT c.cust_city, 
       t.calendar_quarter_desc, 
       SUM(s.amount_sold) sales_amount
FROM   sales s, 
       times t, 
       customers c, 
       channels ch
WHERE  s.time_id = t.time_id
AND    s.cust_id = c.cust_id
AND    s.channel_id = ch.channel_id
AND    c.cust_state_province = 'CA'
AND    ch.channel_desc = 'Internet'
AND    t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY c.cust_city, t.calendar_quarter_desc;

샘플 출력은 다음과 같습니다.

CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
. . .

이 예에서는 sales가 팩트 테이블이고 다른 테이블은 차원 테이블입니다. 테이블 sales에는 제품 판매마다 행이 하나씩 포함되어 있으므로 수십억 개의 판매 기록이 포함될 수 있습니다. 그러나 특정 분기 동안 인터넷을 통해 캘리포니아 고객에게 판매되는 제품은 소수에 불과합니다.

예제 5-6 별 변환

이 예에서는 예 5-5 에 있는 쿼리의 별표 변환을 보여줍니다 . 변환은 의 전체 테이블 스캔을 방지합니다 sales.

SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM   sales s, times t, customers c
WHERE  s.time_id = t.time_id
AND    s.cust_id = c.cust_id
AND    c.cust_state_province = 'CA'
AND    t.calendar_quarter_desc IN ('1999-01','1999-02')
AND    s.time_id IN ( SELECT time_id
                      FROM   times 
                      WHERE  calendar_quarter_desc IN('1999-01','1999-02') )
AND    s.cust_id IN ( SELECT cust_id
                      FROM   customers
                      WHERE  cust_state_province='CA' )
AND    s.channel_id IN ( SELECT channel_id  
                         FROM   channels 
                         WHERE  channel_desc = 'Internet' )
GROUP BY c.cust_city, t.calendar_quarter_desc;

예 5-7 별 변환을 위한 부분 실행 계획

이 예에서는 예 5-6 의 별형 변환에 대한 실행 계획의 편집된 버전을 보여줍니다 .

26행에서는 sales테이블에 전체 테이블 스캔 대신 인덱스 액세스 경로가 있음을 보여줍니다. channels(라인 14), times(라인 19) 및 (라인 24) 의 하위 쿼리 결과로 생성된 각 키 값에 대해 데이터베이스는 팩트 테이블(라인 15, 20, 25) customers의 인덱스에서 비트맵을 검색합니다 .sales

비트맵의 각 비트는 사실 테이블의 행에 해당합니다. 하위 쿼리의 키 값이 팩트 테이블 행의 값과 같을 때 비트가 설정됩니다. 예를 들어 비트맵 101000... (타원은 나머지 행의 값이 임을 나타냄 0)에서 사실 테이블의 행 1과 3에는 하위 쿼리의 일치하는 키 값이 있습니다.

12, 17, 22행의 작업은 하위 쿼리의 키를 반복하고 해당 비트맵을 검색합니다. 예제 5-6 에서 하위 쿼리 는 customers주 또는 도가 인 고객의 ID를 찾습니다 CA. 비트맵이 테이블 하위 쿼리 의 101000... 고객 ID 키 값에 해당한다고 가정합니다 . 또한 하위 쿼리가 비트맵을 사용하여 키 값을 생성한다고 가정합니다 . 이는 의 행 2만 하위 쿼리의 일치하는 키 값을 가짐을 의미합니다. 103515customerscustomers103516010000...sales

데이터베이스는 OR각 하위 쿼리(라인 11, 16, 21)에 대한 비트맵을 연산자를 사용하여 병합합니다. 이 예에서 데이터베이스는 두 비트맵을 병합한 후 하위 쿼리 에 대한 customers단일 비트맵을 생성합니다 .111000...customers

101000...   # bitmap corresponding to key 103515
010000...   # bitmap corresponding to key 103516
---------
111000...   # result of OR operation

10행에서 데이터베이스는 AND병합된 비트맵에 연산자를 적용합니다. 데이터베이스가 모든 OR작업을 수행한 후 결과 비트맵은 다음과 같다고 channels가정 합니다. 100000...데이터베이스가 이 AND비트맵과 하위 쿼리의 비트맵에 대해 작업을 수행하는 경우 customers결과는 다음과 같습니다.

100000...   # channels bitmap after all OR operations performed
111000...   # customers bitmap after all OR operations performed
---------
100000...   # bitmap result of AND operation for channels and customers

9행에서 데이터베이스는 최종 비트맵의 해당 rowid를 생성합니다. 데이터베이스는 salesrowid를 사용하여 사실 테이블에서 행을 검색합니다(라인 26). 이 예에서 데이터베이스는 첫 번째 행에 해당하는 하나의 rowid만 생성하므로 전체 테이블을 스캔하는 대신 단일 행만 가져옵니다 sales.

---------------------------------------------------------------------------
| Id  | Operation                         | Name
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |    
|   1 |  HASH GROUP BY                    | 
|*  2 |   HASH JOIN                       |
|*  3 |    TABLE ACCESS FULL              | CUSTOMERS
|*  4 |    HASH JOIN                      | 
|*  5 |     TABLE ACCESS FULL             | TIMES 
|   6 |     VIEW                          | VW_ST_B1772830 
|   7 |      NESTED LOOPS                 | 
|   8 |       PARTITION RANGE SUBQUERY    |  
|   9 |        BITMAP CONVERSION TO ROWIDS|  
|  10 |         BITMAP AND                |
|  11 |          BITMAP MERGE             | 
|  12 |           BITMAP KEY ITERATION    | 
|  13 |            BUFFER SORT            |
|* 14 |             TABLE ACCESS FULL     | CHANNELS 
|* 15 |            BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX 
|  16 |          BITMAP MERGE             | 
|  17 |           BITMAP KEY ITERATION    |
|  18 |            BUFFER SORT            |
|* 19 |             TABLE ACCESS FULL     | TIMES 
|* 20 |            BITMAP INDEX RANGE SCAN| SALES_TIME_BIX
|  21 |          BITMAP MERGE             |
|  22 |           BITMAP KEY ITERATION    | 
|  23 |            BUFFER SORT            | 
|* 24 |             TABLE ACCESS FULL     | CUSTOMERS 
|* 25 |            BITMAP INDEX RANGE SCAN| SALES_CUST_BIX 
|  26 |       TABLE ACCESS BY USER ROWID  | SALES
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="C"."CUST_ID")
   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   4 - access("ITEM_2"="T"."TIME_ID")
   5 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' 
               OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  14 - filter("CH"."CHANNEL_DESC"='Internet')
  15 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  19 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' 
               OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  20 - access("S"."TIME_ID"="T"."TIME_ID")
  24 - filter("C"."CUST_STATE_PROVINCE"='CA')
  25 - access("S"."CUST_ID"="C"."CUST_ID")

Note
-----
   - star transformation used for this statement

1.5.6 임시 테이블 변환: 시나리오[편집]

이전 시나리오에서 최적화 프로그램은 테이블 이 외부에서 참조되지 않고 고유 하기 때문에 테이블을 channels테이블에 다시 조인하지 않습니다 .saleschannel_id

그러나 최적화 프로그램이 다시 조인을 제거할 수 없는 경우 데이터베이스는 비트맵 키 생성 및 다시 조인을 위해 차원 테이블을 다시 검색하지 않도록 하위 쿼리 결과를 임시 테이블에 저장합니다. 또한 쿼리가 병렬로 실행되면 데이터베이스는 결과를 구체화하여 각 병렬 실행 서버가 하위 쿼리를 다시 실행하는 대신 임시 테이블에서 결과를 선택할 수 있도록 합니다.

예 5-8 임시 테이블을 사용한 별 변환

이 예에서 데이터베이스는 하위 쿼리 결과를 customers임시 테이블로 구체화합니다.

SELECT t1.c1 cust_city, t.calendar_quarter_desc calendar_quarter_desc, 
       SUM(s.amount_sold) sales_amount
FROM   sales s, sh.times t, sys_temp_0fd9d6621_e7e24 t1 
WHERE  s.time_id=t.time_id
AND    s.cust_id=t1.c0
AND    (t.calendar_quarter_desc='1999-q1' OR t.calendar_quarter_desc='1999-q2')
AND    s.cust_id IN    ( SELECT t1.c0 
                         FROM   sys_temp_0fd9d6621_e7e24 t1 )
AND    s.channel_id IN ( SELECT ch.channel_id 
                         FROM   channels ch
                         WHERE  ch.channel_desc='internet' )
AND    s.time_id IN    ( SELECT t.time_id
                         FROM   times t
                         WHERE  t.calendar_quarter_desc='1999-q1'
                         OR     t.calendar_quarter_desc='1999-q2' )
GROUP BY t1.c1, t.calendar_quarter_desc

최적화 프로그램은 customers임시 테이블로 대체 sys_temp_0fd9d6621_e7e24하고 열에 대한 참조 cust_id와 cust_city임시 테이블의 해당 열로 대체합니다. 데이터베이스는 두 개의 열이 있는 임시 테이블을 생성합니다 (c0 NUMBER, c1 VARCHAR2(30)). 이 열은 테이블에 cust_id해당 합니다 . 데이터베이스는 이전 쿼리 실행 시작 시 다음 쿼리를 실행하여 임시 테이블을 채웁니다.cust_citycustomers

SELECT c.cust_id, c.cust_city FROM customers WHERE c.cust_state_province = 'CA'

예 5-9 임시 테이블을 사용한 별 변환 부분 실행 계획

다음 예에서는 예 5-8 의 쿼리에 대한 실행 계획의 편집된 버전을 보여줍니다 .

복사

| Id | Operation | Name

| 0 | SELECT STATEMENT | | 1 | TEMP TABLE TRANSFORMATION | | 2 | LOAD AS SELECT | |* 3 | TABLE ACCESS FULL | CUSTOMERS | 4 | HASH GROUP BY | |* 5 | HASH JOIN | | 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_C716F |* 7 | HASH JOIN | |* 8 | TABLE ACCESS FULL | TIMES | 9 | VIEW | VW_ST_A3F94988 | 10 | NESTED LOOPS | | 11 | PARTITION RANGE SUBQUERY | | 12 | BITMAP CONVERSION TO ROWIDS| | 13 | BITMAP AND | | 14 | BITMAP MERGE | | 15 | BITMAP KEY ITERATION | | 16 | BUFFER SORT | |* 17 | TABLE ACCESS FULL | CHANNELS |* 18 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | 19 | BITMAP MERGE | | 20 | BITMAP KEY ITERATION | | 21 | BUFFER SORT | |* 22 | TABLE ACCESS FULL | TIMES |* 23 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | 24 | BITMAP MERGE | | 25 | BITMAP KEY ITERATION | | 26 | BUFFER SORT | | 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_C716F |* 28 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | 29 | TABLE ACCESS BY USER ROWID | SALES

Predicate Information (identified by operation id):

  3 - filter("C"."CUST_STATE_PROVINCE"='CA')
  5 - access("ITEM_1"="C0")
  7 - access("ITEM_2"="T"."TIME_ID")
  8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR 
              "T"."CALENDAR_QUARTER_DESC"='1999-02'))
 17 - filter("CH"."CHANNEL_DESC"='Internet')
 18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
 22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR 
              "T"."CALENDAR_QUARTER_DESC"='1999-02'))
 23 - access("S"."TIME_ID"="T"."TIME_ID")
 28 - access("S"."CUST_ID"="C0")

계획의 1, 2, 3행에서는 customers하위 쿼리를 임시 테이블로 구체화합니다. 6행에서 데이터베이스는 하위 쿼리 대신 임시 테이블을 스캔하여 팩트 테이블에서 비트맵을 작성합니다. 27행에서는 를 스캔하는 대신 다시 조인하기 위해 임시 테이블을 스캔합니다 customers. customers데이터베이스는 임시 테이블을 구체화하는 동안 필터를 적용하므로 임시 테이블에 필터를 적용할 필요가 없습니다 .

5.7 메모리 내 집계(VECTOR GROUP BY) 인메모리 집계의 핵심 최적화는 스캔하는 동안 집계하는 것입니다.

일반적인 스타 쿼리에서와 같이 하나의 큰 테이블에서 여러 개의 작은 테이블로의 집계 및 조인과 관련된 쿼리 블록을 최적화하기 위해 변환에서는 KEY VECTOR및 VECTOR GROUP BY작업을 사용합니다. 이러한 작업은 조인 및 집계에 효율적인 메모리 내 배열을 사용하며, 기본 테이블이 메모리 내 열 형식 테이블일 때 특히 효과적입니다.

또한보십시오:

인메모리 집계에 대해 자세히 알아보려면 Oracle Database In-Memory 가이드를 참조하세요.

5.8 커서 기간 임시 테이블 쿼리의 중간 결과를 구체화하기 위해 Oracle 데이터베이스는 쿼리 컴파일 중에 메모리에 커서 기간 임시 테이블을 암시적으로 생성할 수 있습니다.

5.8.1 커서 기간 임시 테이블의 목적 복잡한 쿼리가 동일한 쿼리 블록을 여러 번 처리하는 경우가 있어 불필요한 성능 오버헤드가 발생합니다.

이러한 시나리오를 방지하기 위해 Oracle 데이터베이스는 쿼리 결과에 대한 임시 테이블을 자동으로 생성하고 커서가 있는 동안 메모리에 저장할 수 있습니다. WITH절 쿼리, 별 변환 및 그룹화 집합과 같은 복잡한 작업의 경우 이 최적화를 통해 반복적으로 사용되는 하위 쿼리에서 중간 결과의 구체화가 향상됩니다. 이러한 방식으로 커서 기간 임시 테이블은 성능을 향상시키고 I/O를 최적화합니다.

5.8.2 커서 기간 임시 테이블의 작동 방식 커서 정의 임시 테이블의 정의는 메모리에 상주합니다. 테이블 정의는 커서와 연관되어 있으며 커서를 실행하는 세션에만 표시됩니다.

커서 기간 임시 테이블을 사용할 때 데이터베이스는 다음 단계를 수행합니다.

커서 지속 시간 임시 테이블을 사용하는 계획을 선택합니다.

고유한 이름을 사용하여 임시 테이블을 생성합니다.

임시 테이블을 참조하도록 쿼리를 다시 작성합니다.

메모리가 남지 않을 때까지 데이터를 메모리에 로드합니다. 이 경우 디스크에 임시 세그먼트가 생성됩니다.

쿼리를 실행하여 임시 테이블에서 데이터를 반환합니다.

테이블을 잘라서 메모리와 디스크에 있는 임시 세그먼트를 해제합니다.

메모:커서 기간 임시 테이블의 메타데이터는 커서가 메모리에 있는 동안 메모리에 유지됩니다. 메타데이터는 데이터 사전에 저장되지 않습니다. 즉, 데이터 사전 뷰를 통해 볼 수 없습니다. 메타데이터를 명시적으로 삭제할 수는 없습니다. 앞의 시나리오는 메모리 가용성에 따라 달라집니다. 직렬 쿼리의 경우 임시 테이블은 PGA 메모리를 사용합니다.

커서 기간 임시 테이블의 구현은 정렬과 유사합니다. 더 이상 사용할 수 있는 메모리가 없으면 데이터베이스는 임시 세그먼트에 데이터를 씁니다. 커서 기간 임시 테이블의 경우 차이점은 다음과 같습니다.

데이터베이스는 행 소스가 더 이상 활성 상태가 아닐 때가 아니라 쿼리가 끝날 때 메모리와 임시 세그먼트를 해제합니다 .

데이터가 메모리와 임시 세그먼트 사이를 이동할 수 있는 정렬과 달리 메모리의 데이터는 메모리에 유지됩니다.

CURSOR DURATION MEMORY데이터베이스가 커서 기간 임시 테이블을 사용하는 경우 실행 계획에 키워드가 나타납니다.

5.8.3 커서 기간 임시 테이블: 예 동일한 하위 쿼리를 반복하는 쿼리 WITH는 커서 지속 시간 임시 테이블의 이점을 누릴 수 있는 경우가 있습니다.

다음 쿼리는 WITH절을 사용하여 세 개의 하위 쿼리 블록을 만듭니다.

복사 WITH

 q1 AS (SELECT department_id, SUM(salary) sum_sal FROM hr.employees GROUP BY department_id),
 q2 AS (SELECT * FROM q1),
 q3 AS (SELECT department_id, sum_sal FROM q1)

SELECT * FROM q1 UNION ALL SELECT * FROM q2 UNION ALL SELECT * FROM q3; 다음 샘플 계획은 변환을 보여줍니다.

복사 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'BASIC +ROWS +COST'));

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | |6 (100)| | 1 | TEMP TABLE TRANSFORMATION | | | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6606_1AE004 | | | | 3 | HASH GROUP BY | | 11 | 3 (34)| | 4 | TABLE ACCESS FULL | EMPLOYEES |107 | 2 (0) | | 5 | UNION-ALL | | | | | 6 | VIEW | | 11 | 2 (0) | | 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_1AE004 | 11 | 2 (0) | | 8 | VIEW | | 11 | 2 (0) | | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_1AE004 | 11 | 2 (0) | | 10 | VIEW | | 11 | 2 (0) | | 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_1AE004 | 11 | 2 (0) |

이전 계획의 TEMP TABLE TRANSFORMATION1단계에서는 데이터베이스가 쿼리를 실행하기 위해 커서 기간 임시 테이블을 사용했음을 나타냅니다. 2단계의 키워드 CURSOR DURATION MEMORY는 데이터베이스가 의 결과를 저장하기 위해 메모리(사용 가능한 경우)를 사용했음을 나타냅니다 SYS_TEMP_0FD9D6606_1AE004. 메모리를 사용할 수 없으면 데이터베이스는 임시 데이터를 디스크에 썼습니다.

5.9 테이블 확장 테이블 확장 에서 최적화 프로그램은 분할된 테이블의 대부분 읽기 부분에 인덱스를 사용하지만 테이블의 활성 부분에는 인덱스를 사용하지 않는 계획을 생성합니다.

5.9.1 테이블 확장의 목적 인덱스 기반 계획은 성능을 향상시킬 수 있지만 인덱스 유지 관리는 오버헤드를 발생시킵니다. 많은 데이터베이스에서 DML은 데이터의 작은 부분에만 영향을 미칩니다.

테이블 확장은 업데이트가 많은 테이블에 대해 인덱스 기반 계획을 사용합니다. 주로 읽기가 많은 데이터에만 인덱스를 생성하여 활성 데이터에 대한 인덱스 오버헤드를 제거할 수 있습니다. 이러한 방식으로 테이블 확장은 인덱스 유지 관리를 피하면서 성능을 향상시킵니다.

5.9.2 테이블 확장 작동 방식 테이블 파티셔닝을 통해 테이블 확장이 가능해집니다.

분할된 테이블에 로컬 인덱스가 있는 경우 최적화 프로그램은 해당 인덱스를 특정 파티션에 사용할 수 없는 것으로 표시할 수 있습니다. 실제로 일부 파티션은 인덱싱되지 않습니다.

UNION ALL테이블 확장에서 최적화 프로그램은 일부 하위 쿼리가 인덱싱된 파티션에 액세스하고 다른 하위 쿼리가 인덱싱되지 않은 파티션에 액세스하는 문으로 쿼리를 변환합니다 . 최적화 프로그램은 쿼리에서 액세스되는 모든 파티션에 대해 존재 여부에 관계없이 파티션에 사용 가능한 가장 효율적인 액세스 방법을 선택할 수 있습니다.

옵티마이저는 항상 테이블 확장을 선택하지 않습니다.

테이블 확장은 비용 기반입니다.

데이터베이스는 확장된 테이블의 각 파티션에 의 모든 분기에 걸쳐 한 번만 액세스하는 반면 UNION ALL, 데이터베이스가 여기에 조인하는 모든 테이블은 각 분기에서 액세스됩니다.

의미론적 문제로 인해 확장이 무효화될 수 있습니다.

예를 들어 외부 조인의 오른쪽에 나타나는 테이블은 테이블 확장에 유효하지 않습니다.

힌트 힌트를 사용하여 테이블 확장을 제어할 수 있습니다 EXPAND_TABLE. 힌트는 비용 기반 결정을 재정의하지만 의미 체계 확인은 재정의하지 않습니다.

또한보십시오:

" 힌트로 옵티마이저에 영향 주기 "

SQL 힌트에 대해 자세히 알아보려면 Oracle Database SQL 언어 참조를 참조 하세요.

5.9.3 테이블 확장: 시나리오 최적화 프로그램은 쿼리에 나타나는 조건자를 기반으로 각 테이블에서 액세스해야 하는 파티션을 추적합니다. 파티션 가지치기를 사용하면 최적화 프로그램이 테이블 확장을 사용하여 보다 최적의 계획을 생성할 수 있습니다.

가정

이 시나리오에서는 다음을 가정합니다.

sh.sales열 에서 범위로 분할된 테이블에 대해 스타 쿼리를 실행하려고 합니다 time_id.

테이블 확장의 이점을 확인하기 위해 특정 파티션에서 인덱스를 비활성화하려고 합니다.

테이블 확장을 사용하려면:

사용자 로 데이터베이스에 로그인합니다 sh.

다음 쿼리를 실행합니다.

복사 SELECT * FROM sales WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND prod_id = 38; 다음을 쿼리하여 계획을 설명합니다 DBMS_XPLAN.

복사 SET LINESIZE 150 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'BASIC,PARTITION')); Pstart다음 계획의 및 열에 표시된 대로 Pstop최적화 프로그램은 테이블의 28개 파티션 중 16개만 액세스해야 한다고 필터를 통해 결정합니다.

복사 Plan hash value: 3087065703

| 0| SELECT STATEMENT | | | | | 1| PARTITION RANGE ITERATOR | |13| 28 | | 2| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES |13| 28 | | 3| BITMAP CONVERSION TO ROWIDS | | | | |*4| BITMAP INDEX SINGLE VALUE |SALES_PROD_BIX|13| 28 |

Predicate Information (identified by operation id):

  4 - access("PROD_ID"=38)

최적화 프로그램은 액세스할 파티션을 결정한 후 해당 파티션 모두에서 사용할 수 있는 인덱스를 고려합니다. 이전 계획에서 최적화 프로그램은 sales_prod_bix비트맵 인덱스를 사용하기로 선택했습니다.

SALES_1995테이블 파티션 의 인덱스를 비활성화합니다 sales.

복사 ALTER INDEX sales_prod_bix MODIFY PARTITION sales_1995 UNUSABLE; 이전 DDL은 1996년 이전의 모든 매출을 포함하는 파티션 1의 인덱스를 비활성화합니다.

메모:뷰 를 쿼리하여 파티션 정보를 얻을 수 있습니다 USER_IND_PARTITIONS. 판매에 대한 쿼리를 다시 실행한 후 DBMS_XPLAN계획을 가져오기 위해 쿼리합니다.

출력에는 계획이 변경되지 않았음을 보여줍니다.

복사 Plan hash value: 3087065703

| 0| SELECT STATEMENT | | | | | 1| PARTITION RANGE ITERATOR | |13|28 | | 2| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES |13|28 | | 3| BITMAP CONVERSION TO ROWIDS | | | | |*4| BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX|13|28 |

Predicate Information (identified by operation id):

  4 - access("PROD_ID"=38)

비활성화된 인덱스 파티션은 쿼리와 관련이 없기 때문에 계획은 동일합니다. 쿼리가 액세스하는 모든 파티션이 인덱싱된 경우 데이터베이스는 인덱스를 사용하여 쿼리에 응답할 수 있습니다. 쿼리는 파티션 16부터 28까지만 액세스하므로 파티션 1의 인덱스를 비활성화해도 계획에는 영향을 미치지 않습니다.

SALES_Q4_2003쿼리가 액세스해야 하는 파티션인 파티션 28( )에 대한 인덱스를 비활성화합니다 .

복사 ALTER INDEX sales_prod_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; ALTER INDEX sales_time_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; 쿼리가 액세스해야 하는 파티션의 인덱스를 비활성화하면 쿼리는 더 이상 이 인덱스를 사용할 수 없습니다(테이블 확장 없이).

를 사용하여 계획을 쿼리합니다 DBMS_XPLAN.

다음 계획에서 볼 수 있듯이 최적화 프로그램은 인덱스를 사용하지 않습니다.

복사 Plan hash value: 3087065703

| 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ITERATOR | |13 | 28 | |*2 | TABLE ACCESS FULL | SALES |13 | 28 |

Predicate Information (identified by operation id):

  2 - access("PROD_ID"=38)

앞의 예에서 쿼리는 16개의 파티션에 액세스합니다. 이러한 파티션 중 15개에서는 인덱스를 사용할 수 있지만 최종 파티션에는 인덱스를 사용할 수 없습니다. 최적화 프로그램은 하나의 액세스 경로 또는 다른 액세스 경로를 선택해야 하기 때문에 파티션의 인덱스를 사용할 수 없습니다.

테이블 확장을 통해 최적화 프로그램은 원래 쿼리를 다음과 같이 다시 작성합니다.

복사 SELECT * FROM sales WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND time_id < TO_DATE('2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND prod_id = 38 UNION ALL SELECT * FROM sales WHERE time_id >= TO_DATE('2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND time_id < TO_DATE('2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND prod_id = 38; 앞의 쿼리에서 첫 번째 쿼리 블록은 UNION ALL인덱싱된 파티션에 액세스하고, 두 번째 쿼리 블록은 그렇지 않은 파티션에 액세스합니다. 두 개의 하위 쿼리를 통해 최적화 프로그램은 액세스되는 모든 파티션의 테이블 스캔을 사용하는 것보다 더 최적인 경우 첫 번째 쿼리 블록의 인덱스를 사용하도록 선택할 수 있습니다.

를 사용하여 계획을 쿼리합니다 DBMS_XPLAN.

계획은 다음과 같이 나타납니다.

복사 Plan hash value: 2120767686

| 0|SELECT STATEMENT | | | | | 1| VIEW |VW_TE_2 | | | | 2| UNION-ALL | | | | | 3| PARTITION RANGE ITERATOR | |13| 27| | 4| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|SALES |13| 27| | 5| BITMAP CONVERSION TO ROWIDS | | | | |*6| BITMAP INDEX SINGLE VALUE |SALES_PROD_BIX|13| 27| | 7| PARTITION RANGE SINGLE | |28| 28| |*8| TABLE ACCESS FULL |SALES |28| 28|

Predicate Information (identified by operation id):

  6 - access("PROD_ID"=38)
  8 - filter("PROD_ID"=38)

이전 계획에 표시된 대로 최적화 프로그램은 UNION ALL두 개의 쿼리 블록에 대해 를 사용합니다(2단계). 옵티마이저는 첫 번째 쿼리 블록에서 파티션 13~27에 액세스하기 위한 인덱스를 선택합니다(6단계). 파티션 28에 사용할 수 있는 인덱스가 없기 때문에 최적화 프로그램은 두 번째 쿼리 블록에서 전체 테이블 스캔을 선택합니다(8단계).

5.9.4 테이블 확장 및 별 변환: 시나리오 별형 변환을 사용하면 특정 유형의 쿼리를 통해 빅 팩트 테이블의 많은 부분에 액세스하지 않아도 됩니다.

스타 변환을 위해서는 활발하게 업데이트되는 테이블에서 오버헤드가 발생할 수 있는 여러 인덱스를 정의해야 합니다. 테이블 확장을 사용하면 최적화 프로그램이 테이블의 인덱스된 부분에서만 스타 변환을 고려할 수 있도록 비활성 파티션에만 인덱스를 정의할 수 있습니다.

가정

이 시나리오에서는 다음을 가정합니다.

" Star Transformation: Scenario " 에 사용된 것과 동일한 스키마를 쿼리합니다 .

sales시간으로 파티션을 나눈 테이블의 경우처럼 마지막 파티션이 활발하게 업데이트됩니다.

최적화 프로그램이 테이블 확장을 활용하기를 원합니다.

스타 쿼리에서 테이블 확장을 활용하려면 다음을 수행하십시오.

다음과 같이 마지막 파티션의 인덱스를 비활성화합니다.

복사 ALTER INDEX sales_channel_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; ALTER INDEX sales_cust_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; 다음 스타 쿼리를 실행합니다.

복사 SELECT t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c, channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc = 'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP BY t.calendar_quarter_desc; DBMS_XPLAN다음 계획을 보여주는 를 사용하여 커서를 쿼리합니다 .

복사

이전 계획에서는 테이블 확장을 사용합니다. UNION ALL마지막 파티션을 제외한 모든 파티션에 액세스하는 분기는 스타 변환을 사용합니다 . 파티션 28의 인덱스가 비활성화되었기 때문에 데이터베이스는 전체 테이블 스캔을 사용하여 최종 파티션에 액세스합니다.

5.10 죠인 인수분해 조인 분해 라고 알려진 비용 기반 변환에서 최적화 프로그램은 쿼리 분기의 일반적인 계산을 분해할 수 있습니다 UNION ALL.

5.10.1 죠인 분해의 목적 UNION ALL쿼리는 데이터베이스 애플리케이션, 특히 데이터 통합 애플리케이션에서 일반적입니다.

쿼리 의 분기는 UNION ALL동일한 기본 테이블을 참조하는 경우가 많습니다. 조인 분해가 없으면 최적화 프로그램은 쿼리의 각 분기를 UNION ALL독립적으로 평가하므로 데이터 액세스 및 조인을 포함한 반복적인 처리가 발생합니다. 조인 분해 변환은 UNION ALL분기 전체에서 공통 계산을 공유할 수 있습니다. 대규모 기본 테이블을 추가로 스캔하지 않으면 성능이 크게 향상될 수 있습니다.

5.10.2 조인 분해가 작동하는 방식 조인 인수분해는 여러 테이블과 두 개 이상의 UNION ALL분기를 인수분해할 수 있습니다.

결합 분해는 예제를 통해 가장 잘 설명됩니다.

예 5-10 UNION ALL 쿼리

다음 쿼리는 4개의 테이블( t1, t2, t3및 t4)과 2개의 UNION ALL분기에 대한 쿼리를 보여줍니다.

복사 SELECT t1.c1, t2.c2 FROM t1, t2, t3 WHERE t1.c1 = t2.c1 AND t1.c1 > 1 AND t2.c2 = 2 AND t2.c2 = t3.c2 UNION ALL SELECT t1.c1, t2.c2 FROM t1, t2, t4 WHERE t1.c1 = t2.c1 AND t1.c1 > 1 AND t2.c3 = t4.c3 앞의 쿼리에서 table은 필터 조건자 및 Join 조건자 처럼 t1두 분기 모두에 나타납니다 . 변환이 없으면 데이터베이스는 각 분기에 대해 한 번씩, 테이블에 대해 스캔 및 필터링을 두 번 수행해야 합니다 .UNION ALLt1.c1 > 1t1.c1 = t2.c1t1

예 5-11 인수분해된 쿼리

다음 쿼리는 조인 분해를 사용하여 예제 5-10 의 쿼리를 변환합니다 .

복사 SELECT t1.c1, VW_JF_1.item_2 FROM t1, (SELECT t2.c1 item_1, t2.c2 item_2

           FROM   t2, t3
           WHERE  t2.c2 = t3.c2 
           AND    t2.c2 = 2                 
           UNION ALL
           SELECT t2.c1 item_1, t2.c2 item_2
           FROM   t2, t4 
           WHERE  t2.c3 = t4.c3) VW_JF_1

WHERE t1.c1 = VW_JF_1.item_1 AND t1.c1 > 1 이 경우 테이블은 t1인수분해되므로 데이터베이스는 테이블 스캔과 필터링을 t1한 번만 수행합니다. 가 t1크면 이 인수분해를 통해 두 번 검색하고 필터링하는 데 드는 막대한 성능 비용을 피할 수 있습니다 t1.

메모:쿼리 의 분기에 UNION ALL함수를 사용하는 절이 있는 경우 DISTINCT조인 분해가 유효하지 않습니다. 5.10.3 인수분해 및 조인 순서: 시나리오 조인 인수분해는 조인 순서에 대한 더 많은 가능성을 만들 수 있습니다.

예 5-12 5개 테이블이 포함된 쿼리

다음 쿼리에서 view는 예제 5-10V 의 쿼리와 동일합니다 .

복사 SELECT * FROM t5, (SELECT t1.c1, t2.c2

           FROM   t1, t2, t3
           WHERE  t1.c1 = t2.c1 
           AND    t1.c1 > 1 
           AND    t2.c2 = 2 
           AND    t2.c2 = t3.c2 
           UNION ALL
           SELECT t1.c1, t2.c2
           FROM   t1, t2, t4
           WHERE  t1.c1 = t2.c1 
           AND    t1.c1 > 1 
           AND    t2.c3 = t4.c3) V

WHERE t5.c1 = V.c1 조인 분해 전에 데이터베이스는 t1, t2및 t3를 조인 해야 합니다 t5. 예제 5-13 뷰 V에서 t1 인수분해

다음 쿼리에 표시된 것처럼 조인 분해가 t1view에서 인수분해되면 데이터베이스는 .: 와 조인할 수 있습니다.Vt1t5

복사 SELECT * FROM t5, ( SELECT t1.c1, VW_JF_1.item_2

            FROM   t1, (SELECT t2.c1 item_1, t2.c2 item_2
                        FROM   t2, t3
                        WHERE  t2.c2 = t3.c2
                        AND    t2.c2 = 2
                        UNION ALL
                        SELECT t2.c1 item_1, t2.c2 item_2
                        FROM   t2, t4
                        WHERE  t2.c3 = t4.c3) VW_JF_1 
            WHERE  t1.c1 = VW_JF_1.item_1 
            AND    t1.c1 > 1 )

WHERE t5.c1 = V.c1 앞의 쿼리 변환은 새로운 조인 순서를 엽니다. 그러나 조인 분해에서는 특정 조인 순서가 적용됩니다. 예를 들어 이전 쿼리에서 테이블 t2및 는 view 에 있는 쿼리 t3의 첫 번째 분기에 나타납니다 . 데이터베이스는 뷰 내에 정의되지 않은 와 조인하기 전에 와 조인해야 합니다 . 부과된 조인 순서가 반드시 최상의 조인 순서가 아닐 수도 있습니다. 이러한 이유로 옵티마이저는 비용 기반 변환 프레임워크를 사용하여 조인 분해를 수행합니다. 옵티마이저는 조인 분해가 있거나 없는 계획의 비용을 계산한 다음 가장 저렴한 계획을 선택합니다. UNION ALLVW_JF_1t2t3t1VW_JF_1

예 5-14 뷰 정의가 제거된 뷰 V에서 t1 인수분해

다음 쿼리는 예제 5-13 의 쿼리와 동일 하지만 인수분해를 더 쉽게 볼 수 있도록 뷰 정의가 제거되었습니다.

복사 SELECT * FROM t5, (SELECT t1.c1, VW_JF_1.item_2

           FROM   t1, VW_JF_1
           WHERE  t1.c1 = VW_JF_1.item_1
           AND    t1.c1 > 1)

WHERE t5.c1 = V.c1 5.10.4 외부 조인 인수분해: 시나리오 데이터베이스는 외부 조인, 안티 조인 및 세미 조인의 조인 분해를 지원하지만 이러한 조인의 올바른 테이블에 대해서만 지원합니다.

UNION ALL예를 들어, 조인 인수분해는 인수분해를 통해 다음 쿼리를 변환할 수 있습니다 t2.

복사 SELECT t1.c2, t2.c2 FROM t1, t2 WHERE t1.c1 = t2.c1(+) AND t1.c1 = 1 UNION ALL SELECT t1.c2, t2.c2 FROM t1, t2 WHERE t1.c1 = t2.c1(+) AND t1.c1 = 2 다음 예에서는 변환을 보여줍니다. t2이제 테이블은 UNION ALL하위 쿼리 분기에 더 이상 표시되지 않습니다.

복사 SELECT VW_JF_1.item_2, t2.c2 FROM t2, (SELECT t1.c1 item_1, t1.c2 item_2

           FROM   t1
           WHERE  t1.c1 = 1
           UNION ALL
           SELECT t1.c1 item_1, t1.c2 item_2
           FROM   t1
           WHERE  t1.c1 = 2) VW_JF_1

WHERE VW_JF_1.item_1 = t2.c1(+)

DB CAFE

목차

1 쿼리 변환[편집]

1.1 OR Expansion[편집]

1.2 뷰머징 (View Merging)[편집]

1.2.1 뷰 병합의 쿼리 블록[편집]

1.2.2 단순 뷰 병합[편집]

1.2.2.1 select-project-join 뷰 병합[편집]

1.2.2.2 단순뷰 병합에 실패하는 경우[편집]

1.2.3 복합뷰 머징 (Complex View Merging)[편집]

1.3 PUSH SUBQ (술어 푸시)[편집]

1.4 구체화된 뷰를 사용한 쿼리 재작성[편집]

1.4.1 쿼리 재작성 및 최적화 프로그램 정보[편집]

1.4.2 쿼리 재작성을 위한 초기화 매개변수 정보[편집]

1.4.3 쿼리 재작성의 정확성 정보[편집]

1.4.4 쿼리 재작성 예[편집]

1.5 STAR TRANSFORM (별 변환)[편집]

1.5.1 스타 스키마 정보[편집]

1.5.2 별 변환의 목적[편집]

1.5.3 별 변환이 작동하는 방식[편집]

1.5.4 별 변환 제어[편집]

1.5.5 별 변환: 시나리오[편집]

1.5.6 임시 테이블 변환: 시나리오[편집]