Cascading in Oracle: Friend or Foe?

Cascade is one of those clauses (or keywords) which seems to permeate many areas within the Oracle RDBMS. Generally speaking, when an event or action includes a cascade clause, the intent is that good things will follow. Good things, in this context, means that extra work will be done for you. To illustrate the point, instead of having to type “do step A, do step B, do step C,” an equivalent statement of “do step A cascade” will also take care of doing steps B and C. My question to you is this: is cascading always a good thing? Let’s look at some of the more common operations where cascade is used.

Dropping a user

If a user owns objects (making it a schema), and you want to drop the user, does a simple “drop user whomever” work? Even without knowing for sure, you can guess the answer is no, since this article is about where “cascade” is used. An easily constructed demonstration illustrates this point.

SQL> create user cascade_demo identified by demo;

User created.

SQL> alter user cascade_demo quota unlimited on users;

User altered.

SQL> create table cascade_demo.test (id number);

Table created.

SQL> drop user cascade_demo;

drop user cascade_demo

*

ERROR at line 1:

ORA-01922: CASCADE must be specified to drop ‘CASCADE_DEMO’

SQL> drop user cascade_demo cascade;

User dropped.

This is clearly an example of how and where cascade takes care of performing steps for you. As an alternative, you could have dropped all objects within the cascade_demo schema, and when finished with that step, issue the final step of dropping the user. Consider for a moment all that takes place when exporting a user. Every type of object shown in the output below needs to be accounted for (cleaned up) in the data dictionary. It would seem reasonable that a similar or corresponding amount of work has to take place when dropping a user.

Another way of highlighting just how much work is done when dropping a user can be seen by tracing the session where the user is dropped. How many lines and statements (give or take, based on the version of Oracle and the operating system) would you guess are in a trace file produced by the following?

SQL> alter session set sql_trace=true;

Session altered.

SQL> drop user cascade_demo cascade;

User dropped.

SQL> alter session set sql_trace=false;

Session altered.

Run TKPROF on the trace file (tkprof <trace_file_name> <output_file_name>) and look at the end of the file. The amount of work Oracle performs is (I think) quite impressive.

Trace file: db10_ora_1528.trc

Trace file compatibility: 10.01.00

Sort options: default

       1  session in tracefile.

      33  user  SQL statements in trace file.

    3856  internal SQL statements in trace file.

    3889  SQL statements in trace file.

     203  unique SQL statements in trace file.

   40426  lines in trace file.

Of course, you want to be sure that dropping a user is what you really want to do. An erroneous drop user cascade statement can be recovered from, given the right setup (media recovery enabled, flashback enabled, database mounted).

Gathering statistics

Cascade, as a parameter, is used extensively in the DBMS_STATS package. More specifically, it is a parameter in all of the gather-related procedures, and variations of cascade (by name and by function) are included for use with partitioned tables/indexes and the export/import of statistics. What does cascade do for us in statistics gathering? The description of cascade (see below) is repeated several times in PL/SQL Packages and Types Reference.

Typically, if there is a SET_PARAM utility, there will be a corresponding GET_PARAM one. GET_PARAM is a function, and SET_PARAM is a procedure. Why the distinction? Well, to get meaningful output from GET_PARAM, we have to use a “select from dual” wrapper.

SQL> select dbms_stats.get_param(‘cascade’) from dual;

DBMS_STATS.GET_PARAM(‘CASCADE’)

——————————————————

DBMS_STATS.AUTO_CASCADE

Before defining what the value means, let’s see what the possible values are to begin with. One way to “force” Oracle to tell us the possible values is to pass in a bad (or badly formatted) value in the SET_PARAM procedure.

SQL> exec dbms_stats.set_param(‘cascade’, ‘null’);

BEGIN dbms_stats.set_param(‘cascade’, ‘null’); END;

*

ERROR at line 1:

ORA-20001: Illegal value for CASCADE NULL: must be {TRUE, FALSE,DBMS_STATS.AUTO_CASCADE}

This particular approach is safe, but as a general approach, be careful you don’t become victim of a what-if scenario. That is, what if the dummy value you pass in actually works? For example, when NULL is used, is null quoted or not? By and large, null is not quoted throughout most (practically all) of Oracle, but you will find that in the DBMS_STATS.SET_PARAM procedure, null can be quoted and unquoted, each with a different meaning.

Going back to the possible values, FALSE seems clear-cut in its meaning, but what is the difference between TRUE and AUTO_CASCADE? The distinction is this: if set to TRUE, statistics gathering for related indexes will take place, whereas if set to AUTO_CASCADE, index-related statistics gathering may or may not take place. With AUTO_CASCADE set, Oracle decides, not you.

For a table with many indexes, or if invoked at the schema or database levels, having Oracle fire off the “do step B, do step C, etc.” commands is a huge convenience. The “do step A cascade” model takes effect if the CASCADE parameter is set to the appropriate value.

Are there any negatives associated with letting the gather procedures cascade? Possibly, but probably not. Can Oracle use parallelism when gathering index-related statistics? Absolutely, and you can see DEGREE as a parameter in the INDEX_STATS procedures. But by the description of CASCADE in the TABLE, SCHEMA and DATABASE_STATS procedures, you will not be using parallelism during the gathering of index statistics.

One other potential drawback is that you may have wanted to keep index statistics as is. Oracle does provide you with the option of locking table statistics, but does it allow you to specifically lock index statistics for a table? That does not appear to be the case. Overall, why would you want to keep a table’s index statistics the same after you just analyzed the underlying table? One thing you have to admit is this: the Oracle user community is very good at finding business reasons to support options. It may not make sense to you, but it may make perfect sense to someone else (stored outlines come to mind). So, with CASCADE set to fire off other steps, and if in doubt, export the current statistics beforehand.

