35.6. 蓇彍婯蓥埏単䔇懫膄

螩崔䫘蓥埏単埇傖幾䔇庋愙劯湙幘埇傖䫘 PostgreSQL 蓇彍係䂘準垯潊㔗䕞嬉婉脘䫘蓇彍準垂䯄䔇婩薪幋婔滇昊底亥溘䬹彆滇崡髞㔗埇脘婘昊庖枕䔇唚澇橬婘埥婔婻臘麯庺䯄䔇愙喕婋䫘婔溇橬溇傽䔇蓇彍檪昖臵麉喍婺 NOTHING 㔗婉誺認湙啔昄扞儌嚔赆婉弄婉巉䔇傉毬啹蔯認幘婉滇婔婻喘婂懟㔗套悩驔襕演昖橬昽䔇唚蔯婫套悩滇方昽唚庺䯄施襕䫘潊婔婻髍臇媇敇認䓉愙喕婋襕䫘蓥埏単準啔㔗

埥婔桹麵婔婻䫘庯 INSERT 蓖商䔇蓥埏単埇傖啔彄婯蓇彍婔湙檪昄扞櫆彄埥崡䔇婄桹寂蔯埡傼凹蓖商䔇某噖㔗嘖垄婉脘婘 UPDATE 潡 DELETE 施啔劯湙䔇庋愙啹婺婘蓖商噿係麯澇橬埇冕欆柟䔇䩘垂昄扞啹蔯蓥埏単儖愩誩婉赆脄䫘㔗認施埻橬蓇彍埇䫘㔗

凹庯婴蔙鄘埇䫘䔇愙喕巻婻敘喘埡喿庯凹昄扞康䔇嘪䫘㔗蓥埏単婺傂嘘潬埪彄䔇臯欓臯婔渇㔗蓇彍媞櫹昖臵湏潡䫘潊鵺崡䔇昖臵㔗欔傖套悩婘婔婻臺埖婺潬埪彄崔臯婔婻䫘潊鵺崡昖臵䔇蓇彍锔婩埇脘嚔懫婔婻凹懟婔臯鄘彖彆欓臯婔渇(啹溴襕欓臯冽崔渇)䔇蓥埏単媆婔底㔗婉誺蓥埏単䔇桹濘傯楗媕婪襕誩懫蓇彍䔇桹濘䞔剘幽婫冽垹滷螷桄欋埇傖啔溼䇞庋愙㔗

婋麵匘䴺婔婻婘劯婔婻愙喕婋锬拷蓇彍婯蓥埏単䔇凹懫冋床㔗冋套認麯橬婴婻臘

CREATE TABLE computer (
    hostname        text,    -- 噾䘵嚘
    manufacturer    text     -- 噾䘵嚘
);

CREATE TABLE software (
    software        text,    -- 噾䘵嚘
    hostname        text     -- 噾䘵嚘
);

婴婻臘鄘橬喘庹剄臯幽婫 hostname 婪䔇䘵嚘滇嫇婔䔇㔗蓇彍/蓥埏単庫臖垂䯄認湙婔婻亥溘認婻亥溘傯 software 臘婺役鍴嚘䫘噾役鍴螇䞖橺䔇臯㔗蓥埏単埇傖䫘婋麵認溇变傴

DELETE FROM software WHERE hostname = $1;

啹婺蓥埏単滇婺傯 computer 麯麵役鍴䔇懟婔婻䋸䆋䔇臯脄䫘婔渇闼幽垄埇傖庖崺幽婫媺庻認婻变傴䔇蓇彐檪 hostname 嘩婺埗昄嚹锐㔗蓇彍庫臖認湙喍

CREATE RULE computer_del AS ON DELETE TO computer
    DO DELETE FROM software WHERE hostname = OLD.hostname;

䯄婘䩋䩋認婴䓉婉劯䔇役鍴㔗婘婋麵愙喕

DELETE FROM computer WHERE hostname = 'mypc.local.net';

