SQL: use count with Clob

Question

I have a table feature_vector_t with two columns doc_id and feature_vector, where feature_vector is a CLOB containing a string.

Since the same doc_id may have multiple feature_vector values, I am trying to get a count using:

select doc_id, count(feature_vector) from feature_vector_t group by doc_id

However, I got an error saying

ORA--00932 inconsistent datatypes:expected-got CLOB 00932. 00000-"inconsistent datatypes: expected %s got %s"

The other query works by converting Clob to string

select doc_id, count(dbms_lob.substr(feature_vector, 1, 5)) from feature_vector_t group by doc_id

Could someone explain what happened behind the scene? Why doesn't count work with the raw clob?

Answer 1

It appears that Oracle has a limitation, not allowing you to pass LOBs to the COUNT() function.

However, you don't want to be counting LOBs anyway for performance reasons. If feature_vector is never NULL, a simple

select doc_id, count(*) from feature_vector_t group by doc_id 

should suffice, otherwise you could use something like this:

select 
 doc_id, 
 count(case when feature_vector is null then null else 1 end) 
from feature_vector_t group by doc_id