Chapter05.Query Transformations

옵티마이저는 많은 쿼리 변환 기술을 사용합니다. 이 장에서는 가장 중요한 것을 설명합니다.
 
5.1 OR Expansion
OR 확장에서 옵티마이저는 최상위 레벨 분리를 포함하는 쿼리 블록을 두 개 이상의 분기를 포함하는 UNION ALL 쿼리의 형식으로 변환합니다. 옵티마이저는 disjunction을 해당 구성 요소로 분리 한 다음 각 구성 요소를 UNION ALL 쿼리의 분기와 연결하여 이 목표를 달성합니다. 
옵티마이저는 다양한 이유로 OR 확장을 선택할 수 있습니다.
예를 들어, CONCATENATION 곱을 피하는 보다 효율적인 액세스 경로 또는 대체 조인 메소드를 사용할 수 있습니다. 항상 그렇듯이 옵티마이저는 변환된 명령문의 원가가 원래 명령문의 원가보다 낮을 경우에만 확장을 수행합니다. 이전 릴리스에서 옵티마이저는 CONCATENATION 연산자를 사용하여 OR 확장을 수행했습니다.
Oracle Database 12c Release 2 (12.2)부터 옵티마이저는 UNION-ALL 연산자를 대신 사용합니다.
프레임 워크는 다음과 같은 향상된 기능을 제공합니다.
• 다양한 변환 간의 상호 작용을 가능하게 합니다.
• 쿼리 구조 공유 방지
• 다양한 검색 전략을 탐구 할 수 있습니다.
• 비용 주석 재사용 제공
• 표준 SQL 구문 지원
 
Example 5-1 Transformed Query: UNION ALL Condition
이 예제를 준비하려면 데이터베이스로 관리자로 로그인하고 다음 명령문을 실행하여 hr.departments.department_name 열에 고유 제한 조건을 추가 한 다음 hr.employees 테이블에 100,000 개의 행을 추가하십시오.
 
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로 연결하고 employees 및 departments 테이블을 조인하는 다음 쿼리를 실행합니다.
 
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'또는 d.department_name = 'Treasury'를 단일 단위로 취급합니다. 따라서 최적화 프로그램은 e.email 또는 d.department_name 열의 인덱스를 사용할 수 없으므로 직원 및 부서의 전체 테이블 검색을 수행합니다. 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 열을 색인 키로 사용할 수 있습니다.
다음 실행 계획에 표시된 것처럼 데이터베이스가 두 개의 전체 테이블 검색 대신 두 개의 고유 인덱스를 사용하여 데이터를 필터링하기 때문에 성능이 향상됩니다.
 
5.2 View Merging
뷰 병합에서 옵티마이저는 뷰를 나타내는 쿼리 블록을 포함하는 쿼리 블록에 뷰를 병합합니다. 보기 병합은 옵티마이저가 추가 조인 순서, 액세스 방법 및 기타 변환을 고려하도록하여 계획을 향상시킬 수 있습니다. 예를 들어 보기가 병합되고 여러 테이블이 하나의 쿼리 블록에 있는 경우 뷰 내부의 테이블은 옵티마이저가 조인 제거를 사용하여 뷰 외부의 테이블을 제거하도록 허용 할 수 있습니다. 병합이 항상 더 나은 계획으로 이어지는 단순한 보기의 경우 옵티마이저는 비용을 고려하지 않고 보기를 자동으로 병합합니다. 그렇지 않으면 옵티마이저는 비용을 사용하여 결정합니다. 옵티마이저는 비용이나 유효성 제한을 포함하여 여러 가지 이유로 뷰를 병합하지 않을 수도 있습니다.
OPTIMIZER_SECURE_VIEW_MERGING이 true(기본값)면 Oracle Database는 뷰 병합 및 조건부 푸시가 뷰 작성자의 보안 의도를 위반하지 않는지 확인하기 위해 검사를 수행합니다. 특정 보기에 대해 이러한 추가 보안 검사를 사용하지 않으려면 이 보기에 대해 MERGE VIEW 권한을 사용자에게 부여 할 수 있습니다. 특정 사용자에 대한 모든 보기에 대한 추가 보안 검사를 사용하지 않으려면 해당 사용자에게 MERGE ANY VIEW 권한을 부여 할 수 있습니다.
 
Note
힌트를 사용하여 비용 또는 경험적 방법으로 인해 거부 된 뷰 병합을 무시할 수 있지만 유효 하지는 않습니다.
 
참조:
• MERGE ANY VIEW 및 MERGE VIEW 권한에 대한 자세한 내용은 Oracle Database SQL Language Reference를 참조하십시오.
• OPTIMIZER_SECURE_VIEW_MERGING 초기화 매개 변수에 대한 자세한 내용은 Oracle Database Reference를 참조하십시오.
 
