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" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Monday, January 27, 2003 7:59 AM
Subject: Re: Slow running query
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> 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:[EMAIL PROTECTED]]
> > 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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
>
