how to tune up this query? (Long post with example)

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 30 Jan 2003 14:26:54 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7033D51E5@lnewton.leeds.lfs.co.uk>

Hi folks,

Not withstanding Richard's comments on efficiency, you also get the wrong answers :o)

DROP TABLE table_name;
Table dropped  

CREATE TABLE table_name (

       IDENT NUMBER,
       A_NAME VARCHAR2(30),
       priority NUMBER,
       stage VARCHAR2(10),
       state NUMBER);

Table created  

BEGIN

     FOR x IN 1 .. 100000 LOOP
         EXECUTE IMMEDIATE 'insert into table_name values (:id, :name,
:priority, :stage, :state)'
         USING x, 'NAME_'||x, MOD(x, 1000), 'STAGE_'||MOD(x, 2), MOD(x,
10);
     END LOOP;

END;
PL/SQL procedure successfully completed  

COMMIT;
Commit complete  

ANALYZE TABLE table_name COMPUTE STATISTICS; Table analyzed  

SELECT IDENT, A_NAME, priority FROM table_name   WHERE stage = 'STAGE_1' AND state = 1
  AND ROWNUM <= 10
  ORDER BY priority ASC;

     IDENT A_NAME                           PRIORITY
---------- ------------------------------ ----------
         1 NAME_1                                  1
        11 NAME_11                                11
        21 NAME_21                                21
        31 NAME_31                                31
        41 NAME_41                                41
        51 NAME_51                                51
        61 NAME_61                                61
        71 NAME_71                                71
        81 NAME_81                                81
        91 NAME_91                                91

10 rows selected    

SELECT IDENT, A_NAME, priority FROM
(SELECT IDENT, A_NAME, priority FROM table_name
WHERE stage = 'STAGE_1' AND state = 1 ORDER BY priority ASC) WHERE ROWNUM <= 10;

     IDENT A_NAME                           PRIORITY
---------- ------------------------------ ----------
         1 NAME_1                                  1
      1001 NAME_1001                               1
      2001 NAME_2001                               1
      5001 NAME_5001                               1
      4001 NAME_4001                               1
      3001 NAME_3001                               1
      9001 NAME_9001                               1
      8001 NAME_8001                               1
      7001 NAME_7001                               1
      6001 NAME_6001                               1

10 rows selected  

However, an explain plan on both these statements looks pretty much the same to me, so :

SELECT IDENT, A_NAME, priority FROM table_name   WHERE stage = 'STAGE_1' AND state = 1
  AND ROWNUM <= 10
  ORDER BY priority ASC;

Operation	Object Name	Rows	Bytes	Cost	Object Node
In/Out	PStart	PStop
SELECT STATEMENT Hint=CHOOSE		5 K	 	80

  SORT ORDER BY		5 K	126 K	80

    COUNT STOPKEY       TABLE ACCESS FULL TABLE_NAME 5 K 126 K 36 SELECT IDENT, A_NAME, priority FROM
(SELECT IDENT, A_NAME, priority FROM table_name
WHERE stage = 'STAGE_1' AND state = 1 ORDER BY priority ASC) WHERE ROWNUM <= 10;

Operation	Object Name	Rows	Bytes	Cost	Object Node
In/Out	PStart	PStop
SELECT STATEMENT Hint=CHOOSE		5 K	 	80

  COUNT STOPKEY     VIEW 5 K 209 K 80       SORT ORDER BY STOPKEY 5 K 126 K 80         TABLE ACCESS FULL TABLE_NAME 5 K 126 K 36 I can see both sorts doing 5K rows, 209 K bytes and a cost of 80.
(Assuming the formatting hasn't gone awol when I post this !)

What have I missed Richard ?

Cheers,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: Richard Foote [mailto:richard.foote_at_bigpond.com] Posted At: Thursday, January 30, 2003 2:28 PM Posted To: server
Conversation: how to tune up this query? Subject: Re: how to tune up this query?

Hi Ethel,

True, but the sort as a result is potentially going to be one big mamma.

See my previous post why your code is less efficient than Susan's.

Cheers

Richard Received on Thu Jan 30 2003 - 08:26:54 CST