Systems Engineering and RDBMS

Fine grained Dependency

Posted by decipherinfosys on March 17, 2009

With every new release, Oracle is achieving more and more high availability standards not only through newer and enhanced technologies but also through the improvement in existing behavior like fine-grained dependency introduced in oracle 11g.

In previous releases, whenever underlying table’s definition gets altered either for adding new column(s), dropping column(s) or changing column attribute(s), all the dependent objects becomes invalid whether the column in question is being used or not in any of the view, procedure, function and/or package but with 11g, only those dependent objects will be invalidated which uses the column in question. Let us start straight with an example. Create following table and view in both 10g and 11g databases.

CREATE TABLE TEST
AS SELECT object_type, object_name
FROM USER_OBJECTS;

CREATE OR REPLACE VIEW TEST_VIEW
AS
SELECT OBJECT_TYPE,OBJECT_NAME
FROM TEST
WHERE OBJECT_TYPE = ‘TABLE’;

SELECT OBJECT_NAME,STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME = ‘TEST_VIEW’;

OBJECT_NAME                    STATUS
—————————— ——-
TEST_VIEW                      VALID

/* Apply these change to both 10g and 11g databases */
ALTER TABLE TEST ADD TEST_ID NUMBER(5);

/* In 10g */
OBJECT_NAME                    STATUS
—————————— ——-
TEST_VIEW                      INVALID

/* In 11g */
OBJECT_NAME                    STATUS
—————————— ——-
TEST_VIEW                      VALID

In 11g, since new column is not part of the view, it remains ‘VALID’ while in 10g it has become invalid. Similar behavior is expected for procedures and functions as well. Let us create following procedure and function both in 10g and 11g databases.

CREATE OR REPLACE FUNCTION test_func(p_object_Type IN VARCHAR2)
RETURN NUMBER IS
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM test
WHERE object_Type = p_object_Type;
RETURN v_count;
EXCEPTION
WHEN OTHERS THEN
v_count := 0;
RAISE_APPLICATION_ERROR(-20002,SQLERRM);
END test_func;
/

Status of both procedures and functions will be valid in both releases.

SELECT object_Type,object_name,status
FROM user_objects
WHERE object_name IN (‘TEST_PROC’,’TEST_FUNC’);

OBJECT_TYPE     OBJECT_NAME                    STATUS
————— —————————— ——-
FUNCTION        TEST_FUNC                      VALID
PROCEDURE       TEST_PROC                      VALID

Now let us alter the column length of the column test_id or drop it altogether and check the status of the procedure and function again.  For 10g both procedure and function will be invalidated even though function is not using column being modified. In Oracle 11g, only procedure will be invalidated.

ALTER TABLE TEST MODIFY TEST_ID NUMBER(9);

/* In 10g */
OBJECT_TYPE     OBJECT_NAME                    STATUS
————— —————————— ——-
FUNCTION        TEST_FUNC                      INVALID
PROCEDURE       TEST_PROC                      INVALID

/* In 11g */
OBJECT_TYPE     OBJECT_NAME                    STATUS
————— —————————— ——-
FUNCTION        TEST_FUNC                      VALID
PROCEDURE       TEST_PROC                      INVALID

Exception to this behavior is trigger. Whenever table structure gets changed, trigger will always be invalidated just like in earlier releases. Let us create trigger, which doesn’t use column test_id.

CREATE OR REPLACE TRIGGER TEST_TRIG
BEFORE INSERT ON TEST FOR EACH ROW
BEGIN
:NEW.OBJECT_NAME := UPPER(:NEW.OBJECT_NAME);
END;
/

Now if we alter test_id column, trigger will be invalidated in both releases. Following is the result.

/* In 10g */
OBJECT_TYPE     OBJECT_NAME                    STATUS
————— —————————— ——-
TRIGGER         TEST_TRIG                      INVALID

/* In 11g */
OBJECT_TYPE     OBJECT_NAME                    STATUS
————— —————————— ——-
TRIGGER         TEST_TRIG                      INVALID

This is very cool since there is no extra configuration required for this behavior. This also helps in keeping number of invalid objects down during upgrade and/or conversions.

Resources:

  • Book: Oracle 11g New features for Developers and DBAs by Sam Alapati, Charles Kim

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: