test snippet 2

SELECT /*+ use_hash(l u0 u1 u2 u3 u4 u5 m0 m1 m2 m3) */ l.value AS acclocation
, u0.value AS accdatatype
, u1.value AS accprodstep
, u2.value AS accproject
, u3.value AS accrunnumber
, u4.value AS accstreamname
, u5.value AS accversion
, COUNT(DISTINCT l.duid) AS duids, SUM(m0.value) AS nbfiles, SUM(m1.value) AS rnbfiles, SUM(m2.value) AS length, SUM(m3.value) AS rlength, TO_CHAR (systimestamp, 'YYYY-MM-DD HH24:MI:SS') AS tstamp
 FROM t_kvs_partlist l
, t_kvs_partlist u0
, t_kvs_partlist u1
, t_kvs_partlist u2
, t_kvs_partlist u3
, t_kvs_partlist u4
, t_kvs_partlist u5
, t_kvs_partlist m0, t_kvs_partlist m1, t_kvs_partlist m2, t_kvs_partlist m3
 WHERE l.KEY = 'location'
 AND u0.KEY = 'datatype' AND u0.duid = l.duid
 AND u1.KEY = 'prodstep' AND u1.duid = l.duid
 AND u2.KEY = 'project' AND u2.duid = l.duid
 AND u3.KEY = 'runnumber' AND u3.duid = l.duid
 AND u4.KEY = 'streamname' AND u4.duid = l.duid
 AND u5.KEY = 'version' AND u5.duid = l.duid
 AND m0.KEY = 'nbfiles' AND m0.duid = l.duid AND m1.KEY = 'rnbfiles' AND m1.pid = 'location:'||l.value AND m1.duid = l.duid AND m2.KEY = 'length' AND m2.duid = l.duid AND m3.KEY = 'rlength' AND m1.pid = 'location:'||l.value AND m3.duid = l.duid
 GROUP BY l.value
, u0.value
, u1.value
, u2.value
, u3.value
, u4.value
, u5.value
 ORDER BY l.value
, u0.value
, u1.value
, u2.value
, u3.value
, u4.value
, u5.value