Re: Rookie help request

Answers interpspersed below:

Rauf Sarwar wrote:

> DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E6918C9.F077E974_at_exesolutions.com>...
> > Marcus Leon wrote:
> >
> > > Hello, newbie help appreciated!
> > >
> > > Is this the correct way to alter a field to make it NOT NULL?
> > > ALTER TABLE TABLE_X
> > > MODIFY (FIELD_Y DATE NOT NULL) TABLESPACE &&abc;
> > >
> > > Is this the correct way to add an index?
> > > CREATE INDEX pq_idx_XYZ ON TABLE_X
> > > (DATE_FIELD ASC) TABLESPACE &&def;
> > >
> > > Thanks
> >
> > Where did you find this syntax? Because the answer is no. There is no
> > reason to specify a tablespace when altering a constraint.
>
> I am sure you mean "when altering a table".

No I meant what I said. The SQL statement is ALTER TABLE and ALTER TABLE means ALTER TABLE.

If I was talking about a constriant I would have suggested that you drop the appropriate check constraint.

Is it one and the same thing? Of course. But I was being consistent with the DDL.

> >
> > ALTER TABLE TABLE_X
> > MODIFY (FIELD_Y DATE NOT NULL);
>
> Also... make sure to run an update on the column to replace nulls with
> some value before modifying it to be not null.
>

Good advice ... if any NULLs are present.

> >
> > and your syntax for creating an index should include the PCTFREE
> > parameter.
>
> Extent specs are optional. Maybe I am reading too much into it but
> "should include the PCTFREE" comes across as a compulsion.
>
> Regards
> /Rauf Sarwar

As far as I am concerned. Failure to specify PCTFREE in an index creation defaults PCTFREE to the value of the associated tablespace whatever that might be. And almost undoubtedly it will waste a lot of disk. As an instructor I don't encourage bad practices or lazyiness. ;-)

Daniel Morgan Received on Sat Mar 08 2003 - 16:48:11 CST