SQL Query Rewrite – Help Needed (Oracle)

oracle

Need help in re-writing the below SQL. The Query works fine for smaller database below 400 GB, but spends too much time for larger database above 600 GB.

Oracle – 11.2.0.4

Database – Oracle

  SELECT i.ITEM_ID,
         i.ITEM_TYPE,
         i.START_DATETIME,
         i.END_DATETIME,
         MAX (CASE WHEN ip.PROPERTY_TYPE = 'NAME' THEN ip.PROPERTY_STRING END)
            AS FIELD_NAME,
         MAX (CASE WHEN ip.PROPERTY_TYPE = 'STATE' THEN ip.PROPERTY_STRING END)
            AS FIELD_STATE,
         CL_STATE.CODE_TEXT AS STATE_TEXT,
         MAX (
            CASE WHEN ip.PROPERTY_TYPE = 'COUNTY' THEN ip.PROPERTY_STRING END)
            AS FIELD_COUNTY,
         CL_COUNTY.CODE_TEXT AS COUNTY_TEXT
    FROM ITEM i
         INNER JOIN DATE_INFO DI
            ON DI.DATETIME = TRUNC (SYSDATE) AND DI.DATE_TYPE = 'D'
         LEFT OUTER JOIN ITEM_PROPERTY ip ON (i.ITEM_ID = ip.ITEM_ID)


         LEFT OUTER JOIN ITEM_PROPERTY ip_STATE
            ON     ip_STATE.ITEM_ID = i.ITEM_ID
               AND ip_STATE.START_DATETIME <= DI.DATETIME
               AND ip_STATE.END_DATETIME > DI.DATETIME
               AND ip_STATE.PROPERTY_TYPE = 'STATE'
         LEFT OUTER JOIN CODE_LIST CL_STATE
            ON     CL_STATE.CODE = ip_STATE.PROPERTY_STRING
               AND CL_STATE.LIST_TYPE = 'STATE'
               AND CL_STATE.CULTURE = 'en-US'



         LEFT OUTER JOIN ITEM_PROPERTY ip_COUNTY
            ON     ip_COUNTY.ITEM_ID = i.ITEM_ID
               AND ip_COUNTY.START_DATETIME <= DI.DATETIME
               AND ip_COUNTY.END_DATETIME > DI.DATETIME
               AND ip_COUNTY.PROPERTY_TYPE = 'COUNTY'
         LEFT OUTER JOIN CODE_LIST CL_COUNTY
            ON     CL_COUNTY.CODE = ip_COUNTY.PROPERTY_STRING
               AND CL_COUNTY.LIST_TYPE = 'COUNTY'
               AND CL_COUNTY.CULTURE = 'en-US'



   WHERE     i.START_DATETIME <= DI.DATETIME
         AND i.END_DATETIME > DI.DATETIME
         AND ip.START_DATETIME <= DI.DATETIME
         AND ip.END_DATETIME > DI.DATETIME
         AND i.ITEM_TYPE LIKE 'FIELD'
GROUP BY i.ITEM_ID,
         i.ITEM_TYPE,
         i.START_DATETIME,
         i.END_DATETIME,
         CL_STATE.CODE_TEXT,
         CL_COUNTY.CODE_TEXT

Explain Plan

