|
Ok, thanks for the test. you've right.
----- Original Message -----
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 -----
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" <[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).
>
|
