SELECT "仓","工艺号","品名","成份","颜色","规格","数量","MYTYPE" FROM YHJ_V_LJXS LSXS WHERE EXISTS( SELECT 1 FROM YHJ_V_DPBHXQ2 DPBHXQ WHERE TRIM(DPBHXQ.工艺号)=LSXS.工艺号 AND TRIM(DPBHXQ.颜色)=LSXS.颜色)
创建基于这两个字段的trim联合索引 CREATE INDEX idx_234234 ON YHJ_V_DPBHXQ2(TRIM(工艺号),TRIM(颜色))
貌似没有好的办法了吗?用from tablea a,tableb b where a.id=b.id 会有冗余数据产生。但是他执行的速度很快。一会就有结果。 但这不符合设计要求。大家还有好方法吗?
改成内关联试试 SELECT "仓", "工艺号", "品名", "成份", "颜色", "规格", "数量", "MYTYPE" FROM (SELECT TRIM(LSXS.工艺号) || TRIM(LSXS.颜色) AS J ,"仓" ,"工艺号" ,"品名" ,"成份" ,"颜色" ,"规格" ,"数量" ,"MYTYPE" FROM YHJ_V_LJXS LSXS) A ,(SELECT DISTINCT TRIM(DPBHXQ.工艺号) || TRIM(DPBHXQ.颜色) AS J FROM YHJ_V_DPBHXQ2 DPBHXQ) B WHERE A.J = B.J
对于in 和 exists的性能区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。 其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了 有两个简单例子,以说明 “exists”和“in”的效率问题1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ; T1数据量小而T2数据量非常大时,T1<<T2 时,1) 的查询效率高。2) select * from T1 where T1.a in (select T2.a from T2) ; T1数据量非常大而T2数据量小时,T1>>T2 时,2) 的查询效率高。
用表联就行了,参考代码如下: SELECT "仓","工艺号","品名","成份","颜色","规格","数量","MYTYPE" FROM YHJ_V_LJXS LSXS, --26 TRIM(LSXS.工艺号||LSXS.颜色) IN ( SELECT TRIM(DPBHXQ.工艺号) 工艺号,TRIM(DPBHXQ.颜色) 颜色 FROM YHJ_V_DPBHXQ2 DPBHXQ --231 ) AY WHERE LSXS.工艺号=AY.工艺号 AND LSXS.颜色=AY.颜色
多了一段忘记拿掉SELECT "仓","工艺号","品名","成份","颜色","规格","数量","MYTYPE" FROM YHJ_V_LJXS LSXS, --26 ( SELECT TRIM(DPBHXQ.工艺号) 工艺号,TRIM(DPBHXQ.颜色) 颜色 FROM YHJ_V_DPBHXQ2 DPBHXQ --231 ) AY WHERE LSXS.工艺号=AY.工艺号 AND LSXS.颜色=AY.颜色
SELECT "仓","工艺号","品名","成份","颜色","规格","数量","MYTYPE" FROM YHJ_V_LJXS LSXS --执行要26秒 3万行3万行数据26秒,这个先优化了 看看试图怎么生成的。
这个很快,说明相关索引是有的。 YHJ_V_DPBHXQ2 是小表,连接时作为驱动表,走YHJ_V_LJXS 上的索引 In写法没有问题,要命的是在字段上加了trim函数并做字符串连接 如果不方便建函数索引,建议在表中把格式改好 写成: SELECT "仓","工艺号","品名","成份","颜色","规格","数量","MYTYPE" FROM YHJ_V_LJXS LSXS --26 WHERE (LSXS.工艺号,LSXS.颜色) IN ( SELECT trim(DPBHXQ.工艺号),trim(DPBHXQ.颜色) FROM YHJ_V_DPBHXQ2 DPBHXQ --231 )
WHERE EXISTS(
SELECT 1 FROM YHJ_V_DPBHXQ2 DPBHXQ WHERE TRIM(DPBHXQ.工艺号)=LSXS.工艺号 AND TRIM(DPBHXQ.颜色)=LSXS.颜色)
CREATE INDEX idx_234234 ON YHJ_V_DPBHXQ2(TRIM(工艺号),TRIM(颜色))
但这不符合设计要求。大家还有好方法吗?
FROM YHJ_V_DPBHXQ2 DPBHXQ --231
)做成临时表多好。用with as 也可以。
----------------------------------------------------
CREATE INDEX idx_234234 ON YHJ_V_DPBHXQ2(TRIM(工艺号),TRIM(颜色))
加函数级索引,即TRIM(工艺号),TRIM(颜色)
如果来源与多个表可以单独创建索引。
我是想去掉多余的空格。
SELECT "仓", "工艺号", "品名", "成份", "颜色", "规格", "数量", "MYTYPE"
FROM (SELECT TRIM(LSXS.工艺号) || TRIM(LSXS.颜色) AS J
,"仓"
,"工艺号"
,"品名"
,"成份"
,"颜色"
,"规格"
,"数量"
,"MYTYPE"
FROM YHJ_V_LJXS LSXS) A
,(SELECT DISTINCT TRIM(DPBHXQ.工艺号) || TRIM(DPBHXQ.颜色) AS J
FROM YHJ_V_DPBHXQ2 DPBHXQ) B
WHERE A.J = B.J
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了 有两个简单例子,以说明 “exists”和“in”的效率问题1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ; T1数据量小而T2数据量非常大时,T1<<T2 时,1) 的查询效率高。2) select * from T1 where T1.a in (select T2.a from T2) ; T1数据量非常大而T2数据量小时,T1>>T2 时,2) 的查询效率高。
SELECT "仓","工艺号","品名","成份","颜色","规格","数量","MYTYPE"
FROM YHJ_V_LJXS LSXS, --26
TRIM(LSXS.工艺号||LSXS.颜色) IN
(
SELECT TRIM(DPBHXQ.工艺号) 工艺号,TRIM(DPBHXQ.颜色) 颜色 FROM YHJ_V_DPBHXQ2 DPBHXQ --231
) AY
WHERE LSXS.工艺号=AY.工艺号 AND LSXS.颜色=AY.颜色
FROM YHJ_V_LJXS LSXS, --26
(
SELECT TRIM(DPBHXQ.工艺号) 工艺号,TRIM(DPBHXQ.颜色) 颜色 FROM YHJ_V_DPBHXQ2 DPBHXQ --231
) AY
WHERE LSXS.工艺号=AY.工艺号 AND LSXS.颜色=AY.颜色
YHJ_V_DPBHXQ2 是小表,连接时作为驱动表,走YHJ_V_LJXS 上的索引
In写法没有问题,要命的是在字段上加了trim函数并做字符串连接
如果不方便建函数索引,建议在表中把格式改好
写成:
SELECT "仓","工艺号","品名","成份","颜色","规格","数量","MYTYPE" FROM YHJ_V_LJXS LSXS --26
WHERE
(LSXS.工艺号,LSXS.颜色) IN
(
SELECT trim(DPBHXQ.工艺号),trim(DPBHXQ.颜色) FROM YHJ_V_DPBHXQ2 DPBHXQ --231
)
露珠大胆尝试吗。如果不符合要求是不是可以修改某些条件?
SELECT "仓","工艺号","品名","成份","颜色","规格","数量","MYTYPE" FROM YHJ_V_LJXS LSXS --26
WHERE
TRIM(LSXS.工艺号||LSXS.颜色) IN
(
SELECT /*+ no_merge */TRIM(DPBHXQ.工艺号)||TRIM(DPBHXQ.颜色) FROM YHJ_V_DPBHXQ2 DPBHXQ --231
) 不过最好还是把执行计划发出来看一下