Strange Statistics I/O Results 
Author Message
 Strange Statistics I/O Results

I've got a relatively simple query -
 select s.EDIBrchCd, S.ProdCd, B.Name
 from ScrubError s, Branch b
 where b.Hqid='ABC1234'
 and s.EDIBrchCd = b.BrchId

(A headquarter contains many branches - I want everything in
ScrubError table for a single headquarter id....)

The showplan shows an access to the Branch table first, using a

non-clustered index on Hqid (there is only one qualifying
resulting row  
in this case,) then does a nested-iteration to ScrubError using
non-clustered index on EDIBrchCd (there are NO rows in
ScrubError from  
the resulting branch-id) - the query plan seems VERY  

The statistics io show 42837 logical reads to ScrubError (!!!!
coincidentally the number of total rows in ScrubError), and 3
to Branch  
(this part seems ok.)

Can somebody explain the 42837 number to me??  Seems like with
non-clustered index, it should be doing about 2 or 3 reads.  
It's really  

difficult to optimize when the 'optimizer' is giving strange


Tue, 14 Jul 1998 03:00:00 GMT
 [ 1 post ] 

 Relevant Pages 

1. Strange results with Oracle 8.1.7 and statistic gathering dbms_stats vs dbms_utility

2. Retrieve index statistics (estimated results for search term)?

3. Help... float data resulting in inaccurate statistics :(

4. Statistics IO results

5. CPU Statistics as a result of Oracle

6. Is it possible to see the results of Update Statistics in an

7. strange DB/2 OS/390 problem

8. AUDIT_TRAIL=OS: where is the resulting file ?!?

9. Unexpected query results - UDB 7.1 OS/2

10. Strange Results...

11. Strange SP Results

12. strange result when using the into statement

Powered by phpBB® Forum Software