Re: Oracle For Fun

Giganews Newsgroups
Subject: Re: Oracle For Fun
Posted by:  itzk…@rediffmail.com
Date: 27 Jan 2004

What do you say about this:

CREATE TABLE t1 AS
SELECT *
FROM all_objects
WHERE 1=0;

CREATE OR REPLACE PROCEDURE test_proc IS

BEGIN
    INSERT /*+ APPEND */ INTO t1
    (owner, object_name, subobject_name, object_id,
    data_object_id, object_type, created, last_ddl_time,
    timestamp, status, temporary, generated, secondary)
    select owner, object_name, subobject_name, object_id,
    data_object_id, object_type, created, last_ddl_time,
    timestamp, status, temporary, generated, secondary from all_objects nologging;
  COMMIT;
END test_proc;
/

set timing on
exec test_proc;
set timing off

- KDB

Daniel Morgan <damorg…@x.washington.edu> wrote in message news:<1075188942.399322@yasure>...
> At a class I taught this last weekend I brought up the fact that most
> PL/SQL programmers are still writing v7 code. I gave everyone there a
> challenge and thought I'd share it with the group for any of you looking
> for a challenge on which to sharpen your skills.
>
> CREATE TABLE t1 AS
> SELECT *
> FROM all_objects
> WHERE 1=0;
>
> CREATE OR REPLACE PROCEDURE test_proc IS
>
> BEGIN
>  FOR x IN (SELECT * FROM all_objects)
>  LOOP
>    INSERT INTO t1
>    (owner, object_name, subobject_name, object_id,
>      data_object_id, object_type, created, last_ddl_time,
>      timestamp, status, temporary, generated, secondary)
>    VALUES
>    (x.owner, x.object_name, x.subobject_name, x.object_id,
>    x.data_object_id, x.object_type, x.created,
>    x.last_ddl_time, x.timestamp, x.status, x.temporary,
>    x.generated, x.secondary);
>  END LOOP;
>  COMMIT;
> END test_proc;
> /
>
> set timing on
> exec test_proc;
> set timing off
>
> Everyone using 8i+ features should be able to improve the performance of
> this by at least 5X.
>
> I'll post a solution in a week or so.

Replies

None

In response to

Oracle For Fun posted by Daniel Morgan on Mon, 26 Jan 2004