请教一个sql语句的问题_20051014fw_khhfdb表1192132条记录,主键是djid,另外,对fwdwid字段建有一个索引,对hfrq 字段也建立了一个索引,在toad中执行以下语句:SELECT COUNT (*)
FROM fw_khhfdb
WHERE fwdwid =559
AND hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')很快得到结果:12548 但是执行以下各语句时,费时很久,跟死机一样:SELECT COUNT (*)
FROM fw_khhfdb
WHERE (fwdwid =559 or fwdwid = 1119)
AND hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')或者:SELECT COUNT (*)
FROM fw_khhfdb
WHERE hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')
AND (fwdwid =559 or fwdwid = 1119)或者:SELECT COUNT (*)
FROM fw_khhfdb
WHERE HFFS = 1
AND fwdwid =559
AND hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')其中,HFFS 是一个只有0、1两个值的字段,未单独建有索引。请帮我分析一下,多谢!
FROM fw_khhfdb
WHERE fwdwid =559
AND hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')很快得到结果:12548 但是执行以下各语句时,费时很久,跟死机一样:SELECT COUNT (*)
FROM fw_khhfdb
WHERE (fwdwid =559 or fwdwid = 1119)
AND hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')或者:SELECT COUNT (*)
FROM fw_khhfdb
WHERE hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')
AND (fwdwid =559 or fwdwid = 1119)或者:SELECT COUNT (*)
FROM fw_khhfdb
WHERE HFFS = 1
AND fwdwid =559
AND hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')其中,HFFS 是一个只有0、1两个值的字段,未单独建有索引。请帮我分析一下,多谢!
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 31
SORT AGGREGATE 1 12
VIEW FWGL.index$_join$_001 7 84 31
HASH JOIN 7 84
INDEX RANGE SCAN FWGL.INX_FW_KHHFDB_FWDWID 7 84 39.00002622
INDEX RANGE SCAN FWGL.INX_FW_KHHFDB_HFRQ 7 84 39.00002622 第二个语句的执行计划:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStopSELECT STATEMENT Optimizer Mode=CHOOSE 1 13
SORT AGGREGATE 1 12
VIEW FWGL.index$_join$_001 13 156 13
HASH JOIN 13 156
INDEX RANGE SCAN FWGL.INX_FW_KHHFDB_HFRQ 13 156 54.25002254
INLIST ITERATOR
INDEX RANGE SCAN FWGL.INX_FW_KHHFDB_FWDWID 13 156 54.25002254
我不太明白,请帮我分析一下
SELECT COUNT (*)
FROM fw_khhfdb
WHERE hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')
AND fwdwid = 1119union
SELECT COUNT (*)
FROM fw_khhfdb
WHERE hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')
AND fwdwid =559
这个和你上面的不是一个意思啊
SELECT COUNT (*)
FROM fw_khhfdb
WHERE HFFS = 1
AND fwdwid =559
AND hfrq >= TO_DATE ('2005-10-01', 'yyyy-MM-dd')
AND hfrq <= TO_DATE ('2005-10-13', 'yyyy-MM-dd')其中,HFFS 是一个只有0、1两个值的字段,未单独建有索引。