Does the SORT order change due to Partitioning?

deepa_shenoy · Post by **deepa_shenoy** » Mon Mar 22, 2010 11:52 am

Hi,

I wanted to know if the SORT order changes due to partitioning?

For e.g. if I have an Oracle Stage which has a SQL with an 'ORDER BY' clause, and then I send it to a COPY stage which has 'SAME' as its input partitioning, will the sorted data be maintained?

Also, is it a pre-requisite to sort data before a Change data capture stage?

Thanks in advance.

Deepa

anbu · Post by **anbu** » Mon Mar 22, 2010 12:57 pm

Oracle stage runs sequentially( if the table is not partitioned) and if you use SAME partition then you will have only one partition

In general if you use SAME partition then sort order is maintained.

Also, is it a pre-requisite to sort data before a Change data capture stage? Yes

ray.wurlod · Post by **ray.wurlod** » Mon Mar 22, 2010 1:25 pm

Repartitioning will destroy sorted order. "Same" will preserve any sorted order.

deepa_shenoy · Post by **deepa_shenoy** » Mon Mar 22, 2010 9:58 pm

Can I SORT within the Change Data Capture stage itself?

ray.wurlod · Post by **ray.wurlod** » Mon Mar 22, 2010 10:45 pm

No, but it appears that you can. What you're actually doing, on the Input tab, Partitioning, is setting up sorting on the input link to the stage, rather than within the stage itself. This will become a separate tsort operator in the compiled job. Don't forget to partition and sort both inputs identically.

deepa_shenoy · Post by **deepa_shenoy** » Mon Mar 22, 2010 10:47 pm

Hi,

I am getting this error while using the Change data capture Stage

CHCP_SRC_TRGT: When checking operator: Defaulting "SYS_CD" in transfer from "beforeRec" to "outputRec". [api/transfer_rep.C:1832]

SYS_CD is present in both the input links, and neither used as Change Key or Change Value.

What could be the issue?

Thanks in advance.

Deepa

ray.wurlod · Post by **ray.wurlod** » Tue Mar 23, 2010 3:20 am

The issue is nothing to do with SORT order, so please begin a new thread. This will aid future searchers. Indeed, you might essay a Search yourself, since this question has been answered on DSXchange in the past.