Grouping Function

[gunjan14]

Hi,
I have some problem. Just new to Oracle.
I create a table

create table temp_1 (
code_cd varchar2(105),
name_cd1 varchar2(4000),
name_cd2 varchar2(4000),
col_col number
);
And then Insert into the table
insert into temp_1 values (rpad('$',105,'$'),rpad('*',4000,'*'),rpad('&',4000,'&'),1234);

Now when I do the query
select max(substr(col_col,1,2))
from temp_1
group by code_cd, name_cd1, name_cd2
Then I do not get any error but when I do this query

select avg(substr(col_col,1,2))
from temp_1
group by code_cd, name_cd1, name_cd2

Then I get the ORA-01467 Error - Sort Key Too Long.

Why is this happening. Isn't both MAX and AVG Grouping function?
Any information will be helpful.

 

[sem]

You're trying to group by very large (8105 byte) expression. There's a limitation (bug?) that fields being sorted should fit 1 block. Try to look for patches or rebuild your database with larger db_block_size or decrease sizes of name_cd's or group by substr(name_cd, 1, <smth smaller>). Though, I think that you should consider redesigning your application to group by some shorter (numeric) identifiers instead of potentially long names or descriptions.

Regards, Dima

 

[gunjan14]

The main problem with us is numbers. Actually in my programme (this was just a sample) all the grouping column are numbers (grouping some 340 columns) using sum or avg. and there are no varchar2 columns there. The db_block_size is 8192. Now when I was adding some more columns then this error was being generated. To remove this I thought of rewriting the query. We can use &quot;substr&quot; on varchar2 columns but when I use the same on numbers and the grouping function is SUM or AVG then this problem persist. But if the grouping function is MAX or MIN (with substr) then this problem does not come. Why is it so? (I added varchar2 columns here so to get to the limit of ~8000 bytes. No use of varchar2 in our application)
I cannot go for patching now or increasing the db_block_size increase. We are thinking of splitting the table and writing 2 different query. But that is going to be other alternative. If we can find some better solution than we will not go for it.

 

[sem]

IMO 340 columns in single table is not much better than using VARCHAR2(4000) fields for sorting Is this a fact table with a lot of dimensions? I suppose you may try to merge some of them to get smaller number.

Regards, Dima

 

[SantaMufasa]

GunJan,

Please help me understand the business benefit of padding your values with thousands of extraneous bytes of '$', '&', and '*'. You probably would not encounter the errors you encounter if your cell values were not so artificially long. Additionally, you are trying to do arithmetic operations and functions on fields that contain non-numeric characters. I must really be misunderstanding your code.

Your block size has no relationship to the maximum sizes of your columns, rows, or tables, per se. And I totally agree with Dima, that having more than a few dozen columns in a table is probably disobeying &quot;First Normal Form&quot; in one or more ways.

Please advise,

Dave

 

[gunjan14]

