0

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.

Cale Sweeney
  • 1,014
  • 1
  • 15
  • 37
  • 2
    The error suggest that it is not your query causing the error, but an attempt to perform EXPLAIN PLAN on that query. Are you explicitly executing EXPLAIN PLAN? If not, whatever client tool you are using seems to be doing so implicitly, and may be responsible for the error. – Dave Costa Feb 15 '19 at 17:12
  • I'm using the email service platform Responsys which is executing EXPLAIN PLAN on my behalf. I'll add them as a tag to this question. – Cale Sweeney Feb 15 '19 at 17:24

1 Answers1

0

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?

byagihas
  • 16