Oracle编译器和索引中存储的常量

2jcobegt 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(88)

假设我下面有对象，我想用使用my_index的查询创建过程。我试图在我的plsql开发人员中使用解释计划窗口（我不得不将查询从块中剥离出来），似乎变量A中的select将my_constant转换为variable，而my_index没有使用。如果我为变量B中的查询生成解释计划，则使用my_index。我真的必须使用变量B才能让my_index工作吗？

CREATE OR REPLACE PACKAGE my_package IS
  my_constant CONSTANT NUMBER(1) := 1;
END;

CREATE TABLE my_table (my_id NUMBER,my_column NUMBER(1));

CREATE INDEX my_index ON my_table (CASE WHEN my_column = 1 THEN my_id END);

-- variant A (using my_constant directly)
DECLARE
  v_my_id my_table.my_id%TYPE;
BEGIN
  SELECT my_id
    INTO v_my_id
    FROM my_table
   WHERE CASE
           WHEN my_column = my_package.my_constant THEN
            my_id
         END = 123;
END;

-- variant B (using value of constant with commented refference for future me)
DECLARE
  v_my_id my_table.my_id%TYPE;
BEGIN
  SELECT my_id
    INTO v_my_id
    FROM my_table
   WHERE CASE
           WHEN my_column = 1 /*my_package.my_constant*/ THEN
            my_id
         END = 123;
END;

我试着搜索论坛，问我的DBA，聊天GPT，我仍然无法找到满意的答案。

oracle

来源：https://stackoverflow.com/questions/77170315/oracle-compiler-and-stored-constants-in-index

1条答案

按热度按时间

m1m5dgzv1#

不能在SQL作用域中直接使用PL/SQL包变量;但是，您可以在SQL作用域中使用PL/SQL函数，并为常量编写 Package 器：

CREATE OR REPLACE PACKAGE my_package IS
  my_constant CONSTANT NUMBER(1) := 1;

  FUNCTION get_my_constant RETURN NUMBER DETERMINISTIC;
END;
/

CREATE OR REPLACE PACKAGE BODY my_package IS
  FUNCTION get_my_constant RETURN NUMBER DETERMINISTIC
  IS
  BEGIN
    RETURN my_constant;
  END;
END;
/

然后又道：

SELECT my_id
FROM   my_table
WHERE  CASE WHEN my_column = my_package.get_my_constant() THEN my_id END = 123;

您可以在PL/SQL作用域中使用PL/SQL包变量，并将它们作为绑定变量传递给SQL查询：

DECLARE
  v_my_id my_table.my_id%TYPE;
BEGIN
  SELECT my_id
  INTO   v_my_id
  FROM   my_table
  WHERE CASE WHEN my_column = my_package.my_constant THEN my_id END = 123;
END;
/

然而，在这两种情况下，SQL引擎不知道该值在PL/SQL范围内是一个常量，必须将其视为变量，然后它不知道该值将始终为1，因此将不使用索引。
你可以做的是创建索引：

CREATE INDEX my_index2 ON my_table (my_column, my_id);

然后用途：

SELECT my_id
FROM   my_table
WHERE  my_column = my_package.get_my_constant()
AND    my_id = 123;

它可以使用索引。
请参阅fiddle的例子和解释计划。

赞(0）回复(0）举报 2023-10-16

我来回答

Oracle编译器和索引中存储的常量

1条答案

相关问题

热门标签

最新问答