DROP and CREATE - What's the Impact on Any Dependency Information?

Question

question

KillerDBA asked Jan 25, '10

DROP and CREATE - What's the Impact on Any Dependency Information?

A while back, someone aske about TRUNCATE vs DELETE. In reply, HillBillyToad offered the opinion that DROP and CREATE might be better options for quickly destroying all the data in a table. My understanding is that this also allows "recovery" of the table, if you're properly set up, using FLASHBACK. I can see significant advantages to that.

However, a lingering question remains... What's the impact of DROP and CREATE on dependency information in ORACLE? If I have dependent objects (procedures, views, etc), would the linkage to dependency information be lost? Or would ORACLE dynamically figure out all dependency relationships again on the CREATE?

Follow-up question: if you flashback a table to a prior version, is any dependency information restored?

performance ddl dependency

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Christian13467 commented · Jan 26, 2010 at 08:41 AM

The first real question since long, which is not answered by linking to oracle documentation!!

0 ·

Answer 1 · 2010-01-26T06:50:05Z

Andrew Mobbs answered Jan 26, '10

Good question.

I'll deal with the Flashback first.

Oracle doesn't handle RI constraints across a drop and flashback (See documentation for FLASHBACK TABLE), however

SQL> CREATE TABLE foo (a NUMBER NOT NULL, b VARCHAR2(200) NOT NULL, CONSTRAINT pk_foo PRIMARY KEY (a));

Table created.

SQL> CREATE TABLE bar (x NUMBER NOT NULL, a NUMBER NOT NULL, CONSTRAINT fk_bar_a FOREIGN KEY (a) REFERENCES foo (a));

Table created.

SQL> INSERT INTO bar VALUES (42, 23);
insert into bar VALUES (42, 23)
*
ERROR at line 1:
ORA-02291: integrity constraint (AJM.FK_BAR_A) violated - parent key not
found


SQL> DROP TABLE bar;

Table dropped.

SQL> FLASHBACK TABLE bar TO BEFORE DROP;

Flashback complete.

SQL> INSERT INTO bar VALUES (42, 23);

1 row created.

SQL>

After a DROP, dependant objects are invalidated:

SQL> CREATE OR REPLACE PROCEDURE bar_sp AS 
  2  i NUMBER;
  3  BEGIN
  4  SELECT max(a) INTO i FROM bar;
  5  dbms_output.put_line('Value: '||i);
  6  END;
  7  /

Procedure created.

SQL> CREATE OR REPLACE VIEW vw_bar AS SELECT a FROM bar;

View created.

SQL> DROP TABLE bar;

Table dropped.

SQL> column object_name format a25
SQL> SELECT object_name,status FROM user_objects WHERE object_name IN ('VW_BAR','BAR_SP');

OBJECT_NAME               STATUS
------------------------- -------
BAR_SP                    INVALID
VW_BAR                    INVALID

SQL>

A CREATE will leave them invalid initially:

SQL> CREATE TABLE bar (x NUMBER NOT NULL, a NUMBER NOT NULL, CONSTRAINT fk_bar_a FOREIGN KEY (a) REFERENCES foo (a));

Table created.

SQL> SELECT object_name,status FROM user_objects WHERE object_name IN ('VW_BAR','BAR_SP');

OBJECT_NAME               STATUS
------------------------- -------
BAR_SP                    INVALID
VW_BAR                    INVALID

SQL>

However, views and sprocs will be dynamically recompiled on first use:

SQL> SELECT * FROM vw_bar;

no rows selected

SQL> exec bar_sp;
Value:

PL/SQL procedure successfully completed.

SQL> SELECT object_name,status FROM user_objects WHERE object_name IN ('VW_BAR','BAR_SP');

OBJECT_NAME               STATUS
------------------------- -------
BAR_SP                    VALID
VW_BAR                    VALID

The dynamic recompilation will also happen after a FLASHBACK TO BEFORE DROP.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA commented · Jan 26, 2010 at 05:02 PM

Thanks. Nice, clear examples, too.

0 ·

KillerDBA commented · Jan 27, 2010 at 12:53 PM

After further thought, it seems like DROP and CREATE are, in Oracle, a reasonable and reasonably recoverable way for me to replace all the data in a table and have the original content handy in case things go South. Presuming, of course, that we are properly set up with recycle bin, etc. Am I off base, here? We used TRUNCATE on SQL Server for this sort of thing because it could be rolled back, if the replacement (insert or DTS transform) went badly.

0 ·

Andrew Mobbs commented · Jan 29, 2010 at 07:36 AM

@KillerDBA I'd be slightly surprised if DROP were faster than TRUNCATE, I suggest you run some tests to see. As you pointed out in another question, it is possible (though more awkward) to flashback to before a truncate.

0 ·

question