Search

Top 60 Oracle Blogs

Recent comments

January 2010

Subquery factoring in oracle 9i

A new way of expressing subqueries in 9i. December 2005 (updated June 2007)

Ansi joins in oracle 9i

Oracle adds ANSI-compliant joins to SQL in 9i. January 2003 (updated June 2007)

Type enhancements in oracle 9i

Constructor functions, type evolution and substitution in 9i. December 2005 (updated June 2007)

2000 columns - take 2

Thanks to everyone who commented on yesterday's post. After Randolf's comment about the 1000 column limit, I realized that so far, I've not attempted to create the full table in one go. Instead, I've been building 20-ish separate tables that contain the needed raw data plus the computed/aggregated columns. This speaks to Noons comment about having separate tables that access and group data up that can be executed in multiple streams

The SAS guys want to be able to access a single row that represents all the information they need for one type of data (for example, a shipment). So far, as I build the separate tables, I've been using a couple of views that combine several of the tables. The SAS guys then use the view to build a SAS dataset and merge multiple datasets together.

Forall enhancements in 10g

An overview of enhancements to FORALL bulk-binding. June 2004

Quoting string literals in 10g

Oracle 10g provides a quoting mechanism for strings that include single-quotes. June 2004

Sql plan enhancements in 10g

New plan features in 10g make SQL performance investigations much more simple. December 2004 (updated November 2007)

Pl/sql optimisation in 10g

Oracle 10g's compiler optimisation for faster PL/SQL, with a new section on optimisation bugs. November 2004 (updated March 2008)

2000 columns

How many columns does the largest table you've ever worked with contain? The current project I'm working on has 1 table with almost 2000 columns (and it's likely to add more!). This is the most highly denormalized design I've ever encountered and there's something about it that makes the performance optimizer in me cringe. But, the statisticians that have to munch and crunch this data in SAS tell me this format best suits their needs (based on similar designs used successfully in previous projects).

I think I'm really more concerned about the work that has to be done to populate these columns as most of the columns contain aggregations or formulations of some sort or another. So, perhaps it's not the number of columns that really is niggling at me as it is everything that must occur to produce the values contained in a single row (it's a lot).

Using pipelined table function as the UI API object

In my previous post I've introduced you to an example WoD application page, and showed how the render-flow could be built in an Helsinki manner using a ref-cursor. The UI technology stack would call a stored procedure which in turn would return a ref-cursor. Inside the UI code stack all that needs to be done is fetch the rows from the ref-cursor (until %NOTFOUND), then close the cursor, and