Webi Issue - SQL Performance

297 Views

Hi Experts,

I am facing long running webi report with the below logic diagonistic as issue causing the problem.

It will be much much appreciable if experts can let me know how to resolve this issue.

- Rewrite the below predicate into an equivalent form to take advantage of

indices. Alternatively, create a function-based index on the expression.2- Restructure SQL finding (see plan 1 in explain plans section)

----------------------------------------------------------------

The predicate TRUNC(TRUNC("SALES_FACT"."TRANSACTION_DATE"))<=CASE

WHEN (TO_DATE('20-12-2021 00:00:00')<TO_DATE(' 2015-03-21 00:00:00',

'syyyy-mm-dd hh24:mi:ss')) THEN TO_DATE(' 2015-03-21 00:00:00', 'syyyy-mm-dd

hh24:mi:ss') ELSE TO_DATE('20-12-2021 00:00:00') END used at line ID 10 of

the execution plan contains an expression on indexed column "SYS_NC00039$".

This expression prevents the optimizer from selecting indices on table

"WIN"."SALES_FACT". Recommendation

Thanks,

Dinya