tag:blogger.com,1999:blog-518481768015386858.post7831901689775409296..comments2023-08-06T04:28:52.092-04:00Comments on Karen Morton: 2000 columns - take 2Karenhttp://www.blogger.com/profile/03309823327597536648noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-518481768015386858.post-70455314789929849242010-01-30T09:32:34.791-05:002010-01-30T09:32:34.791-05:00I did something similar for a reporting issue. We...I did something similar for a reporting issue. We needed high-throughput for a report sent monthly to certain business partners. We needed to ramp up from a few hundred reports to thousands.<br /><br />Basically, I designed a table to provide exactly what was needed for the end-report. No computation by the reporting tool (Cognos): it does a "3 P" process:<br />1. Pluck the values from the table<br />2. Put them in the report cell<br />3. Push out the PDF report.<br /><br />http://it.toolbox.com/blogs/data-ruminations/the-absolutely-stunningly-beautiful-table-25899<br /><br />One team member hates the ETL because there's a lot of repetition in the SQL: summarizing the data by month-to-date, year-to-date, rolling-12-month, etc. <br /><br />Our table "only" has 105 columns, compared to yours, but I think the principle is the same. <br /><br />And it screams :)<br /><br />http://it.toolbox.com/blogs/data-ruminations/tuning-for-fun-28641DukeGanotehttps://www.blogger.com/profile/01714681520904502051noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-23820448902212211742010-01-17T13:10:29.923-05:002010-01-17T13:10:29.923-05:00Seems like u could use multipule tables with the s...Seems like u could use multipule tables with the same pk using a multi table insert then place a view on top.<br />What is the approach u used?Unknownhttps://www.blogger.com/profile/09236179121194021775noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-41623054684422942852010-01-08T18:42:20.150-05:002010-01-08T18:42:20.150-05:00Jonathan, I'd pretty much decided to do exact...Jonathan, I'd pretty much decided to do exactly what you propose. The SAS guys briefly freaked out when they thought they'd have to merge multiple unsorted datasets on the SAS side (apparently doing that in SAS is a loathsome process), but when I assured them I'd give them what they want without any real effort on their part, they calmed down and agreed that sounded like the best approach. <br /><br />I appreciate the note on SDU size and the tcp transmit and receive buffers as I'm sure I'd have hit a snag there and spent hours figuring that out. Thanks!<br /><br />Taral, Like you mention, the bulk of the processing/aggregating of data is being done in Oracle but due to the massive number of columns, I can't provide a single source table that includes everything for the SAS folks to "select *" from. As I mentioned above in my comment back to Jonathan, I'll be using multiple smaller source tables and creating queries that link them that the SAS guys will use to produce the final datasets they need.<br /><br />Connor, Just for my own edification I plan to do some testing with the 255 column boundary and see what happens. That research will likely come after I've gotten all this other stuff working, but I've got it on my "to do" list and will post results when I finally get it done.<br /><br />Thanks again everyone!Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-77940707522576896912010-01-08T11:32:14.339-05:002010-01-08T11:32:14.339-05:00Karen,
You may not be able to create a table or v...Karen,<br /><br />You may not be able to create a table or view with more than 1,000 columns, but you are allowed to write a query that select far more columns. (I know that you can exceed 2,000 columns in the select list in 10.2.0.3, but don't remember what the limit is).<br /><br />So you can set up a (small) set of tables with one-to-one links, and somem canned SQL at the SAS layer to join them. You many need to construct ordered data sets and force NL joins across the board to get this to work as efficiently as possible.<br /><br />If SAS is running on a different server (and that's what I've usually seen) then you'll need to configure SQL*Net to use the largest possible SDU (32KB), and may need to check the tcp transmit and receive buffers (I can't remember their sqlnet.ora names) to ensure that they are at least as big as the SDU.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-35450465992964693862010-01-07T11:31:28.981-05:002010-01-07T11:31:28.981-05:00Karen,
Not sure this is related to this but we ar...Karen,<br /><br />Not sure this is related to this but we are also using SAS with oracle. What we do is we treat report as ETL kind of thing and process everthing in oracle with all complex logic( Now this is not SAS part of logic it's data massaging for SAS ). Also, use partition tables and other stuff. Because, what happen with SAS in our case it was taking too much time while processing and network two and fro. So, after data is ready from SAS we just issue "select * from table" or can use partition also. So, here there would be no such blocking and you will get result fast.Taralhttps://www.blogger.com/profile/00532059230992312309noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-45494482795995213892010-01-07T02:28:26.264-05:002010-01-07T02:28:26.264-05:00I'm not near a server currently so I cannot ch...I'm not near a server currently so I cannot check this, but if memory serves, there are some interesting side-effects with lots of columns in terms of block usage.<br /><br />(I think) you can only have 255 not-null columns per block, so a row with more than that will be split across multiple blocks (or maybe its into multiple row-pieces within a block)<br /><br />(I stress this may all be fiction, but its just something that tucked away in the brain from many versions ago...)<br /><br />Cheers<br />ConnorConnor McDonaldhttps://www.blogger.com/profile/06246356571229889735noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-53430713534422103102010-01-06T10:03:13.744-05:002010-01-06T10:03:13.744-05:00Let us know the outcome, Karen.
It's an intere...Let us know the outcome, Karen.<br />It's an interesting design problem, which way to go. And it'll be good to know the final result and why.Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.com