cancel
Showing results for 
Search instead for 
Did you mean: 

Webi Issue - SQL Performance

former_member402770
Participant
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

it is not webi perfomance issue. It is your misqualification with sql perfomance tuning

Your query is not use any indexes becouse you use function of column.

For using indexes condition of your query must be

somecolumn<=somefunction(someconstant) and

somecolumn>=somefunction(someconstant)

former_member402770
Participant
0 Kudos

Hi Kalyagin,

Worked with DBA to get the above recommendation on the expression mentioned under where clause

clearly says this expression needs tobe indexed.

I donot see ur syntax condition under where clause tobe indexed.

Can you be specific to the above issue?

Regards,

Dinya

bernhard_keimel
Active Participant
0 Kudos

Which database di you address? Oracle?

Without going deeper:

TRUNC(TRUNC("SALES_FACT"."TRANSACTION_DATE")) -->

1) double trunc does not make sense. Reduce to one trunc. maybe this already helps.

2) when you have joined a dimension on that col, assure that the dimension has this transaction date as truncated day as a table column and an index on it. use then the dimension column

3) without a linked dimension, create another colum for this truncated transaction_date and create an index on it

4) assure that all addressed columns in the where clause also are indexed

former_member402770
Participant
0 Kudos

Hi Keimal,

Thanks for the early response. Using Oracle as source.

point 2), 3), 4) is not clear to me, could you screenshot with an example.

also transaction_date needs an index tobe created?

Thanks,

Dinya.

bernhard_keimel
Active Participant
0 Kudos

ad 4> Sorry little mistake, i mean to check that each column addressed in the where clause is indexed, if possible.

do you have someone around you that can help you with that? to go into data model optimization this is not the right place and i do not have the time to elaborate all details. someone aware of database design and optimization will understand 😉

Basically i said that you need an index on the column TRANSACTION_DATE. But as you are using the TRUNC() function to cut off the time, it would make sense to have an additional column that keeps the truncated value and index this new column. But in case you have a star/snowflake schema this column might be referenced by a dimension table, where this additional column (the truncated date) is already present. Then use this column in your report + universe instead of transaction_date.

I guess with this explanation can someone do optimization that has the knowledge to do so.

bernhard_keimel
Active Participant
0 Kudos

ad 4> aaahhh and again: in the CASE statement 🙂