This discussion is archived
12 Replies Latest reply: Aug 4, 2013 11:39 AM by 9423755 RSS

Does "SELECT *" minimize dependency failure?

9423755 Explorer
Currently Being Moderated

Hmmph, another question.

 

According to UCertify (I won't reproduce the whole question here), "Performing queries with the SELECT * notation" is a method by which "dependency failures can be minimized". This I find hard to believe.

I have done this test myself numerous times, and yet this question and answer keeps coming up. I am wondering if I am going mad.

If you specify a column list in a select statement in a procedure and then add a column to the table, the procedure is not invalidated.

If you use "SELECT *", it is invalidated.

Am I misreading the question?  Dependency failure is about preventing invalidations of dependent objects, right?

 

drop table m1 purge

/

drop view v1

/

drop view v2

/

drop procedure p43a

/

drop procedure p43b

/

create table m1(x number)

/

create view v1 as select * from m1

/

create view v2 as select x from m1

/

create or replace procedure p43a as

    n number;

begin

    select x into n from m1;

end;

/

show errors

 

 

create or replace procedure p43b as

    n number;

begin

    select * into n from m1;

end;

/

show errors

 

select object_name,status from user_objects where object_name in ('M1','V1','V2','P43A','P43B')

/

alter table m1 add(y number)

/

select object_name,status from user_objects where object_name in ('M1','V1','V2','P43A','P43B')

/

 

SCOTT@ORCL> drop table m1 purge

  2  /

Table dropped.


SCOTT@ORCL> drop view v1

  2  /

View dropped.

 

SCOTT@ORCL> drop view v2

  2  /

View dropped.

 

SCOTT@ORCL> drop procedure p43a

  2  /

Procedure dropped.

 

SCOTT@ORCL> drop procedure p43b

  2  /

 

Procedure dropped.

 

SCOTT@ORCL> create table m1(x number)

  2  /

 

Table created.

 

SCOTT@ORCL> create view v1 as select * from m1

  2  /

View created.

 

SCOTT@ORCL> create view v2 as select x from m1

  2  /

View created.

 

SCOTT@ORCL> create or replace procedure p43a as

  2      n number;

  3  begin

  4      select x into n from m1;

  5  end;

  6  /

Procedure created.

 

SCOTT@ORCL> show errors

No errors.

SCOTT@ORCL>

SCOTT@ORCL> create or replace procedure p43b as

  2      n number;

  3  begin

  4      select * into n from m1;

  5  end;

  6  /

Procedure created.

 

SCOTT@ORCL> show errors

No errors.

SCOTT@ORCL>

SCOTT@ORCL> select object_name,status from user_objects where object_name in ('M1','V1','V2','P43A','P43B')

  2  /

 

OBJECT_NAME                    STATUS

------------------------------ -------

M1                             VALID

P43A                           VALID

P43B                           VALID

V1                             VALID

V2                             VALID

 

5 rows selected.


SCOTT@ORCL> alter table m1 add(y number)

  2  /

Table altered.

 

 

SCOTT@ORCL> select object_name,status from user_objects where object_name in ('M1','V1','V2','P43A','P43B')

  2  /


OBJECT_NAME                    STATUS

------------------------------ -------

V2                             VALID

V1                             VALID

P43B                           INVALID

P43A                           VALID

M1                             VALID

 

5 rows selected.

 

SCOTT@ORCL>

  • 1. Re: Does "SELECT *" minimize dependency failure?
    Tubby Guru
    Currently Being Moderated

    Jason_942375 wrote:

     

    Hmmph, another question.

     

    According to UCertify (I won't reproduce the whole question here), "Performing queries with the SELECT * notation" is a method by which "dependency failures can be minimized". This I find hard to believe.

    I have done this test myself numerous times, and yet this question and answer keeps coming up. I am wondering if I am going mad.

    If you specify a column list in a select statement in a procedure and then add a column to the table, the procedure is not invalidated.

    If you use "SELECT *", it is invalidated.

    Am I misreading the question?  Dependency failure is about preventing invalidations of dependent objects, right?

     

     

    What's the purpose of asking the question here? You have set up a nice example and the conclusions seem easy to draw from that. If you take exception with the wording or validity of this "ucertify" site, then you would be better served bringing this to their attention than to alluding to some reference they make (without showing us the actual context of what is said).

     

    Cheers,

  • 2. Re: Does "SELECT *" minimize dependency failure?
    9423755 Explorer
    Currently Being Moderated

    That's a good point, but I did this before when I was studying for 1z0-144 (a different exam prep provider...Kaplan I think) and despite "raising" numerous issues with their "correct" answers, I never heard back from them.

    I doubt very much that UCertify would have the staff on-hand to look at and reply to complaints (or perhaps, "observations"), but maybe I'm doing them a disservice but even if they did, I think the forum is quicker and served by superior minds.

    Like I said, I have seen this question, and this answer, many times, and always the answer is "use SELECT *" to minimize dependency failure.

    When it's repeated in so many places, it does make you wonder, is this question going to come up in the exam, and what am I supposed to say if it does?

  • 3. Re: Does "SELECT *" minimize dependency failure?
    Tubby Guru
    Currently Being Moderated

    Jason_942375 wrote:

     

    That's a good point, but I did this before when I was studying for 1z0-144 (a different exam prep provider...Kaplan I think) and despite "raising" numerous issues with their "correct" answers, I never heard back from them.

    I doubt very much that UCertify would have the staff on-hand to look at and reply to complaints (or perhaps, "observations"), but maybe I'm doing them a disservice but even if they did, I think the forum is quicker and served by superior minds.

    Like I said, I have seen this question, and this answer, many times, and always the answer is "use SELECT *" to minimize dependency failure.

    When it's repeated in so many places, it does make you wonder, is this question going to come up in the exam, and what am I supposed to say if it does?

    That's an even more perplexing answer

     

    Why would you use a site that you have no faith in to help you prepare for an exam?

     

    If you need answers, hit the freely supplied documentation.

     

    Schema Object Dependencies

     

    If you have a question, please ask away. But you'd be best served not to say "I read this somewhere ...." if you cannot provide an actual link to what you are referencing. Context is absolutely important.

     

    Cheers,

  • 4. Re: Does "SELECT *" minimize dependency failure?
    9423755 Explorer
    Currently Being Moderated

    Well, I'm using them because I paid for it (not knowing of course whether it would be any good.

    On the whole, I think what they provide is good. Very good, in fact.  But I expect the questions and answers to be lacking in any vagueness, ambiguity or imprecision. Mostly they are, but often they are not.

    Here's another one, reproduced in full especially for you . :

     

    Q: Which of the following requires an explicit cursor to process more than one row in PL/SQL?

      INSERT

      UPDATE

      SELECT

      DELETE

    Explanation: Answer option C is correct.

     

    The SELECT statement requires an explicit cursor to process more than one row in PL/SQL. You must create an explicit cursor and use the FETCH command within the FOR loop to process data from all rows.

    Answer options D, A, and B are incorrect. You can update, delete, or insert any number of rows in PL/SQL without an explicit cursor.

     

    Reference: Oracle Online Documentation, Contents: "PL/SQL User's Guide and Reference", "Language Elements"

    Chapter: Designing PL/SQL Code

    Objective: Identify guidelines for cursor design

     

    Eh?  Since when MUST I use an explicit cursor to process more than one row in PL/SQL?  Whatever happened to "FOR rec in (SELECT * FROM EMP) LOOP .... END LOOP"?

     

    Furthermore, they are sticking to their guns...in the same "section" but a few questions later there is a similar question, with a similar answer:

     

    Q: Under which of the following conditions is the use of an explicit cursor necessary?

      When a query returns more than one row

      When a query returns only one row

      When any SQL data manipulation language (DML) statement is used

      When a query does not return any row


    Explanation: Answer option A is correct.

     

    An explicit cursor needs to be declared when a query returns more than one row. It is defined in the declarative section of the PL/SQL block. It is opened, fetched, and closed in the executable and exception sections. The OPEN, FETCH, and CLOSE statements can be used with explicit cursors.

  • 5. Re: Does "SELECT *" minimize dependency failure?
    Tubby Guru
    Currently Being Moderated

    But that's kind of the problem with posting questions based on material like this. The question, as posted, has no context.

     

    In version 5 of the Oracle database maybe their answer was correct, in version 7 it might have changed. So for all I know earlier in the chapter you are quoting from they talk exclusively about Oracle version 5, then you come to the forum and copy/paste the question and you're obviously going to get a different answer than the material quoted ... because the context is lacking.

     

    Cheers,

  • 6. Re: Does "SELECT *" minimize dependency failure?
    Justin Cave Oracle ACE
    Currently Being Moderated

    It depends what sort of "dependency failure" they're referring to.

     

    If you are writing PL/SQL, it makes sense to either select explicitly specified individual columns into individual variables declared as appropriate anchored %TYPE's or to do a SELECT * into an appropriate %ROWTYPE variable.  That is, either

     

     

     l_empno emp.empno%type;
     l_ename emp.ename%type;
    BEGIN
      SELECT empno, ename
        INTO l_empno, l_ename
        FROM emp
       WHERE <<some condition>>;
      ...
    END;

     

    or

     

      l_emp emp%rowtype;
    BEGIN
      SELECT *
       INTO l_emp
       FROM emp
    WHERE <<some condition>>
    ...
    END;

     

    In either case, if you add a new column to the table or modify an existing column, the code will recompile successfully when it is next invoked.  This is different from the question of whether the code gets marked as invalid initially since, most of the time, what you really care about is whether Oracle will be able to successfully recompile the code when it is called not whether it simply needs to be automatically recompiled after the change.

     

    If you make use of %ROWTYPE records particularly as parameters to other procedures, that can minimize the amount of effort required to add a new column and modify the code to deal with that new column.  Rather than needing to potentially add a new parameter to a bunch of different procedures, if you're always passing around a %ROWTYPE, you can just add code wherever you want to look at the new column.  Of course, that means that you're paying a performance penalty in that you're always reading the entire row and always storing the entire row in memory even if you're only really interested in a subset of columns that could be part of a covering index.

     

    Justin

  • 7. Re: Does "SELECT *" minimize dependency failure?
    rp0428 Guru
    Currently Being Moderated

    A SELECT . . . BULK COLLECT INTO doesn't require an explicit cursor.


  • 8. Re: Does "SELECT *" minimize dependency failure?
    9423755 Explorer
    Currently Being Moderated

    Tubby wrote:

     

    But that's kind of the problem with posting questions based on material like this. The question, as posted, has no context.

     

    In version 5 of the Oracle database maybe their answer was correct, in version 7 it might have changed. So for all I know earlier in the chapter you are quoting from they talk exclusively about Oracle version 5, then you come to the forum and copy/paste the question and you're obviously going to get a different answer than the material quoted ... because the context is lacking.

     

    Cheers,

     

    This is exam prep for the 1z0-146 exam. It's therefore for 11G. It's a test. There's no more context than what you get in the question, as would be the case in the real exam.

    However, maybe you mean the question that I created this thread for is lacking context because I didn't paste it in its entirety.  I don't think you gain anything from seeing the whole question - the question is simply "does SELECT * minimize dependency failure" -  and I'd rather not have posted the whole thing because the provider might be sensitive to it, but here you go:

     

    Custom Test, 44 of 49

           

    Q: You work as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a table named Employees. You are required to create a procedure named Add_Emp that will be used to insert new records in the Employees table. What precautions will you take while creating the procedure in order to minimize the dependency failure in case the Employees table undergoes any change?

     

    Each correct answer represents a complete solution. Choose all that apply.

     

    a) Declare variables by using the %TYPE attribute.

    b) Specify schema names when referencing objects in the procedure.

    c) Use the SELECT * notation when performing queries through the procedure.

    d) Include a column list with the INSERT statement.

     

    Explanation: Answer options D, A, and C are correct.

    During the recompilation of a dependent subprogram (procedure or function), the dependency failures can be minimized by following the below-mentioned guidelines:

    Declare records by using the %ROWTYPE attribute.

    Declare variables with the %TYPE attribute.

    Perform queries with the SELECT * notation.

    Include a column list with INSERT statements.

     

    In addition to the issue with "SELECT *", there is also the issue with %ROWTYPE. Oracle themselves state (Schema Object Dependency)

     

    Table 18-2 Operations that Cause Fine-Grained Invalidation

    ALTER TABLE table ADD column...

    • Dependent object (except a view) uses SELECT * on table.
    • Dependent object uses table%rowtype.
    • Dependent object performs INSERT on table without specifying column list.
    • Dependent object references table in query that contains a join.
    • Dependent object references table in query that references a PL/SQL variable.

     

    I'm not sure what you mean by version 7 and version 5.

  • 9. Re: Does "SELECT *" minimize dependency failure?
    9423755 Explorer
    Currently Being Moderated

    Yes Justin I see what you mean but the whole point of fine-grained invalidation is to minimize unnecessary invalidations and consequent recompilations. You can do that by [edit] NOT using SELECT * in queries.

  • 10. Re: Does "SELECT *" minimize dependency failure?
    Justin Cave Oracle ACE
    Currently Being Moderated

    I agree that using a %ROWTYPE and a SELECT * causes the procedure to be invalidated when a new column is added to the table (I assume you mean't "you can't do that by using SELECT * in queries").

     

    It isn't obvious to me, though, that a procedure that is invalidated and silently recompiled successfully is what "dependency failure" is referring to.  If the question were to ask how to minimize invalidation, SELECT * would be clearly incorrect.

     

    This isn't to say that the question is a good one.  In general, if you're not finding errors in whatever exam prep material you're using, you're probably not ready to take the exam.  The questions that are actually on OCP exams have generally gone through far more review than the questions on any exam prep material.  The benefit of that extra review is that lots of people have the ability to point out ambiguity and questions get refined or thrown out until the actual questions are generally pretty clear.

     

    Justin

  • 11. Re: Does "SELECT *" minimize dependency failure?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    c) Use the SELECT * notation when performing queries through the procedure.

     

    Explanation: Answer options D, A, and C are correct.

    During the recompilation of a dependent subprogram

     

    Thinking this through - and remembering the threat is "failure to recompile" - and pursuing only one line of several:

     

    Changing from 'select list of columns' to 'select *' protects you in the case where a column name changes or the column is dropped - but only if you've declared a variable of table%rowtype for the table. (If columns are added to the table, the fact that you select a named subset that doesn't reference the new column does not result in a dependency problem).

     

    But if you've selected the column as part of an explicit list, your code is probably supposed to do something with it - so the compilation will fail when it reaches the point where you do something with a column (from a %rowtype variable) that doesn't exist. So where's the minimisation ?

     

    This is the type of question that might be good at an interview to prompt a candidate to discuss the many different aspects that are worth considering - it's not a good question for a multiple choice list.

     

    (The one about schema names is arguable too - omitting them may reduce the risk of failure to compile while increasing the risk of compiling successfully against the wrong object.  It may be the right answer to the given question while being a very bad idea in practice.)

     

    Regards

    Jonathan Lewis

  • 12. Re: Does "SELECT *" minimize dependency failure?
    9423755 Explorer
    Currently Being Moderated

    JonathanLewis wrote:

     

     

     

    This is the type of question that might be good at an interview to prompt a candidate to discuss the many different aspects that are worth considering - it's not a good question for a multiple choice list.

     

     

    Regards

    Jonathan Lewis

     

    I agree. I'll wager though that there is a good chance that this topic comes up in the exam, albeit of course any question could be phrased in many ways.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points