V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
coderstory
V2EX  ›  数据库

Oracle 数据库 怎么开启查询非聚合字段的兼容?

  •  
  •   coderstory · 2023-08-05 15:09:31 +08:00 · 849 次点击
    这是一个创建于 453 天前的主题,其中的信息可能已经有所发展或是发生改变。
    SELECT "name" FROM "User" group by "id"
    

    这个 SQL 执行报错

    ORA-00979: 不是 GROUP BY 表达式
    
    

    已知 mysql sqlserver dm 数据库是可以配置 兼容这种语法的。

    因为完全不懂 oracle ,所以特意来问问 oracle 能修改设置支持这种查询吗?

    第 1 条附言  ·  2023-08-05 16:07:23 +08:00
    问了 chatgpt ,给了一个方案


    大致 SQL 如下:

    sqlplus.exe /nolog

    conn/as sysdba

    ALTER SYSTEM SET "_optimizer_group_by_placement"=FALSE SCOPE=SPFILE;

    SHUTDOWN IMMEDIATE;

    STARTUP;


    但是执行后发现 这个配置没效果,然后去查了一下_optimizer_group_by_placement 的实际值
    发现 alter 好像无效的


    SQL> select "_optimizer_group_by_placement" from SYSTEM
    2
    SQL> ALTER SYSTEM SET "_optimizer_group_by_placement"=FALSE SCOPE=SPFILE;

    系统已更改。

    SQL> select "_optimizer_group_by_placement" from SYSTEM
    2
    6 条回复    2023-08-05 17:15:15 +08:00
    coderstory
        1
    coderstory  
    OP
       2023-08-05 15:19:43 +08:00
    ```
    在 Oracle 数据库中,要查询非 GROUP BY 字段,需要修改数据库的配置,以允许这种查询。默认情况下,Oracle 数据库要求 SELECT 语句中的字段要么包含在 GROUP BY 子句中,要么是聚合函数。

    要修改 Oracle 数据库的配置,以允许查询非 GROUP BY 字段,可以通过以下两种方式之一:

    修改 SESSION 级别的参数:这种方式只会对当前会话生效。
    在会话中执行以下语句,将"optimizer_features_enable"参数设置为旧版本的值(例如,11.2.0.4 ):

    ALTER SESSION SET optimizer_features_enable = '11.2.0.4';

    这个参数的默认值是当前数据库版本的值,通过将其设置为旧版本的值,可以使数据库遵循较旧的语法规则,从而允许查询非 GROUP BY 字段。

    修改数据库级别的参数:这种方式会对整个数据库生效。
    在数据库中执行以下语句,将"optimizer_features_enable"参数设置为旧版本的值(例如,11.2.0.4 ):

    ALTER SYSTEM SET optimizer_features_enable = '11.2.0.4' SCOPE = BOTH;

    这将修改数据库的配置,使其在所有会话中都允许查询非 GROUP BY 字段。需要注意的是,修改数据库级别的参数可能会对其他查询产生影响,因此在进行修改之前应该评估其潜在的影响。

    无论使用哪种方式,修改配置后,就可以在 SELECT 语句中查询非 GROUP BY 字段。但是需要注意的是,查询结果可能不准确,因为非 GROUP BY 字段的值将根据 GROUP BY 字段的分组进行汇总计算。因此,在查询结果时需要谨慎使用非 GROUP BY 字段,以确保数据的准确性。
    ```

    chatgpt 的回复如上,但是这种方式修改感觉怪怪的,也不清楚有什么负面影响
    coderstory
        2
    coderstory  
    OP
       2023-08-05 15:30:48 +08:00
    实测 2 种方案都无效

    数据库是 19c
    opengps
        3
    opengps  
       2023-08-05 15:39:08 +08:00
    “已知 mysql sqlserver dm 数据库是可以配置 兼容这种语法的。” 我怎么记得不是这样?
    nulIptr
        4
    nulIptr  
       2023-08-05 16:16:22 +08:00
    又涨知识了,居然还有这种写法,还是合法的,不过为啥要这么写。。。
    coderstory
        5
    coderstory  
    OP
       2023-08-05 16:23:22 +08:00
    @nulIptr 这个写法本身是不合理的,主流数据库默认都在 SQL 解析阶段抛出异常,需要手段开启。

    实际也有很多方法可以避免写这种语法。不过嘛,这种写法比较简单,直接,贴近自然语言。

    如果某个系统允许用户去写 SQL ,他们就会强烈要求支持这种写法,复杂的 SQL 他们也不会写。。。
    adoal
        6
    adoal  
       2023-08-05 17:15:15 +08:00 via iPhone
    印象中只有 MySQL 这个沙雕支持这种扯蛋的写法……你十分肯定 SQL Server 也支持?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1306 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 23:30 · PVG 07:30 · LAX 16:30 · JFK 19:30
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.