Actually there is no Varchar2 column in my query. Current situation is that we have to go for some 350 columns in 1 table. The actual query is
SELECT int_id,
TRUNC(period_start_time, 'dd') period_start_time,
sum ( bsc_i_drop_calls ) bsc_i_drop_calls,
sum ( bsc_i_nonopt ) bsc_i_nonopt,
sum ( bsc_i_nonopt_at ) bsc_i_nonopt_at,
sum ( bsc_i_sdcch ) bsc_i_sdcch,
sum ( bsc_i_sdcch_at ) bsc_i_sdcch_at,
sum ( bsc_i_sdcch_tch ) bsc_i_sdcch_tch,
sum ( bsc_i_sdcch_tch_at ) bsc_i_sdcch_tch_at,
sum ( bsc_i_succ_ho ) bsc_i_succ_ho,
sum ( bsc_i_tch_tch ) bsc_i_tch_tch,
sum ( bsc_i_tch_tch_at ) bsc_i_tch_tch_at,
sum ( bsc_i_unsucc_a_int_circ_type) bsc_i_unsucc_a_int_circ_type,
sum ( bsc_o_cand_enq ) bsc_o_cand_enq,
sum ( bsc_o_drop_calls ) bsc_o_drop_calls,
sum ( bsc_o_nonopt ) bsc_o_nonopt,
sum ( bsc_o_nonopt_at ) bsc_o_nonopt_at,
sum ( bsc_o_not_allwd ) bsc_o_not_allwd,
sum ( bsc_o_sdcch ) bsc_o_sdcch,
sum ( bsc_o_sdcch_at ) bsc_o_sdcch_at,
sum ( bsc_o_sdcch_tch ) bsc_o_sdcch_tch,
sum ( bsc_o_sdcch_tch_at ) bsc_o_sdcch_tch_at,
sum ( bsc_o_succ_ho ) bsc_o_succ_ho,
sum ( bsc_o_tch_tch ) bsc_o_tch_tch,
sum ( bsc_o_tch_tch_at ) bsc_o_tch_tch_at,
sum ( bsc_o_unsucc_a_int_circ_type) bsc_o_unsucc_a_int_circ_type,
sum ( cause_bad_ci ) cause_bad_ci,
sum ( cause_ch_adm ) cause_ch_adm,
sum ( cause_dir_retry ) cause_dir_retry,
sum ( cause_distance ) cause_distance,
sum ( cause_down_lev ) cause_down_lev,
sum ( cause_down_qual ) cause_down_qual,
sum ( cause_field_drop ) cause_field_drop,
sum ( cause_good_ci ) cause_good_ci,
sum ( cause_intfer_dwn ) cause_intfer_dwn,
sum ( cause_intfer_up ) cause_intfer_up,
sum ( cause_low_distance ) cause_low_distance,
sum ( cause_msc_invoc ) cause_msc_invoc,
sum ( cause_omc ) cause_omc,
sum ( cause_pbdgt ) cause_pbdgt,
sum ( cause_pre_emption ) cause_pre_emption,
sum ( cause_traffic ) cause_traffic,
sum ( cause_umbr ) cause_umbr,
sum ( cause_up_level ) cause_up_level,
sum ( cause_up_qual ) cause_up_qual,
sum ( cell_call_clr ) cell_call_clr,
sum ( cell_drop_calls ) cell_drop_calls,
sum ( cell_not_allwd ) cell_not_allwd,
sum ( cell_sdcch ) cell_sdcch,
sum ( cell_sdcch_at ) cell_sdcch_at,
sum ( cell_sdcch_tch ) cell_sdcch_tch,
sum ( cell_sdcch_tch_at ) cell_sdcch_tch_at,
sum ( cell_succ_ho ) cell_succ_ho,
sum ( cell_tch_tch ) cell_tch_tch,
sum ( cell_tch_tch_at ) cell_tch_tch_at,
sum ( ho_att_due_switch_circ_pool) ho_att_due_switch_circ_pool,
sum ( ho_att_due_to_hscsd )
sum ( ho_att_for_amr_to_fr )
sum ( ho_att_for_amr_to_hr )
sum ( ho_att_inter_band_sdcch )
sum ( ho_att_inter_band_tch )
sum ( ho_att_inter_bts_type_sdcch )
sum ( ho_att_inter_bts_type_tch )
sum (ho_attempt_interband_due_level) ho_attempt_interband_due_level,
sum ( ho_due_ms_high_speed ) ho_due_ms_high_speed,
sum ( ho_due_ms_slow_speed ) ho_due_ms_slow_speed,
sum ( ho_due_slow_mov_ms ) ho_due_slow_mov_ms,
sum ( ho_ext_to_normal ) ho_ext_to_normal,
sum ( ho_normal_to_ext ) ho_normal_to_ext,
sum ( ho_period_duration ) ho_period_duration,
sum ( ho_unsucc_a_int_circ_type) ho_unsucc_a_int_circ_type,
sum (in_seg_succ_sdcch_ho_btw_bands) in_seg_succ_sdcch_ho_btw_bands,
sum ( intra_cell_sdcch_ho_bands )
sum ( intra_cell_sdcch_ho_bts_types )
sum ( intra_cell_sdcch_ho_btss )
sum ( intra_cell_tch_ho_bands )
sum ( intra_cell_tch_ho_bts_types )
sum ( intra_cell_tch_ho_btss )
sum (msc_call_drop_ho_wcdma_ran) msc_call_drop_ho_wcdma_ran,
sum ( msc_controlled_in_ho ) msc_controlled_in_ho,
sum ( msc_controlled_out_ho) msc_controlled_out_ho,
sum (msc_end_of_ho_to_wcdma_ran) msc_end_of_ho_to_wcdma_ran,
sum (msc_end_of_ho_wcdmaran_due_bss) msc_end_of_ho_wcdmaran_due_bss,
sum (msc_gen_sys_wcdma_ran_ho_com) msc_gen_sys_wcdma_ran_ho_com,
sum (msc_gen_sys_wcdma_ran_ho_req) msc_gen_sys_wcdma_ran_ho_req,
sum (msc_ho_to_wcdma_ran_not_allow) msc_ho_to_wcdma_ran_not_allow,
sum (msc_ho_wcdma_ran_succ) msc_ho_wcdma_ran_succ,
sum ( msc_i_att_switch_circ_pool) msc_i_att_switch_circ_pool,
sum ( msc_i_sdcch ) msc_i_sdcch,
sum ( msc_i_sdcch_at ) msc_i_sdcch_at,
sum ( msc_i_sdcch_tch ) msc_i_sdcch_tch,
sum ( msc_i_sdcch_tch_at ) msc_i_sdcch_tch_at,
sum ( msc_i_succ_ho ) msc_i_succ_ho,
sum ( msc_i_succ_switch_circ_pool) msc_i_succ_switch_circ_pool,
sum ( msc_i_tch_tch ) msc_i_tch_tch,
sum ( msc_i_tch_tch_at ) msc_i_tch_tch_at,
sum ( msc_o_adj_cell_id_err_c) msc_o_adj_cell_id_err_c,
sum ( msc_o_att_switch_circ_pool) msc_o_att_switch_circ_pool,
sum ( msc_o_ho_comm ) msc_o_ho_comm,
sum ( msc_o_ho_rq_msg ) msc_o_ho_rq_msg,
sum ( msc_o_not_allwd ) msc_o_not_allwd,
sum ( msc_o_sdcch ) msc_o_sdcch,
sum ( msc_o_sdcch_at ) msc_o_sdcch_at,
sum ( msc_o_sdcch_tch ) msc_o_sdcch_tch,
sum ( msc_o_sdcch_tch_at ) msc_o_sdcch_tch_at,
sum ( msc_o_succ_ho ) msc_o_succ_ho,
sum ( msc_o_succ_switch_circ_pool) msc_o_succ_switch_circ_pool,
sum ( msc_o_tch_tch ) msc_o_tch_tch,
sum ( msc_o_tch_tch_at ) msc_o_tch_tch_at,
sum (msc_tch_ho_from_wcdma_ran_att) msc_tch_ho_from_wcdma_ran_att,
sum (msc_tch_ho_wcdma_ran_att) msc_tch_ho_wcdma_ran_att,
sum (msc_to_wcdma_ran_fail_lack) msc_to_wcdma_ran_fail_lack,
sum (msc_to_wcdma_ran_succ_tch_ho) msc_to_wcdma_ran_succ_tch_ho,
sum (msc_unsuc_ho_wcdmaran_due_conn) msc_unsuc_ho_wcdmaran_due_conn,
sum (msc_wcdma_ran_end_of_ho) msc_wcdma_ran_end_of_ho,
sum (msc_wcdma_ran_ho_att_unsuc_bss) msc_wcdma_ran_ho_att_unsuc_bss,
sum (msc_wcdmaran_ho_att_unsuc_lack) msc_wcdmaran_ho_att_unsuc_lack,
sum (seg_succ_sdcch_ho_btw_bts_type) seg_succ_sdcch_ho_btw_bts_type,
sum (seg_succ_tch_ho_btw_bts_type) seg_succ_tch_ho_btw_bts_type,
sum (seg_success_tch_ho_btw_band) seg_success_tch_ho_btw_band,
sum (succ_ho_inter_band_due_level) succ_ho_inter_band_due_level,
sum (succesful_ho_inter_btstype_tch) succesful_ho_inter_btstype_tch,
sum (successful_ho_inter_band_tch) successful_ho_inter_band_tch,
sum (unsucc_ho_inter_band_due_level) unsucc_ho_inter_band_due_level,
sum (unsuccessful_ho_inter_band_tch) unsuccessful_ho_inter_band_tch,
sum (unsucesful_ho_in_bts_type_tch) unsucesful_ho_in_bts_type_tch,
sum ( ave_bs_power ) ave_bs_power,
sum ( ave_dl_sig_qual ) ave_dl_sig_qual,
sum ( ave_dl_sig_str ) ave_dl_sig_str,
avg ( ave_ms_bs_dist ) ave_ms_bs_dist,
sum ( ave_ms_power ) ave_ms_power,
sum ( ave_ul_sig_qual ) ave_ul_sig_qual,
sum ( ave_ul_sig_str ) ave_ul_sig_str,
sum(power_denom1) power_denom1,
sum(power_denom2) power_denom2,
sum(power_denom3) power_denom3,
sum(power_denom4) power_denom4,
sum(power_denom5) power_denom5,
sum(power_denom6) power_denom6,
sum ( power_period_duration ) power_period_duration,
sum ( ave_drx_agch_load_air_den ) ave_drx_agch_load_air_den,
sum ( ave_drx_agch_load_air_sum ) ave_drx_agch_load_air_sum,
sum ( ave_non_drx_agch_load_air_den ) ave_non_drx_agch_load_air_den,
sum ( ave_non_drx_agch_load_air_sum ) ave_non_drx_agch_load_air_sum,
sum ( ave_paging_gb_buf_den ) ave_paging_gb_buf_den,
sum ( ave_paging_gb_buf_sum ) ave_paging_gb_buf_sum,
sum ( ave_paging_load_air_den ) ave_paging_load_air_den,
sum ( ave_paging_load_air_sum ) ave_paging_load_air_sum,
sum ( ave_pch_gb_load_on_ccch_den ) ave_pch_gb_load_on_ccch_den,
sum ( ave_pch_gb_load_on_ccch_sum ) ave_pch_gb_load_on_ccch_sum,
avg ( ave_rach_access ) ave_rach_access,
avg ( ave_rach_busy ) ave_rach_busy,
sum (call_assign_after_sms) call_assign_after_sms,
sum ( ch_req_msg_rec ) ch_req_msg_rec,
sum ( cs_paging_msg_sent ) cs_paging_msg_sent,
sum ( ghost_ccch_res ) ghost_ccch_res,
sum ( imm_assgn_rej ) imm_assgn_rej,
sum ( imm_assgn_sent ) imm_assgn_sent,
max ( max_paging_gb_buf ) max_paging_gb_buf,
sum ( paging_msg_sent ) paging_msg_sent,
sum ( ps_paging_msg_sent ) ps_paging_msg_sent,
sum ( rej_seiz_att_due_dist) rej_seiz_att_due_dist,
sum ( res_acc_period_duration ) res_acc_period_duration,
sum ( sdcch_call_re_est ) sdcch_call_re_est,
sum ( sdcch_emerg_call ) sdcch_emerg_call,
sum ( sdcch_loc_upd ) sdcch_loc_upd,
sum ( sms_bc_req_sent ) sms_bc_req_sent,
sum ( succ_sdcch_sms_est ) succ_sdcch_sms_est,
sum ( succ_seiz_orig ) succ_seiz_orig,
sum ( succ_seiz_supplem_serv ) succ_seiz_supplem_serv,
sum ( succ_seiz_term ) succ_seiz_term,
sum ( succ_tch_sms_est ) succ_tch_sms_est,
sum ( tch_call_re_est ) tch_call_re_est,
sum ( tch_emerg_call ) tch_emerg_call,
sum ( tch_moc ) tch_moc,
sum ( tch_mtc ) tch_mtc,
sum ( tch_supplem_serv ) tch_supplem_serv,
sum ( unsucc_sdcch_sms_est ) unsucc_sdcch_sms_est,
sum ( unsucc_tch_sms_est ) unsucc_tch_sms_est,
sum ( ave_add_gprs_ch_hold_time_den ) ave_add_gprs_ch_hold_time_den,
sum ( ave_add_gprs_ch_hold_time_sum ) ave_add_gprs_ch_hold_time_sum,
avg ( ave_avail_full_tch ) ave_avail_full_tch,
sum ( ave_avail_tch_den ) ave_avail_tch_den,
sum ( ave_avail_tch_sum ) ave_avail_tch_sum,
avg ( ave_busy_sdcch ) ave_busy_sdcch,
sum(ave_busy_tch*res_avail_period_duration) /sum(res_avail_period_duration) ave_busy_tch,
avg ( ave_busy_tch_hscsd ) ave_busy_tch_hscsd,
avg ( ave_ftch_hold_tim ) ave_ftch_hold_tim,
sum ( ave_gprs_channels_den ) ave_gprs_channels_den,
sum ( ave_gprs_channels_sum ) ave_gprs_channels_sum,
avg ( ave_non_avail_sdcch ) ave_non_avail_sdcch,
avg ( ave_non_avail_tch ) ave_non_avail_tch,
sum ( ave_permanent_gprs_ch_den ) ave_permanent_gprs_ch_den,
sum ( ave_permanent_gprs_ch_sum ) ave_permanent_gprs_ch_sum,
avg ( ave_sdcch_hold_tim ) ave_sdcch_hold_tim,
avg ( ave_sdcch_sub ) ave_sdcch_sub,
avg ( ave_tch_avail_half) ave_tch_avail_half,
avg ( ave_tch_busy_full) ave_tch_busy_full,
avg ( ave_tch_busy_half) ave_tch_busy_half,
max ( peak_gprs_channels ) peak_gprs_channels,
max( peak_permanent_gprs_ch ) peak_permanent_gprs_ch,
sum ( res_avail_period_duration ) res_avail_period_duration,
sum ( sdcch_cong_time ) sdcch_cong_time,
sum ( tch_cong_time ) tch_cong_time,
sum ( tch_fr_radio_congestion_time) tch_fr_radio_congestion_time,
sum ( tch_hr_radio_congestion_time) tch_hr_radio_congestion_time,
sum ( call_setup_failure ) call_setup_failure,
sum ( call_successfull ) call_successfull,
sum ( conver_started ) conver_started,
sum ( dropped_calls ) dropped_calls,
sum ( ext_ho_source_fail ) ext_ho_source_fail,
sum ( ext_ho_source_succ ) ext_ho_source_succ,
sum ( ext_ho_target_fail ) ext_ho_target_fail,
sum ( ext_ho_target_succ ) ext_ho_target_succ,
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 ( int_ho_target_fail ) int_ho_target_fail,
sum ( int_inter_ho_source_fail ) int_inter_ho_source_fail,
sum ( int_inter_ho_succ ) int_inter_ho_succ,
sum ( int_intra_ho_source_fail ) int_intra_ho_source_fail,
sum ( int_intra_ho_succ ) int_intra_ho_succ,
sum ( int_intra_ho_target_fail ) int_intra_ho_target_fail,
sum ( service_period_duration ) service_period_duration,
sum ( setup_succ ) setup_succ,
sum (spare057043) spare057043,
sum (spare057044) spare057044,
sum (spare057045) spare057045,
sum (spare057046) spare057046,
sum ( tch_fails ) tch_fails,
sum ( tch_seizures ) tch_seizures,
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,
avg ( ave_que_time_non_urg_ho_att) ave_que_time_non_urg_ho_att,
avg ( ave_que_time_urg_ho_att) ave_que_time_urg_ho_att,
sum ( forced_handovers ) forced_handovers,
sum ( forced_releases ) forced_releases,
sum ( full_tch_seiz_intra_amr_ho )
sum ( gprs_ter_dg_due_inc_in_csw_tr ) gprs_ter_dg_due_inc_in_csw_tr,
sum ( gprs_ter_downgrade_req ) gprs_ter_downgrade_req,
sum ( gprs_ter_ug_due_dec_csw_tr ) gprs_ter_ug_due_dec_csw_tr,
sum ( gprs_ter_ug_rej_due_csw_tr ) gprs_ter_ug_rej_due_csw_tr,
sum ( gprs_ter_ug_rej_due_lack_pcu ) gprs_ter_ug_rej_due_lack_pcu,
sum ( gprs_ter_ug_rej_due_lack_psw ) gprs_ter_ug_rej_due_lack_psw,
sum ( gprs_ter_upgrd_req ) gprs_ter_upgrd_req,
sum ( half_tch_seiz_intra_amr_ho )
sum ( incompl_serv_gprs_ter_upgr_req ) incompl_serv_gprs_ter_upgr_req,
sum ( que_urg_ho_att) que_urg_ho_att,
sum ( que_urg_ho_att_not_served) que_urg_ho_att_not_served,
sum ( sdcch_a_if_fail_call ) sdcch_a_if_fail_call,
sum ( sdcch_a_if_fail_new ) sdcch_a_if_fail_new,
sum ( sdcch_a_if_fail_old ) sdcch_a_if_fail_old,
sum ( sdcch_abis_fail_call ) sdcch_abis_fail_call,
sum ( sdcch_abis_fail_new ) sdcch_abis_fail_new,
sum ( sdcch_abis_fail_old ) sdcch_abis_fail_old,
sum ( sdcch_act_fail_call ) sdcch_act_fail_call,
sum ( sdcch_act_fail_new ) sdcch_act_fail_new,
sum ( sdcch_assign ) sdcch_assign,
sum ( sdcch_bcsu_reset ) sdcch_bcsu_reset,
sum ( sdcch_bts_fail ) sdcch_bts_fail,
sum ( sdcch_busy_att ) sdcch_busy_att,
sum ( sdcch_fast_seiz ) sdcch_fast_seiz,
sum ( sdcch_ho_seiz ) sdcch_ho_seiz,
sum ( sdcch_lapd_fail ) sdcch_lapd_fail,
sum ( sdcch_moc_seiz_att) sdcch_moc_seiz_att,
sum ( sdcch_mtc_seiz_att) sdcch_mtc_seiz_att,
sum ( sdcch_netw_act ) sdcch_netw_act,
sum ( sdcch_radio_fail ) sdcch_radio_fail,
sum ( sdcch_rf_new_ho ) sdcch_rf_new_ho,
sum ( sdcch_rf_old_ho ) sdcch_rf_old_ho,
sum ( sdcch_seiz_att ) sdcch_seiz_att,
sum ( sdcch_user_act ) sdcch_user_act,
sum ( succ_amr_codec_set_downgr )
sum ( succ_amr_codec_set_upgr )
sum ( succ_tch_seiz_for_96_dat_call )
sum ( tch_a_if_fail_call ) tch_a_if_fail_call,
sum ( tch_a_if_fail_new ) tch_a_if_fail_new,
sum ( tch_a_if_fail_old ) tch_a_if_fail_old,
sum ( tch_abis_fail_call ) tch_abis_fail_call,
sum ( tch_abis_fail_new ) tch_abis_fail_new,
sum ( tch_abis_fail_old ) tch_abis_fail_old,
sum ( tch_act_fail_call ) tch_act_fail_call,
sum ( tch_act_fail_new ) tch_act_fail_new,
sum ( tch_bcsu_reset ) tch_bcsu_reset,
sum ( tch_bts_fail ) tch_bts_fail,
sum ( tch_call_req ) tch_call_req,
sum ( tch_call_req_for_amr )
sum ( tch_fast_call_ch_rate_not_det) tch_fast_call_ch_rate_not_det,
sum ( tch_fast_req ) tch_fast_req,
sum ( tch_fast_seiz ) tch_fast_seiz,
sum ( tch_full_rej_due_hr_traf) tch_full_rej_due_hr_traf,
sum ( tch_full_req_fast_call) tch_full_req_fast_call,
sum ( tch_full_req_preferred) tch_full_req_preferred,
sum ( tch_full_req_succ_unsucc) tch_full_req_succ_unsucc,
sum ( tch_full_seiz_int_ho_ch_rate) tch_full_seiz_int_ho_ch_rate,
sum ( tch_full_seiz_norm_ass) tch_full_seiz_norm_ass,
sum ( tch_full_succ_seiz) tch_full_succ_seiz,
sum ( tch_full_tr_fail) tch_full_tr_fail,
sum ( tch_half_rej_due_fr_traf) tch_half_rej_due_fr_traf,
sum ( tch_half_req_preferred) tch_half_req_preferred,
sum ( tch_half_req_succ_unsucc) tch_half_req_succ_unsucc,
sum ( tch_half_seiz_int_ho_ch_rate) tch_half_seiz_int_ho_ch_rate,
sum ( tch_half_seiz_norm_ass) tch_half_seiz_norm_ass,
sum ( tch_half_succ_seiz ) tch_half_succ_seiz,
sum ( tch_half_tr_fail ) tch_half_tr_fail,
sum ( tch_ho_seiz ) tch_ho_seiz,
sum ( tch_lapd_fail ) tch_lapd_fail,
sum ( tch_moc_seiz_att) tch_moc_seiz_att,
sum ( tch_mtc_seiz_att) tch_mtc_seiz_att,
sum ( tch_netw_act ) tch_netw_act,
sum ( tch_no_pri_subscr_refused_req ) tch_no_pri_subscr_refused_req,
sum ( tch_norm_seiz ) tch_norm_seiz,
sum ( tch_pri_subscr_req) tch_pri_subscr_req,
sum ( tch_pri_subscr_succ_seiz) tch_pri_subscr_succ_seiz,
sum ( tch_radio_fail ) tch_radio_fail,
sum ( tch_rej_due_req_ch_a_if_crc) tch_rej_due_req_ch_a_if_crc,
sum ( tch_rej_req_due_lack_fr) tch_rej_req_due_lack_fr,
sum ( tch_rej_req_due_lack_hr) tch_rej_req_due_lack_hr,
sum ( tch_rej_und_over ) tch_rej_und_over,
sum ( tch_rel_due_bss_fail) tch_rel_due_bss_fail,
sum ( tch_rel_due_radio_fail) tch_rel_due_radio_fail,
sum ( tch_req_for_96_dat_call )
sum ( tch_req_rej_lack ) tch_req_rej_lack,
sum ( tch_request ) tch_request,
sum ( tch_request_und_over ) tch_request_und_over,
sum ( tch_rf_new_ho ) tch_rf_new_ho,
sum ( tch_rf_old_ho ) tch_rf_old_ho,
sum ( tch_seiz_att_due_sdcch_con ) tch_seiz_att_due_sdcch_con,
sum ( tch_seiz_due_sdcch_con) tch_seiz_due_sdcch_con,
sum ( tch_seiz_fails_due_cong) tch_seiz_fails_due_cong,
sum ( tch_seiz_und_over ) tch_seiz_und_over,
sum ( tch_succ_full_seiz_fast_call ) tch_succ_full_seiz_fast_call,
sum ( tch_succ_half_seiz_fast_call) tch_succ_half_seiz_fast_call,
sum ( tch_succ_seiz_for_dir_acc ) tch_succ_seiz_for_dir_acc,
sum ( tch_tr_fail ) tch_tr_fail,
sum ( tch_tr_fail_new ) tch_tr_fail_new,
sum ( tch_tr_fail_old ) tch_tr_fail_old,
sum ( tch_trunk_refused_fr_req ) tch_trunk_refused_fr_req,
sum ( tch_trunk_refused_hr_req) tch_trunk_refused_hr_req,
sum ( tch_user_act ) tch_user_act,
sum ( traffic_period_duration ) traffic_period_duration,
sum ( trho_enq ) trho_enq,
sum ( trho_req ) trho_req,
sum ( trunk_res_invoc) trunk_res_invoc,
sum ( trunk_res_invoc_refused) trunk_res_invoc_refused,
sum ( trunk_res_invoc_succ) trunk_res_invoc_succ,
sum ( unsucc_amr_codec_set_downgr )
sum ( unsucc_amr_codec_set_upgr )
FROM pv_hdbbss_bsc_traffic_hour
WHERE period_start_time >= TO_DATE('20030907', 'YYYYMMDD')
AND period_start_time < TO_DATE('20030907', 'YYYYMMDD') + 1
GROUP BY int_id