5.2.1 Query Blocks in View Merging
옵티마이저는 별도의 쿼리 블록을 통해 각각의 중첩된 하위 쿼리 또는 병합되지 않은 뷰를 나타냅니다. 데이터베이스는 쿼리 블록을 아래에서 위로 개별적으로 최적화합니다. 따라서 데이터베이스는 가장 안쪽의 쿼리 블록을 먼저 최적화하고 계획의 일부를 생성한 다음 전체 쿼리를 나타내는 외부 쿼리 블록에 대한 계획을 생성합니다. 구문 분석기는 쿼리에서 참조하는 각 뷰를 별도의 쿼리 블록으로 확장합니다. 블록은 본질적으로 뷰 정의를 나타내며 따라서 뷰의 결과를 나타냅니다. 최적화 프로그램의 한 가지 옵션은 뷰 쿼리 블록을 개별적으로 분석하고 뷰 하위 계획을 생성 한 다음 뷰 하위 계획을 사용하여 전체 실행 계획을 생성하여 나머지 쿼리를 처리하는 것입니다. 그러나 이 기술은 보기가 개별적으로 최적화되므로 차선 실행 계획으로 이어질 수 있습니다. 병합 보기는 때로 성능을 향상시킬 수 있습니다.
예제 5-2에서 볼 수 있듯이 뷰 병합은 뷰의 테이블을 외부 쿼리 블록으로 병합하여 내부 쿼리 블록을 제거합니다. 따라서 뷰를 별도로 최적화 할 필요가 없습니다.
 
5.2.2 Simple View Merging
단순 보기 병합에서 옵티마이저는 select-project-join 뷰를 병합합니다. 예를 들어, employees 테이블의 쿼리에는 departments 및 locations 테이블을 조인하는 하위 쿼리가 들어 있습니다. 단순 보기 병합은 병합 후에 사용 가능한 추가 조인 순서 및 액세스 경로로 인해 보다 최적의 계획을 생성합니다.
다음과 같은 이유로 보기가 단순보기 병합에 유효하지 않을 수 있습니다.
• 뷰에는 다음을 포함하여 select-project-join 뷰에 포함되지 않은 구문이 포함되어 있습니다.
– GROUP BY
– DISTINCT
– Outer join
– MODEL
– CONNECT BY
– Set operators
– Aggregation
• 뷰는 세미 조인 또는 안티 조인의 오른쪽에 나타납니다.
• 뷰에 SELECT 목록의 하위 쿼리가 포함되어 있습니다.
• 외부 질의 블록은 PL/SQL 함수를 포함합니다.
• 뷰는 외부 조인에 참여하며 뷰를 병합 할 수 있는지 여부를 결정하는 몇 가지 추가 유효성 요구 사항 중 하나를 충족하지 않습니다.
 
Example 5-2 Simple View Merging
다음 쿼리는 각 부서의 주소를 반환하는 dept_locs_v 뷰와 함께 hr.employees 테이블을 조인합니다. dept_locs_v는 departments 및 locations 테이블의 조인입니다.
 
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';
 
 
데이터베이스는 부서 및 위치를 결합하여 뷰의 행을 생성 한 다음이 결과를 직원들과 조인하여 이전 쿼리를 실행할 수 있습니다.
쿼리에 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
조인 메소드도 제한됩니다. 이 뷰의 열에 인덱스가 없기 때문에 직원으로 시작하는 조인 순서의 경우 인덱스 기반 중첩 루프 조인을 수행 할 수 없습니다. 보기 병합이 없으면 최적화 프로그램은 다음 실행 계획을 생성합니다.
 
 
뷰 병합은 뷰의 테이블을 외부 쿼리 블록으로 병합하여 내부 쿼리 블록을 제거합니다. 뷰 병합 후 쿼리는 다음과 같습니다.
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';
 
3개의 테이블 모두가 하나의 쿼리 블록에 나타나므로 옵티마이저는 다음과 같은 6개의 조인 순서 중에서 선택할 수 있습니다.
• employees, departments, locations
• employees, locations, departments
• departments, employees, locations
• departments, locations, employees
• locations, employees, departments
• locations, departments, employees
이제 직원 및 부서에 대한 조인을 인덱스 기반으로 할 수 있습니다. 뷰 병합 후에 옵티마이저는 중첩 루프를 사용하는 보다 효율적인 다음 플랜을 선택합니다.
 
