Re: 3 Questions on ORACLE 7.2/SQLPLUS

badri_at_cc.gatech.edu (badri) wrote:

>Hi:
 

> I have the following questions:
 

> 1. How to drop an existing column? I can think of exporting
>and importing the table without the column. Also, creating a temp table
>with the desired columns, dropping the original and renaming the temp table
>to the original table. I want to know if there is a DDL command to do it.

There is no ddl command to do this. Create table as select from anotherTable NONRECOVERABLE will probably be the fastest method (nonrecoverable is a new option with 7.2)

> 2. I have an intermittent occurrence of cost based optimization
>"showing up" by itself. I determine this by monitoring the chain_cnt and
>avg_row_len in User_Tab_Columns. My init.ora says that the optimization
>mode is RULE. Besides, even if I delete statistics, they get regenerated
>again. How is this? How can I prevent it?

Someone must be analyzing the tables. Even if you specify cost based optimization, Oracle will not auto collect statistics. Someone must be running analyze table on you.

> 3. I have some columns in a table that are defined to be non-null.
>I need to define an additional constraint that they cannot be < 0. I
>would prefer to do this as a constraint and not a trigger. I tried
> Alter table mytable add (constraint myCons myColumn number > 0)
> and a host of other attempts, and failed all the time. What
>I need is something like "Check". The problem is that the table already
>exists and I cannot do a "Check" on an existing column!

alter table mytable add constraint check_gt_zero check ( myColumn > 0 );

>Any and all help on any and all of the 3 questions would be
>greatly appreciated,
 

>thanks a lot,
 

>-Badri

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Sun Dec 10 1995 - 00:00:00 CET