1

I am trying to write a query to get count of devices from 11 different table, ie there will be 11 separate counts from 11 tables. I need to join them based on a key column and display them side by side based on the key column. Device is the key column. So the output should be Device A followed by the 11 different counts from 11 different tables.

Device A Count 1 count 2 count 3 .....
Device B Count 1 Count 2 Count 3....
Device C Count 1 Count 2 Count 3...

So here is what i have done so far. I created a view doing a union all of all the 11 tables. And used that as the source table to derive my counts.I have written sub queries in the select clause. It works but the problem is its extremely slow, takes forever since the data volume is huge. Am sure this is not the best approach but am running out of ideas... Would like to know if there is a efficient and simpler approach to solve this problem.

 select
B.MODEL_ID,
B.REC_CREATE_DT_KEY,
B.DEVICE_ID,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'STAGE_CTDI' and test_result = 'PASSED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) stg_pass_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'STAGE_CTDI' and test_result = 'FAILED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) stg_fail_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'TEST_CTDI' and test_result = 'PASSED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) test_pass_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'TEST_CTDI' and test_result = 'FAILED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) test_fail_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_total_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT in ('FS001','FS005')
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT in ('FS001','FS005')
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_wlbl_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT = 'FS003'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_ber_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_sent_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS002'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repaired_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS001'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_ntf_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS003'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_ber_count
from V_CPE_BAU_METRICS_SRC b
group by
B.MODEL_ID,
B.REC_CREATE_DT_KEY,
B.DEVICE_ID;
Improve this question
2
  • The subqueries in your column list are probably killing you. Oracle evaluates each SELECT once per row. You can probably do the same thing with a query on the base table that uses clever CASE and SUM statements to aggregate things the way you want. Commented Aug 8, 2019 at 19:01
  • @TadHarrison i did try that initially but wasn't able to get it working as i intended, i will probably git it another try. Thanks much. Commented Aug 8, 2019 at 19:18

2 Answers 2

Reset to default
2

Use a Common Table Expression to eliminate the subqueries:

WITH cteCounts AS (SELECT MODEL_ID,
                          NVL(REC_CREATE_DT_KEY, 2) AS REC_CREATE_DT_KEY,
                          NVL(DEVICE_ID, 1) AS DEVICE_ID,
                          FILE_TYPE,
                          TEST_RESULT,
                          COUNT(DISTINCT(SERIAL_NBR)) AS COUNT_DISTINCT_SERIAL_NBR
                     FROM V_CPE_BAU_METRICS_SRC
                     GROUP BY MODEL_ID,
                              NVL(REC_CREATE_DT_KEY, 2),
                              NVL(DEVICE_ID, 1),
                              FILE_TYPE,
                              TEST_RESULT)
SELECT b.MODEL_ID,
       b.REC_CREATE_DT_KEY,
       b.DEVICE_ID,
       SUM(CASE
             WHEN c.FILE_TYPE = 'STAGE_CTDI' AND c.TEST_RESULT = 'PASSED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS STG_PASS_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'STAGE_CTDI' AND c.TEST_RESULT = 'FAILED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS STG_FAIL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'TEST_CTDI' AND c.TEST_RESULT = 'PASSED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS TEST_PASS_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'TEST_CTDI' AND c.TEST_RESULT = 'FAILED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS TEST_FAIL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_TOTAL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT IN ('FS001','FS005') THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT IN ('FS001','FS005') THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_WLBL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT = 'FS003' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_BER_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_SENT_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS002' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIRED_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS001' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_NTF_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS003' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_BER_COUNT
  FROM V_CPE_BAU_METRICS_SRC b
  INNER JOIN cteCounts c
    ON c.MODEL_ID = b.MODEL_ID AND
       c.REC_CREATE_DT_KEY = NVL(b.REC_CREATE_DT_KEY, 2) AND
       c.DEVICE_ID = NVL(b.DEVICE_ID, 1)
  GROUP BY b.MODEL_ID,
           b.REC_CREATE_DT_KEY,
           b.DEVICE_ID
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks much. This works perfectly fine but was still very slow. Guess , i will have to limit the data.
You might try adding an index on (MODEL_ID, NVL(REC_CREATE_DT_KEY, 2), NVL(DEVICE_ID, 1), FILE_TYPE, TEST_RESULT, SERIAL_NBR).
1

You can do it with SELF JOIN.

I have given the demo of the first two counts as following, You can implement the code for other counts in the same way.

SELECT
    MODEL_ID,
    REC_CREATE_DT_KEY,
    DEVICE_ID,
    COUNT(DISTINCT STG_PASS_COUNT) AS STG_PASS_COUNT, -- COUNT OF DISTINCT SERIAL NUMBER 
    COUNT(DISTINCT STG_FAIL_COUNT) AS STG_FAIL_COUNT  -- COUNT OF DISTINCT SERIAL NUMBER 
    --
    -- ..........
    --
    --
FROM
    ( --OUTER SELECT IS NEEDED FOR TAKING DISTINCT VALUES IN THE COUNT
        SELECT
            B.MODEL_ID,
            B.REC_CREATE_DT_KEY,
            B.DEVICE_ID,
            CASE ---- IF CONDITION IS SATISFIED THEN ITS SERIAL NUMBER IS CONSIDERED
                WHEN B1.FILE_TYPE = 'STAGE_CTDI'
                     AND B1.TEST_RESULT = 'PASSED' THEN B1.SERIAL_NBR
            END AS STG_PASS_COUNT, 
            CASE -- IF CONDITION IS SATISFIED THEN ITS SERIAL NUMBER IS CONSIDERED
                WHEN B1.FILE_TYPE = 'STAGE_CTDI'
                     AND B1.TEST_RESULT = 'FAILED' THEN B1.SERIAL_NBR
            END AS STG_FAIL_COUNT
    --
    -- PUT ALL OTHER CONDITIONS AS ABOVE
    --
        FROM
            V_CPE_BAU_METRICS_SRC B
            JOIN V_CPE_BAU_METRICS_SRC B1 
            -- COMMON CONDITION IS USED FOR SELF JOIN
            ON ( B1.MODEL_ID = B.MODEL_ID
                AND NVL(B1.REC_CREATE_DT_KEY, 2) = NVL(B.REC_CREATE_DT_KEY, 2)
                AND NVL(B1.DEVICE_ID, 1) = NVL(B.DEVICE_ID, 1) )
    )
GROUP BY
    MODEL_ID,
    REC_CREATE_DT_KEY,
    DEVICE_ID;

Cheers!!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.