Re: Select * into an external DS, and then adding columns

Vern,

... just reading my comments might help! There is nothig wrong with select * at all, there is nothing wrong with using views. Just put all database logic to your view layer and the programmer will mostly use select * from ... order by ...- SQL is very easy to use, isn't it?
Make your life easy and let the Database management system make the real work:
- normalize your database!!!
- create your physical layer (create table, don't forget primary key and referential constraints, journal all your tables, even if you don't use commit - if you don't use now, start using it today!!!)
- create a base one to one view layer to decouple your physical layer completely from the access layers, don't use select * in your create view statements!!! There might be technical columns, you don't need in your external schema and you might rename the columns to fullfill some corporate naming rules.
- that will be the only place you will see the table names
- now create your external schema (create view) don't use table names here, just use the base view layer, you've created first.
- create a view for nearly every SQL statement you need in more than one place
- most needed access pathes will be created by your constraints, do some measuring (database monitor and STRDBG are your friends) and create the base indexes for your internal schema)

Yes, the DBMS is using the views only to get the names of the tables behind, but this won't be a problem, it's only needed at creation time of the access plan and these are stored in the package, if there s one and cached wherever possible.

As long as you could provide the views in your external layer (with the same charachteristics), you won't need to touch any running programm to adopt changes in the database. There is only one complicated operation remaining: changing dimensions of key columns, so be sure to make them big enough in the first shot (Bigint might be a good choice).

D*B

<Vern>
Valid point, Glenn - it wasn't clear to me that this is what Deiter was saying.

And since a view presumably has the column list one wants for a certain purpose, the select * is fine there.

So perhaps I can revise my statement to say to almost always use PFs in the SELECT within a view?
</Vern>