Re: Would be really nice if...

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 24 Feb 2005 09:18:15 -0800
Message-ID: <1109265313.999405@yasure>

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.

I disagree. Select all 50 and just ignore the two you don't need.

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?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Thu Feb 24 2005 - 11:18:15 CST