AnsweredAssumed Answered

How many big files there are in my Alfresco?

Question asked by luca on Sep 16, 2010
I was stumbling in the Alfresco Database to make some statistics and I was trying to answer to this post title question.

I made this query to find documents greater than 100MB:

select t.* from (
select np.node_id, to_number(substring(string_value, 'size=*([0-9]{1,15})'), '999999999999999')/(1024*1024) as dimension, np.string_value
from alf_node_properties np, alf_qname qn
where = np.qname_id
and qn.local_name = 'content'
) as t
where t.dimension> 100
order by t.dimension desc;

N.B. this script is tested with Postgres 8 and in my environment it takes about 1 minute to run (we have about 50GB of repository)

I made this post principally to help anyone that has my same needs, but, just in case, is there some more efficent way?
Does anyone have other useful script to share?