ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ на больших столах

Question

Я выполняю простое естественное соединение на двух больших столах.

• полигоны содержат 68 000 строк (45 МБ)
• Roadshydro содержит около 2 миллионов строк (210 МБ) .

Означает ли это, что ядро ​​базы данных создает набор данных из 68 000*2 миллионов строк при внутреннем естественном объединении? Если это так, то объем требуемой памяти должен составлять 45*210 МБ, что намного больше, чем у моей системы, и составляет всего 1,5 ГБ.

Когда я выполнил этот запрос, через 5 минут моя система вылетает (аварийное завершение работы) . Разве он не может обработать 250 МБ данных в базе данных? Чем же тогда полезны базы данных?

"I am modifying the above Question to clear the doubts of readers. 29-02-2012 today."

Кажется, многие мои друзья запутались, потому что я упомянул слово "естественное соединение" в вопросе выше. Реальный пространственный запрос, который я использовал:

select p.OID , r.OID
    from poygons as p , roadshydro as r
                Where st_intersects(p.the_geom , r.the_geom) ;

где таблицы полигонов и roadshydro имеют по два поля: OID, the_geom . Очевидно, что это перекрестное произведение двух таблиц, а не Natural Join по какому-то общему ключу.

Я контролирую потребление основной памяти, когда я выполняю вышеуказанный запрос. Ничего не происходит Там нет ни малейшего количества потребления памяти, и при этом я не получаю никакой информации, но загрузка процессора составляет почти 100%. Кажется, база данных вообще не выполняет никаких вычислений. Однако если я удалю предложение where из запроса, потребление основной памяти постепенно станет слишком высоким (через 5–6 минут), что приведет к сбою системы и внезапному отключению компьютера. Это то, что я испытываю. Что такого особенного в удалении предложения where? почему postgres не может выполнить запрос!! Удивлен таким поведением.

Answer 1

Существует мало смысла в использовании NATURAL JOIN построить. Как уже было сказано, запрос, который вы описываете, произведет произведение двух таблиц, только если соединение соответствует каждой записи в обеих таблицах.

Это произойдет только в том случае, если в обеих таблицах имеется поле с одинаковым именем и одинаковым значением для каждой записи - это крайне маловероятно, но логически невозможно, ИЛИ если в двух таблицах, совпадающих по имени, нет полей.

На твоем месте я бы отказался от NATURAL JOIN в пользу равнины JOIN, указав поля, которые вы хотите сопоставить.

Если это решит крах, то все хорошо, но для меня было бы сюрпризом, если бы это произошло.

Answer 2

Расширяя пример данных Хью, здесь приведен пример двух запросов NATURAL JOIN. Надеемся, что видно, что они "защищены" от проблемы, описанной Хью, и что версия NJ на самом деле менее многословна (и, на мой взгляд, более читабельна), чем версия INNER JOIN.

SELECT *
FROM 
(SELECT FIELD1, DESCR_T1 FROM TABLE1) T1
NATURAL JOIN
(SELECT FIELD1, DESCR_T2 FROM TABLE2) T2;

SELECT * 
FROM 
(SELECT FIELD1, FIELD2, DESCR_T1 FROM TABLE1) T1
NATURAL JOIN
(SELECT FIELD1, FIELD2, DESCR_T2 FROM TABLE2) T2;

Проблема, о которой говорит Хью, не существует, если вы не написали небрежный код. Если вы пишете неаккуратный код, то INNER JOIN тоже "небезопасен". Этот обмен может иллюстрировать то, что естественные объединения не всегда хорошо поняты. Это может быть причиной, почему некоторые люди необоснованно относятся к ним с подозрением.

Answer 3

Это действительно зависит от множества различных факторов, но больше всего от используемой СУБД и ее конфигурации.

Но чтобы устранить самое большое недоразумение: СУБД не должна хранить все строки в памяти: она может записывать во временную таблицу (на жестком диске) и предоставлять вам результат... медленно... поэтому, если она выходит из строя, это не нормально.

Опять же, почему вы спрашиваете 68k*2M строк? Это 136 000 000 000 строк! Вы уверены, что не хотите использовать прямое соединение для какой-то клавиши?

Answer 4

Поскольку меня критикуют за мои комментарии к этому посту, я подготовил пример, чтобы проиллюстрировать свое мнение по этому вопросу.

Следующий скрипт Oracle является иллюстрацией того, что я считаю опасностью, связанной с использованием NATURAL JOIN построить. Я признаю, что это надуманный пример, но в интересах оборонительного развития я считаю, что моя позиция верна.

DROP TABLE TABLE1;
DROP TABLE TABLE2;

CREATE TABLE TABLE1 (
FIELD1   VARCHAR2(10),
FIELD2   VARCHAR2(10),
DESCR_T1 VARCHAR2(20)
);

CREATE TABLE TABLE2 (
FIELD1   VARCHAR2(10),
FIELD2   VARCHAR2(10),
DESCR_T2 VARCHAR2(20)
);

INSERT INTO TABLE1 VALUES('AAA','AAA',    'AAA_AAA_T1'   );
INSERT INTO TABLE1 VALUES('BBB','BBB',    'BBB_BBB_T1'   );
INSERT INTO TABLE1 VALUES('CCC','T1_CCC', 'CCC_T1_CCC_T1');
INSERT INTO TABLE1 VALUES('DDD','T1_DDD', 'DDD_T1_DDD_T1');
INSERT INTO TABLE1 VALUES('EEE',NULL    , 'EEE_NULL_T1'  );

INSERT INTO TABLE2 VALUES('AAA','AAA',    'AAA_AAA_T2'   );
INSERT INTO TABLE2 VALUES('BBB','BBB',    'BBB_BBB_T2'   );
INSERT INTO TABLE2 VALUES('CCC','T2_CCC', 'CCC_T1_CCC_T2');
INSERT INTO TABLE2 VALUES('DDD','T2_DDD', 'DDD_T1_DDD_T2');
INSERT INTO TABLE2 VALUES('EEE',NULL    , 'EEE_NULL_T2'  );

COMMIT;

-- try the following queries and review the results

SELECT 
  FIELD1, DESCR_T1, DESCR_T2 
FROM 
  TABLE1 NATURAL JOIN TABLE2;

SELECT 
  * 
FROM 
  TABLE1 NATURAL JOIN TABLE2;

SELECT 
  TABLE1.FIELD1, TABLE1.DESCR_T1, TABLE2.DESCR_T2 
FROM 
  TABLE1 JOIN 
    TABLE2 ON 
      TABLE2.FIELD1 = TABLE1.FIELD1 AND 
      TABLE2.FIELD2 = TABLE1.FIELD2;

SELECT * FROM 
  TABLE1 NATURAL JOIN TABLE2;

-- Issue the following statement then retry the previous 3 statements.
-- The 'NJs' silently change behaviour and produce radically different results
-- whereas the third requires hands-on attention.  I believe this third behaviour
-- is desirable.  (You could equally drop the column TABLE2.FIELD2 as dportas 
-- has suggested

-- ALTER TABLE TABLE2 RENAME COLUMN FIELD2 TO T2_FIELD2;