참조:
간단한 뷰 병합의 특수한 경우 인 외부 조인 뷰 병합에 대해 알아 보려면 Oracle Optimizer 블로그(https://blogs.oracle.com/optimizer/)를 참조하십시오.
 
5.2.3 Complex View Merging
복잡한 뷰 병합에서 옵티마이저는 GROUP BY 및 DISTINCT 뷰가 포함 된 뷰를 병합합니다. 단순 보기 병합과 같이 복잡한 병합을 사용하면 옵티마이저가 추가 조인 순서 및 액세스 경로를 고려할 수 있습니다. 옵티마이저는 조인을 평가할 때까지 GROUP BY 또는 DISTINCT 연산의 평가를 지연시킬 수 있습니다. 이러한 작업을 지연하면 데이터 특성에 따라 성능을 향상 시키거나 악화시킬 수 있습니다. 조인에서 필터를 사용하는 경우 조인 후까지 조작을 지연시키면 조작이 수행 될 데이터 세트가 줄어들 수 있습니다. 조작을 조기에 평가하면 후속 조인에서 처리 할 데이터의 양이 줄어들거나 조인이 조작에 의해 처리 될 데이터의 양을 늘릴 수 있습니다. 옵티마이저는 비용을 사용하여 뷰 병합을 평가하고 낮은 비용 옵션인 경우에만 뷰를 병합합니다. 비용 외에도 최적화 프로그램은 다음과 같은 이유로 복잡한 보기 병합을 수행하지 못할 수 있습니다.
• The outer query tables do not have a rowid or unique column.
• The view appears in a CONNECT BY query block.
• The view contains GROUPING SETS, ROLLUP, or PIVOT clauses.
• The view or outer query block contains the MODEL clause.
 
Example 5-3 Complex View Joins with 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 테이블에 적용됩니다. 변환 된 쿼리에서 제품 및 고객에 대한 조인은 판매 테이블에서 행의 많은 부분을 필터링하므로 GROUP BY 연산은 비용이 적습니다. GROUP BY 연산은 원래 쿼리에서 행 집합의 크기를 크게 줄이지 않기 때문에 sales 테이블이 축소되지 않았으므로 조인이 더 비쌉니다. 위의 특성 중 하나라도 변경되면 뷰를 병합하는 것이 더 이상 비용이 적게 드는 일은 아닙니다. 최종 계획에는 보기가 포함되지 않지만 다음과 같습니다.
 
Example 5-4 Complex View Joins with 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;
위 쿼리의 계획은 다음과 같습니다.
 
 
위의 계획에는 보기 병합이 발생한 후에도 프로젝션 보기로 알려진 vm_nwvw_1이라는 보기가 포함되어 있습니다. 투영 뷰는 DISTINCT 뷰가 병합된 쿼리에 나타나거나 GROUP BY 뷰가 GROUP BY, HAVING 또는 집계를 포함하는 외부 쿼리 블록에 병합됩니다. 후자의 경우 투영 뷰에는 원본 외부 쿼리 블록의 GROUP BY, HAVING 및 집계가 포함됩니다. 위의 프로젝션 보기의 예에서 옵티마이저는 뷰를 병합 할 때 DISTINCT 연산자를 외부 쿼리 블록으로 이동 한 다음 몇 가지 추가 열을 추가하여 원래 쿼리와의 의미론적 동일성을 유지합니다. 그런 다음 쿼리는 외부 쿼리 블록의 SELECT 목록에서 원하는 열만 선택할 수 있습니다.
최적화는 뷰 병합의 모든 이점을 유지합니다. 모든 테이블은 하나의 쿼리 블록에 있고, 옵티마이 저는 최종 조인 순서에서 필요에 따라 순서를 바꿀 수 있으며 DISTINCT 연산은 모든 조인이 완료 될 때까지 지연되었습니다.

 

5.3 Predicate Pushing
predicate pushing에서 옵티마이저는 포함하는 쿼리 블록의 관련 술어를 뷰 쿼리 블록으로 "푸시(push)"합니다. 병합되지 않은 보기의 경우 이 기술은 데이터베이스가 밀어 넣기 조건자를 사용하여 색인에 액세스하거나 필터로 사용할 수 있기 때문에 병합되지 않은 보기의 하위 계획을 향상시킵니다. 예를 들어 다음과 같이 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);
 
employee 및 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 Subquery Unnesting
하위 쿼리 중첩에서 옵티마이저는 중첩 쿼리를 동등한 조인 문으로 변환 한 다음 조인을 최적화합니다. 이 변환을 통해 옵티마이저가 액세스 경로, 조인 방법 및 조인 순서 선택 중에 하위 쿼리 테이블을 고려할 수 있습니다. 옵티마이저가 결과 조인 문에서 원래 명령문과 동일한 행을 반환하도록 보장되고 하위 쿼리에 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;
 
옵티마이저가 복잡한 명령문을 조인 명령문으로 변환 할 수 없는 경우, 별도의 명령문인 것처럼 부모 명령문 및 하위 명령문에 대한 실행 계획을 선택합니다. 그런 다음 옵티마이저는 부속 조회를 실행하고 리턴 된 행을 사용하여 상위 조회를 실행합니다. 전체 실행 계획의 실행 속도를 향상시키기 위해 최적화 프로그램은 하위 계획을 효율적으로 정렬합니다.
 
5.5 Query Rewrite with Materialized Views
구체화 된 보기는 데이터베이스가 구체화하여 테이블에 저장하는 조회 결과입니다. 옵티마이저가 구체화 된 뷰와 연관된 쿼리와 호환되는 사용자 쿼리를 발견하면 데이터베이스는 구체화 된 뷰 측면에서 쿼리를 다시 작성할 수 있습니다. 이 기술은 데이터베이스가 대부분의 쿼리 결과를 미리 계산했기 때문에 쿼리 실행을 향상시킵니다. 옵티마이저는 사용자 쿼리와 호환되는 구체화 된 뷰를 찾은 다음 하나 이상의 구체화 된 뷰를 선택하여 사용자 쿼리를 다시 작성합니다.
Materialized View를 사용하여 쿼리를 다시 작성하는 것은 비용 기반입니다. 따라서 구체화 된 뷰가 구체화 된 뷰로 생성된 계획보다 비용이 낮지 않으면 계획이 생성 될 때 옵티마이저가 쿼리를 다시 작성하지 않습니다. 매달 팔리는 달러 금액을 집계하는 다음의 구체화 된 보기, 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;
전형적인 한 달에 판매량이 약 백만 달러라고 가정합니다. 이 뷰에는 매월 판매된 달러 금액에 대한 미리 계산 된 집계가 있습니다. 매월 판매되는 금액의 합계를 묻는 다음 쿼리를 고려해보십시오.
 
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;
 
쿼리 재 작성이 없으면 데이터베이스는 판매에 직접 액세스하여 판매 된 금액의 합계를 계산 해야 합니다. 이 방법은 매출에서 수백만 행을 읽는 작업으로 쿼리 응답 시간을 늘립니다. 또한 데이터베이스는 수 백만 행의 조인을 계산해야하기 때문에 조인은 쿼리 응답을 더욱 지연시킵니다. 쿼리 재 작성을 사용하면 옵티마이저는 다음과 같이 쿼리를 투명하게 다시 작성합니다.
 
