如何使用数千个WHERE子句优化SQL查询 [英] How to optimize an SQL query with many thousands of WHERE clauses

问题描述

我对一个超大型数据库有一系列查询，并且在WHERE子句中有成千上万个OR.优化此类SQL查询的最佳和最简便的方法是什么?我找到了一些有关创建临时表和使用联接的文章，但是不确定.我是认真的SQL的新手，并且一直将结果从一个粘贴到另一个.

I have a series of queries against a very mega large database, and I have hundreds-of-thousands of ORs in WHERE clauses. What is the best and easiest way to optimize such SQL queries? I found some articles about creating temporary tables and using joins, but I am unsure. I'm new to serious SQL, and have been cutting and pasting results from one into the next.

SELECT doc_id, language, author, title FROM doc_text WHERE language='fr' OR language='es'
SELECT doc_id, ref_id FROM doc_ref WHERE doc_id=1234567 OR doc_id=1234570 OR doc_id=1234572 OR doc_id=1234596 OR OR OR ...
SELECT ref_id, location_id FROM ref_master WHERE ref_id=098765 OR ref_id=987654 OR ref_id=876543 OR OR OR ...
SELECT location_id, location_display_name FROM location
SELECT doc_id, index_code, FROM doc_index WHERE doc_id=1234567 OR doc_id=1234570 OR doc_id=1234572 OR doc_id=1234596 OR OR OR x100,000

这些未优化的查询每个查询可能要花费24个小时以上.干杯.

These unoptimized query can take over 24 hours each. Cheers.

推荐答案

我想我刚刚回答了自己的问题...嵌套桌！

I think I just answered my own question... NESTED TABLES!

SELECT doc_text.doc_id, doc_text.language, doc_text.author, doc_text.title, doc_ref.ref_id, ref_master.location_id, location.location_display_name, doc_index.doc_id, doc_index.display_heading
FROM DOC_TEXT, DOC_REF, REF_MASTER, LOCATION, DOC_INDEX
WHERE
    doc_text.language='fr' OR doc_text.language='es'
AND
    doc_text.doc_id=doc_ref.doc_id
AND
    doc_ref.doc_id=ref_master.ref_id
AND
    ref_master.location_id=location.location_id
AND
    doc_text.doc_id=doc_index.doc_id

这篇关于如何使用数千个WHERE子句优化SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！