Oracle table creation by DS: column order?

stuartjvnorton · Post by **stuartjvnorton** » Sun Apr 19, 2009 8:18 pm

Does anyone else have an issue where the metadata for the output table that is to be created by a DS job has the columns in a particular order, yet the table created by running the job always mixes up the order of the columns?

Not a huge drama, but annoying if I have 100 columns in a table organised logically for ease of viewing and then the job just pulls the order out of a hat...

Is this just something I have to live with, or is there a solution to this?

chulett · Post by **chulett** » Sun Apr 19, 2009 9:39 pm

What stage? Using the OCI stage on the Server side, the order always matches the column order if the stage is generating the DDL, but it sounds like that may not be the case on the PX side.

stuartjvnorton · Post by **stuartjvnorton** » Mon Apr 20, 2009 12:20 am

It's the Oracle Enterprise stage in a parallel job.

I have a job where I might have some Transformer stages and I've specifically ordered the output columns. The destination Oracle Enterprise stage shows the columns ordered correctly, but when I run it for the first time and it creates the table, the columns are in an apparently random order.

priyadarshikunal · Post by **priyadarshikunal** » Mon Apr 20, 2009 2:41 am

@Craig

It should not be the case with PX jobs too.

I am creating the tables with oracle enterprise stage and the colums are created perfectly ordered.
It might be exceptional case but I am getting ordered columns.

@stuartjvnorton

Check RCP settings and try to run a test job without RCP to verify the column order.

chulett · Post by **chulett** » Mon Apr 20, 2009 7:17 am

When you check the DDL in the OE stage, the CREATE TABLE statement it will use, does it show them in the 'proper' order there? How are you checking the actual table itself? I ask because tools like Toad can show you the columns in different orders, so just want to make sure it's not a 'viewer' issue.

ShaneMuir · Post by **ShaneMuir** » Mon Apr 20, 2009 8:55 am

Actually I have found the same issue.
I have noticed that the columns seem to be determined by the preceding transform stage. Those columns which have some sort of derivation logic seem to be created first and in the order that they would appear in the transform. Those columns which are just 'passing through' or have no transformation attached to them are tacked onto the end.

It can be an issue sometime if you write update statements based on position or extract a table using 'select * from ...' but how often would do that anyway.

throbinson · Post by **throbinson** » Mon Apr 20, 2009 10:05 am

I eye-balled the same when DDL-ing a Teradata table in EE. I had the newbie hope my first column would be the P.I. if none was explicit-ized.
By bad as the stage Hal2000'd a column of it's own choosing causing major skew and hot ampin' like a Moe foe.
I'll circle the wagons to see if the derivations were the smoking gun that reordered the columns.
thx for the 411.

chulett · Post by **chulett** » Mon Apr 20, 2009 10:10 am

PI, HAL, MoFo and 411 all in the same post, not to mention 'eye-balled', 'smoking gun', 'hot ampin' and 'circle the wagons'. This amused me.

(HAL 9000, btw, unless you meant a much earlier model)

stuartjvnorton · Post by **stuartjvnorton** » Mon Apr 20, 2009 7:54 pm

throbinson wrote:I eye-balled the same when DDL-ing a Teradata table in EE. I had the newbie hope my first column would be the P.I. if none was explicit-ized.
By bad as the stage Hal2000'd a column of it's own choosing causing major skew and hot ampin' like a Moe foe.
I'll circle the wagons to see if the derivations were the smoking gun that reordered the columns.
thx for the 411.

Word, dog.

stuartjvnorton · Post by **stuartjvnorton** » Tue Apr 21, 2009 12:43 am

priyadarshikunal wrote: @stuartjvnorton

Check RCP settings and try to run a test job without RCP to verify the column order.

Sorry priyadarshikunal, I'm not sure what RCP stands for. ?

@chulett:

I've looked in Toad and Oracle SQL Developer, and the metadata for the table has the columns in the same order as I view the data and the same order as specified in the CREATE TABLE call in Oracle (watched it in the session viewer in Toad).
It seems like the columns are grouped. The fields from the source table are in 1 group and the others I have added (I'm doing an audit table with befores and afters), and the fields I have added are in a random order, followed by the fields from the original source in their correct order.

chulett · Post by **chulett** » Tue Apr 21, 2009 7:31 am

"RCP" is Runtime Column Propagation where columns not specifically mentioned are magically added at runtime and passed through the job. I assume that is not the case here and that you have specified a distinct set of columns to work with.

So, the table matches the DDL used, it just seems a little mysterious as to the column order it picked... or can you see the method of its madness now? Perhaps opening a ticket with your official support provider can get us an 'official' explanation of how this works.

Bottom line, though, column order doesn't make any difference in how the table 'performs' or functions, it is strictly an aesthetic / cosmetic consideration for the wetware side of the equation. For whatever that is worth.

throbinson · Post by **throbinson** » Tue Apr 21, 2009 7:50 am

With Seinfeld and the swirl as with Teradata Enterprise Stage and the PI; the order can make a significant difference.

chulett · Post by **chulett** » Tue Apr 21, 2009 7:54 am

If you mean the choice of the PI column, then yes, of course. What I mean is when the proper column is picked, it doesn't matter if that column is column number 1 or number 42, it doesn't affect how it functions. All this under the umbrella of Oracle, of course, which is the subject of this post.

Are you saying that the actual column number / order in Teradata can be an issue?

throbinson · Post by **throbinson** » Tue Apr 21, 2009 8:01 am

This is the last high-jacking of this post, I promise. That is exactly what I am saying. When creating a teradata table in EE, if a PI is not specified, the first column becomes the PI.
Primary Index Property Help;

Comma-separated list of field names that will become the primary index for tables, if not the first column.

Meaning the first column could be selected through some weird EE internal logic and cause trouble.

chulett · Post by **chulett** » Tue Apr 21, 2009 8:11 am

Ah... thanks for that.

We now return you to your reguarly scheduled problem...