SELECT calendar_month, dollars
FROM cal_month_sales_mv;
참조:
쿼리 재 작성에 대한 자세한 내용은 Oracle Database 데이터웨어 하우징 가이드를 참조하십시오.
 
5.6 Star Transformation
Star transformation은 스타 스키마의 사실 테이블에 대한 전체 테이블 스캔을 피하는 옵티마이저 변환입니다.
 
5.6.1 About Star Schemas
스타 스키마는 데이터를 사실과 차원으로 나눕니다. 사실은 판매와 같은 이벤트의 측정이며 일반적으로 숫자입니다. 측정 기준은 날짜, 위치 및 제품과 같은 사실을 식별하는 카테고리입니다. 사실 테이블에는 스키마의 차원 테이블의 기본 키로 구성된 복합 키가 있습니다. 차원 테이블은 조회를 제한하는 값을 선택할 수 있도록 조회 또는 참조 테이블의 역할을 합니다. 다이어그램은 일반적으로 차원 테이블에 조인하는 라인이 있는 중앙 팩트 테이블을 보여 주며 별 모양을 나타냅니다. 다음 그래픽은 영업을 사실 테이블로, 제품, 시간, 고객 및 채널을 차원 테이블로 표시합니다.
 
눈송이 스키마는 차원 테이블이 다른 테이블을 참조하는 스타 스키마입니다. 눈보라 스키마는 눈송이 스키마의 조합입니다.
 
참조:
스타 스키마에 대한 자세한 내용은 Oracle Database 데이터웨어 하우징 안내서를 참조하십시오.
 
5.6.2 Purpose of Star Transformations
사실 테이블과 차원 테이블의 조인에서 별 변환은 사실 테이블의 전체 스캔을 피할 수 있습니다. Star Transformations은 제한 조건 차원 행에 결합하는 관련 사실 행만 반입하여 성능을 향상시킵니다. 경우에 따라 쿼리에 차원 테이블의 다른 열에 제한적인 필터가 있습니다. 필터를 조합하면 데이터베이스가 사실 테이블에서 처리하는 데이터 세트를 크게 줄일 수 있습니다.
5.6.3 How Star Transformation Works
Star Transformations은 제한 조건 차원에 해당하는 비트 맵 세미 조인 술어라고하는 하위 쿼리 술어를 추가합니다. 옵티마이저는 사실 조인 컬럼에 인덱스가 있을 때 변환을 수행합니다. 하위 쿼리가 제공하는 키 값의 비트 맵 AND 및 OR 연산을 수행하면 데이터베이스는 팩트 테이블에서 관련 행을 검색하면 됩니다. 차원 테이블의 술어가 중요한 데이터를 필터링하면 사실 테이블의 전체 스캔보다 변환이 더 효율적일 수 있습니다. 데이터베이스가 사실 테이블에서 관련 행을 검색하면 데이터베이스는 원래 술어를 사용하여 이 행을 차원 테이블에 다시 결합해야 합니다.
데이터베이스는 다음 조건이 충족 될 때 차원 테이블 조인을 제거 할 수 있습니다.
• 차원 테이블의 모든 조건 자 (predicates)는 semijoin 하위 쿼리 조건 자의 일부입니다.
• 하위 쿼리에서 선택한 열은 고유합니다.
• 차원 열은 SELECT 목록, GROUP BY 절 등에 없습니다.
 
5.6.4 Controls for Star Transformation
STAR_TRANSFORMATION_ENABLED 초기화 매개 변수는 Star Transformations을 제어합니다.
이 매개 변수에는 다음 값이 사용됩니다.
• true
옵티마이저는 사실 및 제한 조건 차원 테이블을 자동으로 식별하여 Star Transformations을 수행합니다. 옵티마이저는 변환 된 계획의 비용이 대안보다 낮은 경우에만 Star Transformations을 수행합니다. 또한 구체화가 성능을 향상시킬 때마다 최적화 알고리즘이 임시 테이블 변환을 자동으로 시도합니다("임시 테이블 변환: 시나리오"참조).
• false (default)
최적화 프로그램은 Star Transformations을 수행하지 않습니다.
• TEMP_DISABLE
옵티마이저가 임시 테이블 변환을 시도하지 않는다는 점을 제외하면 이 값은 true와 동일합니다.
 
참조:
STAR_TRANSFORMATION_ENABLED 초기화 매개 변수에 대해 배우려면 Oracle Database Reference를 참조하십시오.
 
5.6.5 Star Transformation: Scenario
이 시나리오는 스타 쿼리의 스타 변환을 보여줍니다.
Example 5-5 Star Query
다음 쿼리는 1999 년 1/4 분기와 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;
샘플 출력은 다음과 같습니다.
 
이 예에서 sales는 팩트 테이블이고 다른 테이블은 차원 테이블입니다. 판매 테이블에는 제품 판매에 대해 하나의 행이 포함되어 있으므로 수십억 개의 판매 기록이 포함될 수 있습니다. 그러나 특정 분기 동안 인터넷을 통해 캘리포니아의 고객에게 판매되는 제품은 극히 일부에 지나지 않습니다.
 
Example 5-6 Star Transformation
이 예제는 예제 5-5에 있는 쿼리의 Star Transformations을 보여줍니다. 이 변환으로 전체 테이블 스캔을 피할 수 있습니다.
 
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;
 
