PL/SQL:insert behaves different in procedure and interactive

From: Carsten Jacobs <carsten.jacobs_at_tool42.com>
Date: Thu, 02 Nov 2000 17:40:57 +0100
Message-ID: <3A019919.131851E@tool42.com>

Hi,

I want to store the 100 most ordered items by a particular customer (120) in octobre.
I can do the following:

INSERT INTO topten

   SELECT * FROM

       (
       SELECT TO_DATE('2000-10-31 16:00:00','YYYY-MM-DD HH24:MI:SS'),
              order.item_no,
              COUNT(*)
       FROM   order
       WHERE  order.customer_id  = 120
       AND    order.order_date >= TO_DATE('2000-10-01
00:00:00','YYYY-MM-DD HH24:MI:SS')
       AND    order.order_date <  TO_DATE('2000-11-01
00:00:00','YYYY-MM-DD HH24:MI:SS')
       GROUP BY order.item_no
       ORDER BY 3 DESC
       )

    WHERE rownum < 101;

But this results in very, very long execution time and I don't know why because the select by itself is fast.

Then I tried to first store all rows ordered into another table

INSERT INTO tmp_topten

   SELECT TO_DATE('2000-10-31 16:00:00','YYYY-MM-DD HH24:MI:SS'),

          order.item_no,
          COUNT(*)

   FROM order
   WHERE order.customer_id = 120
   AND order.order_date >= TO_DATE('2000-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

   AND order.order_date < TO_DATE('2000-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

   GROUP BY order.item_no
   ORDER BY 3 DESC; This is fast. The only differnce is that there is no embracing SELECT * FROM () WHERE rownum < 11.
This I do in a second step:

INSERT INTO topten

	SELECT	* 
        FROM tmp_topten
	WHERE rownum < 101;
	ORDER BY rownum;

This is also fast and the workaround is complete.

Then there was the next handicap.
I tried all this interactivly in SQL+ and it worked fine. Then I put this (first and second step) into a stored procedure but couldn't compile it.
And it looked like that there is someting wrong with the ORDER BY in the INSERT clause.
It seems like there is no order by clause allowed in a stored procedure together with INSERT.

I thank everybody who reads until this point. And I appreciate every help because I feel like
running agoinst walls.

Greetings
Carsten Received on Thu Nov 02 2000 - 10:40:57 CST