Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: To SELECT * or not SELECT *

    Obviously, in general SELECT * is not good practise as you may not want all columns and you are making the DB do more work. However is there ever a cae when it's ok to SELECT * , for example:

    1. Suppose you wanted all columns anyway. Surely SELECT * is just shorthand for this and from the DB perspective there is no performance difference?

    2. Suppose you want to use the ROWNUM feature of oracle. Your query has an ORDER BY, so you have turn your query into a subselect and then use ROWNUM on it.

    Does it matter if you do it by

    SELECT * FROM (SELECT A, B, C FROM TABLE ORDERBY I) ROWNUM = ...

    as opposed to

    SELECT A, B, C FROM (SELECT A, B, C FROM TABLE ORDERBY I) ROWNUM = ...

    Comments welcomed.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    The general problem with SELECT * is that it tightly couples any code that depends on the SELECT to the underlying table structure. ie., dependent code may break if columns are added to a table, even when those new columns ought to have been ignored. Therefore it's poor practice to use SELECT * because it increases the potential impact of database change on your code.

    However, there are cases like the example you gave where that isn't a problem because the set of columns to be returned is defined elsewhere in the query anyway.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Also, it's OK in PL/SQL if you are selecting into a ROWTYPE variable e.g.

    Code:
    DECLARE
       l_emp emp%ROWTYPE;
    BEGIN
       SELECT * INTO l_emp WHERE empno = 123;
    END;
    However, it would be inefficient to do that in cases where you are only going to use a small proportion of the columns in the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •