Oracle中的全文搜索优化
Oracle中的全文搜索优化
提问于 2015-01-22 11:50:24
欢迎，我对Oracle数据库有一个优化问题。我想在搜索框中搜索一些与前端用户输入的文本相匹配的对象。我在我的应用程序中使用PHP和Doctrine，Doctrine生成如下代码：
SELECT p0_."BD" AS BD --...
FROM "P" p0_
LEFT JOIN "G" g1_ ON p0_.GID = g1_.ID
LEFT JOIN "PD" p2_ ON p0_.PDID = p2_.ID
LEFT JOIN "PI" p3_ ON p2_.ID = p3_.PDID --ONE TO MANY, SO OPERATOR 'IN' REQUIRED
LEFT JOIN "IT" i4_ ON i4_.ID = p3_.ITID
LEFT JOIN "A" a5_ ON p0_.AID = a5_.ID
LEFT JOIN "P" p6_ ON p0_.MPID = p6_.ID
LEFT JOIN "RS" r7_ ON p0_.RSID = r7_.ID
WHERE (((
LOWER(p2_."FN") LIKE '%abc%'
OR LOWER(p2_."GN") LIKE '%abc%'
OR LOWER(a5_."SN") LIKE '%abc%'
OR LOWER(a5_."CN") LIKE '%abc%'
OR LOWER(a5_."CON") LIKE '%abc%'))
OR p2_."ID" IN
(SELECT p8_."ID"
FROM "PI" p9_
LEFT JOIN "PD" p8_
ON p9_.PDID = p8_.ID
WHERE LOWER(p9_."VALUE") LIKE '%abc%'
))
AND p6_."ID" = p0_."ID";数据库相当大(大约半毫行)，系统返回结果需要40秒左右。
中的“解释计划”显示系统使用哈希连接和全表访问。使用嵌套循环不会改变很多事情(即使使用INDEXes)。
是否有可能以某种方式优化这一过程？
编辑
下面的查询返回相同的结果，但速度要快得多(1.5s)：
SELECT p0_."BD" AS BD --...
FROM "P" p0_
LEFT JOIN "G" g1_ ON p0_.GID = g1_.ID
LEFT JOIN "PD" p2_ ON p0_.PDID = p2_.ID
LEFT JOIN "PI" p3_ ON p2_.ID = p3_.PDID
LEFT JOIN "IT" i4_ ON i4_.ID = p3_.ITID
LEFT JOIN "A" a5_ ON p0_.AID = a5_.ID
LEFT JOIN "P" p6_ ON p0_.MPID = p6_.ID
LEFT JOIN "RS" r7_ ON p0_.RSID = r7_.ID
WHERE
p0_.id IN
(
SELECT p0_A.id FROM "P" p0_A JOIN "PD" p2_A ON (p0_A.PDID = p2_A.ID AND (LOWER(p2_."GN") LIKE '%abc%' OR LOWER(p2_."FN") LIKE '%abc%'))
UNION
SELECT p0_B.id FROM "P" p0_B LEFT JOIN "PD" p2_B ON p0_B.PDID = p2_B.ID JOIN "PI" p3_B ON (p2_B.ID = p3_B.PDID AND(LOWER(p3_B."VALUE") LIKE '%abc%'))
--UNION ANOTHER SELECTS
)
AND p6_."ID" = p0_."ID";诀窍是在部分查询中添加附加条件，而不是左联接。它必须是子查询，因为这个技巧只适用于一个表的字段。不幸的是，教义不支持联合党。甲骨文的优化机制是那么愚蠢，还是我遗漏了什么？也许我能以某种方式修改查询？
伟大的事情！
回答 1
Stack Overflow用户
发布于 2015-05-27 09:15:53
如果不能测试代码，这会是你想要的吗？
SELECT P0_."BD" AS BD --...
FROM "P" P0_
LEFT JOIN "G" g1_ ON p0_.GID = g1_.ID
LEFT JOIN "PD" P2_ ON P0_.PDID = P2_.ID
LEFT JOIN "PI" p3_ ON p2_.ID = p3_.PDID
LEFT JOIN "IT" I4_ ON I4_.ID = P3_.ITID
LEFT JOIN "A" a5_ ON p0_.AID = a5_.ID
LEFT JOIN "P" P6_ ON P0_.MPID = P6_.ID
LEFT JOIN "RS" r7_ ON p0_.RSID = r7_.ID
LEFT JOIN (SELECT P0_A.ID FROM "P" P0_A JOIN "PD" P2_A ON (P0_A.PDID = P2_A.ID AND (LOWER(P2_."GN") LIKE '%abc%' OR LOWER(P2_."FN") LIKE '%abc%'))) XX ON P0_.ID = XX.ID
LEFT JOIN (SELECT P0_B.ID FROM "P" P0_B LEFT JOIN "PD" P2_B ON P0_B.PDID = P2_B.ID JOIN "PI" P3_B ON (P2_B.ID = P3_B.PDID AND(LOWER(P3_B."VALUE") LIKE '%abc%'))) YY ON P0_.ID = YY.ID
WHERE P6_."ID" = P0_."ID"
and (XX.ID is not null or XX.ID is not null);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28088101
复制相关文章
相似问题
腾讯云开发者
