Home » RDBMS Server » Performance Tuning » Incremental logic on a complex query
Incremental logic on a complex query [message #298222] Tue, 05 February 2008 06:42 Go to next message
iammanohar
Messages: 3
Registered: February 2008
Junior Member
I have a complex query. I need to extract the latest updated records using that query. This query contains the following tables....

"INV"."MTL_CATEGORY_SETS_TL"
"APPS"."MFG_LOOKUPS"
"APPS"."MFG_LOOKUPS"
"APPS"."MFG_LOOKUPS"
"APPS"."MFG_LOOKUPS"
"APPLSYS"."FND_CURRENCIES_TL"
"GL"."GL_SETS_OF_BOOKS"
"APPS"."HR_ORGANIZATION_UNITS"
"INV"."MTL_PARAMETERS"
"APPS"."ORG_ORGANIZATION_DEFINITIONS"
"APPS"."MTL_ITEM_FLEXFIELDS"
"BOM"."CST_COST_TYPES"
"BOM"."CST_ITEM_COSTS"
"INV"."MTL_SYSTEM_ITEMS_B"
"INV"."MTL_ITEM_CATEGORIES"
"INV"."MTL_CATEGORIES_B"
"APPLSYS"."FND_FLEX_VALUES_TL"
"APPLSYS"."FND_FLEX_VALUE_SETS"
"APPLSYS"."FND_FLEX_VALUES"
inv.mtl_atp_rules

How to findout the main sources(tables) in that query?
On which tables I have to implement the incremental logic?
Is there any specific way to find out the main tables in sql script?


Thanks in advance.
Re: Incremental logic on a complex query [message #298232 is a reply to message #298222] Tue, 05 February 2008 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "main".

Quote:
I need to extract the latest updated records using that query

A query does not update records.
You don't post any query.

Clear your post (and your mind), take a breath, and repost in a comprehensible way.

Regards
Michel
Re: Incremental logic on a complex query [message #298239 is a reply to message #298232] Tue, 05 February 2008 07:46 Go to previous messageGo to next message
iammanohar
Messages: 3
Registered: February 2008
Junior Member
you might misunderstand my question...
Please find that query in the attachment.
Here I implemented incremental logic in all the tables...
My question is "Is there any way to remove the incremental logic on some tables?"
Thanks......
Re: Incremental logic on a complex query [message #298249 is a reply to message #298239] Tue, 05 February 2008 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
you might misunderstand my question...

I don't understand at all your question. Not the single bit.

What is "incremental logic on some tables"? Can you explain that?
Can you explain the whole question?

Regards
Michel

[Updated on: Tue, 05 February 2008 08:08]

Report message to a moderator

Re: Incremental logic on a complex query [message #298256 is a reply to message #298239] Tue, 05 February 2008 08:21 Go to previous messageGo to next message
iammanohar
Messages: 3
Registered: February 2008
Junior Member
Hi Michel,
By using that script, I am extracting the records during a period of time like one day.. (I have taken sysdate and sysdate -1).
Therefore I mentioned as incremental logic..

Incremental LogicL: extracting the records for a time period like one day or two days etc..

What are you understanding my oberving that query?
It's very simple... I am extracting records which are updated for one day before. This script is taking lot of time to complete. I am trying to tune this one..

Thanks.....
Re: Incremental logic on a complex query [message #298257 is a reply to message #298256] Tue, 05 February 2008 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now the question is clear.
But now you have to provide the requested information for query tuning you'll find in OraFAQ Forum Guide and How to Identify Performance Problem and Bottleneck and many similar questions in Performance forum.

Regards
Michel
Re: Incremental logic on a complex query [message #298267 is a reply to message #298256] Tue, 05 February 2008 08:53 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This has nothing to do with performance but something to do with the query logic
Quote:

inv.mtl_atp_rules r
....
AND (r.RULE_ID(+) = "MTL_SYSTEM_ITEMS_B".atp_rule_id)
.....
(R.LAST_UPDATE_DATE >= SYSDATE - 1 AND R.LAST_UPDATE_DATE <= SYSDATE))

In the above statement you are outerjoining mtl_atp_rules with another table but further down you are referring a column from the outer joined table with the sysdate. But I am not able to find any outerjoin clause there. By doing it this way you will be missing some records. I am giving you an example. I know it's not a great example but just to prove the point.
SQL> select * from table_a;

       SNO TEXT
---------- ----------
         1 one
         2 two
         3 three
         4 one

SQL> select * from table_b;

       SNO TEXT
---------- ----------
         1 one
         2 two


SQL> select a.sno, a.text, b.sno, b.text from table_a a, table_b b
  2  where a.sno = b.sno (+)
  3  and
  4  b.text = 'one';

       SNO TEXT              SNO TEXT
---------- ---------- ---------- ----------
         1 one                 1 one


  1  select a.sno, a.text, b.sno, b.text from table_a a, table_b b
  2  where a.sno = b.sno (+)
  3  and
  4* b.text (+) = 'one'
SQL> /

       SNO TEXT              SNO TEXT
---------- ---------- ---------- ----------
         1 one                 1 one
         2 two
         3 three
         4 one
Having said that this could be your intention but in case if you haven't thought about it.

Regards

Raj
Previous Topic: oracle 10g analyze table temporary segments
Next Topic: DB deadlock issue
Goto Forum:
  


Current Time: Fri Dec 09 05:37:27 CST 2016

Total time taken to generate the page: 0.09522 seconds