Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Tuning problem: diff. between "SELECT COUNT(1)" and "SELECT <fields list>"

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

From: TurkBear <noone_at_nowhere.com>
Date: Mon, 16 Jul 2001 15:38:44 -0500
Message-ID: <h6k6ltsts0c171hsv45lk876uk2aa4b4n2@4ax.com>

Spendius <spendius_at_MailAndNews.com> wrote:

I won't ask why you want to do that, so try :

select count(*) from
 PV_ISSUEHISTORYLIST, -- view that contains 765195 rows

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



I am also not sure why you have an 'order by' when you are only counting..

>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)
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Mon Jul 16 2001 - 15:38:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US