Some columns were added recently and due to it the ORA-01467 error was thrown. I was thinking of re-writing the query using substr but it was working fine if there was MAX and MIN but with SUM and AVG it was not. Now its real messy.
Gunjan

 

[sem]

I suppose that 2 queries provided by you are completely different: the 1st really performs some complex grouping/sorting while the 2nd obviously doesn't (or at leas should not). I supose it's a bug, try to apply the latest patchset. What Oracle version do you use?

Regards, Dima

 

[gunjan14]

I use Oracle 8.0.6.0.0. Actually the first query I used so that I can get to the limit of the block size (just for some wild idea purpose). Actually I was trying to see if the substr has any effect on numeric field. The grouping function (sum) I was having, was on the number column. It was just for testing purpose. Varchar2 has absolutely no use. But the problem is that (2nd query) we cannot split the table (at least not for now, 2nd last option) and the DB_BLOCK_SIZE is 8192 and could not afford to increase the block size (last option). So what to do so that I do not get the ORA-01467.
Is there any way can I re-write this (2nd) query?

 

[sem]

Ok, .0.0 gives birth to hopes Try to patch it.

Though I still can't understand why you can't split your query. Performance will degrade, but at least it has chance to work:

select a.*, b.*
from (
select id,
sum(attr1),
sum(attr2)
...
sum(attr200)
from table
group by id
) a,
(
select id,
sum(attr201),
sum(attr202)
...
sum(attr390)
from table
group by id
) b
where a.id=b.id

???

Regards, Dima

 

[gunjan14]

Thanks Dima,

It worked.

 

[sem]

Which one? Patching or query?

Regards, Dima

 

[gunjan14]

Query.


Gunjan