Re: Query help please

From: bob123 <bob123_at_gmail.com>
Date: Wed, 18 Mar 2009 21:41:15 +0100
Message-ID: <49c15c60$0$21947$426a74cc_at_news.free.fr>

Thanks shakespeare end randolf
tkprof gives :

Rows Row Source Operation

-------  ---------------------------------------------------

  42210 SORT ORDER BY (cr=1385255 r=73108 w=2548 time=787446353 us)   71323 COUNT (cr=1385255 r=71572 w=0 time=2485287401 us)

  71323    FILTER  (cr=1385255 r=71572 w=0 time=2485075427 us)
  71323     NESTED LOOPS  (cr=1242562 r=71563 w=0 time=2481867874 us)
  71323      NESTED LOOPS  (cr=1028551 r=7281 w=0 time=60655450 us)
  71323       NESTED LOOPS  (cr=885863 r=7266 w=0 time=58894577 us)
 291183        TABLE ACCESS FULL PFMQ_MESSAGESTATUS (cr=12300 r=7266 w=0 
time=53594430 us)
  71323        TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGEINFO (cr=873563 r=0 
w=0 time=4048887 us)
 291183         INDEX UNIQUE SCAN XPKPF_MESSAGEINFO (cr=582371 r=0 w=0 
time=2211983 us)(object id 6256)
  71323       INDEX UNIQUE SCAN XPKPF_MESSAGEDATA (cr=142688 r=15 w=0 
time=1304993 us)(object id 6260)
  71323      TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGEDATASTORAGE (cr=214011 
r=64282 w=0 time=2420654752 us)
  71323       INDEX UNIQUE SCAN XPKPF_MESSAGEDATASTORAGE (cr=142688 r=15 w=0 
time=1232782 us)(object id 6258)
  71323     SORT AGGREGATE (cr=142693 r=9 w=0 time=2140698 us)
  71323      FIRST ROW  (cr=142693 r=9 w=0 time=1516638 us)
  71323       INDEX RANGE SCAN (MIN/MAX) XPKPF_MESSAGESTATUS (cr=142693 r=9 

w=0 time=1161703 us)(object id 6254) Received on Wed Mar 18 2009 - 15:41:15 CDT