Re: Rookie help request

On Sat, 08 Mar 2003 14:48:11 -0800, DA Morgan <damorgan_at_exesolutions.com> wrote:

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

Hi "Mr Instructor",

There you go again : Being more lazy and *ignorant* than is good for any instructor : PCTFREE defaults to 10, which is a system default, and certainly not a tablespace default. You can't assign block utillization parameters to tablespaces.

What do you teach/instruct ? Hopefully not Oracle !

• Kenneth Koenraadt