| Incremental logic on a complex query [message #298222] |
Tue, 05 February 2008 06:42  |
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 #298239 is a reply to message #298232] |
Tue, 05 February 2008 07:46  |
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 #298256 is a reply to message #298239] |
Tue, 05 February 2008 08:21 |
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 #298267 is a reply to message #298256] |
Tue, 05 February 2008 08:53 |
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
Regards
Raj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