Example 5-7 Partial Execution Plan for Star Transformation
이 예는 Example 5-6에서 Star Transformations에 대한 실행 계획의 편집된 버전을 보여줍니다. 26행에서는 sales 테이블이 전체 테이블 스캔 대신 인덱스 액세스 경로를 가지고 있음을 보여줍니다. 채널 하위 쿼리(14번째 줄), 시간(19번째 줄) 및 고객(24 번째 줄)의 결과인 각 키 값에 대해 데이터베이스는 영업 팩트 테이블(15, 20, 25 행)의 인덱스에서 비트 맵을 검색합니다. 비트 맵의 각 비트는 사실 테이블의 한 행에 해당합니다. 서브 쿼리의 키 값이 사실 테이블 행의 값과 같을 때 비트가 설정됩니다. 예를 들어 비트 맵 101000... (나머지 줄의 값이 0임을 나타내는 타원)은 팩트 테이블의 행 1과 3에 하위 쿼리의 키 값이 일치합니다. 12, 17 및 22행의 작업은 하위 쿼리의 키를 반복하고 해당 비트 맵을 검색합니다. 예제 5-6에서 customers 하위 쿼리는시/도가 CA 인 고객의 ID를 찾습니다. 비트 맵 101000 ...이 customers 테이블 하위 쿼리의 고객 ID 키 값 103515에 해당한다고 가정합니다. 또한 customers 하위 쿼리가 비트 맵 010000 ...이 있는 스타 변환 키 값 103516을 생성한다고 가정합니다. 이는 판매에서 행 2만 하위 쿼리의 키 값과 일치한다는 것을 의미합니다. 데이터베이스는 각 하위 쿼리(행 11, 16, 21)의 비트 맵을 (OR 연산자를 사용하여) 병합합니다. 고객 예제에서 데이터베이스는 두 비트 맵을 병합 한 후 customers 하위 쿼리에 대해 단일 비트 맵 111000 ...을 생성합니다.
101000... # bitmap corresponding to key 103515
010000... # bitmap corresponding to key 103516
---------
111000... # result of OR operation
10행에서 데이터베이스는 병합 된 비트 맵에 AND 연산자를 적용합니다. 데이터베이스가 모든 OR 연산을 수행 한 후에 채널에 대한 결과 비트 맵이 100000이라고 가정하십시오. 데이터베이스가 이 비트 맵과 customers 하위 쿼리의 비트 맵에서 AND 연산을 수행하면 결과는 다음과 같습니다.
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를 생성합니다. 데이터베이스는 ROWID(행 26)를 사용하여 판매 사실 테이블에서 행을 검색합니다. 이 예에서 데이터베이스는 첫 번째 행에 해당하는 하나의 rowid만 생성하므로 전체 sales 테이블을 스캔하는 대신 단일 행만 가져옵니다.
 
 
5.6.6 Temporary Table Transformation: Scenario
예제 5-7에서 외부에서 참조되지 않고 channel_id가 고유하므로 옵티마이저는 테이블 채널을 판매 테이블에 다시 결합하지 않습니다. 그러나 옵티마이저가 조인 백을 제거 할 수 없는 경우 데이터베이스는 비트 맵 키 생성을 위해 차원 테이블을 다시 스캔하고 다시 조인하지 않도록 임시 테이블에 하위 쿼리 결과를 저장합니다. 또한 쿼리가 병렬로 실행되면 데이터베이스는 각 병렬 실행 서버가 하위 쿼리를 다시 실행하는 대신 임시 테이블에서 결과를 선택할 수 있도록 결과를 구체화합니다.
 
Example 5-8 Star Transformation Using Temporary Table
이 예에서 데이터베이스는 고객의 하위 쿼리 결과를 임시 테이블로 구체화합니다.
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;
 
최적화 프로그램은 고객을 임시 테이블 sys_temp_0fd9d6621_e7e24로 대체하고 cust_id 및 cust_city 열에 대한 참조를 임시 테이블의 해당 열로 바꿉니다. 데이터베이스는(c0 NUMBER, c1 VARCHAR2 (30)) 두 개의 컬럼으로 임시 테이블을 작성합니다. 이 열은 customers 테이블의 cust_id 및 cust_city에 해당합니다. 데이터베이스는 이전 쿼리의 실행 시작 부분에서 다음 쿼리를 실행하여 임시 테이블을 채웁니다.
 
SELECT c.cust_id, c.cust_city FROM customers WHERE c.cust_state_province = 'CA';
Example 5-9 Partial Execution Plan for Star Transformation Using Temporary Table 
다음 예는 예제 5-8의 쿼리에 대한 편집 된 실행 계획 버전을 보여줍니다.
 
계획의 1, 2, 3 라인은 임시 테이블로 고객 부속 조회를 구체화합니다. 6행에서 데이터베이스는 하위 테이블 대신 임시 테이블을 검색하여 팩트 테이블에서 비트 맵을 작성합니다. 라인 27은 임시 테이블을 스캔하여 고객을 검색하는 대신 다시 결합합니다. 임시 테이블을 구체화하는 동안 필터가 적용되므로 데이터베이스는 임시 테이블의 고객에게 필터를 적용 할 필요가 없습니다.
 

5.7 In-Memory Aggregation (VECTOR GROUP BY)
메모리 내 집계의 핵심 최적화는 스캔하는 동안 집계하는 것입니다. 집계를 포함하는 쿼리 블록을 최적화하고 하나의 대형 테이블에서 여러 개의 작은 테이블(예: 일반적인 별 조회)을 조인 할 때 변환은 KEY VECTOR 및 VECTOR GROUP BY 조작을 사용합니다. 이러한 연산은 조인 및 집계에 효율적인 메모리 배열을 사용하며 기본 테이블이 메모리 내주(in-memory) 컬럼 테이블인 경우 특히 효과적입니다.
 
