SQL questions

• Quoting Magick to All dated 09-13-94 ***
  > 1. what is the maximum number of allowed columns per
  > table. I am trying to talk the application
  > developer out of a table with 6200+ columns and
  > would be grateful if someone could provide me
  > with a reference.
  

It's 255 -- check in the Server manuals, under ANSI compliance. There you will find a listing of what ANSI requires (100, as I recall) and what Oracle supports. Of course, anything over 25-30 is extremely unusual, and often indicates a problem with the model.

> 2. when creating a non-unique index on a table for a
> column which is either null or some value, will
> index entries be created for the rows whose
> indexed column value is null?

No, they will not be.

> 3. one of the tables being created is timestamped and
> the end users want to access, for example, the last
> ten rows added. Except for trying to create a
> descending index, doing a massive sort in descending
> order (this will be a very large table) or trying
> to manipulate the date into a character or number
> which would be inserted into the key (reversing the
> date order), does anyone know of an easier or, more
> importantly, a more efficient method of doing this
> which reduce the overhead of the select statement?

Of the many options available, what you describe is probably the worst.

One easier way is through the use of database triggers. Put the last ten rows in their own table, and age the oldest one out of the table upon insertion. This will be the quickest retrieval, but the slowest insertion/update.

Or, use this SQL statement to retrieve the last 10 rows: select * from table a where 10 > (select count(*) from table b

             where a.date_stamp > b.date_stamp); or, if you just want to retrieve the rows in descending order:

select /*+ INDEX_DESC(table) */ * from table  order by date_stamp desc;

which is extremely quick.

Michael Stowe
Constellation Engineering Received on Fri Sep 16 1994 - 15:20:57 CEST