Toad World® Forums

script output and compute statements with set timi on

Hi all,

Toad 12.11.0.40 (but last prod version) x64.

There is a small bug in script output.
Script is:

clear breaks
break on report

clear computes
compute sum of tot_blocks on report
compute sum of tot_rows on report
compute AVG of avg_rows on report

column avg_rows format 990.90

select
twentieth,
min(rows_per_block) min_rows,
max(rows_per_block) max_rows,
sum(block_ct) tot_blocks,
sum(row_total) tot_rows,
round(sum(row_total)/sum(block_ct),2) avg_rows
from
(
select
ntile(nvl(&&3,20)) over (order by rows_per_block) twentieth,
rows_per_block,
count() block_ct,
rows_per_block * count(
) row_total
from
(
select
fno, bno, count(*) rows_per_block
from
(
select
dbms_rowid.rowid_relative_fno(rowid) as fno,
dbms_rOwId.rowid_block_number(rowid) as bno
from
&&1…&&2
)
group by
fno, bno
)
group by
rows_per_block
order by
rows_per_block
)
group by
twentieth
order by
twentieth
;

Where last few lines from output in toad are:

     3         15         21       1572      27081    17.23
     4         22         28        223       5272    23.64
     5         29         36         19        582    30.63
                                                   --------

avg 17.23
sum 22672 169615Elapsed: 00:00:04.85

Brg,
Damir