Re: SQL questions

hack_at_netcom.com (Magick) writes:
|> 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.

254. The answer is documented in Appendix D of the Oracle7 Server Administrators Guide.

|> 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?

Nulls are not indexed, except when a cluster key column value is null (page 5-6 Oracle7 Server Concepts Manual).

|> 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?

This answer I'm not too sure about since I don't remember if I've actually tried this yet, but theoretically if you create the index on the timestamp column and issue the query:

select /*+ index_desc(T timestamp_index) */ ... from T
order by timestamp

It should things in a nice way. You can set up the application to just fetch the first 10 records.
It's sort of documented in Chapter 5 of the Oracle7 Server Application Developer's Guide but this exact example isn't listed.

Hope this helps

Roderick Manalac
Oracle Corporation Received on Wed Sep 14 1994 - 06:57:21 CEST