Re: How To Insert DATE

"Harald Henkel" <Harald_at_Henkel.DAH.UUnet.DE> wrote in message news:95hgsv$h958t$1_at_ID-56141.news.dfncis.de...
> Hello Howard.
>
> "Howard J. Rogers" <howardjr_at_www.com> schrieb im Newsbeitrag
> news:3a7c21e5_at_news.iprimus.com.au...
> >
> > "Harald Henkel" <Harald_at_Henkel.DAH.UUnet.DE> wrote in message
> > news:95h3t0$h9o6s$1_at_ID-56141.news.dfncis.de...
>
> <SNIP>
>
> > > ALTER TABLE employee
> > > ADD CONSTRAINT employee_PK PRIMARY KEY
> > > (emp_id) ;
 

> > > Of course in the real world, you would have to add tablespace and
 table
 size
> > > clauses to the create table and alter table statements for indexes.
>
>
> > Can't let that one pass! In the real, real world, you ought to get out
 of
> > the habit of specifying a storage clause (and hence 'table size' things
 like
> > iniital and next extents) at segment level. If you've done your job
> > properly, the tablespace will be sitting there with an appropriate
 default
> > storage clause that can fill in the gaps.
>
> One default, that will be OK for any kind of table ?
> Or do you suggest to create many different tablespaces for different
 "types"
> of tables ?

Yes, as has been posted in this group a zillion times before: tablespaces are *supposed* only to house tables with the same growth habits. So if you have different growth habits, then you should have different tablespaces. :Locally managed tablespace (available in 8i) does exactly this -but with a degree of automation that is highly desirable.

>
> > On the other hand, I don't like to see any syntactical advice given on
> > Primary or Unique keys that doesn't EXPLICITLY include a tablespace
 clause
> > for the indexes associated with those constraints, because fishing
 indexes
> > out of DATA tablespace is a royal pain in the butt to have to keep
 doing,
 is
> > one of the simplest things to avoid, and all newbies should be taught
 about
> > it properly upfront.
>
> Hmm. Perhaps you're right. But I thought, that he's a real beginner, just
> starting with Oracle's default database, which has only one table space
> (USER_DATA). I didn't want to confuse somebody, who doesn't even know the
> base syntax, with information he didn't request.
>

"Real beginners" deserve to be put on the right road from the word go. Getting indexes created in with the data tables is a performance nightmare in the making, and is preventable.

I've never found it to be of long-term benefit to withold crucial information from beginners because you simply think 'they won't handle it' or 'they didn't ask for it'... they'll get into difficulties with short-changed information, and be back, nigger and badder than ever, before every long.

>
> > Hence, the CORRECT syntax for declaring something to be a primary key
 after
> > the event is:
 

> > Alter table employee
> > add constraint employee_empid_pk Primary Key (emp_id)
> > Using Index
> > Tablespace INDEXBLAH ;
> >
> > And for doing it at the time of creating the table in the first place:
> >
> > Create table employee (
> > emp_id number (4) constraint employee_empid_pk Primary Key
> > using index
> > Tablespace INDEXBLAH,
> > name varchar2 (15),
> > age number (3),
> > date_hired date,
> > address varchar2 (30))
> > Tablespace DATABLAH;
>
> If I would have written that, I'm sure the next reply would have been:
> "Why am I getting the error:
>
> Tablespace DATABLAH
> *
> FEHLER in Zeile 9:
> ORA-00959: Tablespace 'DATABLAH' nicht vorhanden"
>
> (Sorry this is in German ;-) )
>

I think you underestimate the average reader, or even the average beginner. It doesn't require a degree in rocket science to know that code examples posted in public will include user names, tablenames, tablespace names and so on which will need to be altered to fit your own circumstances.

Many people here use 'table foo'... I use 'blah' all the time. It's not unusual.

> > In short, you are asking for trouble if you don't specify tablespaces
 for
> > both the table and any associated constraint-inspired indexes, and the
> > sensible thing to do is (a) to get into the habit of always declaring
 them
> > and (b) always including those parts of the syntax when advising others.
> >
> > It is probably a bit picky of me, I know: but honestly, the amount of
 time
 I
> > spend advising on how to split tables apart from their indexes beggars
> > belief -and it's largely because few seem to realise that certain
> > constraints imply indexes, and those indexes must be housed
 appropriately.
>
> Sure.
> But I think to understand this, the guy has still to learn a lot and I
> didn't think it's been appropriate to be to explicit here.

I disagree, very strongly. He asked how to create a primary key. You told him how to do half a job (and a bad one at that). The correct, fuller syntax is an extra 2 lines. Precisely because he's a beginner, he doesn't deserve to be misled on something this trivially-easy to get right or wrong.

> I gave a hint, that he would have to declare tablespaces in a real
database.

He has to declare two tablespaces, and you weren't clear on that at all. And they have to be different (unless he wants his head tested), and you didn't even hint about that at all. And hints, in any case, are for those who are able to pick up on them... beginners don't need hints, they need explicit instruction.

> That seemed to be enough information.
> After all, there are some books about Oracle syntax out there, he could
 look
> up.

Absolutely. He could look *anything* up there, couldn't he?? But he posted *here*, so here is where it is incumbent upon the respondents to provide full and accurate knowledge, at least to the requisite level of understanding. You are somehow implying that beginners should be able to create unique and primary constraints, without ANY indication that indexes are involved, or that there are dire performance implications for not housing the indexes in their own tablespace. That, somehow, such knowledge is "advanced" or "tricky". I'm saying to you that such an approach is wrong, because it's as beginners that we learn the DBA habits that will last -and not housing indexes for constraints in their own tablespace is an especially bad habit to get into (particularly because the syntax itself defaults itself to doing precisely the wrong thing).

> I didn't intend to give him a complete Oracle DB setup and administration
> course in here, but simply answer his questions.

My point is: you didn't "answer" his questions. You misled him. You mentioned something in passing which is fundamental, and critical to successful constraint creation and subsequent database performance. The correct version takes an extra 2 lines. That is hardly a complete Oracle DBA course.

In short: a short answer, if its worse than the long answer, should be no answer at all.

HJR
>
> With kind regards,
> Harald Henkel
>
>
>
>
Received on Sat Feb 03 2001 - 16:24:38 CST