참조: 메모리 내 집계에 대한 자세한 내용은 Oracle Database In-Memory Guide를 참조하십시오.
 
5.8 Cursor-Duration Temporary Tables
쿼리의 중간 결과를 구체화하기 위해 Oracle Database는 쿼리 컴파일 중 커서 지속 기간 임시 테이블을 메모리에 만들 수 있습니다. 복잡한 쿼리는 때때로 동일한 쿼리 블록을 여러 번 처리합니다. 이 시나리오를 피하기 위해 Oracle Database는 쿼리 결과에 대한 임시 테이블을 생성하고 커서가 지속되는 동안 메모리에 저장합니다. WITH 절 쿼리 및 Star Transformations과 같은 복잡한 연산의 경우 이 최적화는 반복적으로 사용되는 하위 쿼리의 중간 결과를 구체화합니다.
이런 식으로, 커서 지속 시간 임시 테이블은 성능을 향상시키고 I/O를 최적화합니다.
커서 지속 기간 임시 테이블을 사용할 때 데이터베이스는 다음 단계를 수행합니다.
1. 커서 지속 기간 임시 테이블을 사용하는 계획을 선택합니다.
2. 임시 테이블을 만듭니다.
3. 임시 테이블을 참조하도록 쿼리를 다시 씁니다.
4. 메모리가 남아 있지 않을 때까지 데이터를 메모리에 로드합니다. 이 경우 디스크에 임시 세그먼트가 생성됩니다
5. 쿼리를 실행하여 임시 테이블에서 데이터를 반환합니다.
6. 테이블을 자르고 메모리와 디스크상의 임시 세그먼트를 해제합니다.
 
직렬 조회의 경우, 임시 테이블은 PGA 메모리를 사용합니다. 병렬 쿼리는 MGA 메모리를 사용합니다. 두 시나리오 모두 메모리 가용성에 달려 있습니다. 커서 지속 기간 임시 테이블의 구현은 정렬과 유사합니다. 사용할 수 있는 메모리가 더 이상 없으면 데이터베이스는 임시 세그먼트에 데이터를 씁니다. Cursor duration 임시 테이블의 경우 차이점은 다음과 같습니다.
• 데이터베이스는 행 소스가 더 이상 활성화되지 않을 때 보다 쿼리가 끝날 때 메모리 세그먼트와 임시 세그먼트를 해제합니다.
• 메모리와 임시 세그먼트 간에 데이터를 이동할 수 있는 정렬과 달리 메모리의 데이터는 메모리에 남아 있습니다.
데이터베이스가 커서 지속 기간 임시 테이블을 사용하면 CURSOR DURATION MEMORY 키워드가 실행 계획에 나타납니다.
 
Example 5-10 Cursor-Duration Temporary Tables
다음 쿼리는 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'));
 
In the preceding plan, TEMP TABLE TRANSFORMATION 1 단계는 데이터베이스가 커서 실행 임시 테이블을 사용하여 쿼리를 실행했음을 나타냅니다.
2 단계의 CURSOR DURATION MEMORY 키워드는 데이터베이스가 사용 가능한 경우 메모리를 사용하여 SYS_TEMP_0FD9D6606_1AE004의 결과를 저장함을 나타냅니다.
메모리를 사용할 수 없는 경우 데이터베이스는 임시 데이터를 디스크에 기록합니다.
 
5.9 Table Expansion
테이블 확장에서, 옵티마이저는 파티션 된 테이블의 readmostly 부분에는 인덱스를 사용하지만 테이블의 활성 부분에는 인덱스를 사용하지 않는 계획을 생성합니다.
 
5.9.1 Purpose of Table Expansion
인덱스 기반 계획은 성능을 향상시킬 수 있지만 인덱스 유지 관리는 오버 헤드를 발생시킵니다. 많은 데이터베이스에서 DML은 데이터의 작은 부분에만 영향을 줍니다. 테이블 확장은 업데이트가 많은 테이블에 대해 인덱스 기반 계획을 사용합니다. 읽기 전용 데이터에만 인덱스를 생성하여 활성 데이터의 인덱스 오버 헤드를 제거 할 수 있습니다. 이런 식으로 테이블 확장은 인덱스 유지 보수를 피하면서 성능을 향상시킵니다.
5.9.2 How Table Expansion Works
테이블 파티셔닝은 테이블 확장을 가능하게 합니다. 파티션 된 테이블에 로컬 인덱스가 있으면 옵티마이저는 해당 인덱스를 특정 파티션에서 사용할 수 없도록 표시 할 수 있습니다. 실제로 일부 파티션은 인덱싱되지 않습니다. 테이블 확장에서 최적화 프로그램은 쿼리를 UNION ALL 문으로 변환합니다. 일부 하위 쿼리는 인덱싱 된 파티션 및 인덱싱 되지 않은 파티션에 액세스하는 다른 하위 쿼리에 액세스합니다. 옵티마이저는 쿼리에서 액세스 된 모든 파티션에 대해 존재하는지 여부에 관계없이 파티션에서 사용 가능한 가장 효율적인 액세스 방법을 선택할 수 있습니다.
최적화 프로그램이 항상 테이블 확장을 선택하지는 않습니다.
• 테이블 확장은 비용 기반입니다.
데이터베이스가 UNION ALL의 모든 분기에서 한 번만 확장된 테이블의 각 파티션에 액세스하는 동안 데이터베이스가 조인하는 테이블은 각 분기에서 액세스됩니다.
• 의미론적 문제로 인해 확장이 무효화 될 수 있습니다.
예를 들어, 외부 조인의 오른쪽에 나타나는 테이블은 테이블 확장에 유효하지 않습니다. 힌트 EXPAND_TABLE 힌트를 사용하여 테이블 확장을 제어 할 수 있습니다. 힌트는 비용 기반 결정보다 우선하지만 시맨틱 검사보다 우선합니다.
 