Cascading with unintended consequences

Let’s say user A requests you (via an approved change control ticket) to delete some rows from a table. A bit later, users B through, oh, say Z, including those starting with VP, are in your cubicle complaining about data missing in other tables. The users are all up in your face about the missing data, and you’re swearing up and down that you never touched those other tables. You did the work requested/approved by the change control board (and supposedly tested by SQA), nothing more, and nothing less. So, why is there missing data and why are you the recipient of those dagger-like stares?

Not to worry, you have good backups and can use RMAN to do a tablespace point in time recovery (as one option; flashback being another). Count on a good chunk of time to do the TSPITR, and look at the bright side: what a good training opportunity for you to do what you’ve practiced many times before (recovering data). After the dust has settled (meaning the next time you come to work, your badge still works with the badge reader on the door), you want to understand what, exactly, caused data in other tables to go missing.

If it wasn’t a trigger that caused other tables to have data deleted, then what else could have done it? What happened was this: a foreign key had a delete rule of cascade. Put another way, when the base table had a foreign key constraint created/enabled, there was a “on delete cascade” clause added/used. Deleting from this table fired off corresponding deletes in the table(s) the foreign key references.

Does that mean a deletion from a table with a foreign key constraint always causes a cascading deletion? If the delete rule is set to CASCADE, count on it. If it doesn’t happen all of the time, then it is highly probable there are other delete rule options. In fact, the other two options are NO ACTION (what our hapless DBA was expecting) and SET NULL (which can also be bad). Let’s take a look at the (slightly modified) example shown in the Database Application Developer’s Guide – Fundamentals. I added “ON DELETE CASCADE” to DEPT_FKEY. The intent then is this: when a department is deleted, so are all of the employees assigned to that department.

SQL> CREATE TABLE Dept_tab (

  2  Deptno   NUMBER(3) PRIMARY KEY,

  3  Dname    VARCHAR2(15),

  4  Loc      VARCHAR2(15),

  5  CONSTRAINT Dname_ukey UNIQUE (Dname, Loc),

  6  CONSTRAINT LOC_CHECK1

  7  CHECK (Loc IN (‘NEW YORK’, ‘BOSTON’, ‘CHICAGO’)));

Table created.

SQL>

SQL> CREATE TABLE Emp_tab (

  2  Empno    NUMBER(5) PRIMARY KEY,

  3  Ename    VARCHAR2(15) NOT NULL,

  4  Job      VARCHAR2(10),

  5  Mgr      NUMBER(5) CONSTRAINT Mgr_fkey

  6  REFERENCES Emp_tab ON DELETE CASCADE,

  7  Hiredate DATE,

  8  Sal      NUMBER(7,2),

  9  Comm     NUMBER(5,2),

 10  Deptno   NUMBER(3) NOT NULL

 11  CONSTRAINT Dept_fkey REFERENCES Dept_tab

 12    ON DELETE CASCADE);

Table created.

SQL> insert into dept_tab values (1,’ENGR’,’BOSTON’);

1 row created.

SQL> insert into emp_tab (empno,ename,deptno)

  2  values (1234,’COLE’,1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete from dept_tab where deptno=1;

1 row deleted.

SQL> select * from emp_tab;

no rows selected

Just like that (i.e., no warning from Oracle that other tables will be affected), data elsewhere is deleted. It also explains layoffs when companies merge, but I digress. This may seem inconvenient, as it is not uncommon for a department to go away but still have employees on the books. For obvious practical reasons, you’re going to have employees assigned to some department, so what can you do if you don’t know what a person’s new department is yet? One option is to re-assign them to a placeholder department number, or let the department number go null for the time being. What if the DEPT_FKEY constraint’s delete rule was set to “ON DELETE SET NULL?”

Drop and re-create the tables (new version of EMP_TAB shown below).

CREATE TABLE Emp_tab (

Empno    NUMBER(5) PRIMARY KEY,

Ename    VARCHAR2(15) NOT NULL,

Job      VARCHAR2(10),

Mgr      NUMBER(5) CONSTRAINT Mgr_fkey

REFERENCES Emp_tab ON DELETE CASCADE,

Hiredate DATE,

Sal      NUMBER(7,2),

Comm     NUMBER(5,2),

Deptno   NUMBER(3) NOT NULL

CONSTRAINT Dept_fkey REFERENCES Dept_tab

  ON DELETE SET NULL);

Now let’s delete from DEPT_TAB where deptno=1:

SQL> delete from dept_tab where deptno=1;

delete from dept_tab where deptno=1

*

ERROR at line 1:

ORA-01407: cannot update (“SCOTT”.”EMP_TAB”.”DEPTNO”) to NULL

If it’s not one thing, then it is another. In this case, the NOT NULL restriction on DEPTNO can be handled and the desired results achieved.

SQL> alter table emp_tab modify (deptno null);

Table altered.

SQL> delete from dept_tab where deptno=1;

1 row deleted.

SQL> select empno, ename, deptno from emp_tab;

     EMPNO ENAME               DEPTNO

———- ————— ———-

      1234 COLE

The ON DELETE SET NULL delete rule yields, in effect, a cascading result. An action was cascaded in this example without saying CASCADE per se.

In Closing

Oracle’s ability to cascade effects from the actions on one object to another is a very powerful feature, which has the potential to save you innumerable steps. At the same time, it is a very powerful feature, which has the potential to cost you innumerable steps. The savings or cost viewpoint is largely up to you, and having a good understanding of how, when, and where cascading works is key to having this feature be a friend to you as opposed to being a foe.

» See All Articles by Columnist Steve Callan