Re: why administrator refuse to give permission on PLUSTRACE

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 03 Nov 2007 09:29:45 -0700
Message-ID: <1194107382.728562@bubbleator.drizzle.com>

Marc Blum wrote:
> On Fri, 02 Nov 2007 23:30:48 -0700, DA Morgan <damorgan_at_psoug.org> wrote:
>
> What a bunch of crap:
>

>> CREATE TABLE parent (
>> part_num  NUMBER,
>> part_name VARCHAR2(15));
>>

>
> no primary key declared, I wouls suspect one on part_num

Irrelevant. No WHERE clause so no filters or joins. A constraint and its associated index are meaningless.

Strike 1

> no alternate key declared, I would suspect one on part_name

Strike 2 for the same reason.

> no NOT NULL attributes, I would suspect one on part_num and part name
>

>> CREATE TABLE child AS
>> SELECT *
>>FROM parent;

And that will speed up an FTE? Strike 3.

> same as above, plus
>
> no foreign key declared, I would suspect one because of the table's name
>

>> DECLARE
>>  j PLS_INTEGER := 1;
>>  k parent.part_name%TYPE := 'Transducer';
>> BEGIN
>>   FOR i IN 1 .. 200000
>>   LOOP
>>     SELECT DECODE(k, 'Transducer', 'Rectifier',
>>     'Rectifier', 'Capacitor',
>>     'Capacitor', 'Knob',
>>     'Knob', 'Chassis',
>>     'Chassis', 'Transducer')
>>     INTO k
>>     FROM dual;
>>
>>     INSERT INTO parent VALUES (j+i, k);
>>   END LOOP;
>>   COMMIT;
>> END;
>> /

This is just loading the data for the demo ... it isn't part of the problem. Sorry if this caused some confusion.

> beside, this could be easily done in in one INSERT INTO...SELECT FROM, I leave
> it to you as an exercise
>
>

>> CREATE OR REPLACE PROCEDURE test IS
>> BEGIN
>>   FOR r IN (SELECT * FROM parent)
>>   LOOP
>>     r.part_num := r.part_num * 10;
>>     INSERT INTO child
>>     VALUES
>>     (r.part_num, r.part_name);
>>   END LOOP;
>>   COMMIT;
>> END test;
>> /

>
>
>
> again, INSERT without qualifiing the columns, bad habits, bad habits

Bad habit but doesn't affect performance.

> again, this could be done with one statement

Finally the correct solution.

> why you are shifting the part_num remains a mystery, due to the crappy data
> model without any constraints, you receive no exception and have to live with
> corupted data

Because it is a problem given to first year students to solve. If you give students perfectly written code to tune they don't learn anything.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Nov 03 2007 - 11:29:45 CDT