Re: Would be really nice if...

DA Morgan wrote:
> Haximus wrote:
>

>>> Possibly someone could explain how this would be beneficial as the
>>> fundamental concept of this request escapes me.
>>
>>
>>
>> Table or queries returning large number of columns...  for arguments 
>> sake lets say 50 columns, and you want to select 48 of them... makes 
>> sense to select * and exclude the two you don't want.
>>
>> Another way... if you have "N" columns and the number of columns 
>> desired is greater than N/2, it's less work to specify which columns 
>> you don't want, especially the closer you get to N. 

And if those two columns happen to be blobs containing large amounts of data, you inadvertantly just initiated a huge amount of traffic and resource consumption.

>
> Consider the following:
>
> SQL> DECLARE
> 2
> 3 l_owner dbms_sql.VARCHAR2_table;
> 4 l_object_name dbms_sql.VARCHAR2_table;
> 5 l_subobject_name dbms_sql.VARCHAR2_table;
> 6 l_object_id dbms_sql.NUMBER_table;
> 7 l_data_object_id dbms_sql.NUMBER_table;
> 8 l_object_type dbms_sql.VARCHAR2_table;
> 9 l_created dbms_sql.DATE_table;
> 10 l_last_ddl_time dbms_sql.DATE_table;
> 11 l_timestamp dbms_sql.VARCHAR2_table;
> 12 l_status dbms_sql.VARCHAR2_table;
> 13 l_temporary dbms_sql.VARCHAR2_table;
> 14 l_generated dbms_sql.VARCHAR2_table;
> 15 l_secondary dbms_sql.VARCHAR2_table;
> 16
> 17 CURSOR c IS
> 18 SELECT *
> 19 FROM all_objects;
> 20
> 21 BEGIN
> 22 OPEN c;
> 23 LOOP
> 24 FETCH c BULK COLLECT INTO
> 25 l_owner, l_object_name, l_subobject_name, l_object_id,
> 26 l_data_object_id, l_object_type, l_created,
> 27 l_last_ddl_time, l_timestamp, l_status, l_temporary,
> 28 l_generated, l_secondary
> 29 LIMIT 100;
> 30
> 31 FORALL i in 1 .. l_owner.COUNT
> 32 INSERT INTO t1
> 33 (owner, object_name, subobject_name, object_id,
> 34 data_object_id, object_type, created, last_ddl_time
> 35 timestamp, status, temporary, generated, secondary)
> 36 VALUES
> 37 (l_owner(i), l_object_name(i), l_subobject_name(i),
> 38 l_object_id(i), l_data_object_id(i),
> 39 l_object_type(i), l_created(i), l_last_ddl_time(i),
> 40 l_timestamp(i), l_status(i), l_temporary(i),
> 41 l_generated(i), l_secondary(i));
> 42 EXIT WHEN c%NOTFOUND;
> 43 END LOOP;
> 44 CLOSE c;
> 45 END x;
> 46 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:02.63
> SQL> DECLARE
> 2
> 3 l_owner dbms_sql.VARCHAR2_table;
> 4 l_object_name dbms_sql.VARCHAR2_table;
> 5 l_subobject_name dbms_sql.VARCHAR2_table;
> 6 l_object_id dbms_sql.NUMBER_table;
> 7 l_data_object_id dbms_sql.NUMBER_table;
> 8 l_object_type dbms_sql.VARCHAR2_table;
> 9 l_created dbms_sql.DATE_table;
> 10 l_last_ddl_time dbms_sql.DATE_table;
> 11 l_timestamp dbms_sql.VARCHAR2_table;
> 12 l_status dbms_sql.VARCHAR2_table;
> 13 l_temporary dbms_sql.VARCHAR2_table;
> 14 l_generated dbms_sql.VARCHAR2_table;
> 15 l_secondary dbms_sql.VARCHAR2_table;
> 16
> 17 CURSOR c IS
> 18 SELECT *
> 19 FROM all_objects;
> 20
> 21 BEGIN
> 22 OPEN c;
> 23 LOOP
> 24 FETCH c BULK COLLECT INTO
> 25 l_owner, l_object_name, l_subobject_name, l_object_id,
> 26 l_data_object_id, l_object_type, l_created,
> 27 l_last_ddl_time, l_timestamp, l_status, l_temporary,
> 28 l_generated, l_secondary
> 29 LIMIT 100;
> 30
> 31 FORALL i in 1 .. l_owner.COUNT
> 32 INSERT INTO t2
> 33 (owner, object_name)
> 34 VALUES
> 35 (l_owner(i), l_object_name(i));
> 36 EXIT WHEN c%NOTFOUND;
> 37 END LOOP;
> 38 CLOSE c;
> 39 END x;
> 40 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:01.43
> SQL>
>
> In the first case I select all columns and insert all into t1.
> In the second case I select all columns and use only two of them.
> Where is the pain?

The pain is that it's a waste. C'mon, you can do better than that! Received on Thu Feb 24 2005 - 15:27:57 CST