5
\$\begingroup\$

I have a few views that all follow the same pattern. They select a primary key from some main table and then a few semicolon seperated strings of captions of related rows over many-to-many relations. All columns that are used in joins are either primary keys or have a manually set index and are of type NUMBER(18). The columns that hold the captions are either VARCHAR2 or - rarely - CLOB. Usually the view will be joined with the main table to select between 1 and 50 rows of the main table.

The creation of the semicolon seperated strings is done with the listagg function from Oracle 11gR2 because my research showed that this is the easiest and also most performant solution. There is just one problem: There could be so many related rows that the listagg function reaches the limit of 4000 chars of its VARCHAR2 result. I'm currently solving this by limiting the set that listagg operates on to 50 rows. This is just a random number that I've chosen, it should prevent errors and the users don't care for more than 50 results anyway. But of course, it's still ugly code.

If anyone has any ideas how to improve the following code, I'd be very happy. I'm especially interested in improvements that lead to less complexity, more performance or a better solution for the 4000 chars limit.

CREATE OR REPLACE VIEW MAILS_RELATED
(
  MAIL_ID,
  CUSTOMERS,
  PROJECTS,
  [some more columns]
)
AS
SELECT m.MAIL_ID,
       cust_list.CUSTOMERS,
       proj_list.PROJECTS,
       [some more columns]
  FROM MAILS m LEFT OUTER JOIN (
         SELECT inner.MAIL_ID,
                LISTAGG(inner.CUSTOMER_NAME, '; ')
                  WITHIN GROUP (ORDER BY inner.CUSTOMER_NAME) AS CUSTOMERS
           FROM (
                SELECT rel.MAIL_ID,
                       cust.CUSTOMER_NAME,
                       ROW_NUMBER() OVER
                         (PARTITION BY rel.MAIL_ID ORDER BY cust.CUST_ID) AS RN
                  FROM CUSTOMERS_MAILS rel INNER JOIN
                       CUSTOMERS cust ON (cust.CUST_ID = rel.CUST_ID)
                ) inner
          WHERE inner.RN <= 50
          GROUP BY inner.MAIL_ID
       ) cust_list ON (cust_list.MAIL_ID = m.MAIL_ID) LEFT OUTER JOIN (
         SELECT inner.MAIL_ID,
                LISTAGG(inner.PROJECT_NAME, '; ')
                  WITHIN GROUP (ORDER BY inner.PROJECT_NAME) AS PROJECTS
           FROM (
                SELECT rel.MAIL_ID,
                       proj.PROJECT_NAME,
                       ROW_NUMBER() OVER
                         (PARTITION BY rel.MAIL_ID ORDER BY proj.PROJ_ID) AS RN
                  FROM PROJECTS_MAILS rel INNER JOIN
                       PROJECTS proj ON (proj.PROJ_ID = rel.PROJ_ID)
                ) inner
          WHERE inner.RN <= 50
          GROUP BY inner.MAIL_ID
       ) proj_list ON (proj_list.MAIL_ID = m.MAIL_ID) LEFT OUTER JOIN
       [some more joins]
\$\endgroup\$
1
+50
\$\begingroup\$

Oracle regular Character types are limited to 4000 bytes. Really, Oracle should find a way to extend that, but until they do, using CLOB is the only real option.

On the other hand, Oracle has a rich infrastructure available for creating user defined functions. You are already creating the view, you may as well extend that to include the function.

There are a number of articles out there that can help:

The one that is missing from those is the use of XML functions.... It is a relatively commonly used 'hack', to use the XML-processing functions in Oracle (and other databases) to reformat the data in a more convenient way, and then to extract from that XML just the parts you want.

So, ideally you should create a user-defined function for your problem, but, you can accomplish the same thing, perhaps faster, with a hack of XML....

Consider swapping the nested select:

     SELECT inner.MAIL_ID,
            LISTAGG(inner.CUSTOMER_NAME, '; ')
              WITHIN GROUP (ORDER BY inner.CUSTOMER_NAME) AS CUSTOMERS
       FROM (
            SELECT rel.MAIL_ID,
                   cust.CUSTOMER_NAME,
                   ROW_NUMBER() OVER
                     (PARTITION BY rel.MAIL_ID ORDER BY cust.CUST_ID) AS RN
              FROM CUSTOMERS_MAILS rel INNER JOIN
                   CUSTOMERS cust ON (cust.CUST_ID = rel.CUST_ID)
            ) inner
      WHERE inner.RN <= 50
      GROUP BY inner.MAIL_ID

with:

Select CM.MAIL_ID,
  substr(xmlcast(
    xmlagg(
      xmlelement(E, '; ' || C.CUSTOMER_NAME)
      ORDER BY C.CUSTOMER_NAME
    ) AS CLOB -- or AS VARCHAR2(4000)
  ), 3
) as CUSTOMERS
from CUSTOMERS C, CUSTOMER_MAIL CM
where C.CUST_ID = CM.CUST_ID
group by CM.MAIL_ID

I have put together a simple SQLFiddle that shows this select in action. It assumes two sets of users, (some male names, some female names), and it puts the different users in to different MAIL_ID's.

Once you have swapped out these nested statements, it will significantly shorten your query.... Your handling code though will have to change. Getting a CLOB back from the view is a big change.... and it is best to handle that in a different way....

Edit: About this operation as a general problem

At an academic level, the problem you are experiencing is because you are doing a non-relational operation in a relational database. Data in a relational database is treated using set arithmetic. You have sets of data that you can intersect, union, filter, and otherwise manipulate. The problem you are tying to solve in this question is the conversion of a set of values in to a single merged value (with internal consistency requirements). This type of operation is not well defined in set theory.

The standard/recommended mechanism for solving this problem is to export the data set in to an external application, and process the data from there. Any system you use in an SQL query to remove the set-like nature of the data is a 'hack', and that is why you are unsatisfied with the result you are seeing. The right solution is to not use SQL.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Thanks for your suggestion. But in my opinion, while this is shorter, it's way harder to understand if you'r not familiar with that hack. I prefer self speaking code above short code. \$\endgroup\$ – cremor May 12 '14 at 11:19
  • \$\begingroup\$ @cremor - I agree, which is why I suggested the UDF as well. Also, this works, which is more than what you have (and it's only shorter because it is not doing the 50-at-a-time system). \$\endgroup\$ – rolfl May 12 '14 at 11:21
  • \$\begingroup\$ Maybe I should have made it clearer that I'm fine with a result that is limited to 4000 chars (or near it). A solution with UDFs will indeed make the view easier to read, but then again I'll need length checking code in my custom type. I'll need to test how this affects performance. \$\endgroup\$ – cremor May 12 '14 at 11:32
  • \$\begingroup\$ The performance with user defined aggregate functions is good for a single row. But when I select my whole view (430,000 rows) it's very bad. Tkprof shows an increase of CPU time from 12 to 72 seconds and of elapsed time from 18 to 76 seconds. Selecting the whole view is not a real use case, but at least selecting 5000 rows needs to be at the same speed, which it isn't (6/16 CPU/elapsed seconds vs. 38/46). I gave you the bounty since the code is indeed quite nice with that solution (although the user defined aggregate functions itself aren't very nice), but I'll leave the question open. \$\endgroup\$ – cremor May 14 '14 at 11:49
  • \$\begingroup\$ @cremor - edited my answer to include a section on why SQL is not the right tool. \$\endgroup\$ – rolfl May 14 '14 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.