5.9.3 Table Expansion: Scenario
옵티마이저는 쿼리에 표시되는 술어에 따라 각 테이블에서 액세스해야 하는 파티션을 추적합니다. 파티션 프루닝은 옵티마이저가 테이블 확장을 사용하여 보다 최적의 플랜을 생성 할 수 있게 합니다.
 
이 시나리오에서는 다음을 가정합니다.
• time_id 열에서 range partition 된 sh.sales 테이블에 대해 스타 쿼리를 실행하려고 합니다.
• 특정 파티션에서 인덱스를 비활성화하여 테이블 확장의 이점을 확인하려고 합니다.
표 확장을 사용하려면:
1. sh 사용자로 데이터베이스에 로그인하십시오.
2. 다음 쿼리를 실행합니다.
SELECT *
FROM sales
WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
AND prod_id = 38;
 
3. DBMS_XPLAN을 쿼리하여 계획을 설명하십시오.
SET LINESIZE 150
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'BASIC,PARTITION'));
 
다음 계획의 Pstart 및 Pstop 열에서 볼 수 있듯이 옵티마이저는 필터에서 테이블의 28개 파티션 중 16개만 액세스해야 한다고 결정합니다.
 
옵티마이저가 액세스 할 파티션을 결정하면 모든 파티션에서 사용할 수 있는 인덱스를 고려합니다. 앞의 계획에서 옵티마이저는 sales_prod_bix 비트 맵 인덱스를 사용하기로 결정했습니다.
4. 판매 테이블의 SALES_1995 파티션에서 색인을 비활성화하십시오.
ALTER INDEX sales_prod_bix MODIFY PARTITION sales_1995 UNUSABLE;
앞의 DDL은 1996년 이전의 모든 판매가 들어있는 파티션 1의 색인을 비활성화합니다.
 
Note:
USER_IND_PARTITIONS 뷰를 조회하여 파티션 정보를 얻을 수 있습니다.
 
5. sales의 쿼리를 다시 실행 한 다음 DBMS_XPLAN을 쿼리하여 계획을 가져옵니다.
산출물은 계획이 변경되지 않았 음을 보여줍니다.
-------------------------------------------------------------------------------
 
사용 불가능한 인덱스 파티션이 쿼리와 관련이 없으므로 계획은 동일합니다. 쿼리가 액세스하는 모든 파티션이 인덱싱되면 데이터베이스는 인덱스를 사용하여 쿼리에 응답 할 수 있습니다. 쿼리는 파티션 16에서 28까지만 액세스하므로 파티션 1에서 인덱스를 비활성화해도 계획에는 영향을주지 않습니다.
 
6. 쿼리가 액세스해야 하는 파티션 인 파티션 28(SALES_Q4_2003)의 인덱스를 비활성화합니다.
ALTER INDEX sales_prod_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
ALTER INDEX sales_time_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
쿼리가 액세스해야 하는 파티션의 인덱스를 비활성화하면 테이블 확장 없이 이 인덱스를 쿼리에서 더 이상 사용할 수 없습니다.
 
7. Query the plan using DBMS_XPLAN.
다음 계획에 표시된 것처럼 옵티마이 저는 인덱스를 사용하지 않습니다.
 
앞의 예에서 쿼리는 16 개의 파티션에 액세스합니다. 이러한 파티션 중 15개에서 인덱스를 사용할 수 있지만 최종 파티션에는 인덱스를 사용할 수 없습니다. 옵티마이저는 하나의 액세스 경로 또는 다른 액세스 경로를 선택해야하기 때문에 옵티마이저는 모든 파티션에서 인덱스를 사용할 수 없습니다.
 
8. 테이블 확장을 사용하면 옵티마이저는 다음과 같이 원래의 쿼리를 다시 작성합니다.
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의 첫 번째 쿼리 블록은 인덱싱 된 파티션에 액세스하는 반면 두 번째 쿼리 블록은 그렇지 않은 파티션에 액세스합니다. 두 하위 쿼리는 액세스되는 모든 파티션의 테이블 검색을 사용하는 것보다 최적인 경우 최적화 프로그램이 첫 번째 쿼리 블록에서 인덱스를 사용하도록 선택할 수 있도록 합니다.
 
9. Query the plan using DBMS_XPLAN.
계획은 다음과 같이 나타납니다.
 
이전 계획에 표시된 것처럼 옵티마이저는 두 개의 쿼리 블록에 대해 UNION ALL을 사용합니다(2 단계). 옵티마이저는 첫 번째 쿼리 블록(단계 6)에서 파티션 13 - 27에 액세스하기 위해 인덱스를 선택합니다. 파티션 28에 사용할 수 있는 인덱스가 없기 때문에 옵티마이저는 두 번째 쿼리 블록(8 단계)에서 전체 테이블 스캔을 선택합니다.
 
