3 Replies Latest reply: Mar 8, 2013 8:43 AM by 991835 RSS

    Getting Error : ORA-01467: sort key too long

    991835
      We are getting ORA-01467: sort key too long in one of the table TABLE_HEADER.
      The above view is doing transpose of data for 400 columns and it was working fine till last week. Now getting error as ORA-01467: sort key too long.
      I do not want to alter the query ,please provide a solution on how do I make the query work?

      select
      distinct
      Table_Name,
      Feed_ID,
      first_value(case when Field_Display_Order = 1 then field_name end ignore nulls) over (partition by table_name ) as field_1 ,
      first_value(case when Field_Display_Order = 2 then field_name end ignore nulls) over (partition by table_name ) as field_2 ,
      .
      .
      .
      .
      .
      .
      .
      .
      --Upto
      .
      .
      .
      .
      first_value(case when Field_Display_Order = 399 then field_name end ignore nulls) over (partition by table_name ) as field_399 ,
      first_value(case when Field_Display_Order = 400 then field_name end ignore nulls) over (partition by table_name ) as field_400
      from
      TABLE_HEADER
      where
      table_name like 'STG%';
        • 1. Re: Getting Error : ORA-01467: sort key too long
          L-MachineGun
          The length of the Sort key must fit in one database block, and obviously DISTINCT generates a sort key comprised of all selected columns.
          You need to remove the DISTINCT or code the query some other way.
          :P
          • 2. Re: Getting Error : ORA-01467: sort key too long
            Bill Haverberg
            Can you partition the data on fields such as year, quarter, and other invariant columns? You could strike those fields from the sort key for each partition.

            Otherwise, you could remove the distinct and populate the results to a staging table, and write another set of queries to clean it up.

            A simple fix might be to change the block size on the table. I would try that first.

            Edited by: Bill Haverberg on Mar 5, 2013 1:31 PM
            • 3. Re: Getting Error : ORA-01467: sort key too long
              991835
              Hi ,
              I have used the below query and it did the trick. I broke it down to 3 union clauses.

              (
              SElECT
              columns 1 to 100 in first select, & rest 101 to 400 all nulls
              UNION
              columns 1 to 100 as NULL, 101 to 201 in select, & rest 201 to 400 all NULL again
              UNION
              columns 1 to 200 as NULL, 201 to 300 in select, & rest 301 to 400 all NULL again
              UNION
              columns 1 to 300 as NULL & rest 301 to 400 all nulls
              )
              And included max on top of the select to fetch only 1 value.

              Query below:

              select
              Table_Name,Feed_ID,
              max(field_1) as field_1,
              max(field_2) as field_2,
              .
              .
              .
              .
              .from (
                        select
                        Table_Name,
                        Feed_ID,
                        first_value(case when Field_Display_Order = 1 then field_name end ignore nulls) over (partition by table_name ) as field_1 ,
                        first_value(case when Field_Display_Order = 2 then field_name end ignore nulls) over (partition by table_name ) as field_2 ,
                        .
                        .
                        .
                        .
                        NULL as field_301, upto field_400
                   UNION
                        select
                        Table_Name,
                        Feed_ID,
                        NULL as field_1 ,
                        NULL as field_2 ,
                        NULL as field_3, ..upto field_100
                        first_value(case when Field_Display_Order = 101 then field_name end ignore nulls) over (partition by table_name ) as field_101 ,
                        first_value(case when Field_Display_Order = 102 then field_name end ignore nulls) over (partition by table_name ) as field_102 ,
                        first_value(case when Field_Display_Order = 103 then field_name end ignore nulls) over (partition by table_name ) as field_103 ,
                        .
                        .
                        first_value(case when Field_Display_Order = 300 then field_name end ignore nulls) over (partition by table_name ) as field_300 ,
                        NULL as field_301, ..upto field_400
                   UNION
                        .
                        .
                        .
                   REST OF UNIONS
                        .
                        .
                        .
                        from
                        GW_RPT_TABLE_HEADER
                        where
                        table_name like 'STG%'
                        )
              where table_name is not null
              group by Table_Name,Feed_ID;