Plan hash value: 792208276

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |  9354 |  5161K|       | 11252   (1)| 00:02:16 |
|   1 |  HASH GROUP BY                      |                      |  9354 |  5161K|  5360K| 11252   (1)| 00:02:16 |
|*  2 |   HASH JOIN RIGHT OUTER             |                      |  9354 |  5161K|       | 10132   (1)| 00:02:02 |
|*  3 |    TABLE ACCESS FULL                | CODE_LIST            |  5328 |   353K|       |   120   (0)| 00:00:02 |
|*  4 |    HASH JOIN RIGHT OUTER            |                      |  9354 |  4539K|       | 10012   (1)| 00:02:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID     | CODE_LIST            |   115 |  7820 |       |    25   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN               | CODE_LIST_CUSTOM     |   115 |       |       |     1   (0)| 00:00:01 |
|*  7 |     HASH JOIN OUTER                 |                      |  9354 |  3918K|  2832K|  9987   (1)| 00:02:00 |
|*  8 |      HASH JOIN OUTER                |                      |  7916 |  2736K|       |  5357   (1)| 00:01:05 |
|   9 |       VIEW                          |                      |  6698 |  1824K|       |  1017   (0)| 00:00:13 |
|  10 |        NESTED LOOPS                 |                      |  6698 |   941K|       |  1017   (0)| 00:00:13 |
|  11 |         NESTED LOOPS                |                      |   482 | 33258 |       |    53   (0)| 00:00:01 |
|* 12 |          INDEX UNIQUE SCAN          | PK_DATE_INFO         |     1 |    10 |       |     1   (0)| 00:00:01 |
|  13 |          TABLE ACCESS BY INDEX ROWID| ITEM                 |   482 | 28438 |       |    52   (0)| 00:00:01 |
|* 14 |           INDEX RANGE SCAN          | IX_ITEM_ITEM_TYPE_DT |   482 |       |       |     4   (0)| 00:00:01 |
|* 15 |         INDEX RANGE SCAN            | IX_ITEM_PROPERTY4    |    14 |  1050 |       |     2   (0)| 00:00:01 |
|* 16 |       INDEX FAST FULL SCAN          | IX_ITEM_PROPERTY4    | 36913 |  2703K|       |  4340   (1)| 00:00:53 |
|* 17 |      INDEX FAST FULL SCAN           | IX_ITEM_PROPERTY4    | 36866 |  2700K|       |  4340   (1)| 00:00:53 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CL_COUNTY"."CODE"(+)="IP_COUNTY"."PROPERTY_STRING")
   3 - filter("CL_COUNTY"."LIST_TYPE"(+)='COUNTY' AND "CL_COUNTY"."CULTURE"(+)='en-US')
   4 - access("CL_STATE"."CODE"(+)="IP_STATE"."PROPERTY_STRING")
   6 - access("CL_STATE"."LIST_TYPE"(+)='STATE' AND "CL_STATE"."CULTURE"(+)='en-US')
   7 - access("IP_COUNTY"."ITEM_ID"(+)="from$_subquery$_005"."QCSJ_C000000000500000")
       filter("IP_COUNTY"."END_DATETIME"(+)>"from$_subquery$_005"."DATETIME" AND 
              "IP_COUNTY"."START_DATETIME"(+)<="from$_subquery$_005"."DATETIME")
   8 - access("IP_STATE"."ITEM_ID"(+)="I"."ITEM_ID")
       filter("IP_STATE"."END_DATETIME"(+)>"DI"."DATETIME" AND 
              "IP_STATE"."START_DATETIME"(+)<="DI"."DATETIME")
  12 - access("DI"."DATETIME"=TRUNC(SYSDATE@!) AND "DI"."DATE_TYPE"='D')
  14 - access("I"."ITEM_TYPE"='FIELD' AND "I"."END_DATETIME">TRUNC(SYSDATE@!) AND 
              "I"."START_DATETIME"<=TRUNC(SYSDATE@!))
       filter("I"."END_DATETIME">TRUNC(SYSDATE@!) AND "I"."START_DATETIME"<="DI"."DATETIME" AND 
              "I"."END_DATETIME">"DI"."DATETIME")
  15 - access("I"."ITEM_ID"="IP"."ITEM_ID" AND "IP"."END_DATETIME">TRUNC(SYSDATE@!) AND 
              "IP"."START_DATETIME"<=TRUNC(SYSDATE@!))
       filter("IP"."END_DATETIME">TRUNC(SYSDATE@!) AND "IP"."START_DATETIME"<=TRUNC(SYSDATE@!) AND 
              "IP"."START_DATETIME"<="DI"."DATETIME" AND "IP"."END_DATETIME">"DI"."DATETIME")
  16 - filter("IP_STATE"."PROPERTY_TYPE"(+)='STATE')
  17 - filter("IP_COUNTY"."PROPERTY_TYPE"(+)='COUNTY')

Explain Plan –

Plan
1 One or more rows were retrieved using index USAVM.AK_CODE_LIST .  The index was scanned in ascending order..
2 Rows from table USAVM.CODE_LIST  were accessed using rowid got from an index.
3 Rows were retrieved using the unique index USAVM.PK_DATE_INFO .
4 One or more rows were retrieved using index USAVM.IX_ITEM_ITEM_TYPE_DT .  The index was scanned in ascending order..
5 Rows from table USAVM.ITEM  were accessed using rowid got from an index.
6 For each row retrieved by step 3, the operation in step 5 was performed to find a matching row.
7 One or more rows were retrieved using index USAVM.IX_ITEM_PROPERTY4 .  The index was scanned in ascending order..
8 For each row retrieved by step 6, the operation in step 7 was performed to find a matching row.
9 A view definition was processed, either from a stored view SYS.  or as defined by steps 8.
10 Rows were retrieved by performing a fast read of all index records in USAVM.IX_ITEM_PROPERTY4 .
11 Rows from step 9 which matched rows from step 10 were returned (hash join).
12 The result sets from steps 2, 11 were joined (hash).
13 HASH GROUP BY
14 Rows were returned by the SELECT statement.

