What slows down this histogram query?
Short Answer

Your 7-way Cartesian JOIN is going to have some serious performance problems.

Long Answer

Think in Sets.

I am assuming that your Data Set needs to contain:  speedcat, bucket_index, count(*)

The simple solution for that Data Set is straight forward:

select t.speedcat
  , WIDTH_BUCKET (t.weight, 0, 0.6, 601) as bucket_index
  , count(*) N
from my_link_t t
group by t.speedcat, WIDTH_BUCKET (t.weight, 0, 0.6, 601)


This is format (x,y,z) is the expect format for most graphing packages.

If you want the results in a "grid" format, then PIVOT the results.

with data as (
  -- same SELECT as previous answer
  select t.speedcat
    , WIDTH_BUCKET (t.weight, 0, 0.6, 601) as bucket_index
    , count(*) N
  from my_link_t t
  group by t.speedcat, WIDTH_BUCKET (t.weight, 0, 0.6, 601)
)
select *
from data
  pivot (
    sum(N)
    for speedcat in ( 2 as "speedcat_2"
                     ,3 as "speedcat_3"
                     ,4 as "speedcat_4"
                     ,5 as "speedcat_5"
                     ,6 as "speedcat_6"
                     ,7 as "speedcat_7"
                     ,8 as "speedcat_8"
                     )
  )