Why does limiting a working query by WHERE ROWNUM <=10 result in an error from the EXPLAIN PLAN "ORA-12899: value too large for column"?

Question

I am executing this query and explain plan inside Responsys.

The explain plan for this query works fine and returns about 70 rows of count data:

select * from ( Select LOCATION, count(LOCATION) COUNT_LOCATION From TABLE Group By LOCATION Order By COUNT_LOCATION Desc )

But add the row limiting WHERE ROWNUM <= 10 clause at the end:

select * from ( Select LOCATION, count(LOCATION) COUNT_LOCATION From TABLE Group By LOCATION Order By COUNT_LOCATION Desc ) WHERE ROWNUM <= 10

And this error in the explain plan results:

Error: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 ORA-12899: value too large for column "ACME_CUST"."PLAN_TABLE"."OPTIONS" (actual: 33, maximum: 30) : explain plan set statement_id ='ACME_CUST:1550184818627' into acme_CUST.PLAN_TABLE for select * from ( Select LOCATION, count(LOCATION) COUNT_LOCATION From TABLE Group By LOCATION Order By COUNT_LOCATION Desc ) WHERE ROWNUM <= 10

The results I am looking for is just the top 10 row counts for LOCATION.

Answer 1

Try using ROW_NUMBER() over the sort. I think it's because of the GROUP BY/ORDER BY combination in conjunction with ROWNUM:

select * from ( Select LOCATION, count(LOCATION), COUNT_LOCATION, ROW_NUMBER() OVER (ORDER BY COUNT_LOCATION GROUP BY LOCATION Desc) RowNumbers From TABLE Group By LOCATION Order By COUNT_LOCATION Desc) WHERE RowNumbers <= 10

See this article for other forms: How to use Oracle ORDER BY and ROWNUM correctly?