sql优化or语句

《sql优化or语句》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

 如何优化带or条件的sql----------本文章转自网络，互相学习，互相帮助以下为转帖内容：================================================================================================今天在论坛上看到了一个帖子，问题如下：select*  fromccwhere((a1='ffff'andz1='mmmm')or(b1='sss'andz2='nnnn')) andc1='ggggg'其中表有30万行数据，返回的数据10行左右，怎样创建index访问最快。    按照别人的说法测试了一下，步骤如下：createtableCC(A1VARCHAR2(5),Z1VARCHAR2(5),B1VARCHAR2(5),Z2VARCHAR2(5),C1VARCHAR2(5))insertintoccvalues('dffd','dfsd','fdf','fdsfs','sfds');--重复插入2097152条，对查询时间可能有影响SQL>selectcount(*)fromcc;COUNT(*)----------  2097160SQL>settimingonSQL>edit已写入fileafiedt.buf1select*fromcc2where((a1='ffff'andz1='mmmm')or(b1='sss'andz2='nnnn'))3*andc1='ggggg'--无索引情况下or查询SQL>/A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnnggggg ffffmmmmmsss  nnnngggggffffmmmmmsss  nnnnggggg已选择8行。已用时间:00:00:00.21SQL>edit已写入fileafiedt.buf1select*fromcc2where(a1='ffff'andz1='mmmm')3andc1='ggggg'4union5select*fromcc6where(b1='sss'andz2='nnnn')7*andc1='ggggg'--无索引情况下union查询，注意与unionall查询结果的区别SQL>/A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnnggggg已用时间:00:00:00.33SQL>edit已写入fileafiedt.buf1select*fromcc2where(a1='ffff'andz1='mmmm')3andc1='ggggg'4unionall5select*fromcc6where(b1='sss'andz2='nnnn')7*andc1='ggggg'--无索引情况下unionall查询SQL>/A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnnggggg已选择8行。已用时间:00:00:00.35SQL>createindexcc_idxoncc(c1);索引已创建。 已用时间:00:00:11.14SQL>edit已写入fileafiedt.buf1select*fromcc2where((a1='ffff'andz1='mmmm')or(b1='sss'andz2='nnnn'))3*andc1='ggggg'--有索引or查询，注意，虽然没有列出执行计划，索引肯定用到了。SQL>/A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnnggggg已选择8行。已用时间:00:00:00.01SQL>edit已写入fileafiedt.buf1select*fromcc2where(a1='ffff'andz1='mmmm')3andc1='ggggg'4union5select*fromcc6where(b1='sss'andz2='nnnn')7*andc1='ggggg'--有索引union查询，注意与unionall查询结果的区别SQL>/A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnnggggg已用时间:00:00:00.00SQL>edit已写入fileafiedt.buf1select*fromcc2where(a1='ffff'andz1='mmmm')3andc1='ggggg'4unionall5select*fromcc6where(b1='sss'andz2='nnnn')7*andc1='ggggg'--有索引unionall查询SQL>/ A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnnggggg已选择8行。已用时间:00:00:00.01SQL>createindexCC_IDX2onCC(A1,Z1);SQL>createindexCC_IDX3onCC(B1,Z2);SQL>setautotonSQL>edit已写入fileafiedt.buf1select*fromcc2where((a1='ffff'andz1='mmmm')or(b1='sss'andz2='nnnn'))3*andc1='ggggg'--3索引情况下or查询SQL>/A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnnggggg已选择8行。已用时间:00:00:00.60--时间明显比单索引扫描时间长执行计划----------------------------------------------------------Planhashvalue:1540710700---------------------------------------------------------------------------|Id|Operation         |Name|Rows|Bytes|Cost(%CPU)|Time    |---------------------------------------------------------------------------|  0|SELECTSTATEMENT  |     |    2|   40|    4  (0)|00:00:01||  1|CONCATENATION    |     |      |      |           |         ||*2|  TABLEACCESSFULL|CC  |    1|   20|    2  (0)|00:00:01 |--注意：从执行计划上可以看到，|*3|  TABLEACCESSFULL|CC  |    1|   20|    2  (0)|00:00:01 |--索引失效，全表扫描--------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid):---------------------------------------------------  2-filter("B1"='sss'AND"Z2"='nnnn'AND"C1"='ggggg')  3-filter("C1"='ggggg'AND"A1"='ffff'AND"Z1"='mmmm'AND             (LNNVL("B1"='sss')ORLNNVL("Z2"='nnnn')))Note-----  -dynamicsamplingusedforthisstatement统计信息----------------------------------------------------------         0recursivecalls         0dbblockgets     17673consistentgets       405physicalreads         0redosize       703bytessentviaSQL*Nettoclient       400bytesreceivedviaSQL*Netfromclient         2SQL*Netroundtripsto/fromclient         0sorts(memory)         0sorts(disk)         8rowsprocessedSQL>edit已写入fileafiedt.buf1select*fromcc2where(a1='ffff'andz1='mmmm')3andc1='ggggg'4union5select*fromcc6where(b1='sss'andz2='nnnn')7*andc1='ggggg'--3索引情况下union查询，注意与unionall查询结果的区别SQL>/A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnnggggg已用时间:00:00:00.10--时间明显比单索引扫描时间长执行计划----------------------------------------------------------Planhashvalue:1185376162-----------------------------------------------------------------------------------------|Id|Operation                    |Name   |Rows|Bytes|Cost(%CPU)|Time    |-------------------------------------------------------------------------------- ---------|  0|SELECTSTATEMENT             |        |    4|   80|   10(60)|00:00:01||  1|SORTUNIQUE                 |        |    4|   80|   10(60)|00:00:01||  2|  UNION-ALL                  |        |      |      |           |       ||*3|   TABLEACCESSBYINDEXROWID|CC     |    2|   40|    4  (0)|00:00:01||*4|    INDEXRANGESCAN         |CC_IDX2|   34|      |    3  (0)|00:00:01||*5|   TABLEACCESSBYINDEXROWID|CC     |    2|   40|    4  (0)|00:00:01||*6|    INDEXRANGESCAN         |CC_IDX3|   34|      |    3  (0)|00:00:01|-----------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------  3-filter("C1"='ggggg')  4-access("A1"='ffff'AND"Z1"='mmmm')  5-filter("C1"='ggggg')  6-access("B1"='sss'AND"Z2"='nnnn')Note-----  -dynamicsamplingusedforthisstatement统计信息----------------------------------------------------------         9recursivecalls         0dbblockgets       174consistentgets         7physicalreads         0redosize       637bytessentviaSQL*Nettoclient       400bytesreceivedviaSQL*Netfromclient         2SQL*Netroundtripsto/fromclient         1sorts(memory)--注意，进行了排序         0sorts(disk)         1rowsprocessedSQL>edit已写入fileafiedt.buf 1select*fromcc2where(a1='ffff'andz1='mmmm')3andc1='ggggg'4unionall5select*fromcc6where(b1='sss'andz2='nnnn')7*andc1='ggggg'--3索引情况下unionall查询SQL>/A1   Z1   B1   Z2   C1-------------------------ffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnngggggffffmmmmmsss  nnnnggggg已选择8行。已用时间:00:00:00.06--时间明显比单索引扫描时间长执行计划----------------------------------------------------------Planhashvalue:198920981----------------------------------------------------------------------------------------|Id|Operation                   |Name   |Rows|Bytes|Cost(%CPU)|Time    |----------------------------------------------------------------------------------------|  0|SELECTSTATEMENT            |        |    4|   80|    8(50)|00:00:01||  1|UNION-ALL                  |        |      |      |           |      ||*2|  TABLEACCESSBYINDEXROWID|CC     |    2|   40|    4  (0)|00:00:01||*3|   INDEXRANGESCAN         |CC_IDX2|   34|      |    3  (0)|00:00:01||*4|  TABLEACCESSBYINDEXROWID|CC     |    2|   40|    4  (0)|00:00:01||*5|   INDEXRANGESCAN         |CC_IDX3|   34|      |    3  (0)|00:00:01|---------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid):---------------------------------------------------  2-filter("C1"='ggggg')  3-access("A1"='ffff'AND"Z1"='mmmm')  4-filter("C1"='ggggg')  5-access("B1"='sss'AND"Z2"='nnnn')Note-----  -dynamicsamplingusedforthisstatement统计信息----------------------------------------------------------         7recursivecalls         0dbblockgets       175consistentgets         0physicalreads         0redosize       703bytessentviaSQL*Nettoclient       400bytesreceivedviaSQL*Netfromclient         2SQL*Netroundtripsto/fromclient         0sorts(memory)         0sorts(disk)         8rowsprocessedSQL>在数据库中，UNION和UNIONALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：select*fromgc_dfysunionselect*fromls_jg_dfys这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。而UNIONALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。从效率上说，UNIONALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNIONALL。这个从论坛上摘下来的：emp表比较大时，而且deptno=10条件能查询出表中大部分的数据如(50%)。如该表共有4000万行数据，共放在有500000个数据块中，每个数据块为8k，则该表共有约4G，则这么多的数据不可能全放在内存中，绝大多数需要放在硬盘上。此时如果该查询通过索引查询，则是你梦魇的 开始。db_file_multiblock_read_count参数的值200。如果采用全表扫描，则需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引扫描，假设deptno列上的索引都已经cache到内存中，所以可以将访问索引的开销忽略不计。因为要读出4000万x50%=2000万数据，假设在读这2000万数据时，有99.9%的命中率，则还是需要20000次I/O,比上面的全表扫描需要的2500次多多了，所以在这种情况下，用索引扫描反而性能会差很多。在这样的情况下，用全表扫描的时间是固定的，但是用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。建立合适的索引，写适当的语句，才能达到最优。