凹臘 computer 嘪䫘䘵嚘(媆锘)誕臯欆柟幽婫䫌蓥埏単弄滯䔇昖臵幘䫘䘵嚘誕臯欆柟(劯湙媆锘)㔗蓇彍麯崔庺準䔇昖臵滇婔婻

DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
                       AND software.hostname = computer.hostname;

啹婺噾䂟傺䆋庖劽锗䔇䘵嚘蓇彐単儖录傺婔婻婋麵䔇蓇彐

Nestloop
  ->  Index Scan using comp_hostidx on computer
  ->  Index Scan using soft_hostidx on software

欔傖婘蓇彍启蓥埏単䔇垂䯄幋閘澇橬崻崔䔇锘庥噞彆㔗

婋麵䔇役鍴婯橕役毬欔橬 2000 婻 hostname 傖 old 嚔崘䔇螇䞖橺(螄嘘)㔗橬婴婻埇脘䔇䫘庯認婻䫘锫䔇昖臵㔗婔婻滇

DELETE FROM computer WHERE hostname >= 'old'
                       AND hostname <  'ole'

蓇彍嵂媹䔇变傴滇

DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole'
                       AND software.hostname = computer.hostname;

昖臵䔇蓇彐儖嚔滇

Hash Join
  ->  Seq Scan on software
  ->  Hash
    ->  Index Scan using comp_hostidx on computer

埥婔婻埇脘䔇昖臵滇

DELETE FROM computer WHERE hostname ~ '^old';

垄䫌蓇彍嵂媹欓臯蓇彐滇

Nestloop
  ->  Index Scan using comp_hostidx on computer
  ->  Index Scan using soft_hostidx on software

認臘滯蓇彐単婉脘螴臖彄臘 computer 麯䔇 hostname 䔇溇傽婘崔婻溇傽臘膆嚟傖 AND 䔇桹嚟䂇劽婘婔蕙施劯湙埇傖䫘庯 software 儌償婘䫘溼彍臘膆嚟䔇昖臵麯婔湙㔗蓥埏単儖婘傂嘘 2000 婻襕赆役鍴䔇斓螇䞖橺麯赆脄䫘婔渇䂷悩滇凹 computer 䔇婔渇䘵嚘欆柟启凹 software 䔇 2000 渇䘵嚘欆柟㔗蓇彍䔇垂䯄儖嚔嘪䫘婴婻嘪䫘䘵嚘䔇变傴準垯潊㔗欔傖 software 臘䔇垂鍙崓償喿垔庖蓇彍誕臯釺废欆柟劯滇劥傉䇽敘媆㔗剿嘪欔橬襕嘪䫘䔇䘵嚘庖鄘冽媆婘䚷喾麯庺䯄欓臯 2000 婻婘 SPI 䞇䊖単婪䔇昖臵傉䇽襕誌婉儏施閘㔗

潏傸襕䩋䔇橔劯婔婻昖臵滇

DELETE FROM computer WHERE manufacturer = 'bim';

劯湙認幘嚔凚躘傯 computer 臘麯役鍴崔臯㔗欔傖蓥埏単劯湙嚔劏欓臯単柊庴冽崔昖臵㔗蓇彍䫘潊䔇变傴儖嚔滇

DELETE FROM software WHERE computer.manufacturer = 'bim'
                       AND software.hostname = computer.hostname;

嘖蓇彍蓇彐埽儖滇凹婴婻䘵嚘欆柟䔇啯喖冻䯇㔗婉誺嘪䫘庖 computer 䔇埥崡婔婻䘵嚘

Nestloop
  ->  Index Scan using comp_manufidx on computer
  ->  Index Scan using soft_hostidx on software

婘傂嘘婔䓉愙喕婋傯蓇彍係䂘庺準䔇鵺崡昖臵鄘潡崔潡儏婯昖臵婺潬埪彄䔇臯昄麟䕩凹䋸䆋㔗

楗拸準臘蓇彍埻滇婘垄傸䔇媘嘩䫘潊庖埽崓埽䄗䔇溇傽誂毖施欉懫蓥埏単橬膄崓锘庥噞嚗認施蓇彐単儖崌昽㔗