Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01467: sort key too long (Oracle 10g)
icon5.gif  ORA-01467: sort key too long [message #343462] Wed, 27 August 2008 06:24 Go to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
I aggregate about 560 columns in a query. when i run the query i got error message
ORA-12801: error signaled in parallel query server P018
ORA-01467: sort key too long

Here is my query. Any idea?
Thanks in advance...

SELECT SUM(PFCADMSUC_THP1ARP1)PFCADMSUC_THP1ARP1,SUM(PFCADMSUC_THP1ARP2)PFCADMSUC_THP1ARP2,
SUM(PFCADMSUC_THP1ARP3)PFCADMSUC_THP1ARP3,SUM(PFCADMSUC_THP2ARP3)PFCADMSUC_THP2ARP3,SUM(PFCADMSUC_THP3ARP1)PFCADMSUC_THP3ARP1,SUM(PFC ADMSUC_THP3ARP2)PFCADMSUC_THP3ARP2,
SUM(PFCADMSUC_BGRNARP1)PFCADMSUC_BGRNARP1,SUM(PFCADMSUC_BGRNARP2)PFCADMSUC_BGRNARP2,SUM(PFCADMSUC_BGRNARP3)PFCADMSUC_BGRNARP3,SUM(PFC ADMSUC_EFFARP2)PFCADMSUC_EFFARP2,
.
.
.
.
PFCADMSUC_THP2ARP2,SUM(PFCADMSUC_EFFARP1)PFCADMSUC_EFFARP1,
SUM(PFCADMREJ_THP1ARP1)PFCADMREJ_THP1ARP1
FROM CELL_STATISTICS_2, NORTHI.OMC_BSC_BTS_CELL_LIST WHERE FRAGMENT_DATE =:XDATA_DATE
AND NETWORK_ID= CELL_ID AND TO_NUMBER(REVERSE(SUBSTR(REVERSE(TO_CHAR(NETWORK_ID)),10)))=:XOMCFOLDERID
GROUP BY BSC_ID,FRAGMENT_DATE


Re: ORA-01467: sort key too long [message #343473 is a reply to message #343462] Wed, 27 August 2008 06:48 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member


http://www.orafaq.com/forum/t/88153/0/
Would you please read & FOLLOW the Posting Guidelines as stated in URL above

It will not only help you but also others in this forum to understand your code

Regards,
Oli


Regards,
Oli
Re: ORA-01467: sort key too long [message #343479 is a reply to message #343462] Wed, 27 August 2008 06:54 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
This issue is discussed here

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:508222056084

Regards,
Dwarak
Re: ORA-01467: sort key too long [message #343485 is a reply to message #343462] Wed, 27 August 2008 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: ORA-01467: sort key too long [message #343533 is a reply to message #343485] Wed, 27 August 2008 08:31 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
Here is the formatted code.
Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi


Regards

/* Formatted on 2008/08/27 16:27 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE northi_parser.p_cell_statistics_2_h_bsc (
   xdata_date     IN   DATE,
   xomcfolderid   IN   NUMBER
)
AS
BEGIN
   INSERT INTO northi_raw.cell_statistics_2_h_bsc
               (pfcadmsuc_thp1arp1, pfcadmsuc_thp1arp2, pfcadmsuc_thp1arp3,
                pfcadmsuc_thp2arp3, pfcadmsuc_thp3arp1, pfcadmsuc_thp3arp2,
                pfcadmsuc_bgrnarp1, pfcadmsuc_bgrnarp2, pfcadmsuc_bgrnarp3,
                pfcadmsuc_effarp2, pfcadmsuc_effarp3, pfc_admsuccessoth,
                pfcadmsuc_sms, pfcadmsuc_signal, pfc_admreject,
                pfcadmrej_thp1arp2, pfcadmrej_thp1arp3, pfcadmrej_thp2arp1,
                pfcadmrej_thp2arp2, pfcadmrej_thp3arp1, pfcadmrej_thp3arp2,
                pfcadmrej_bgrnarp1, pfcadmrej_bgrnarp2, pfcadmrej_bgrnarp3,
                pfcadmrej_effarp3, pfc_admrejectoth, pfc_ds_thp3_1_arp3,
                pfc_ds_thp3_2_arp2, pfc_ds_thp3_2_arp3, pfc_downgrade_fail,
                pfc_df_thp2_1_arp1, pfc_df_thp2_1_arp2, pfc_df_thp2_1_arp3,
                pfc_df_thp3_1_arp1, pfc_df_thp3_1_arp3, pfc_df_thp3_2_arp1,
                .
                .
                .
                .
                .
                pfc_df_thp3_2_arp2, pfc_df_thp3_2_arp3, pfc_preemptions,
                gprs_cellcong_70, gprs_cellcong_75, gprs_cellcong_85,
                gprs_cellcong_90, gprs_cellcong_95, gprs_cellcong_100,
                pdu_discard_llc, pdu_dscrdllc_cbuf, pdu_dscrdllc_mvpkt,
                pdu_dscrdllc_seq, pdu_dscrdllc_tbf, pdu_dscrdllc_rej,
                dl_llc_frames_gb, dl_llcframegb_40, dl_llcframegb_80,
                dl_llcframegb_160, dl_llcframegb_320, dl_llcframegb_640,
                dl_llcframegb_1280, dl_llcframegb_over, pfc_out_tbf_thp1,
                dlllcvol_thp3_arp2, ulllcvol_eff_arp1, ulrlcnanewbks_mcs1,
                ulrlc_retxbks_mcs9, ho_ack_pm_gsm_hr, pdu_dscrdllc_rslot,
                pfc_ds_thp3_2_arp1, pfc_df_thp3_1_arp2, num_multrat_msorig,
                resel_pcco_micro, q_prmpatt_ho, tch_preempt_rqd,
                tch_prmpatt_cs_vg, vgcs_vbs_assgnfail, sdch_o_inter_b_h_s,
                pkt_sysresp_valid, creat_t3168_expiry, pfcadmsuc_thp2arp2,
                pfcadmsuc_effarp1, pfcadmrej_thp1arp1)
      SELECT   SUM (pfcadmsuc_thp1arp1) pfcadmsuc_thp1arp1,
               SUM (pfcadmsuc_thp1arp2) pfcadmsuc_thp1arp2,
               SUM (pfcadmsuc_thp1arp3) pfcadmsuc_thp1arp3,
               SUM (pfcadmsuc_thp2arp3) pfcadmsuc_thp2arp3,
               SUM (pfcadmsuc_thp3arp1) pfcadmsuc_thp3arp1,
               SUM (pfcadmsuc_thp3arp2) pfcadmsuc_thp3arp2,
               SUM (pfcadmsuc_bgrnarp1) pfcadmsuc_bgrnarp1,
               .
               .
               .
               .
               
               SUM (pfc_df_thp3_1_arp2) pfc_df_thp3_1_arp2,
               SUM (num_multrat_msorig) num_multrat_msorig,
               SUM (resel_pcco_micro) resel_pcco_micro,
               SUM (q_prmpatt_ho) q_prmpatt_ho,
               SUM (tch_preempt_rqd) tch_preempt_rqd,
               SUM (tch_prmpatt_cs_vg) tch_prmpatt_cs_vg,
               SUM (vgcs_vbs_assgnfail) vgcs_vbs_assgnfail,
               SUM (sdch_o_inter_b_h_s) sdch_o_inter_b_h_s,
               SUM (pkt_sysresp_valid) pkt_sysresp_valid,
               SUM (creat_t3168_expiry) creat_t3168_expiry,
               SUM (pfcadmsuc_thp2arp2) pfcadmsuc_thp2arp2,
               SUM (pfcadmsuc_effarp1) pfcadmsuc_effarp1,
               SUM (pfcadmrej_thp1arp1) pfcadmrej_thp1arp1
          FROM cell_statistics_2, northi.omc_bsc_bts_cell_list
         WHERE fragment_date = xdata_date
           AND network_id = cell_id
           AND TO_NUMBER (REVERSE (SUBSTR (REVERSE (TO_CHAR (network_id)), 10))
                         ) = xomcfolderid
      GROUP BY bsc_id, fragment_date;
END;
/


(Edit MC: Add code tags, do it yourself next time]

[Updated on: Wed, 27 August 2008 08:48] by Moderator

Report message to a moderator

Re: ORA-01467: sort key too long [message #343806 is a reply to message #343462] Thu, 28 August 2008 04:06 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Did you look at the link Dwarak posted?

Re: ORA-01467: sort key too long [message #343958 is a reply to message #343806] Thu, 28 August 2008 09:03 Go to previous message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
I changed the db_block_size but still problem exists.Any idea?
ORA-01467: sort key too long
Previous Topic: sql output (merged)
Next Topic: Simultaneous Access of Database
Goto Forum:
  


Current Time: Sat Dec 10 18:22:25 CST 2016

Total time taken to generate the page: 0.23227 seconds