| PostgreSQL 9.1.24 문서 | ||||
|---|---|---|---|---|
| PostgreSQL : 문서 : 9.1 : 토토 트리 | 위로 | 제37장. 토토 핫 시스템 | 다음 | |
다음의 조회수포스트그레SQL있습니다 토토 핫 시스템을 사용하여 구현되었습니다. 사실 본질적으로 존재하는 것은 다음 사이에는 차이가 없습니다:
CREATE VIEW myview AS SELECT * FROM mytab;
두 명령과 비교:
테이블 생성 myview (mytab과 동일한 열 목록);
대신 myview로 선택하여 토토 핫 "_RETURN"을 생성하세요.
SELECT * FROM mytab;
이것이 바로 이것이기 때문입니다만들기 토토 핫명령은 내부적으로 수행됩니다. 여기에는 몇 가지 부작용이 있습니다. 그 중 하나는 뷰에 대한 정보가포스트그레SQL시스템 카탈로그는 테이블의 경우와 똑같습니다. 파서의 경우 테이블과 뷰 사이에는 전혀 차이가 없습니다. 그들은 같은 것: 관계.
토토 핫선택 중모두에 적용됩니다 주어진 명령이 다음과 같더라도 마지막 단계로 쿼리를 수행합니다.삽입, 업데이트또는삭제. 그리고 다른 명령의 토토 핫과 의미가 다릅니다. 대신 쿼리 트리를 수정한다는 점에서 유형을 지정합니다. 새로운 것을 만드는 중입니다. 그래서선택토토 핫 먼저 설명합니다.
현재는 하나의 작업만 가능합니다.ON 선택토토 핫이며 무조건적이어야 합니다선택즉 행동은대신. 이 제한은 다음을 수행하는 데 필요했습니다. 일반 사용자에게도 공개할 수 있을 만큼 안전한 토토 핫입니다. 제한한다선택 중다음과 같이 행동하는 토토 핫 조회수.
이 장의 예는 다음을 수행하는 두 개의 조인 뷰입니다. 일부 계산과 이를 차례로 사용하는 추가 보기. 하나 두 개의 첫 번째 보기 중 나중에 다음에 대한 토토 핫을 추가하여 사용자 정의됩니다.삽입, 업데이트및삭제최종 결과가 동작하는 뷰가 되도록 작업 마법의 기능을 갖춘 실제 테이블과 같습니다. 이것은 아니다 시작하기에 매우 간단한 예이며 이로 인해 상황이 발생합니다. 들어가기가 더 어렵습니다. 하지만 한 가지 예를 갖는 것이 더 좋습니다. 논의된 모든 사항을 단계별로 다루기보다는 단계별로 설명합니다. 마음 속에 얽힐 수 있는 다양한 것들이 있습니다.
예를 들어, 조금 필요합니다분2 정수 중 낮은 값을 반환하는 함수 가치. 우리는 그것을 다음과 같이 생성합니다:
CREATE FUNCTION min(정수, 정수) $$로 정수를 반환합니다.
$1 < $2일 때 사례 선택 그런 다음 $1 ELSE $2 END
$$ 언어 SQL STRICT;
처음 두 토토 핫 시스템에 필요한 실제 테이블 설명은 다음과 같습니다:
테이블 신발_데이터 생성(
신발 이름 텍스트, -- 기본 키
sh_avail 정수, -- 사용 가능한 쌍 수
slcolor 텍스트, -- 선호하는 신발끈 색상
slminlen real, -- 최소 신발끈 길이
slmaxlen real, -- 최대 신발끈 길이
slunit 텍스트 - 길이 단위
);
CREATE TABLE 신발끈_데이터(
sl_name text, -- 기본 키
sl_avail 정수, -- 사용 가능한 쌍 수
sl_color text, -- 신발끈 색상
sl_len real, -- 신발끈 길이
sl_unit 텍스트 -- 길이 단위
);
CREATE TABLE 단위(
un_name 텍스트, -- 기본 키
un_fact real -- cm로 변환할 인수
);
보시다시피 신발가게 데이터를 나타냅니다.
뷰는 다음과 같이 생성됩니다:
신발 AS 토토 핫 만들기
sh.신발이름 선택,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
shoe_data sh, 단위 un에서
sh.slunit = un.un_name;
신발끈 AS 토토 핫 만들기
s.sl_name 선택,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_유닛,
s.sl_len * u.un_fact AS sl_len_cm
신발끈_데이터 s, 단위 u에서
s.sl_unit = u.un_name;
shoe_ready AS 토토 핫 만들기
rsh.신발이름 선택,
rsh.sh_avail,
rsl.sl_이름,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM 신발 rsh, 신발끈 rsl
여기서 rsl.sl_color = rsh.slcolor
그리고 rsl.sl_len_cm = rsh.slminlen_cm
그리고 rsl.sl_len_cm <= rsh.slmaxlen_cm;
그토토 핫 만들기에 대한 명령신발끈토토 핫(가장 간단함) 우리가 가지고 있는 것)은 관계를 생성할 것입니다.신발끈및 다음 항목pg_rewrite재작성이 있음을 알려줍니다. 관계가 발생할 때마다 적용되어야 하는 토토 핫신발끈은 쿼리의 범위 테이블에서 참조됩니다. 토토 핫에는 토토 핫 제한이 없습니다(나중에 논의됨). 비-선택토토 핫, 이후선택토토 핫은 현재 이를 가질 수 없습니다) 그리고 이다대신. 그 토토 핫을 참고하세요 제한은 쿼리 제한과 동일하지 않습니다. 는 우리 토토 핫의 작업에는 쿼리 제한이 있습니다. 의 행동 토토 핫은의 복사본인 하나의 쿼리 트리입니다.선택뷰 생성의 문 명령.
참고:다음에 대한 두 개의 추가 범위 테이블 항목신규그리고오래된당신이 볼 수 있는 것은pg_rewrite다음 항목에 관심이 없습니다.선택토토 핫.
이제 우리는 채웁니다단위, 신발_데이터그리고신발끈_데이터그리고 간단한 쿼리를 실행합니다. 보기:
INSERT INTO 단위 VALUES('cm', 1.0);
INSERT INTO 단위 VALUES('m', 100.0);
INSERT INTO 단위 VALUES ('인치', 2.54);
INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, '검은색', 30.0, 40.0, '인치');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
SELECT * 신발끈에서;
sl_이름 | sl_avail | sl_color | sl_len | sl_유닛 | sl_len_cm
---------+----------+----------+---------+---------+----------
sl1 | 5 | 검정 | 80 | cm | 80
sl2 | 6 | 검정 | 100 | cm | 100
sl7 | 7 | 갈색 | 60 | cm | 60
sl3 | 0 | 검정 | 35 | 인치 | 88.9
sl4 | 8 | 검정 | 40 | 인치 | 101.6
sl8 | 1 | 갈색 | 40 | 인치 | 101.6
sl5 | 4 | 갈색 | 1 | 남 | 100
sl6 | 0 | 갈색 | 0.9 | 남 | 90
(8행)
이것이 가장 간단합니다.선택당신은 할 수 있습니다 그래서 우리는 이 기회에 보기 토토 핫의 기본입니다.SELECT * FROM 신발끈파서가 해석하여 다음을 생성했습니다. 쿼리 트리:
SELECT 신발끈.sl_name, 신발끈.sl_avail,
신발끈.sl_color, 신발끈.sl_len,
신발끈.sl_unit, 신발끈.sl_len_cm
신발끈 신발끈에서;
그리고 이것은 토토 핫 시스템에 제공됩니다. 룰 시스템 워크 범위 테이블을 통해 토토 핫이 있는지 확인합니다. 관계. 에 대한 범위 테이블 항목을 처리할 때신발끈(지금까지 유일한 것)은 다음을 찾습니다._RETURN쿼리 트리를 사용한 토토 핫:
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len, s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
낡은 신발끈, 새 신발끈,
신발끈_데이터 s, 단위 u
s.sl_unit = u.un_name;
뷰를 확장하기 위해 리라이터는 단순히 하위 쿼리를 생성합니다. 토토 핫의 작업 쿼리 트리를 포함하는 범위 테이블 항목 이 범위 테이블 항목을 원래 항목으로 대체합니다. 뷰를 참고했습니다. 결과적으로 다시 작성된 쿼리 트리는 다음과 같습니다. 다음을 입력한 것과 거의 동일합니다.
SELECT 신발끈.sl_name, 신발끈.sl_avail,
신발끈.sl_color, 신발끈.sl_len,
신발끈.sl_unit, 신발끈.sl_len_cm
FROM(SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_유닛,
s.sl_len * u.un_fact AS sl_len_cm
신발끈_데이터 s, 단위 u에서
WHERE s.sl_unit = u.un_name) 신발끈;
그러나 한 가지 차이점이 있습니다: 하위 쿼리의 범위 테이블 두 개의 추가 항목이 있습니다.낡은 신발끈그리고새 신발끈. 이 항목은 그렇지 않습니다 참조되지 않으므로 쿼리에 직접 참여합니다. 하위 쿼리의 조인 트리 또는 대상 목록을 기준으로 합니다. 리라이터는 다음을 사용합니다. 접근 권한 확인 정보를 저장하기 위해 원래는 다음을 참조한 범위 테이블 항목에 있었습니다. 보기. 이러한 방식으로 실행자는 여전히 사용자가 보기에 액세스할 수 있는 적절한 권한이 있습니다. 다시 작성된 쿼리에서는 뷰를 직접 사용하지 않습니다.
그것이 적용된 첫 번째 토토 핫이었습니다. 토토 핫 시스템은 상단의 나머지 범위 테이블 항목을 계속 확인하세요. 쿼리(이 예에서는 더 이상 없음)가 수행됩니다. 추가된 하위 쿼리의 범위 테이블 항목을 재귀적으로 확인합니다. 그 중 뷰를 참조하는 것이 있는지 확인하세요. (그러나 확장되지는 않습니다.오래된또는신규— 그렇지 않으면 무한 재귀가 발생하게 됩니다!) 이 예에서는 재작성 토토 핫이 없습니다.신발끈_데이터또는단위이므로 재작성이 완료되었으며 위 내용은 기획자에게 전달된 최종 결과입니다.
이제 어떤 신발인지 알아내는 쿼리를 작성하겠습니다. 현재 매장에 어울리는 신발끈(색상)이 있습니다. 길이) 그리고 정확히 일치하는 총 개수는 어디입니까? 쌍은 2보다 크거나 같습니다.
SELECT * FROM shoe_ready WHERE total_avail = 2; 신발이름 | sh_avail | sl_이름 | sl_avail | total_avail -----------+----------+---------+----------+------------- sh1 | 2 | sl1 | 5 | 2 sh3 | 4 | sl7 | 7 | 4 (2행)
이번 파서의 출력은 쿼리 트리입니다:
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
신발 준비 신발 준비에서
shoe_ready.total_avail = 2;
적용되는 첫 번째 토토 핫은 다음에 대한 토토 핫이 될 것입니다.shoe_ready토토 핫 및 쿼리 결과 트리:
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_이름,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM 신발 rsh, 신발끈 rsl
여기서 rsl.sl_color = rsh.slcolor
그리고 rsl.sl_len_cm = rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
shoe_ready.total_avail = 2;
마찬가지로, 토토 핫은신발그리고신발끈으로 대체됩니다. 3단계 최종으로 이어지는 하위 쿼리의 범위 테이블 쿼리 트리:
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_이름,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM (SELECT sh.신발이름,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
shoe_data sh, 단위 un에서
sh.slunit = un.un_name) rsh,
(s.sl_name 선택,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_유닛,
s.sl_len * u.un_fact AS sl_len_cm
신발끈_데이터 s, 단위 u에서
어디서 s.sl_unit = u.un_name) rsl
여기서 rsl.sl_color = rsh.slcolor
그리고 rsl.sl_len_cm = rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
shoe_ready.total_avail 2;
계획자는 이 트리를 축소하여 2단계 쿼리 트리: 맨 아래선택명령은 다음과 같습니다"끌어올림"중간으로선택처리할 필요가 없기 때문에 별도로. 하지만 중간선택할 것이다 집계가 포함되어 있으므로 상단과 별도로 유지됩니다. 기능. 우리가 그것을 끌어당기면 행동이 바뀔 것입니다. 맨 위선택, 우리는 그렇지 않습니다. 원한다. 그러나 쿼리 트리를 축소하는 것은 최적화입니다. 다시 쓰기 시스템은 그 자체에 관심을 가질 필요가 없습니다 와.
쿼리 트리의 두 가지 세부 사항은 위의 보기 토토 핫에 대한 설명입니다. 다음은 명령 유형과 결과관계. 실제로 명령 유형은 다음에서 필요하지 않습니다. 토토 핫을 볼 수 있지만 결과 관계는 토토 핫을 보는 방식에 영향을 미칠 수 있습니다. 특별한 주의가 필요하기 때문에 쿼리 재작성이 작동합니다. 결과 관계가 뷰인 경우 사용됩니다.
a에 대한 쿼리 트리 사이에는 단지 몇 가지 차이점이 있습니다.선택그리고 하나는 다른 명령용입니다. 분명히 그들은 다른 명령 유형을 가지고 있으며 명령에 대해 이외의 것선택, 결과 관계는 결과가 있는 범위 테이블 항목을 가리킵니다. 가야한다. 다른 모든 것은 완전히 동일합니다. 그래서 테이블 두 개t1그리고t2열 포함a그리고b, 두 가지에 대한 쿼리 트리 진술:
t1.a = t2.a인 경우 t1, t2에서 t2.b를 선택하세요. t1.a = t2.a인 경우 t2에서 t1 세트 b = t2.b 업데이트;
거의 동일합니다. 특히:
범위 테이블에는 테이블에 대한 항목이 포함되어 있습니다.t1그리고t2.
대상 목록에는 다음을 가리키는 하나의 변수가 포함되어 있습니다. 열b27444_27490t2.
한정 표현식은 열을 비교합니다.a두 범위 테이블 항목 모두에 대한 평등.
조인 트리는 다음 사이의 간단한 조인을 보여줍니다.t1그리고t2.
결과적으로 두 쿼리 트리 모두 비슷한 결과가 나옵니다. 실행 계획: 둘 다 두 테이블에 대한 조인입니다. 에 대한업데이트다음에서 누락된 열t1다음에 의해 대상 목록에 추가됩니다. 플래너와 최종 쿼리 트리는 다음과 같습니다.
t1.a = t2.a인 t2에서 t1 설정 a = t1.a, b = t2.b 업데이트;
따라서 조인을 실행하는 실행자는 정확히 다음을 생성합니다. 다음과 동일한 결과 집합:
t1.a = t2.a인 경우 t1, t2에서 t1.a, t2.b를 선택하세요.
하지만 약간의 문제가 있습니다업데이트: 실행자 계획의 일부 조인은 조인 결과가 무엇을 의미하는지 상관하지 않습니다. 위해. 단지 행의 결과 집합을 생성합니다. 하나라는 사실 는선택명령이고 다른 하나는 an업데이트은 더 높은 수준에서 처리됩니다. 집행자는 이것이임을 알고 있습니다.업데이트그리고 이 결과가 나와야 한다는 것을 알고 있습니다. 테이블에t1. 그러나 어느 행 새 행으로 교체해야 합니까?
이 문제를 해결하기 위해 다른 항목이 대상 목록 in업데이트(또한삭제) 명령문: 현재 튜플 아이디(CTID). 이것은 시스템이다 파일 블록 번호와 위치가 포함된 열 행에 대한 블록입니다. 테이블을 아는 것은,CTID원본을 검색하는 데 사용할 수 있습니다. 행t1업데이트 예정입니다. 추가한 후CTID대상 목록에, 쿼리는 실제로 다음과 같습니다.
t1.a = t2.a인 경우 t1, t2에서 t1.a, t2.b, t1.ctid를 선택하세요.
이제 또 다른 세부사항PostgreSQL무대에 들어갑니다. 오래된 테이블 행을 덮어쓰지 않으므로 이것이 바로 이유입니다.롤백빠릅니다. 에서업데이트, 새 결과 행이 테이블(제거한 후)CTID) 및 이전 행의 행 헤더에서 어느CTID지시됨,cmax그리고x최대항목이 현재 명령으로 설정되었습니다 카운터 및 현재 거래 ID. 따라서 이전 행은 숨겨집니다. 트랜잭션이 커밋된 후 진공청소기는 다음 작업을 수행할 수 있습니다. 결국 데드 행을 제거하십시오.
이 모든 것을 알고 있으면 우리는 보기 토토 핫을 간단히 적용할 수 있습니다. 모든 명령과 완전히 동일한 방식입니다. 없다 차이.
위는 토토 핫 시스템이 보기를 통합하는 방법을 보여줍니다. 정의를 원래 쿼리 트리에 추가합니다. 두 번째에는 예를 들어, 간단한선택하나의 토토 핫에서 4개의 테이블을 조인하는 최종 쿼리 트리를 생성했습니다. (단위다른 이름으로 두 번 사용되었습니다. 이름).
토토 핫 시스템으로 보기를 구현하면 다음과 같은 이점이 있습니다. 플래너는 어떤 테이블에 대한 모든 정보를 가지고 있는지 이 테이블 간의 관계와 함께 스캔해야 합니다. 보기의 제한적인 자격과 하나의 단일 쿼리에서 원래 쿼리의 자격 나무. 그리고 이는 원래 쿼리가 실행될 때의 상황입니다. 이미 조회수에 대한 조인입니다. 기획자는 무엇을 할지 결정해야 한다. 쿼리를 실행하는 가장 좋은 경로이며 더 많은 정보가 기획자가 그럴수록 이 결정은 더 좋을 수 있습니다. 그리고 토토 핫 구현된 시스템포스트그레SQL이것이 전부임을 보장합니다 해당 시점까지 쿼리에 대해 사용 가능한 정보입니다.
뷰가 대상 관계로 명명되면 어떻게 됩니까? an삽입, 업데이트또는삭제? 위에서 설명한 대체 작업을 수행하면 결과 관계가 하위 쿼리를 가리키는 쿼리 트리 작동하지 않는 범위 테이블 항목입니다. 대신 리라이터는 작업이에 의해 처리된다고 가정합니다.대신뷰를 트리거합니다. (없으면 이러한 트리거를 사용하면 실행자는 실행 시 오류를 발생시킵니다. 시작됩니다.) 이 경우 재작성은 약간 다르게 작동합니다. 에 대한삽입, 리라이터는 아무 작업도 수행하지 않습니다. 모두 뷰에 대한 결과 관계로 남겨 둡니다. 쿼리. 에 대한업데이트그리고삭제, 여전히 뷰를 확장해야 합니다. 쿼리를 생성하여"오래된"행 명령은 업데이트 또는 삭제를 시도합니다. 그래서 뷰는 정상적으로 확장되었지만 확장되지 않은 또 다른 범위 테이블 항목은 다음과 같습니다. 해당 용량 내에서 뷰를 표현하기 위해 쿼리에 추가되었습니다. 결과 관계.
이제 발생하는 문제는 행을 식별하는 방법입니다. 뷰에서 업데이트됩니다. 결과 관계가 다음과 같을 때를 기억하세요. 테이블, 특별한CTID물리적 식별을 위해 대상 목록에 항목이 추가됩니다. 업데이트할 행의 위치입니다. 다음과 같은 경우에는 작동하지 않습니다. 뷰에는 아무 것도 없기 때문에 결과 관계는 뷰입니다.CTID, 해당 행은 그렇지 않기 때문에 실제 물리적 위치가 있어야 합니다. 대신에,업데이트또는삭제작업, 특수전체항목은 다음과 같습니다 모든 열을 포함하도록 확장되는 대상 목록에 추가됨 보기에서. 실행자는 이 값을 사용하여"오래된"행에서대신트리거. 이는 트리거에 달려 있습니다. 이전 행과 새 행을 기반으로 무엇을 업데이트할지 알아보세요. 값.
없을 경우대신트리거 뷰를 업데이트하기 위해 실행자는 오류를 발생시킵니다. 왜냐하면 자체적으로 뷰를 자동으로 업데이트할 수는 없습니다. 변경하려면 이를 통해 동작을 수정하는 토토 핫을 정의할 수 있습니다.삽입, 업데이트및삭제뷰에 대한 명령. 이 토토 핫은 명령을 다시 작성합니다. 일반적으로 하나 이상의 테이블을 업데이트하는 명령으로, 조회수보다는요. 이것이 다음 섹션의 주제입니다.
토토 핫이 먼저 평가되어 원본을 다시 작성한다는 점에 유의하세요. 계획하고 실행하기 전에 쿼리합니다. 따라서 뷰의 경우 있다대신트리거뿐만 아니라 토토 핫 켜기삽입, 업데이트또는삭제그러면 토토 핫이 먼저 평가되고 결과에 따라 트리거는 전혀 사용되지 않을 수 있습니다.