I have a table having nearly 20+ billions of records and there's a query (with PIVOT) on the same table. The query is running very slow.
Is there any way to improve the performance of the query?
Any help is appreciated.
Following are details
Table structure ----
CREATE TABLE ODT.ODT_COMP_DIAGNO_MSR
(
SITE_CODE VARCHAR2(6 BYTE),
JOB_TYPE VARCHAR2(20 BYTE),
VEHICLE_TYPE VARCHAR2(4 BYTE),
CURR_DT DATE,
UNIT_ID NUMBER,
FLAG VARCHAR2(20 BYTE)
)
-----------------------------------------------
Query ------
SELECT *
FROM (SELECT JOB_TYPE, FLAG, UNIT_ID
FROM ODT_COMP_DIAGNO_MSR D,
( SELECT FAC_IDU
FROM ODT_COMP_FAC_FULL
CONNECT BY PRIOR FAC_IDU = PARENT_FAC_IDU
START WITH FAC_IDU = 'H00001') T
WHERE D.SITE_CODE = T.FAC_IDU
AND D.VEHICLE_TYPE = 'COLL'
AND D.CURR_DT BETWEEN '01-JAN-2014' AND '01-JUN-2014'
AND FLAG IN ('LATE_DAYS', 'CURRENT_DAYS', 'LATE_PM')) PIVOT (COUNT (
UNIT_ID)
FOR JOB_TYPE
IN ('PMG' AS PMG,
'PMC' AS PMC,
'PMF' AS PMF,
'PMA' AS PMA,
'PMZ' AS PMZ,
'PMB' AS PMB,
'PMD' AS PMD,
'OIL' AS OIL,
'PME' AS PME,
'PMX' AS PMX,
'PMY' AS PMY,
'PM_TOTAL' AS PM_TOTAL,
'UNIT_TOTAL' AS UNIT_TOTAL))
UNION
SELECT *
FROM (SELECT JOB_TYPE, FLAG, UNIT_ID
FROM ODT_COMP_DIAGNO_MSR D,
( SELECT FAC_IDU
FROM ODT_COMP_FAC_FULL
CONNECT BY PRIOR FAC_IDU = PARENT_FAC_IDU
START WITH FAC_IDU = 'H00001') T
WHERE D.SITE_CODE = T.FAC_IDU
AND D.VEHICLE_TYPE = 'COLL'
AND D.CURR_DT BETWEEN '01-JAN-2014' AND '01-JUN-2014'
AND FLAG IN ('LATE_TRUCK')) PIVOT (COUNT (DISTINCT UNIT_ID)
FOR JOB_TYPE
IN ('PMG' AS PMG,
'PMC' AS PMC,
'PMF' AS PMF,
'PMA' AS PMA,
'PMZ' AS PMZ,
'PMB' AS PMB,
'PMD' AS PMD,
'OIL' AS OIL,
'PME' AS PME,
'PMX' AS PMX,
'PMY' AS PMY,
'PM_TOTAL' AS PM_TOTAL,
'UNIT_TOTAL' AS UNIT_TOTAL));
------------------------------------------------------------
Explain Plan for query
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 124 | 11 (64)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 124 | 11 (64)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | HASH GROUP BY PIVOT | | 1 | 62 | 6 (34)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1 | 62 | 4 (0)| 00:00:01 |
| 6 | VIEW | | 4 | 28 | 5 (40)| 00:00:01 |
|* 7 | CONNECT BY WITH FILTERING | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | ODT_COMP_FAC_FULL | 1 | 14 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | ODT_COMP_FAC_FULL_PK | 1 | | 1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 3 | 57 | 2 (0)| 00:00:01 |
| 11 | CONNECT BY PUMP | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| ODT_COMP_FAC_FULL | 3 | 42 | 1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | ODT_COMP_FAC_FULL_IDX03 | 3 | | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | ODT_COMP_DIAGNO_MSR_IDX01 | 1 | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | ODT_COMP_DIAGNO_MSR | 1 | 55 | 1 (0)| 00:00:01 |
| 16 | SORT GROUP BY PIVOT | | 1 | 62 | 5 (20)| 00:00:01 |
| 17 | NESTED LOOPS | | | | | |
| 18 | NESTED LOOPS | | 1 | 62 | 4 (0)| 00:00:01 |
| 19 | VIEW | | 4 | 28 | 5 (40)| 00:00:01 |
|* 20 | CONNECT BY WITH FILTERING | | | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | ODT_COMP_FAC_FULL | 1 | 14 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | ODT_COMP_FAC_FULL_PK | 1 | | 1 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 3 | 57 | 2 (0)| 00:00:01 |
| 24 | CONNECT BY PUMP | | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID| ODT_COMP_FAC_FULL | 3 | 42 | 1 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | ODT_COMP_FAC_FULL_IDX03 | 3 | | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | ODT_COMP_DIAGNO_MSR_IDX01 | 1 | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | ODT_COMP_DIAGNO_MSR | 1 | 55 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
There is composite index on (SITE_CODE, VEHICLE_TYPE, CURR_DT,FLAG ) columns.
If I execute same query for one SITE_CODE it executes in milliseconds. But when I use connect by or if I use more than 10 SITE_CODE in condition, It takes more than mins.