ORA-01467 Error

[gunjan14]

We are running a particular query in Test environment (with no data/very small amount of data)and its giving an error but the same query being run on the Client side is not giving any error which contain good amount of data.

Error : ORA-01467: sort key too long

SELECT int_id,
bts_id,
period_start_time,
sum (gsm_to_wcdma_ran_ho_success) gsm_to_wcdma_ran_ho_success,
sum (gsm_wcdma_ran_ho_fail_source) gsm_wcdma_ran_ho_fail_source,
sum ( sdcch_assign ) sdcch_assign,
sum ( sdcch_ho_call_assign ) sdcch_ho_call_assign,
sum ( sdcch_ho_release ) sdcch_ho_release,
sum ( sdcch_new_call_assign ) sdcch_new_call_assign,
sum ( sdcch_re_est_assign ) sdcch_re_est_assign,
sum ( sdcch_re_est_release ) sdcch_re_est_release,
sum ( sdcch_req ) sdcch_req,
sum ( sdcch_sms_assign ) sdcch_sms_assign,
sum ( served_dr_req ) served_dr_req,
sum ( served_facch_req ) served_facch_req,
sum ( served_sdcch_req ) served_sdcch_req,
sum ( served_tch_call_req ) served_tch_call_req,
avg ( service_period_duration ) service_period_duration,
sum (spare057043) spare057043,
sum (spare057044) spare057044,
sum (spare057045) spare057045,
sum (spare057046) spare057046,
sum ( t3101_expired ) t3101_expired,
sum ( tch_call_req ) tch_call_req,
sum ( tch_dr_req ) tch_dr_req,
sum ( tch_ho_assign ) tch_ho_assign,
sum ( tch_ho_release ) tch_ho_release,
sum ( tch_new_call_assign ) tch_new_call_assign,
sum ( tch_norm_release ) tch_norm_release,
sum ( tch_re_est_assign ) tch_re_est_assign,
sum ( tch_re_est_release ) tch_re_est_release,
sum ( tch_t3101_expired ) tch_t3101_expired,
sum (wcdma_ran_gsm_ho_fail_target) wcdma_ran_gsm_ho_fail_target,
sum (wcdma_ran_to_gsm_ho_success) wcdma_ran_to_gsm_ho_success
FROM pt_hdbbss_ebts_service_day
WHERE
(
( period_start_time >= TO_DATE('20030901', 'YYYYMMDD')
AND period_start_time < TO_DATE('20030901', 'YYYYMMDD') + 1
)
)
GROUP BY int_id,
bts_id,
period_start_time

The datatype of columns are numbers except for period_start_time which is date.

We are working on Oracle 8.0.6
and the OS is HP-UX B11
The DB-BLOCK_SIZE is 8KB

The problem is that it is given only in test environment with very small amount of data but not at customer site with huge amount of data.
Kindly tell me what to do?

 

[sem]

Is pt_hdbbss_ebts_service_day a table or a view? I can't find any reason for this error if it's a table. Can you also provide execution plan when it fails?

Regards, Dima

 

[gunjan14]

pt_hdbbss_ebts_service_day is a table. Its a temporary table created during run time. The problem is that when we drop some columns it start to work.

 

[sem]

What is your Oracle version (full number)?

Regards, Dima

 

[gunjan14]

Its 8.0.6.0.0
Is it that all the aggregate function has to fit in one DB Block? We have defined the columns as number. So I checked in user_tab_columns and the data_length there is shown as 22 for the columns.

 

[sem]

Unfortunately I have no suggestions besides patching your 0.0 version.

Regards, Dima

 

[gunjan14]

Can it be because of some of the columns have NULL for all the rows. Does it affects?

 

[abhijit74]

Gunjan,
Null doesn't affect the SUM function.

Seems to me that you have missed something else.

Cheers!
Abhijit