Optimizing subqueries on oracle db

Question

I am having only read rights on an oracle db 11. My problem is that I have developed now the following query, however, it is extremely slow. It can take up to 5 min to execute this query. Furthermore, I would like to add more tags to my query in the future and I guess this time will increase kind of exponentially.

SELECT distinct *
FROM (
  SELECT sqOne.value_number AS Interest_Rate, sqTwo.value_number AS Equity, u.update_as_of AS REFERENZ_DATUM, u.update_dat_ins AS INSERTION_DATE, p.cust_id, p.project_name
  FROM update u
   JOIN project p ON p.project_id= u.project_id
   JOIN customer cust ON p.CUST_ID = cust.CUST_ID
   JOIN value v ON u.update_id = v.update_id 
   JOIN tag t ON t.tag_id = v.tag_id
-- ###############################################
-- Interest Rate
-- ###############################################
   LEFT OUTER JOIN 
      (
          SELECT va.value_number, pr.project_id
          FROM update up
          JOIN project pr ON pr.project_id= up.project_id
          JOIN value va ON up.update_id = va.update_id 
          JOIN tag ta ON ta.tag_id = va.tag_id
          WHERE ta.tag_desc LIKE 'IR%' AND va.value_number > 0 AND va.value_number IS NOT NULL
      ) sqOne 
  ON sqOne.project_id= p.project_id
-- ###############################################
-- Equity
-- ###############################################
   LEFT OUTER JOIN 
      (
          SELECT va.value_number, pr.project_id--return the tic which is the latest!!! TODO
          FROM update up
          JOIN project pr ON pr.project_id= up.project_id
          JOIN value va ON up.update_id = va.update_id 
          JOIN tag ta ON ta.tag_id = va.tag_id
          WHERE ta.tag_desc LIKE 'EQUITY%' AND va.value_number > 0 AND va.value_number IS NOT NULL
      ) sqTwo 
  ON sqTwo.project_id= p.project_id
 WHERE p.project_stat = 'A'
 ORDER BY p.project_id
);

Here I tried to reconstruct the table definitions, with select * queries:

• select * from update;

+--------------------------------------------------+
| UPDATE_ID UPDATE_AS_OF UPDATE_DAT_INS PROJECT_ID |
+--------------------------------------------------+
| 1         17-DEC-12    17-DEC-12        34       |
| 2         17-DEC-12    17-DEC-12        31       |
| 14        17-DEC-12    17-DEC-12        29       |
| 21        17-DEC-12    18-DEC-12        2        |
| 22        17-DEC-12    18-DEC-12        2        |
| 25        18-DEC-12    18-DEC-12        66       |
+--------------------------------------------------+

• select * from project;

+----------------------------------------------------------------------+
| PROJECT_NAME     PROJECT_DESC           CUST_ID           PROJECT_ID |
+----------------------------------------------------------------------+
| Test                null                654321                21     |
| test1               null                654321                39     |
| test2               null                123456                76     |
| test                null                123456                90     |
| Usability           null                323423                158    |
| 5. Test             null                654321                181    |
| fsf                 null                123456                361    |
| Test 100            null                123456                425    |
+----------------------------------------------------------------------+

• select * from customer;

+-----------------------------------------------------+
|  CUST_ID   CUST_DAT_INS  CUST_DAT_UPD     CUST_NAME |
+-----------------------------------------------------+
| 1            13-DEC-12     01-MAR-14     abc1       |
| 2            13-DEC-12     30-OCT-13     abc2       |
| 3            13-DEC-12     30-OCT-13     abds       |
| 4            13-DEC-12     30-OCT-13     test1      |
| 5            13-DEC-12     30-OCT-13     test23     |
+-----------------------------------------------------+

• select * from value;

+---------------------------------------------------------------------------------+
| VALUE_ID   VALUE_NUMBER  VALUE_DAT_INS    TAG_ID  PROJECT_ID          UPDATE_ID |
+---------------------------------------------------------------------------------+
| 1          null          17-DEC-12          118          34           1         |
| 2          5000          17-DEC-12          48           34           1         |
| 3          8             17-DEC-12          73           34           1         |
| 4          null          17-DEC-12          187          34           1         |
| 5          null          17-DEC-12          123          34           1         |
+---------------------------------------------------------------------------------+

• select * from tag;

+------------------------------------------------------------+
| TAG_ID      TAG_CODE         TAG_DESC          TAG_DAT_INS |
+------------------------------------------------------------+
| 1             EQU             EQUITY             13-DEC-12 |
| 2             IR              Interest Rate      13-DEC-12 |
+------------------------------------------------------------+

I would appreciate your answers on how to optimize my query to load faster!

UPDATE

This is an update for the explain for query:

