Results 1 to 14 of 14

Thread: Query

  1. #1
    Join Date
    Jul 2009
    Posts
    11

    Unanswered: Query

    how to code select * except a column..

    please help!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Very simple: list all columns that you do want

  3. #3
    Join Date
    Jul 2009
    Posts
    11
    Quote Originally Posted by shammat
    Very simple: list all columns that you do want
    what if you have already an existing table with 30 columns?

    do you still select it explicitely?

    Is there no way to use it as "SELECT EVERYTHING FROM TABLE EXCEPT A COLUMN"?

    Pls do help if you have some very good idea or suggestion to solve this matter..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what if you have already an existing table with 30 columns?
    You list the 29 columns you desire.

    >do you still select it explicitely?
    YES

    >Is there no way to use it as "SELECT EVERYTHING FROM TABLE EXCEPT A COLUMN"?
    NO

    >Pls do help if you have some very good idea or suggestion to solve this matter..

    do you pay for each character you type?

    SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = <MANY_COLUMN_TABLE>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2009
    Posts
    11
    Quote Originally Posted by anacedent
    >
    do you pay for each character you type?
    nope..

    but thanks anyway for the comments.. and suggestion.. it helps me a lot..

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by UtOkbOlinAO
    what if you have already an existing table with 30 columns?
    do you still select it explicitely?
    Yes. Using SELECT * inside a production application is a very bad habbit and a an expression "all columns except one" is no different from that.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    something to consider if you use the select * or select all but this_col(if such a construct were to ever exist) is how do your programs handle this when you add a column to the table? Thus the reasoning for spelling out exactly what you want to see in your select clause.
    Dave

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Another benefit of specifying only the columns that are needed in a particular situation is that such queries will perform faster if all columns are present in an index, as there will not be any table access. SELECT * will always involve table access.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jul 2009
    Posts
    11
    Quote Originally Posted by anacedent
    >
    SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = <MANY_COLUMN_TABLE>
    This is just what i nid.. Thanks for this...

  10. #10
    Join Date
    Jul 2009
    Posts
    1
    Quote Originally Posted by UtOkbOlinAO
    how to code select * except a column..

    please help!
    OFFTOP^ It's my first message (because of stupid forum's politics).

  11. #11
    Join Date
    Feb 2009
    Posts
    62
    Another benefit of specifying only the columns that are needed in a particular situation is that such queries will perform faster if all columns are present in an index, as there will not be any table access. SELECT * will always involve table access.
    I have learned the long and hard way that absolute statements like this often lead to learning experiences

    This is one such situation. One Select * with no table access coming right up:
    Code:
    SQL> create table test_020 (col_1  number not null, col_2 number not null);
    
    Table created.
    
    SQL> 
    SQL> insert into test_020 select level,mod(level,1000) from dual connect by level <= 1000000;
    
    1000000 rows created.
    
    SQL> 
    SQL> create index test_020_idx on test_020(col_1,col_2);
    
    Index created.
    
    SQL> 
    SQL> begin 
      2    dbms_stats.gather_table_stats(ownname => null, tabname =>'TEST_020', cascade => true);
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> explain plan for
      2  select /*+ INDEX_FFS (TEST_020) */
      3  *
      4  from test_020;
    
    Explained.
    
    SQL> 
    SQL> select * from table(dbms_xplan.display());
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------
    Plan hash value: 2431877972
    
    -------------------------------------------------------------------------------------
    | Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |              |   995K|  7774K|   632   (4)| 00:00:08 |
    |   1 |  INDEX FAST FULL SCAN| TEST_020_IDX |   995K|  7774K|   632   (4)| 00:00:08 |
    -------------------------------------------------------------------------------------
    
    8 rows selected.
    
    SQL>

  12. #12
    Join Date
    Feb 2009
    Posts
    62
    something to consider if you use the select * or select all but this_col(if such a construct were to ever exist) is how do your programs handle this when you add a column to the table? Thus the reasoning for spelling out exactly what you want to see in your select clause.
    worked for one company where all table access was done via stored procedures, and every table had a Data model package, with a <table_name>%rowtype variable declared. The generic table access procedure just did a SELECT * INTO <rowtype variable>

    That actually worked quite well. You didn't need to make any package changes, and any new variables became available automatically.

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by JRowbottom
    I have learned the long and hard way that absolute statements like this often lead to learning experiences
    Doesn't really contradict what I've said. May be I should have phrased it as "SELECT * will always involve table access, unless by some miracle you have an index that includes all columns in the table, which mostly happens in textbooks and examples for the sake of examples".

    Happy?
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Feb 2009
    Posts
    62
    Or we could rephrase it to a more general case that has less sour grapes in it:
    SELECT <any list of columns> will always involve table access, unless you have an index that includes all columns in the list

Posting Permissions

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