Re: Slow running query

Ok, thanks for the test. you've right.

• Original Message ----- From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 5:13 PM Subject: Re: Slow running query

  Looks to me like that hurts more than it helps...     SQL> declare

      2          v_cnt           number;
      3  begin
      4          for i in 1..10000 loop
      5                  select  count(*) into v_cnt from x$dual;
      6          end loop;
      7  end;
      8  /
     
    PL/SQL procedure successfully completed.
     

    Elapsed: 00:00:02.60
    SQL>
    SQL> declare

      2          v_cnt           number;
      3  begin
      4          for i in 1..10000 loop
      5                  select  count(dummy) into v_cnt from x$dual;
      6          end loop;
      7  end;
      8  /
     
    PL/SQL procedure successfully completed.
     

    Elapsed: 00:00:03.72
  These are results from 9.0.1 on Solaris, but I tested it on 8.1.7 on Solaris and 9.2.0 on Windows as well, results were similar but not as pronounced. More likely, "count(*)" is optimized not to actually access the table if it isn't necessary, similar to "count(literal-expression)". Advising "count(column-name)" guarantees that column values must be read, which is only useful if column values are important...    

• Original Message ----- From: "Amos KABORE" <akabore_at_experco.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, January 27, 2003 7:59 AM Subject: Re: Slow running query

> first thing to do, try to use count(a_column_name) instead of count(*)
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Monday, January 27, 2003 2:29 PM
>
>
> > First thought,
> >
> > Count(*) and FIRST_ROWS are self contradictory. If you want to count all
> the
> > records, how will first_rows help?
> >
> > Regards
> > Naveen
> >
> > -----Original Message-----
> > [mailto:Ranganath.Krishnaswamy_at_blr.hpsglobal.com]
> > Sent: Monday, January 27, 2003 6:34 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Hi List:
> >
> > I have the below query which is taking 5 min. 20 sec. to fetch the
> > records. Can you please let me know as to how do I reduce the responste
> > time? I have created indexes on Fahrzeug.FZGBRIEF and
> > Historie.mytechobjekt and also the Oid column is uniquely indexed. I
> > replaced the FIRST_ROWS hint with RULE hint as a result of which it is
> > taking 3-4 min.
> >
> > select /*+FIRST_ROWS*/ count(*)
> >
> > from
> >
> > ZPAB.FZGBRIEF B1,
> >
> > ZPAB.FAHRZEUG F1,
> >
> > ZPAB.HISTORIE H1
> >
> > where
> >
> > F1.FZGBRIEF = B1.OID
> >
> > AND F1.OID = H1.MYTECHOBJEKT(+)
> >
> > AND (H1.CCCONTROL IN(1, 2, 3)
> >
> > OR NOT EXISTS
> >
> > (SELECT /*+INDEX_FFS(HISTORIE, I_MYTECHOBJEKT) */ ZPAB.FAHRZEUG.OID
> > FROM ZPAB.FAHRZEUG,ZPAB.HISTORIE
> >
> > WHERE ZPAB.FAHRZEUG.OID = ZPAB.HISTORIE.MYTECHOBJEKT and
> >
> > ZPAB.FAHRZEUG.oid=F1.oid))
> >
> > AND F1.AMTLICHESKENNZEICHEN LIKE 'DD%'
> >
> > Any help in this regard is very much appreciated.
> >
> > Thanks and Regards,
> >
> > Ranganath
> > WARNING: The information in this message is confidential and may be
> legally
> > privileged. It is intended solely for the addressee. Access to this
> message
> > by anyone else is unauthorised. If you are not the intended recipient,
> any
> > disclosure, copying, or distribution of the message, or any action or
> > omission taken by you in reliance on it, is prohibited and may be
> unlawful.
> > Please immediately contact the sender if you have received this message in
> > error. Thank you.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Krishnaswamy, Ranganath
> > INET: Ranganath.Krishnaswamy_at_blr.hpsglobal.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
> >
> > DISCLAIMER:
> > This message (including attachment if any) is confidential and may be
> privileged. Before opening attachments please check them for viruses and
> defects. MindTree Consulting Private Limited (MindTree) will not be
> responsible for any viruses or defects or any forwarded attachments
> emanating either from within MindTree or outside. If you have received this
> message by mistake please notify the sender by return e-mail and delete
> this message from your system. Any unauthorized use or dissemination of this
> message in whole or in part is strictly prohibited. Please note that
> e-mails are susceptible to change and MindTree shall not be liable for any
> improper, untimely or incomplete transmission.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Naveen Nahata
> > INET: naveen_nahata_at_mindtree.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Amos KABORE
> INET: akabore_at_experco.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Amos KABORE
  INET: akabore_at_experco.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).