Tuning problem: diff. between "SELECT COUNT(1)" and "SELECT <fields list>"

From: Spendius <spendius_at_MailAndNews.com>
Date: Mon, 16 Jul 2001 10:52:39 -0400
Message-ID: <3B590C19@MailAndNews.com>

Hello,

I have a query that returns me about 15 000 rows. When I set TIMING on in my SQL*Plus session, the SELECT COUNT(1) completes within 6 seconds
(more or less), but if I replace the COUNT(1) with a list of 34 fields
(that obviously need lots of room: most of them are VARCHAR2(50) or
(250)), the execution time takes 55 to 60 seconds.

My question is: is there a way of getting an execution time with all fields selected more or less equal to that that it takes to compute the COUNT(1) only ??

Thanks a lot in advance...

Info:

---

Oracle 8.1.7.1 on Sun/Solaris

SQL> SET AUTOT TRACE
SQL> select count(1)
  2 from PV_ISSUEHISTORYLIST, -- view that contains 765195 rows

  3        TB_DELIVVERSION     -- table "     "        1505  "
  4  where PV_ISSUEHISTORYLIST.DI = TB_DELIVVERSION.ID
  5    and TB_DELIVVERSION.DELIVERABLE = 1313

  6 order by PV_ISSUEHISTORYLIST.DATEHISTORY desc;

Statistics:

---

         32  recursive calls
         16  db block gets
     232810  consistent gets
       3736  physical reads
          0  redo size
        188  bytes sent via SQL*Net to client
        311  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

the same with the 34 fields selected:

Statistics:

---

        128  recursive calls
        106  db block gets
      17394  consistent gets
      25053  physical reads
          0  redo size
     870376  bytes sent via SQL*Net to client
      68651  bytes received via SQL*Net from client
       1022  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      15310  rows processed

and the execution plan is:
Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1486 Card=1 Bytes=12
          4)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (OUTER) (Cost=1486 Card=14274 Bytes=1769976
          )
   3    2       NESTED LOOPS (OUTER) (Cost=1486 Card=14274 Bytes=17414
          28)
   4    3         NESTED LOOPS (OUTER) (Cost=1486 Card=14274 Bytes=164
          1510)
   5    4           NESTED LOOPS (OUTER) (Cost=1486 Card=14274 Bytes=1
          541592)
   6    5             NESTED LOOPS (OUTER) (Cost=1486 Card=14274 Bytes
          =1498770)
   7    6               NESTED LOOPS (OUTER) (Cost=1486 Card=14274 Byt
          es=1455948)
   8    7                 HASH JOIN (OUTER) (Cost=1486 Card=14274 Byte
          s=1413126)
   9    8                   HASH JOIN (OUTER) (Cost=1385 Card=14274 By
          tes=1313208)
  10    9                     HASH JOIN (OUTER) (Cost=1290 Card=14274
          Bytes=1213290)
  11   10                       NESTED LOOPS (OUTER) (Cost=1202 Card=1
          4274 Bytes=1113372)
  12   11                         HASH JOIN (OUTER) (Cost=1202 Card=14
          274 Bytes=1070550)
  13   12                           NESTED LOOPS (OUTER) (Cost=1122 Ca
          rd=14274 Bytes=970632)
  14   13                             NESTED LOOPS (OUTER) (Cost=1122
          Card=14274 Bytes=927810)
  15   14                               NESTED LOOPS (OUTER) (Cost=112
          2 Card=14274 Bytes=884988)
  16   15                                 NESTED LOOPS (OUTER) (Cost=1
          122 Card=14274 Bytes=856440)
  17   16                                   NESTED LOOPS (OUTER) (Cost
          =1122 Card=14274 Bytes=827892)
  18   17                                     NESTED LOOPS (Cost=1122
          Card=14274 Bytes=799344)
  19   18                                       INDEX (RANGE SCAN) OF
          'DEVE_UK' (NON-UNIQUE) (Cost=2 Card=28 Bytes=196)
  20   18                                       TABLE ACCESS (BY INDEX
           ROWID) OF 'TB_ISSUEHISTORYF' (Cost=40 Card=765192 Bytes=374
          94408)
  21   20                                         INDEX (RANGE SCAN) O
          F 'ISHF_NI' (NON-UNIQUE) (Cost=3 Card=765192)
  22   17                                     INDEX (UNIQUE SCAN) OF '
          TB_ISSUESEVERITY_PK' (UNIQUE)
  23   16                                   INDEX (UNIQUE SCAN) OF 'TB
          _ISSUEENVIRON_PK' (UNIQUE)
  24   15                                 INDEX (UNIQUE SCAN) OF 'TB_I
          SSUESTATUS_PK' (UNIQUE)
  25   14                               INDEX (UNIQUE SCAN) OF 'TB_ISS
          UETYPE_PK' (UNIQUE)
  26   13                             INDEX (UNIQUE SCAN) OF 'TB_IMPAC
          TTYPE' (UNIQUE)
  27   12                           INDEX (FAST FULL SCAN) OF 'DEVE_UK
          ' (NON-UNIQUE) (Cost=2 Card=1505 Bytes=10535)
  28   11                         INDEX (UNIQUE SCAN) OF 'TBDELPK' (UN
          IQUE)
  29   10                       INDEX (FAST FULL SCAN) OF 'DEVE_UK' (N
          ON-UNIQUE) (Cost=2 Card=1505 Bytes=10535)
  30    9                     INDEX (FAST FULL SCAN) OF 'DEVE_UK' (NON
          -UNIQUE) (Cost=2 Card=1505 Bytes=10535)
  31    8                   INDEX (FAST FULL SCAN) OF 'DEVE_UK' (NON-U
          NIQUE) (Cost=2 Card=1505 Bytes=10535)
  32    7                 INDEX (UNIQUE SCAN) OF 'TBDELPK' (UNIQUE)
  33    6               INDEX (UNIQUE SCAN) OF 'TBDELPK' (UNIQUE)
  34    5             INDEX (UNIQUE SCAN) OF 'TBDELPK' (UNIQUE)
  35    4           INDEX (UNIQUE SCAN) OF 'TB_EMPLOYEE_PK' (UNIQUE)
  36    3         INDEX (UNIQUE SCAN) OF 'TB_EMPLOYEE_PK' (UNIQUE)
  37    2       INDEX (UNIQUE SCAN) OF 'TB_ISSUEMAINTYPE_PK' (UNIQUE)

Received on Mon Jul 16 2001 - 09:52:39 CDT