compare two large tables SQL - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>JobHunting - 待字闺中

compare two large tables SQL

compare two large tables SQL# JobHunting - 待字闺中

l*92014-05-08 07:05

1 楼

I am comparing two tables to make sure they are same row by row and column
by column on SQL server.
SELECT *
FROM t1, t2
WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
The tables are vey large, more than 100 million.
I got error:
ERROR [HY000] ERROR: 9434 : Not enough memory for merge-style join
Are there better ways to do this comparison.
thanks !

l*82014-05-08 07:05

2 楼

CREATE UNIQUE INDEX
ON t1(column1, column2, column3);
CREATE UNIQUE INDEX
ON t2(column1, column2, column3);
SELECT 'different'
FROM dual
WHERE EXIST
(
SELECT *
FROM t1, t2
WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
);

【在 l******9 的大作中提到】

: I am comparing two tables to make sure they are same row by row and column
: by column on SQL server.
: SELECT *
: FROM t1, t2
: WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
: AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
: The tables are vey large, more than 100 million.
: I got error:
: ERROR [HY000] ERROR: 9434 : Not enough memory for merge-style join
: Are there better ways to do this comparison.

l*92014-05-08 07:05

3 楼

i got error:
CREATE UNIQUE INDEX Pindex1
ON t1 (column1, column2, column3);
ERROR [HY000] ERROR: CREATE INDEX not supported in this release
Thanks

【在 l*********8 的大作中提到】

: CREATE UNIQUE INDEX
: ON t1(column1, column2, column3);
: CREATE UNIQUE INDEX
: ON t2(column1, column2, column3);
: SELECT 'different'
: FROM dual
: WHERE EXIST
: (
: SELECT *
: FROM t1, t2

l*82014-05-08 07:05

4 楼

不让建索引太奇怪了。
看了一下，你这个好像是IBM一个系统里的SQL。不知道有哪些限制。

【在 l******9 的大作中提到】

: i got error:
: CREATE UNIQUE INDEX Pindex1
: ON t1 (column1, column2, column3);
: ERROR [HY000] ERROR: CREATE INDEX not supported in this release
: Thanks