BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Table comparison with sorted input option


 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
illo
Forum Member
Forum Member



Joined: 07 Jun 2005

Posts: 19
Location: Hamburg


flag
PostPosted: Tue Mar 14, 2006 4:14 am 
Post subject: Table comparison with sorted input option

Hi all,
i have a problem using tc transform with big tables. I added the same primary key constraint (index) on both tables but tc-transform reports error: Input ... is not ordered by the transform's primary key...
How does it figure this out? Do i have to declare the pk-index somewhere?
In first run, my comparison table is empty. Could this cause a problem?

Thanx,
Illo

My environment is Win2003; Oracle10g; DI 11.5
Back to top
eepjr24
Forum Enthusiast
Forum Enthusiast



Joined: 16 Sep 2005
ASUG Icon
Posts: 1815
Location: Orlando, FL


flag
PostPosted: Tue Mar 14, 2006 7:37 am 
Post subject: Re: Table comparison with sorted input option

Forgive me if this is too obvious of a question, but have you verified that the PK you set in the trasform is exactly the same fields in exactly the same order as the PK you set on the tables?

- Ernie
Back to top
illo
Forum Member
Forum Member



Joined: 07 Jun 2005

Posts: 19
Location: Hamburg


flag
PostPosted: Tue Mar 14, 2006 8:13 am 
Post subject: Same order and sorting

Hi Ernie,
icon_mrgreen.gif yes the pk contains the same columns in the same sequence with the same ordering (ascending) in both tables.
So this should work, doesn't it?
I wondered if i have to sort the table even if a corresponding pk is defined...
Back to top
eepjr24
Forum Enthusiast
Forum Enthusiast



Joined: 16 Sep 2005
ASUG Icon
Posts: 1815
Location: Orlando, FL


flag
PostPosted: Tue Mar 14, 2006 8:56 am 
Post subject: Re: Table comparison with sorted input option

Not to belabor the point, but is the key the same in the TRANSFORM as it is in both the tables? In other words, do you have a query transform feeding into the table transform and if so have you set the primary key in the transform to be the same as the others?

- E
Back to top
illo
Forum Member
Forum Member



Joined: 07 Jun 2005

Posts: 19
Location: Hamburg


flag
PostPosted: Tue Mar 14, 2006 9:08 am 
Post subject: Re: Table comparison with sorted input option

Hm.
I feed the transform directly from a (template) table which has the correct pk defined (validated on th database, too)
any more ideas?

by the way: does this mean i can use this only with standard (asc) order?
Back to top
eepjr24
Forum Enthusiast
Forum Enthusiast



Joined: 16 Sep 2005
ASUG Icon
Posts: 1815
Location: Orlando, FL


flag
PostPosted: Tue Mar 14, 2006 9:12 am 
Post subject: Re: Table comparison with sorted input option

Not sure on the ordering, I was just trying to eliminate things that might be your problem. One more shot and then I will let others give it a whirl or you can try opening a case. If you import the template table, does that fix the issue?

- Ernie
Back to top
illo
Forum Member
Forum Member



Joined: 07 Jun 2005

Posts: 19
Location: Hamburg


flag
PostPosted: Tue Mar 14, 2006 9:56 am 
Post subject: Re: Table comparison with sorted input option

I tried, but no difference.

I have something like the following:

TABLE_A
* KEY_B,
* KEY_C,
COL_D

TABLE_B
ID_A,
* KEY_B,
* KEY_C,
COL_D

*) Key symbol in DI-table definiton

with the same pk-index (KEY_C, KEY_B).
In the TC Transform input table is TABLE_A and comparison is TABLE_B
Input pk-columns are KEY_C, KEY_B
Compare column is COL_D
Generated key column is ID_A

Even if i remove the key-symbols (but keep the indexes) i receive the same message banghead.gif

Could this be an oracle 10 issue?
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Tue Mar 14, 2006 10:16 am 
Post subject: Re: Table comparison with sorted input option

icon_question.gif

You TC transform is configured for "sorted input" so you have to make sure the input data comes in ordered by the same columns as you specified as key columns in the TC. This is not related to having primary keys.

example:

my source table has a PK called KEY_ID and this is a surrogate key, one per CUSTOMER_ID, COUNTRY_ID.
In TC CUSTOMER_ID & COUNTRY_ID are the key columns, so I need to add a Query between the source and TC with those two columns used in the order by clause.

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
illo
Forum Member
Forum Member



Joined: 07 Jun 2005

Posts: 19
Location: Hamburg


flag
PostPosted: Tue Mar 14, 2006 11:19 am 
Post subject: Re: Table comparison with sorted input option

So that means indexing doesn't help me at all? I HAVE to sort both tables to use the feature? icon_cry.gif
Back to top
eepjr24
Forum Enthusiast
Forum Enthusiast



Joined: 16 Sep 2005
ASUG Icon
Posts: 1815
Location: Orlando, FL


flag
PostPosted: Tue Mar 14, 2006 11:25 am 
Post subject: Re: Table comparison with sorted input option

Curious about this one myself. If the primary key is a clustered index, will that solve the problem? I know that in SQL Server at least (probably others) having a clustered index determines the order in which the records are stored physically, forcing them to be in the clustered indexes order.

- Ernie
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Tue Mar 14, 2006 11:45 am 
Post subject: Re: Table comparison with sorted input option

If you want to perform a sort-merge operation the data should be sorted. Otherwise you cannot.

Your question points more into the direction of the database optimizer. If I order by two columns and those two columns are indexed, will the sort be executed in the temp area or will it read the index together with the table so it retrieves the data sorted already?

An index is nothing else than a tree or list of ROWIDs. So all the DB has to do is looping through the index adn fetching the row data via the ROWID. This way you will end up with the data sorted without having an actual sort operation.

Giv it a try and look at the optimized plan for a

"select * from table" and a
"select * from table order by primary_key"

In the second you will find that index plus table is read, in the first you will find the full table scan plus a sort operation.

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.1568 seconds using 17 queries. (SQL 0.1279 Parse 0.0016 Other 0.0273)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo