Query

UtOkbOlinAO · #1 (**permalink**) 07-09-09, 04:30

how to code select * except a column..

please help!

shammat · #2 (**permalink**) 07-09-09, 05:23

Very simple: list all columns that you do want

UtOkbOlinAO · #3 (**permalink**) 07-09-09, 20:44

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..

anacedent · #4 (**permalink**) 07-09-09, 21:50

>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>

UtOkbOlinAO · #5 (**permalink**) 07-10-09, 00:22

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..

shammat · #6 (**permalink**) 07-10-09, 02:29

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.

dav1mo · #7 (**permalink**) 07-10-09, 11:43

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

n_i · #8 (**permalink**) 07-10-09, 12:16

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.

UtOkbOlinAO · #9 (**permalink**) 07-12-09, 20:25

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...

Dummy123 · #10 (**permalink**) 07-15-09, 08:51

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).

JRowbottom · #11 (**permalink**) 07-17-09, 10:50

Quote:

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>

JRowbottom · #12 (**permalink**) 07-17-09, 10:53

Quote:

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.

n_i · #13 (**permalink**) 07-17-09, 11:39

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?