Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: Re: Re: Handling conditions DBI in Oracle 8.1

by Rhose (Priest)
on Jan 16, 2002 at 20:01 UTC ( [id://139231]=note: print w/replies, xml ) Need Help??


in reply to Re: Re: Handling conditions DBI in Oracle 8.1
in thread Handling conditions DBI in Oracle 8.1

You can't use SELECT COUNT(*) in Oracle because it will always give you the wrong count. For you to get an accurate count you will have to ANALYZE.

I am 100% sure this is not true.

ANALYZE collect statistics for the cost based optimizer and is used to improve (hopefully) the speed at which a query completes. It in no way impacts the results of the query... only the speed.

Update:

Just to confirm the above, I threw together a sample SQL script. Its output is below.

Update #2:

Now I think I know about what you are thinking -- the column 'num_rows' in the 'dba_tables' view is not populated until you analyze the table. This is not the same thing as doing a COUNT(*), but is similar.

--------------------------- ----- Test SQL Script ----- --------------------------- 1 SELECT version 2* FROM v$instance VERSION ----------------- 8.1.7.2.0 1* DROP TABLE oracle.bobtest DROP TABLE oracle.bobtest * ERROR at line 1: ORA-00942: table or view does not exist 1 CREATE TABLE oracle.bobtest 2* ( junk VARCHAR(1) ) Table created. 1 INSERT INTO oracle.bobtest 2* VALUES ( 'A' ) 1 row created. 1 row created. 1 row created. 1* COMMIT Commit complete. 1 SELECT COUNT(*) 2* FROM oracle.bobtest COUNT(*) ---------- 3 1 ANALYZE TABLE oracle.bobtest 2* COMPUTE STATISTICS Table analyzed. 1 SELECT COUNT(*) 2* FROM oracle.bobtest COUNT(*) ---------- 3 1 ANALYZE TABLE oracle.bobtest 2* DELETE STATISTICS Table analyzed. 1 SELECT COUNT(*) 2* FROM oracle.bobtest COUNT(*) ---------- 3 1 ANALYZE TABLE oracle.bobtest 2* ESTIMATE STATISTICS SAMPLE 20 PERCENT Table analyzed. 1 SELECT COUNT(*) 2* FROM oracle.bobtest COUNT(*) ---------- 3 1* DROP TABLE oracle.bobtest Table dropped.

Replies are listed 'Best First'.
Re: Re: Re: Re: Handling conditions DBI in Oracle 8.1
by data67 (Monk) on Jan 17, 2002 at 00:44 UTC
    I have been enlightened, thanks guys for the input. I guess i need to read up more on Oracle.

    Data     ;-}

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://139231]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (3)
As of 2024-04-16 21:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found