Best Answer

can you put an export of the schema on something like drop box or mail it to me at kylelf@gmail.com With the schema definition we can draw a Visual SQL Tuning (VST) diagram. Here is a presentation on the method http://www.youtube.com/watch?v=3ETNcMhRM5s&feature=youtu.be and slides from the presentation http://www.slideshare.net/khailey/doag-visual-sql-tuning A VST and especially a VST with the execution path is much much more powerful than a simple execution plan.

Related Solutions

Privileges needed for Oracle Text

What I ended up doing was giving the DMG schema DBA access right before creating the index then revoking it immediately after. I was afraid that would cause the index to fail to sync but it seems to work just fine. I was able move everything into the DMG schema= and remove the initial grants to the ctxsys schema.

I'm still not clear on what specific permissions I needed to give the DMG schema, but I got around my problem.

Should I group by and then join, or join and then group by

The optimizer always tries to reduce the amount of data as quickly as possible. If not, your statistics might not be good.

Your plan 1 shows that less rows are processed this is good. The optimizer was able to reduce the amount of data more quickly. The numbers might not be exactly true but it gives you an idea based on the optimizer statistics.

Plan 1:

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |     1 |    79 |     7  (15)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                         |      |     1 |    79 |     7  (15)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                         |      |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                        |      |     1 |    79 |     6   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                       |      |     1 |    63 |     5   (0)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| t1   |     1 |    22 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  6 |       INDEX RANGE SCAN                 | t1pk |     1 |       |     2   (0)| 00:00:01 |       |       |
|   7 |      PARTITION RANGE ITERATOR          |      |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID| t3   |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |        INDEX RANGE SCAN                | t3fk |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 10 |     INDEX UNIQUE SCAN                  | t2pk |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 11 |    TABLE ACCESS BY GLOBAL INDEX ROWID  | t2   |     1 |    16 |     1   (0)| 00:00:01 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------

It shows that Oracle did a HASH GROUP BY while plan 2 did a SORT GROUP BY.

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |      |     1 |   311 |     7  (15)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                            |      |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                           |      |     1 |   311 |     7  (15)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                          |      |     1 |   253 |     6  (17)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | t1   |     1 |   164 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX RANGE SCAN                    | t1pk |     1 |       |     2   (0)| 00:00:01 |       |       |
|   6 |     VIEW PUSHED PREDICATE                |      |     1 |    89 |     3  (34)| 00:00:01 |       |       |
|   7 |      SORT GROUP BY                       |      |     1 |    41 |     3  (34)| 00:00:01 |       |       |
|*  8 |       FILTER                             |      |       |       |            |          |       |       |
|   9 |        PARTITION RANGE SINGLE            |      |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 10 |         TABLE ACCESS BY LOCAL INDEX ROWID| t3   |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 11 |          INDEX RANGE SCAN                | t3fk |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 12 |    INDEX UNIQUE SCAN                     | t2pk |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 13 |   TABLE ACCESS BY GLOBAL INDEX ROWID     | t2   |     1 |    58 |     1   (0)| 00:00:01 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------

Conclusion: In most cases I would prefer a HASH GROUP BY. So in plan 1 Oracle does a better the GROUP BY much later with much less data. 2 reasons why plan 1 is better.

I assume that this is not the only SQL statement which will be using this join constellation. You will have to decide for each SQL which way is the best.

If you have a SQL similar to the following, the with clause can be much better:

with t3g as ( -- as in 't3 grouped'
    select id2, count(1) t3count
    from t3
    where id = :1 -- new line !!!!!!!!
    group by id2
)
select t1.id t1id, t2id, t3count
from t1 join t2  on (t1.p = t2.p  and t1.id = t2.id1)
        join t3g on (t2.p = t3g.p and t2.id = id2)

Best Answer

Related Solutions

Privileges needed for Oracle Text

Should I group by and then join, or join and then group by

Related Question