Oracle 时区转换和查询优化

Question

我有一个针对5个以上国家的计算用户频率表（约40M）：

DB: Oracle 11g
Minimal Schema: ID - Integer, Freq - Varchar

所以基本上，我每天都在尝试动态地提取订阅者。

Frequency Chart (May differ based for each countries):
7X - Everyday
5X - All weekdays
3X - Monday, Wednesday, Friday
2X - Tuesday, Thursday
1X - Wednesday

我写了以下查询，每天都会给出不同的列表（为了更好地理解，修改^）：

SELECT ID
FROM (
    WITH CONST AS (
            SELECT TO_CHAR(TRUNC(SYSDATE + 5 / 8), 'D') TODAY_
            FROM DUAL
            )
    SELECT C.ID
        ,FREQ
        ,CASE 
            WHEN FREQ = '7X'
                THEN TODAY_
            WHEN FREQ = '5X'
                AND TODAY_ NOT IN (
                    1
                    ,7
                    )
                THEN TODAY_
            WHEN FREQ = '3X'
                AND TODAY_ IN (
                    2
                    ,4
                    ,6
                    )
                THEN TODAY_
            WHEN FREQ = '2X'
                AND TODAY_ IN (
                    3
                    ,5
                    )
                THEN TODAY_
            WHEN FREQ = '1X'
                AND TODAY_ = 4
                THEN TODAY_
            END AS TARGET_
    FROM CONST
        ,CONTACT C
    INNER JOIN ENGAGEMENT ENG ON C.ID = ENG.ID
        AND C.COUNTRY_ID = '1'
    )
WHERE TARGET_ IS NOT NULL

我有两个问题

1） 在CONST子查询中，我试图将PST转换为ICT。有没有其他更好的方法来转换oracle 中的时区

2） 因为我对每个国家都有不同的疑问。每个查询大约需要5-10分钟才能获取结果。我不确定查询的优化程度。

Answer 1

您不应该依赖TO_CHAR(..., 'D')，因为结果取决于NLS_TERRITORY的当前设置，每个会话的设置可能不同。此外，NLS_DATE_LANGUAGE可能不同，因此最好明确指定它。

ICT不是一个有效的时区名称，我想你的意思是"亚洲/曼谷"。CCD_ 6也是不明确的。

这个应该有效：

WITH t AS
    (SELECT
        TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'Asia/Bangkok', 'fmDay', 'NLS_DATE_LANGUAGE = american' ) AS TODAY
    FROM dual) 
SELECT ID
FROM CONTACT C
    INNER JOIN ENGAGEMENT ENG ON C.ID = ENG.ID
    CROSS JOIN t
WHERE C.COUNTRY_ID = 1
    AND (
        (FREQ = '7X') 
        OR (FREQ = '5X' AND TODAY NOT IN ('Saturday','Sunday') )
        OR (FREQ = '3X' AND TODAY IN ('Monday', 'Wednesday', 'Friday') )
        OR (FREQ = '2X' AND TODAY IN ('Tuesday', 'Thursday') )
        OR (FREQ = '1X' AND TODAY IN ('Wednesday') )
    );