5.9.4 Table Expansion and Star Transformation: Scenario Star transformation enables specific types of queries to avoid accessing large portions of big fact.
Star Transformation을 위해서는 적극적으로 업데이트되는 테이블에서 오버 헤드가 발생할 수 있는 여러 인덱스를 정의해야 합니다. 테이블 확장을 사용하면 최적화되지 않은 파티션에서만 인덱스를 정의 할 수 있으므로 옵티마이저가 테이블의 인덱싱 된 부분에서만 별 변환을 고려할 수 있습니다.
 
이 시나리오에서는 다음을 가정합니다.
• "Star Transformation: 시나리오"에서 사용 된 것과 동일한 스키마를 쿼리합니다.
• 시간 분할 테이블의 경우와 마찬가지로 판매의 마지막 파티션이 업데이트되고 있습니다.
• 옵티마이저가 테이블 확장을 이용하기를 원합니다.
스타 쿼리에서 테이블 확장을 활용하려면 다음을 수행하십시오.
 
1. 다음과 같이 마지막 파티션의 인덱스를 비활성화합니다.
ALTER INDEX sales_channel_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
ALTER INDEX sales_cust_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
2. 다음 별 쿼리를 실행합니다.
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;
3. 다음 계획을 보여주는 DBMS_XPLAN을 사용하여 커서를 질의하십시오.
 
 
앞의 계획은 테이블 확장을 사용합니다. 마지막 파티션을 제외한 모든 파티션에 액세스하는 UNION ALL 분기는 별 변환을 사용합니다. 파티션 28의 인덱스가 사용 불가능하기 때문에 데이터베이스는 전체 테이블 스캔을 사용하여 최종 파티션에 액세스합니다.
 
5.10 Join Factorization
Join 인수로 알려진 비용 기반 변환에서 옵티마이저는 UNION ALL 쿼리의 분기에서 일반적인 계산을 인수 분해 할 수 있습니다.
 
5.10.1 Purpose of Join Factorization
UNION ALL 쿼리는 데이터베이스 응용 프로그램, 특히 데이터 통합 응용 프로그램에서 일반적입니다. 종종 UNION ALL 쿼리의 분기는 동일한 기본 테이블을 참조합니다. 조인 인수 분해가 없으면 옵티마이저는 UNION ALL 쿼리의 각 분기를 독립적으로 평가하므로 데이터 액세스 및 조인을 비롯한 반복적 인 처리가 발생합니다. 결합 인수 변환은 UNION ALL 분기 전반에서 공통 계산을 공유 할 수 있습니다. 대형 기본 테이블을 추가로 스캔하지 않으면 성능이 크게 향상 될 수 있습니다.
 
5.10.2 How Join Factorization Works
결합 인수 분해는 여러 테이블과 세 개 이상의 UNION ALL 분기를 인수 분해 할 수 있습니다. 결합 인수 분해는 예제를 통해 가장 잘 설명됩니다.
 
Example 5-11 UNION ALL Query
다음 쿼리는 네 개의 테이블(t1, t2, t3 및 t4)과 두 개의 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;
앞의 쿼리에서 테이블 t1은 UNION ALL 분기 모두에 나타나며 필터 조건부 t1.c1> 1 및 조인 조건부 t1.c1 = t2.c1과 같습니다. 변형없이 데이터베이스는 테이블 t1에서 각 분기에 대해 한 번씩 두 번 스캔 및 필터링을 수행해야합니다.
Example 5-12 Factorized Query
다음 쿼리는 예제 5-11에서 조인 인수 분해를 사용하여 쿼리를 변환합니다.
 
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을 두 번 스캔하고 필터링하는데 드는 성능 비용을 피합니다.
 
Note:
UNION ALL 쿼리의 분기에 DISTINCT 함수를 사용하는 절이 있으면 조인 분해가 유효하지 않습니다.
 
5.10.3 Factorization and Join Orders: Scenario Join factorization can create more possibilities for join orders
 
Example 5-13 Query Involving Five Tables
다음 쿼리에서 뷰 V는 예제 5-11 에서처럼 쿼리와 같습니다.
 
 
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와 조인해야 합니다.
Example 5-14 Factorization of t1 from View V
조인 인수 분해가 뷰 V에서 t1을 인수 분해하는 경우, 다음 u 리에서와 같이 데이터베이스는 t1과 t1을 결합 할 수 있습니다.
 
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 및 t3은 VW_JF_1 뷰의 UNION ALL 쿼리의 첫 번째 분기에 나타납니다. 데이터베이스는 t2와 t3을 결합해야 VW_JF_1 뷰에서 정의되지 않은 t1과 조인 할 수 있습니다. 부과된 조인 순서가 반드시 최상의 조인 순서가 아닐 수도 있습니다. 이러한 이유로 옵티마이저는 비용 기반 변환 프레임 워크를 사용하여 결합 인수 분해를 수행합니다. 옵티마이저는 조인 인수가 있거나 없는 플랜의 비용을 계산 한 다음 가장 저렴한 플랜을 선택합니다. 보기 5-15보기 정의가 제거 된 상태에서 View V에서 t1의 인수 분해 다음 조회는 예 5-14에서와 동일한 조회이지만 인수 정의가 더 쉽게 보일 수 있도록 보기 정의가 제거되었습니다.
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 Factorization of Outer Joins: Scenario
데이터베이스는 외부 조인, antijoins 및 semijoins의 조인 인수 분해를 지원하지만 조인의 오른쪽 테이블에 대해서만 지원합니다. 예를 들어, 조인 인수 분해는 t2를 인수 분해하여 다음의 UNION ALL 조 건을 변환 할 수 있습니다.
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(+);