[Bug]: SQL window ODC parsing SQL speed optimization

[Huangxiao-mas]

ODC version

ODC432

OB version

independent

What happened?

SQL window ODC parsing SQL speed optimization

What did you expect to happen?

。

How can we reproduce it (as minimally and precisely as possible)?

SELECT
    bd.TARIFFPLAN_ID,
    bd.PLANITEM_ID,
    bd.DISC_ENTITY,
    bd.DISC_COND,
    bd.G_DISC_COND,
    bd.DISC_TYPE,
    bd.REFITEM_TYPE,
    bd.FREEREFITEM_TYPE,
    bd.REFITEM_CODE,
    bd.REFITEM_CYCLES,
    bd.REFITEM_CYCLE_OFFSET,
    bd.REFVALUE_CALC_TYPE,
    bd.REFVALUE_UNIT,
    bd.DISC_OBJECT_TYPE,
    bd.DISC_ITEM_CODE,
    bd.START_CYCLE_OFFSET,
    bd.VALID_CYCLE_TYPE,
    bd.VALID_CYCLES,
    bd.INUSE,
    bd.TARIFFPLAN_TYPE,
    bd.DISC_ITEM_TYPE,
    bd.FREEREFITEM_MIN_USAGE,
    bd.NOTE,
    bd.ISDISCTSPEC,
    bd.SPECDISCT_EXPR,
    bd.G_SPECDISCT_EXPR,
    bd.CONVERT_TYPE,
    bd.REF_ENTITY_TYPE,
    bd.DISC_SOURCE,
    bd.G_DISC_SOURCE,
    bd.DISC_SOURCE_FACTOR,
    bd.DISC_ITEM_EXPR_ID,
    bd.FORCE_OUTPUT_EVENT,
    bd.DISC_SOURCE_TYPE,

    bducd.WORKORDER_ID,
    bducd.DISC_ENTITY AS UCD_DISC_ENTITY,
    bducd.DISC_COND AS UCD_DISC_COND,
    bducd.G_DISC_COND AS UCD_G_DISC_COND,
    bducd.DISC_TYPE AS UCD_DISC_TYPE,
    bducd.REFITEM_TYPE AS UCD_REFITEM_TYPE,
    bducd.FREEREFITEM_TYPE AS UCD_FREEREFITEM_TYPE,
    bducd.REFITEM_CODE AS UCD_REFITEM_CODE,
    bducd.REFITEM_CYCLES AS UCD_REFITEM_CYCLES,
    bducd.REFITEM_CYCLE_OFFSET AS UCD_REFITEM_CYCLE_OFFSET,
    bducd.REFVALUE_CALC_TYPE AS UCD_REFVALUE_CALC_TYPE,
    bducd.REFVALUE_UNIT AS UCD_REFVALUE_UNIT,
    bducd.DISC_OBJECT_TYPE AS UCD_DISC_OBJECT_TYPE,
    bducd.DISC_ITEM_CODE AS UCD_DISC_ITEM_CODE,
    bducd.START_CYCLE_OFFSET AS UCD_START_CYCLE_OFFSET,
    bducd.VALID_CYCLE_TYPE AS UCD_VALID_CYCLE_TYPE,
    bducd.VALID_CYCLES AS UCD_VALID_CYCLES,
    bducd.INUSE AS UCD_INUSE,
    bducd.TARIFFPLAN_TYPE AS UCD_TARIFFPLAN_TYPE,
    bducd.DISC_ITEM_TYPE AS UCD_DISC_ITEM_TYPE,
    bducd.FREEREFITEM_MIN_USAGE AS UCD_FREEREFITEM_MIN_USAGE,
    bducd.NOTE AS UCD_NOTE,
    bducd.ISDISCTSPEC AS UCD_ISDISCTSPEC,
    bducd.SPECDISCT_EXPR AS UCD_SPECDISCT_EXPR,
    bducd.G_SPECDISCT_EXPR AS UCD_G_SPECDISCT_EXPR,

    bcl.LOGONID,
    bcl.OPERID,
    bcl.MACADDR,
    bcl.IPADDR,
    bcl.SUCCESS,
    bcl.LOGONTIME,
    bcl.HOSTNAME,
    bcl.SID,

    -- Fields from BILLINGCYCLE
    bc.CYCLE_SCHEMA_ID,
    bc.CYCLE_ID,
    bc.CYCLE_NAME,
    bc.CYCLE_STATUS,
    bc.OPENDATE,
    bc.CLOSEDATE,
    bc.INVOICEDATE,

    CASE
        WHEN bd.DISC_TYPE = 1 THEN 'Type 1'
        WHEN bd.DISC_TYPE = 2 THEN 'Type 2'
        ELSE 'Other Type'
    END AS DISC_TYPE_DESCRIPTION,

    NVL(bd.NOTE, 'No Note') AS NOTE_STATUS,

    DECODE(bcl.SUCCESS, 1, 'Success', 0, 'Failure', 'Unknown') AS LOGIN_STATUS,

    TO_CHAR(bcl.LOGONTIME, 'YYYY-MM-DD HH24:MI:SS') AS FORMATTED_LOGONTIME,

    (bd.REFITEM_CYCLES * bd.REFVALUE_UNIT) AS TOTAL_REF_VALUE,

    (bducd.REFITEM_CYCLES + bducd.REFITEM_CYCLE_OFFSET) AS TOTAL_UCD_REF_CYCLES,

    COUNT(bd.TARIFFPLAN_ID) OVER (PARTITION BY bd.TARIFFPLAN_ID) AS TARIFFPLAN_COUNT,

    SUM(bd.FREEREFITEM_MIN_USAGE) OVER (PARTITION BY bd.PLANITEM_ID) AS TOTAL_USAGE_PER_PLAN,

    AVG(bd.DISC_SOURCE_FACTOR) OVER () AS AVERAGE_DISC_SOURCE_FACTOR,

    ROW_NUMBER() OVER (ORDER BY bcl.LOGONTIME DESC) AS RN,

    RANK() OVER (PARTITION BY bd.TARIFFPLAN_TYPE ORDER BY bd.DISC_TYPE DESC) AS DISC_TYPE_RANK,

    DENSE_RANK() OVER (ORDER BY bc.CYCLE_STATUS) AS CYCLE_STATUS_RANK,

    NTILE(4) OVER (ORDER BY bd.TARIFFPLAN_ID) AS QUARTILE_TARIFFPLAN,

    LEAD(bcl.LOGONTIME, 1) OVER (ORDER BY bcl.LOGONTIME) AS NEXT_LOGONTIME,

    LAG(bcl.LOGONTIME, 1) OVER (ORDER BY bcl.LOGONTIME) AS PREV_LOGONTIME,

    FIRST_VALUE(bd.DISC_COND) OVER (PARTITION BY bd.TARIFFPLAN_ID ORDER BY bd.PLANITEM_ID) AS FIRST_DISC_COND,

    LAST_VALUE(bd.DISC_COND) OVER (PARTITION BY bd.TARIFFPLAN_ID ORDER BY bd.PLANITEM_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_DISC_COND,

    (SELECT COUNT(*)
     FROM BILLDISC_DEF bd_sub
     WHERE bd_sub.TARIFFPLAN_ID = bd.TARIFFPLAN_ID
    ) AS SUBQUERY_COUNT_BD,

    (SELECT MAX(bcl_sub.LOGONTIME)
     FROM BILLINGCLIENTLOG bcl_sub
     WHERE bcl_sub.LOGONID = bcl.LOGONID
    ) AS LAST_LOGON_TIME,

    (SELECT bc_sub.CYCLE_NAME
     FROM BILLINGCYCLE bc_sub
     WHERE bc_sub.CYCLE_ID = bc.CYCLE_ID
    ) AS CURRENT_CYCLE_NAME,

    (SELECT AVG(bducd_sub.FREEREFITEM_MIN_USAGE)
     FROM BILLDISC_DEF_UCD bducd_sub
     WHERE bducd_sub.PLANITEM_ID = bducd.PLANITEM_ID
    ) AS AVG_FREEREFITEM_MIN_USAGE,

    (SELECT SUM(bd_sub.REFVALUE_UNIT)
     FROM BILLDISC_DEF bd_sub
     WHERE bd_sub.DISC_ENTITY = bd.DISC_ENTITY
    ) AS TOTAL_REFVALUE_UNIT_PER_ENTITY,

    (
        SELECT MAX(
            (
                SELECT COUNT(*)
                FROM BILLINGCLIENTLOG bcl_inner
                WHERE bcl_inner.LOGONID = bcl.LOGONID
            )
        )
        FROM DUAL
    ) AS MAX_LOGIN_COUNT,

    (
        SELECT MIN(bc_sub.OPENDATE)
        FROM BILLINGCYCLE bc_sub
        WHERE bc_sub.CYCLE_STATUS = bc.CYCLE_STATUS
    ) AS MIN_OPEN_DATE_SAME_STATUS,

    CASE
        WHEN bd.DISC_TYPE = 1 AND bcl.SUCCESS = 1 THEN 'Active Discount and Successful Login'
        WHEN bd.DISC_TYPE = 2 AND bcl.SUCCESS = 0 THEN 'Inactive Discount and Failed Login'
        ELSE 'Other Conditions'
    END AS COMBINED_STATUS,

    CONCAT(bd.DISC_ITEM_CODE, '_', bducd.WORKORDER_ID) AS DISC_WORKORDER_COMBINE,

    UPPER(bcl.OPERID) AS OPERID_UPPERCASE,

    LOWER(bd.DISC_SOURCE) AS DISC_SOURCE_LOWERCASE,

    INITCAP(bc.CYCLE_NAME) AS CYCLE_NAME_INITCAP,

    MONTHS_BETWEEN(bc.CLOSEDATE, bc.OPENDATE) AS CYCLE_DURATION_MONTHS,

    ROUND((bc.CLOSEDATE - bc.OPENDATE)) AS CYCLE_DURATION_DAYS,

    ADD_MONTHS(bc.OPENDATE, 6) AS SIX_MONTHS_AFTER_OPEN,

    NEXT_DAY(bc.OPENDATE, 'MONDAY') AS NEXT_MONDAY_AFTER_OPEN,

    LAST_DAY(bc.CLOSEDATE) AS LAST_DAY_CLOSEDATE,

    bd.REFITEM_CYCLES * bducd.REFITEM_CYCLE_OFFSET AS CYCLE_MULTIPLICATION,

    bd.FORCE_OUTPUT_EVENT + bducd.DISC_SOURCE_TYPE AS EVENT_SUM,

    bd.DISC_SOURCE_FACTOR / NULLIF(bducd.REFVALUE_UNIT, 0) AS FACTOR_DIVISION,

    POWER(bd.DISC_TYPE, 2) AS DISC_TYPE_SQUARED,

    SQRT(bd.REFITEM_CYCLES) AS SQRT_REFITEM_CYCLES,

    SUM(CASE WHEN bcl.SUCCESS = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY bd.TARIFFPLAN_ID) AS SUCCESSFUL_LOGINS,

    COUNT(CASE WHEN bc.CYCLE_STATUS = 1 THEN 1 END) OVER (PARTITION BY bd.TARIFFPLAN_ID) AS ACTIVE_CYCLES,

    MAX(bd.DISC_SOURCE_FACTOR) OVER (PARTITION BY bd.TARIFFPLAN_TYPE) AS MAX_DISC_SOURCE_FACTOR_PER_TYPE,

    MIN(bcl.LOGONTIME) OVER (PARTITION BY bcl.LOGONID) AS FIRST_LOGIN_TIME_PER_USER,

    AVG(bd.FREEREFITEM_MIN_USAGE) OVER (PARTITION BY bd.DISC_ENTITY) AS AVG_USAGE_PER_ENTITY,

    bd.TARIFFPLAN_ID + bducd.DISC_ENTITY AS TARIFFDISC_COMBINED_ID,

    bcl.SID * bc.CYCLE_ID AS SID_CYCLE_MULTIPLIER,

    bd.PLANITEM_ID || '-' || bducd.WORKORDER_ID AS PLANITEM_WORKORDER_COMBINE,

    (bd.START_CYCLE_OFFSET + bc.CYCLE_SCHEMA_ID) AS CYCLE_OFFSET_SUM,

    (bd.VALID_CYCLES - bducd.VALID_CYCLES) AS VALID_CYCLES_DIFFERENCE,

    (bd.DISC_SOURCE_FACTOR * bd.CONVERT_TYPE) AS DISC_SOURCE_CONVERTED,

    CASE
        WHEN (bd.REFVALUE_CALC_TYPE = 1 AND bducd.REFVALUE_CALC_TYPE = 2) THEN 'Type Combination 1-2'
        WHEN (bd.REFVALUE_CALC_TYPE = 2 AND bducd.REFVALUE_CALC_TYPE = 1) THEN 'Type Combination 2-1'
        ELSE 'Other Type Combinations'
    END AS REFVALUE_TYPE_COMBINATION,

    bd.TARIFFPLAN_ID + 0 AS TD1,
    bd.TARIFFPLAN_ID + 1 AS TD2,
    bd.TARIFFPLAN_ID + 2 AS TD3,
    bd.TARIFFPLAN_ID + 3 AS TD4,
    bd.TARIFFPLAN_ID + 4 AS TD5,
    bd.TARIFFPLAN_ID + 5 AS TD6,
    bd.TARIFFPLAN_ID + 6 AS TD7,
    bd.TARIFFPLAN_ID + 7 AS TD8,
    bd.TARIFFPLAN_ID + 8 AS TD9,
    bd.TARIFFPLAN_ID + 9 AS TD10,
    bd.TARIFFPLAN_ID + 10 AS TD11,
    bd.TARIFFPLAN_ID + 11 AS TD12,
    bd.TARIFFPLAN_ID + 12 AS TD13,
    bd.TARIFFPLAN_ID + 13 AS TD14,
    bd.TARIFFPLAN_ID + 14 AS TD15,
    bd.TARIFFPLAN_ID + 15 AS TD16,
    bd.TARIFFPLAN_ID + 16 AS TD17,
    bd.TARIFFPLAN_ID + 17 AS TD18,
    bd.TARIFFPLAN_ID + 18 AS TD19,
    bd.TARIFFPLAN_ID + 19 AS TD20,

    bducd.DISC_ENTITY * 1 AS UCD_DE1,
    bducd.DISC_ENTITY * 2 AS UCD_DE2,
    bducd.DISC_ENTITY * 3 AS UCD_DE3,
    bducd.DISC_ENTITY * 4 AS UCD_DE4,
    bducd.DISC_ENTITY * 5 AS UCD_DE5,
    bducd.DISC_ENTITY * 6 AS UCD_DE6,
    bducd.DISC_ENTITY * 7 AS UCD_DE7,
    bducd.DISC_ENTITY * 8 AS UCD_DE8,
    bducd.DISC_ENTITY * 9 AS UCD_DE9,
    bducd.DISC_ENTITY * 10 AS UCD_DE10,

    bd.REFITEM_CYCLES - bducd.REFITEM_CYCLES AS REF_CYCLES_DIFF,
    bd.FREEREFITEM_MIN_USAGE + bducd.FREEREFITEM_MIN_USAGE AS TOTAL_MIN_USAGE,

    LISTAGG(bd.DISC_COND, ', ') WITHIN GROUP (ORDER BY bd.DISC_COND) OVER (PARTITION BY bd.TARIFFPLAN_ID) AS LISTAGG_DISC_COND,

    LISTAGG(bducd.SPECDISCT_EXPR, '; ') WITHIN GROUP (ORDER BY bducd.SPECDISCT_EXPR) OVER (PARTITION BY bducd.PLANITEM_ID) AS LISTAGG_SPECDISCT_EXPR,

    CONCAT(CONCAT(bd.DISC_ITEM_CODE, '-'), bducd.WORKORDER_ID) AS CONCAT_DISC_WORKORDER,

    SUBSTR(bd.DISC_COND, 1, 10) AS DISC_COND_SUBSTR,

    REPLACE(bd.NOTE, ' ', '_') AS NOTE_REPLACED,

    MOD(bd.TARIFFPLAN_ID, 100) AS TARIFFPLAN_ID_MOD,

    FLOOR(bducd.REFVALUE_UNIT / 2) AS FLOOR_REFVALUE_UNIT,

    CEIL(bd.FREEREFITEM_MIN_USAGE / 1000) AS CEIL_MIN_USAGE,

    ROUND(bd.DISC_SOURCE_FACTOR, 2) AS ROUND_DISC_SOURCE_FACTOR,
    CASE
        WHEN bd.INUSE = 1 AND bducd.INUSE = 1 THEN 'Both In Use'
        WHEN bd.INUSE = 1 THEN 'BD In Use Only'
        WHEN bducd.INUSE = 1 THEN 'UCD In Use Only'
        ELSE 'Neither In Use'
    END AS INUSE_STATUS,

    CASE
        WHEN bc.CYCLE_STATUS = 1 AND bd.DISC_TYPE = 1 THEN 'Active Cycle & Type 1'
        WHEN bc.CYCLE_STATUS = 2 AND bd.DISC_TYPE = 2 THEN 'Closed Cycle & Type 2'
        ELSE 'Other Cycle Status & Types'
    END AS CYCLE_TYPE_COMBINATION,

    TRUNC(bcl.LOGONTIME, 'MONTH') AS LOGON_MONTH,

    EXTRACT(YEAR FROM bc.OPENDATE) AS OPEN_YEAR,

    EXTRACT(MONTH FROM bc.CLOSEDATE) AS CLOSE_MONTH,

    bd.TARIFFPLAN_ID + 20 AS TD21,
    bd.TARIFFPLAN_ID + 21 AS TD22,
    bd.TARIFFPLAN_ID + 22 AS TD23,
    bd.TARIFFPLAN_ID + 23 AS TD24,
    bd.TARIFFPLAN_ID + 24 AS TD25,
    bd.TARIFFPLAN_ID + 25 AS TD26,
    bd.TARIFFPLAN_ID + 26 AS TD27,
    bd.TARIFFPLAN_ID + 27 AS TD28,
    bd.TARIFFPLAN_ID + 28 AS TD29,
    bd.TARIFFPLAN_ID + 29 AS TD30,

    bducd.DISC_ENTITY * 11 AS UCD_DE11,
    bducd.DISC_ENTITY * 12 AS UCD_DE12,
    bducd.DISC_ENTITY * 13 AS UCD_DE13,
    bducd.DISC_ENTITY * 14 AS UCD_DE14,
    bducd.DISC_ENTITY * 15 AS UCD_DE15,
    bducd.DISC_ENTITY * 16 AS UCD_DE16,
    bducd.DISC_ENTITY * 17 AS UCD_DE17,
    bducd.DISC_ENTITY * 18 AS UCD_DE18,
    bducd.DISC_ENTITY * 19 AS UCD_DE19,
    bducd.DISC_ENTITY * 20 AS UCD_DE20,

    bd.TARIFFPLAN_ID + 30 AS TD31,
    bd.TARIFFPLAN_ID + 31 AS TD32,
    bd.TARIFFPLAN_ID + 32 AS TD33,
    bd.TARIFFPLAN_ID + 33 AS TD34,
    bd.TARIFFPLAN_ID + 34 AS TD35,
    bd.TARIFFPLAN_ID + 35 AS TD36,
    bd.TARIFFPLAN_ID + 36 AS TD37,
    bd.TARIFFPLAN_ID + 37 AS TD38,
    bd.TARIFFPLAN_ID + 38 AS TD39,
    bd.TARIFFPLAN_ID + 39 AS TD40,

    bducd.DISC_ENTITY * 21 AS UCD_DE21,
    bducd.DISC_ENTITY * 22 AS UCD_DE22,
    bducd.DISC_ENTITY * 23 AS UCD_DE23,
    bducd.DISC_ENTITY * 24 AS UCD_DE24,
    bducd.DISC_ENTITY * 25 AS UCD_DE25,
    bducd.DISC_ENTITY * 26 AS UCD_DE26,
    bducd.DISC_ENTITY * 27 AS UCD_DE27,
    bducd.DISC_ENTITY * 28 AS UCD_DE28,
    bducd.DISC_ENTITY * 29 AS UCD_DE29,
    bducd.DISC_ENTITY * 30 AS UCD_DE30,

    -- Additional Conditions and Filters
    CASE
        WHEN bd.VALID_CYCLE_TYPE IN (1, 2, 3) THEN 'Valid Cycle Type 1-3'
        WHEN bd.VALID_CYCLE_TYPE IN (4, 5) THEN 'Valid Cycle Type 4-5'
        ELSE 'Other Valid Cycle Types'
    END AS VALID_CYCLE_TYPE_DESC,

    CASE
        WHEN bc.CYCLE_STATUS = 1 AND bd.INUSE = 1 THEN 'Active and In Use'
        WHEN bc.CYCLE_STATUS = 0 AND bd.INUSE = 0 THEN 'Inactive and Not In Use'
        ELSE 'Mixed Status'
    END AS STATUS_COMBINATION,

    -- Final Repetitions to Ensure Length
    bd.TARIFFPLAN_ID + 40 AS TD41,
    bd.TARIFFPLAN_ID + 41 AS TD42,
    bd.TARIFFPLAN_ID + 42 AS TD43,
    bd.TARIFFPLAN_ID + 43 AS TD44,
    bd.TARIFFPLAN_ID + 44 AS TD45,
    bd.TARIFFPLAN_ID + 45 AS TD46,
    bd.TARIFFPLAN_ID + 46 AS TD47,
    bd.TARIFFPLAN_ID + 47 AS TD48,
    bd.TARIFFPLAN_ID + 48 AS TD49,
    bd.TARIFFPLAN_ID + 49 AS TD50,

    bducd.DISC_ENTITY * 31 AS UCD_DE31,
    bducd.DISC_ENTITY * 32 AS UCD_DE32,
    bducd.DISC_ENTITY * 33 AS UCD_DE33,
    bducd.DISC_ENTITY * 34 AS UCD_DE34,
    bducd.DISC_ENTITY * 35 AS UCD_DE35,
    bducd.DISC_ENTITY * 36 AS UCD_DE36,
    bducd.DISC_ENTITY * 37 AS UCD_DE37,
    bducd.DISC_ENTITY * 38 AS UCD_DE38,
    bducd.DISC_ENTITY * 39 AS UCD_DE39,
    bducd.DISC_ENTITY * 40 AS UCD_DE40,

    -- Final Fields to Reach Required Length
    bd.TARIFFPLAN_ID + 50 AS TD51,
    bd.TARIFFPLAN_ID + 51 AS TD52,
    bd.TARIFFPLAN_ID + 52 AS TD53,
    bd.TARIFFPLAN_ID + 53 AS TD54,
    bd.TARIFFPLAN_ID + 54 AS TD55,
    bd.TARIFFPLAN_ID + 55 AS TD56,
    bd.TARIFFPLAN_ID + 56 AS TD57,
    bd.TARIFFPLAN_ID + 57 AS TD58,
    bd.TARIFFPLAN_ID + 58 AS TD59,
    bd.TARIFFPLAN_ID + 59 AS TD60,

    bducd.DISC_ENTITY * 41 AS UCD_DE41,
    bducd.DISC_ENTITY * 42 AS UCD_DE42,
    bducd.DISC_ENTITY * 43 AS UCD_DE43,
    bducd.DISC_ENTITY * 44 AS UCD_DE44,
    bducd.DISC_ENTITY * 45 AS UCD_DE45,
    bducd.DISC_ENTITY * 46 AS UCD_DE46,
    bducd.DISC_ENTITY * 47 AS UCD_DE47,
    bducd.DISC_ENTITY * 48 AS UCD_DE48,
    bducd.DISC_ENTITY * 49 AS UCD_DE49,
    bducd.DISC_ENTITY * 50 AS UCD_DE50

FROM
    GSH.BILLDISC_DEF bd
    INNER JOIN GSH.BILLDISC_DEF_UCD bducd
        ON bd.TARIFFPLAN_ID = bducd.TARIFFPLAN_ID
        AND bd.PLANITEM_ID = bducd.PLANITEM_ID
    LEFT JOIN GSH.BILLINGCLIENTLOG bcl
        ON bd.TARIFFPLAN_ID = bcl.LOGONID
    LEFT JOIN GSH.BILLINGCYCLE bc
        ON bcl.SID = bc.CYCLE_ID

WHERE
    -- Complex WHERE Conditions
    (
        (bd.DISC_TYPE = 1 AND bd.INUSE = 1)
        OR
        (bducd.DISC_TYPE = 2 AND bducd.INUSE = 0)
    )
    AND
    (
        bc.CYCLE_STATUS IN (1, 2, 3)
        AND
        bcl.SUCCESS = 1
    )
    AND
    (
        bd.REFITEM_CYCLES BETWEEN 1 AND 10
        OR
        bducd.REFITEM_CYCLES BETWEEN 5 AND 15
    )
    AND
    (
        bd.DISC_COND LIKE '%Discount%'
        OR
        bducd.DISC_COND LIKE '%Condition%'
    )
    AND
    (
        bcl.IPADDR IS NOT NULL
        AND
        bcl.MACADDR IS NOT NULL
    )
    AND
    (
        bc.OPENDATE < SYSDATE
        AND
        bc.CLOSEDATE > SYSDATE
    )
    AND
    (
        bd.TARIFFPLAN_TYPE IN (100, 200, 300)
        OR
        bducd.TARIFFPLAN_TYPE IN (400, 500)
    )
    AND
    (
        bcl.LOGONTIME BETWEEN ADD_MONTHS(SYSDATE, -6) AND SYSDATE
    )
    AND
    (
        bc.CYCLE_NAME LIKE 'Cycle%'
    )
    AND
    (
        (
            bd.DISC_SOURCE_FACTOR > 0
            AND
            bducd.REFVALUE_UNIT > 1
        )
        OR
        (
            bd.DISC_SOURCE_FACTOR < 5
            AND
            bducd.REFVALUE_UNIT < 10
        )
    )
    AND
    (
        NVL(bd.NOTE, 'No Note') <> 'Deprecated'
    )
    AND
    (
        bducd.SPECDISCT_EXPR IS NOT NULL
        OR
        bd.SPECDISCT_EXPR IS NOT NULL
    )
    AND
    (
        (
            bd.CONVERT_TYPE = 1
            AND
            bducd.CONVERT_TYPE = 0
        )
        OR
        (
            bd.CONVERT_TYPE = 0
            AND
            bducd.CONVERT_TYPE = 1
        )
    )
    AND
    (
        EXISTS (
            SELECT 1
            FROM GSH.BILLDISC_DEF bd_inner
            WHERE bd_inner.TARIFFPLAN_ID = bd.TARIFFPLAN_ID
            AND bd_inner.DISC_ENTITY = bd.DISC_ENTITY
        )
    )
    AND
    (
        NOT EXISTS (
            SELECT 1
            FROM GSH.BILLINGCLIENTLOG bcl_inner
            WHERE bcl_inner.OPERID = bcl.OPERID
            AND bcl_inner.SUCCESS = 0
        )
    )
    AND
    (
        bd.DISC_ITEM_CODE IN (
            SELECT DISC_ITEM_CODE
            FROM GSH.BILLDISC_DEF
            WHERE DISC_TYPE = 1
        )
    )
    AND
    (
        bducd.WORKORDER_ID IS NOT NULL
    )
    AND
    (
        bc.CYCLE_ID IS NOT NULL
    )

ORDER BY
    bd.TARIFFPLAN_ID,
    bducd.PLANITEM_ID,
    bcl.LOGONTIME DESC,
    bc.CLOSEDATE ASC;

Anything else we need to know?

No response

[zijiacj]

This is an old problem that requires a special person to be responsible for, and can be done as an optimization requirement