---------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |  2600 |   162K|  2747 |
|   1 |  HASH UNIQUE                        |                 |  2600 |   162K|  2747 |
|   2 |   HASH JOIN                         |                 |  2600 |   162K|  2746 |
|   3 |    VIEW                             | VW_DTP_104D9B21 |  4191 | 20955 |   623 |
|   4 |     HASH UNIQUE                     |                 |  4191 | 20955 |   623 |
|   5 |      INDEX FAST FULL SCAN           | VALUE_IDX4      |   531K|  2594K|   590 |
|   6 |    HASH JOIN                        |                 |  2600 |   149K|  2123 |
|   7 |     HASH JOIN RIGHT OUTER           |                 |   495 | 16830 |  2109 |
|   8 |      VIEW                           |                 |   483 |  2898 |  1050 |
|   9 |       NESTED LOOPS                  |                 |   483 | 19320 |  1050 |
|  10 |        HASH JOIN                    |                 |   765 | 27540 |  1050 |
|  11 |         NESTED LOOPS                |                 |  4191 |   102K|    14 |
|  12 |          TABLE ACCESS BY INDEX ROWID|    TAG          |     1 |    16 |     1 |
|  13 |           INDEX UNIQUE SCAN         |    TAG_UDX1     |     1 |       |     0 |
|  14 |          TABLE ACCESS FULL          |    UPDATE       |  4191 | 37719 |    13 |
|  15 |         TABLE ACCESS FULL           |    VALUE        |   123K|  1331K|  1035 |
|  16 |        INDEX UNIQUE SCAN            |    PROJECT_PK   |     1 |     4 |     0 |
|  17 |      HASH JOIN RIGHT OUTER          |                 |   495 | 13860 |  1058 |
|  18 |       VIEW                          |                 |   242 |  1452 |  1052 |
|  19 |        NESTED LOOPS                 |                 |   242 | 12826 |  1052 |
|  20 |         HASH JOIN                   |                 |   383 | 18767 |  1052 |
|  21 |          MERGE JOIN CARTESIAN       |                 |  2096 | 79648 |    16 |
|  22 |           TABLE ACCESS FULL         |    TAG          |     1 |    29 |     3 |
|  23 |           BUFFER SORT               |                 |  4191 | 37719 |    13 |
|  24 |            TABLE ACCESS FULL        |    UPDATE       |  4191 | 37719 |    13 |
|  25 |          TABLE ACCESS FULL          |    VALUE        |   123K|  1331K|  1035 |
|  26 |         INDEX UNIQUE SCAN           |    PROJECT_PK   |     1 |     4 |     0 |
|  27 |       TABLE ACCESS FULL             |    PROJECT      |   495 | 10890 |     5 |
|  28 |     TABLE ACCESS FULL               |    UPDATE       |  4191 |   102K|    13 |
---------------------------------------------------------------------------------------

Note

• 'PLAN_TABLE' is old version

Answer 1

From where I sit it seems that the query is just plain poorly written. The following things seem to be useless pieces of code:

• join with customer in the "level 2" query ... No data from the customer table used anywhere on that query level.
• join with value in the "level 2" query ... No data from the value table used anywhere on that query level.
• join with tag in the "level 2" query ... No data from the tag table used anywhere on that query level.
• join with project in both sqOne and sqTwo ... Both pr.project_id can be selected as up.project_id.
• predicates va.value_number IS NOT NULL in both sqOne and sqTwo ... Both are automatically implied by the predicate va.value_number > 0.
• order by clause in the "level 2 query" ... Completely senseless due to the distinct on "level 1".
• It may well be the case that after getting rid of the value and tag joins there won't be need for select distinct ... because I suspect that those two joins (along with what I suspect to be an incorrect join of sqOne/sqTwo on only the project_id and not also the update_id) duplicate the rows in the "level 2" query. But I may as well be wrong, you'll have to test it for yourself.

So, after this basic code cleanup the query could look like this:

SELECT distinct *
FROM (
    SELECT sqOne.value_number AS Interest_Rate, sqTwo.value_number AS Equity, u.update_as_of AS REFERENZ_DATUM, u.update_dat_ins AS INSERTION_DATE, p.cust_id, p.project_name
    FROM update u
        JOIN project p ON p.project_id = u.project_id
        -- Interest Rate
        LEFT OUTER JOIN (
            SELECT va.value_number, up.project_id
            FROM update up
                JOIN value va ON up.update_id = va.update_id 
                JOIN tag ta ON ta.tag_id = va.tag_id
                WHERE ta.tag_desc LIKE 'IR%' AND va.value_number > 0
        ) sqOne 
            ON sqOne.project_id = p.project_id
        -- Equity
        LEFT OUTER JOIN (
            SELECT va.value_number, up.project_id --return the tic which is the latest!!! TODO
            FROM update up
                JOIN value va ON up.update_id = va.update_id 
                JOIN tag ta ON ta.tag_id = va.tag_id
            WHERE ta.tag_desc LIKE 'EQUITY%' AND va.value_number > 0
        ) sqTwo 
            ON sqTwo.project_id = p.project_id
    WHERE p.project_stat = 'A'
        -- the following supplements the original inner joins with `tag` and `value`; those two originally left the result with only those `product` and `update` rows which had at least some tags and values
        AND (sqOne.project_id is not null or sqTwo.project_id is not null)
);

Please, try it, if it even runs (I might as well have deleted something important), if it does what you need it to be doing, if it runs faster or not, and if not then post the elapsed time of its execution and a new execution plan.

Answer 2

You can try to redesign the query.

1) You don't need the 2 sub queries. Since the join conditions are same in the main query and the subquery we can calculate interest and equity there itself e.g. for interest use the following statement in the base query itself

(CASE WHEN t.tag_desc LIKE 'IR%' AND v.value_number > 0 AND v.value_number IS NOT NULL THEN v.value_number ELSE NULL END) AS Interest_Rate

Similarly write for equity and remove the 2 sub queries.

2) Just check if you need to use DISTINCT

Hope it helps.