without_oid.sql

来自「PostgreSQL7.4.6 for Linux」· SQL 代码 · 共 36 行

--
-- WITHOUT OID
--

CREATE TABLE wi (i INT);
CREATE TABLE wo (i INT) WITHOUT OIDS;
INSERT INTO wi VALUES (1);  -- 1
INSERT INTO wo SELECT i FROM wi;  -- 1
INSERT INTO wo SELECT i+1 FROM wi;  -- 1+1=2
INSERT INTO wi SELECT i+1 FROM wo;  -- 1+2=3
INSERT INTO wi SELECT i+3 FROM wi;  -- 3+3=6
INSERT INTO wo SELECT i+2 FROM wi;  -- 2+6=8
INSERT INTO wo SELECT i+8 FROM wo;  -- 8+8=16
INSERT INTO wi SELECT i+6 FROM wo;  -- 6+16=22
INSERT INTO wi SELECT i+22 FROM wi;  -- 22+22=44
INSERT INTO wo SELECT i+16 FROM wi;  -- 16+44=60
INSERT INTO wo SELECT i+60 FROM wo;  -- 60+60=120
INSERT INTO wi SELECT i+44 FROM wo;  -- 44+120=164
INSERT INTO wi SELECT i+164 FROM wi;  -- 164+164=328
INSERT INTO wo SELECT i+120 FROM wi;  -- 120+328=448
INSERT INTO wo SELECT i+448 FROM wo;  -- 448+448=896
INSERT INTO wi SELECT i+328 FROM wo;  -- 328+896=1224
INSERT INTO wi SELECT i+1224 FROM wi;  -- 1224+1224=2448
INSERT INTO wo SELECT i+896 FROM wi;  -- 896+2448=3344
INSERT INTO wo SELECT i+3344 FROM wo;  -- 3344+3344=6688
INSERT INTO wi SELECT i+2448 FROM wo;  -- 2448+6688=9136
INSERT INTO wo SELECT i+6688 FROM wi WHERE i<=2448;  -- 6688+2448=9136
VACUUM ANALYZE wi;
VACUUM ANALYZE wo;
SELECT min(relpages) < max(relpages), min(reltuples) - max(reltuples)
  FROM pg_class
 WHERE relname IN ('wi', 'wo');

DROP TABLE wi;
DROP TABLE wo;