OraFAQ Forum: Performance Tuning » Please help me to improve the performance of this query.

Home » RDBMS Server » Performance Tuning » Please help me to improve the performance of this query. (Oracle 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Re: Please help me to improve the performance of this query. [message #592760 is a reply to message #592697]

Mon, 12 August 2013 03:03

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi

Actually I am looking for alternative logic to avoid the below.

filter(INTERNAL_FUNCTION("SP"."EFFECTIVE_START_DATE")<SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) AND
              INTERNAL_FUNCTION("SP"."EFFECTIVE_END_DATE")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592779 is a reply to message #592760]

Mon, 12 August 2013 05:58

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

The filter is due to the predicate -

AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
     OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
     OR sp.effective_end_date IS NULL)

You have to tell what does it mean in your code and what type it is DATE or TIMESTAMP? Per the explain plan you posted earlier, this does not seem to be much of a heck, compared to the FTS on FAX_HEADER and other tables for which already a lot of suggestions have been made.

Did you try the function-based index using TRUNC(CAST(<date column>))... Did it avoid FTS? You must see an INDEX RANGE SCAN to verify.

Report message to a moderator

Pages (2): [ « ‹ 1 2]

Previous Topic:	Optimize Query using Explain Plan
Next Topic:	about index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:00:49 CDT 2024