Support identified as existing Bug 20214168 (QUERY WITH CASE CLAUSE IS
RETURNING INCORRECT RESULT)
On 6 August 2015 at 07:19, Patrick Jolliffe <jolliffe@xxxxxxxxx> wrote:
Nice one, thanks Tanel.
SQL> ALTER SESSION SET EVENTS '10055 trace name context forever, level
0x200';
Session
altered.
SQL>
SQL> SELECT /*+ gather_plan_statistics tanel */
SUM(
2
CASE
3 WHEN (CHRTYPE = 'R03' OR CHRTYPE =
'LDU')
4 THEN RATE
/10
5 END
)
6 FROM
TEST_FACT;
SUM(CASEWHEN(CHRTYPE='R03'ORCHRTYPE='LDU')THENRATE/10END)
---------------------------------------------------------
.281
SQL>
On 6 August 2015 at 07:04, Stefan Koehler <contact@xxxxxxxx> wrote:
Hi Tanel,
maybe a little OT, but i don't want to miss the chance as you already
have given a basic description about rowsets.
Martin Bach asked me about the rowset spec in the column projection
information some time ago and i could not figure out (or find) any details
about
it. The question was in a different context (FTS in his case), but the
only stuff i could figure out was event 10055 and some hidden parameters to
control it. You can find my test case here:
http://www.soocs.de/public/research/150120_dbms_xplan_count_rowset.txt
Do you have any more details about the implementation of this pipeline
optimization (rowsets)?
Thank you very much.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Tanel Poder <tanel@xxxxxxxxxxxxxx> hat am 5. August 2015 um 23:37geschrieben:
yet another execution plan pipeline optimization to save a few CPU cycles,
Just in case you're wondering what the heck this is - rowsets are a
which is apparently buggy. The CASE statements are evaluated in EVAfunctions, thus 0x200 should help (helped on my exadata test).
--
//www.freelists.org/webpage/oracle-l
