| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow running query
Tim,
As you said, a count(column_name) has to query the table (if there is no index on the column) to see if the column is populated. only non-null columns are counted. if an index exists on that column, then all entries of the index are counted.
a count (*) will simply count the PK entries for the table. absent that, it will count any index supporting a not-null column. absent that, I'm guessing it counts all records in the table.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Monday, January 27, 2003 12:14 PM
To: Multiple recipients of list ORACLE-L
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;
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;
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...
> first thing to do, try to use count(a_column_name) instead of count(*)
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <
<mailto:ORACLE-L_at_fatcity.com> 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>
http://www.orafaq.net
> > --
> > Author: Krishnaswamy, Ranganath
> > INET: <mailto:Ranganath.Krishnaswamy_at_blr.hpsglobal.com>
Ranganath.Krishnaswamy_at_blr.hpsglobal.com
> >
> > Fat City Network Services -- 858-538-5051 <http://www.fatcity.com>
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: <mailto:ListGuru_at_fatcity.com> 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>
http://www.orafaq.net
> > --
> > Author: Naveen Nahata
> > INET: <mailto:naveen_nahata_at_mindtree.com> naveen_nahata_at_mindtree.com
> >
> > Fat City Network Services -- 858-538-5051 <http://www.fatcity.com>
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: <mailto:ListGuru_at_fatcity.com> 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>
http://www.orafaq.net
> --
> Author: Amos KABORE
> INET: <mailto:akabore_at_experco.com> akabore_at_experco.com
>
> Fat City Network Services -- 858-538-5051 <http://www.fatcity.com>
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: <mailto:ListGuru_at_fatcity.com> 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: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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).Received on Mon Jan 27 2003 - 11:55:32 CST
 |
 |