Update: "ORA-01467 : sort key too long" refreshing MV

Synopsis:
The refresh of materialized views (several) fails if not done interactively. Consecutive analysis (statistics gathering) fails with the dbms_stats package, but not with the analyze command.

This is an update to the (still unsolved) problem:

ORA-01467 : sort key too long

"Cause: A DISTINCT, GROUP BY, ORDER BY, or SET operation requires a sort key longer than that supported by ORACLE. Either too many columns or too many group functions were specified in the SELECT statement.

Action: Reduce the number of columns or group functions involved in the operation."

The error appears while trying to analyze the table (mat. view). But the actual problem is creating the mat. view being analyzed.

We are struggling several weeks with this problem now. I was adviced in this forum to trace the operation (exec dbms_refresh.refresh), but the trace file does not reveal any problem (?).

The refresh of the mat. view prior to gathering statistics fails, but only when not done manually!!! (i.e. from a cron job or using the "at" command). And: It turns out that if using the "analyze table ... compute statistics" command after a manual (successfull) invocation of the dbms_refresh.refresh procedure, everything goes well (while dbms_stats fails). Very strange.

So during the last weeks, I have being calling the refresh procedure manually. Every day. Users are angry, because it takes several hours to complete (it is normally scheduled to run at night) and during this time the mat. view appears as an empty table (and when called from cron, the mat. view stays empty at the end even when it contained data).

My question now is, perhaps the SNP background process has crashed. But is it needed at all when using a cron job? This is not an Oracle job from the user_jobs table.

The mat. view is a of type "complex", the refresh method is "force" (=complete), and its definition is based mostly on tables from a remote DB using a DB-link (well, the "remote" instance runs on the same server). And it used to run flawlessly for longer than one year.

job_queue_processes=1, job_queue_invertals=60 Oracle 8.1.7.0 on Sun/Solaris.

Thanks in advance
Rick Denoire Received on Fri Nov 07 2003 - 10:27:19 CST