代码之家 › 专栏 › 技术社区 › PiC

当“分区列表子查询”在执行计划中时,某些东西(bug?)会反实例化包

sql-execution-plan oracle12c oracle

PiC · 技术社区 · 8 年前

这是Oracle 12c的错误吗?

查询使用一个分区表,该分区表通过使用确定性“getter”函数读取的包中的变量与另一个受限表连接而受到限制。 如果我将函数更改为非确定性,或者更改任何内容,以便解释计划不使用“分区列表子查询”,那么问题就不会出现。

请参阅在my databases上使用“PARTITION LIST SUBQUERY”生成解释计划的合成示例(在4上试用):

-- Clean-up ----------------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE facts CASCADE CONSTRAINTS;
DROP TABLE DIM_CALENDAR CASCADE CONSTRAINTS;
DROP PACKAGE Parameters_PKG;

-- First, partitioned table  ----------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE factS
( mth VARCHAR2(6 BYTE)    NOT NULL
, just_data VARCHAR2(120 BYTE)  NOT NULL
)
PARTITION BY LIST (mth)
(  
   PARTITION M01 VALUES ('M01')
,  PARTITION M02 VALUES ('M02')
,  PARTITION M03 VALUES ('M03')
,  PARTITION M04 VALUES ('M04')
,  PARTITION M05 VALUES ('M05')
,  PARTITION MAX_VALUE VALUES (DEFAULT)
    LOGGING
    ROW STORE COMPRESS BASIC
)
;
INSERT INTO facts SELECT 'M' || TO_CHAR(LEVEL, 'FM09'), STANDARD_HASH(LEVEL, 'SHA384' ) FROM dual connect BY LEVEL < 4;
COMMIT;

-- Second table to iterate the partitions of the first table ------------------------------------------------------------------------------------------------
CREATE TABLE DIM_CALENDAR
(
  CLIENT_ID  VARCHAR2(10 BYTE)              NOT NULL,
  mth        VARCHAR2(6 BYTE)               NOT NULL
)
;
INSERT INTO DIM_CALENDAR SELECT 'TEST', 'M' || TO_CHAR(LEVEL, 'FM09') FROM dual connect BY LEVEL < 2;
INSERT INTO DIM_CALENDAR SELECT 'OTHER', 'M' || TO_CHAR(LEVEL, 'FM009') FROM dual connect BY LEVEL < 10;
INSERT INTO DIM_CALENDAR SELECT 'ANOTHER', 'M' || TO_CHAR(LEVEL, 'FM09') FROM dual connect BY LEVEL < 2;
COMMIT;

-- Analyzing both tables to get to the desired explain plan ----------------------------------------------------------------------------------------------------
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (NULL, NULL);

-- A package with deterministic ffunction ---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE Parameters_PKG
AUTHID DEFINER
AS
   FUNCTION  get_Client_ID RETURN VARCHAR2 deterministic; 
   PROCEDURE Set_Client_ID (p_Client_ID     VARCHAR2);
END Parameters_PKG
;
CREATE OR REPLACE PACKAGE BODY Parameters_PKG
AS
   Client_ID  VARCHAR2(255);
FUNCTION get_Client_ID   RETURN VARCHAR2   
AS
   PRAGMA UDF;
BEGIN
   IF Client_ID IS NULL THEN 
      RAISE_APPLICATION_ERROR(-20001, 'Fatal error.');
   END IF;
   RETURN Client_ID;
END get_Client_ID;
PROCEDURE Set_Client_ID (P_Client_ID VARCHAR2)
IS
BEGIN
   Client_ID := UPPER(TRIM(p_Client_ID));
END Set_Client_ID;
END Parameters_PKG
;
----------------------------------------------------------------------------------------------------------------------------------------------------------------


-- The test ----------------------------------------------------------------------------------------------------------------------------------------------------
-- exec Parameters_PKG.Set_Client_ID('TEST') -- this should return 2 rows
exec Parameters_PKG.Set_Client_ID('wrong_value_to_have_0_rows_returned') 
SELECT Parameters_PKG.Get_Client_ID FROM dual; -- a check that the value is really set.

SELECT f.mth, f.just_data  
FROM facts f, DIM_CALENDAR c  
WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
AND F.mth = C.mth
;


/* Failing explain plan with  "PARTITION LIST SUBQUERY"

SELECT STATEMENT  ALL_ROWSCost: 43.194  Bytes: 336  Cardinality: 3              
 4 HASH JOIN  Cost: 43.194  Bytes: 336  Cardinality: 3          
  2 PARTITION LIST SUBQUERY  Cost: 43.107  Bytes: 303  Cardinality: 3  Partition #: 2  Partitions accessed #KEY(SUBQUERY)   
   1 TABLE ACCESS FULL TABLE FACTS Cost: 43.107  Bytes: 303  Cardinality: 3  Partition #: 2  Partitions accessed #KEY(SUBQUERY)
  3 TABLE ACCESS FULL TABLE DIM_CALENDAR Cost: 87  Bytes: 44  Cardinality: 4    

*/

1 回复 | 直到 8 年前

Connor McDonald 8 年前

删除“deterministic”关键字。确定性意味着对于同一组输入,我可以返回相同的输出,而无需再次计算函数。你有不函数的输入,所以我们不需要运行它。所以我们将返回null,例如(省略设置代码)

--
-- with deterministic
--
SQL> CREATE OR REPLACE PACKAGE Parameters_PKG
  2  AUTHID DEFINER
  3  AS
  4     FUNCTION  get_Client_ID RETURN VARCHAR2 deterministic;
  5     PROCEDURE Set_Client_ID (p_Client_ID     VARCHAR2);
  6  END Parameters_PKG
  7  ;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Parameters_PKG
  2  AS
  3     Client_ID  VARCHAR2(255);
  4  FUNCTION get_Client_ID   RETURN VARCHAR2
  5  AS
  6     PRAGMA UDF;
  7  BEGIN
  8     IF Client_ID IS NULL THEN
  9        RAISE_APPLICATION_ERROR(-20001, 'Fatal error.');
 10     END IF;
 11     RETURN Client_ID;
 12  END get_Client_ID;
 13  PROCEDURE Set_Client_ID (P_Client_ID VARCHAR2)
 14  IS
 15  BEGIN
 16     Client_ID := UPPER(TRIM(p_Client_ID));
 17  END Set_Client_ID;
 18  END Parameters_PKG;
 19  /

Package body created.

SQL>
SQL>
SQL> exec Parameters_PKG.Set_Client_ID('TEST')

PL/SQL procedure successfully completed.

SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
  4  AND F.mth = C.mth
  5  ;
FROM facts f, DIM_CALENDAR c
     *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Fatal error.
ORA-06512: at "MCDONAC.PARAMETERS_PKG", line 9


SQL>
SQL> exec Parameters_PKG.Set_Client_ID('wrong_value_to_have_0_rows_returned')

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
  4  AND F.mth = C.mth
  5  ;
SELECT f.mth, f.just_data
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Fatal error.
ORA-06512: at "MCDONAC.PARAMETERS_PKG", line 9

--
-- without deterministic
--
SQL>
SQL> CREATE OR REPLACE PACKAGE Parameters_PKG
  2  AUTHID DEFINER
  3  AS
  4     FUNCTION  get_Client_ID RETURN VARCHAR2;
  5     PROCEDURE Set_Client_ID (p_Client_ID     VARCHAR2);
  6  END Parameters_PKG
  7  ;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Parameters_PKG
  2  AS
  3     Client_ID  VARCHAR2(255);
  4  FUNCTION get_Client_ID   RETURN VARCHAR2
  5  AS
  6     PRAGMA UDF;
  7  BEGIN
  8     IF Client_ID IS NULL THEN
  9        RAISE_APPLICATION_ERROR(-20001, 'Fatal error.');
 10     END IF;
 11     RETURN Client_ID;
 12  END get_Client_ID;
 13  PROCEDURE Set_Client_ID (P_Client_ID VARCHAR2)
 14  IS
 15  BEGIN
 16     Client_ID := UPPER(TRIM(p_Client_ID));
 17  END Set_Client_ID;
 18  END Parameters_PKG;
 19  /

Package body created.

SQL>
SQL>
SQL> exec Parameters_PKG.Set_Client_ID('TEST')

PL/SQL procedure successfully completed.

SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
  4  AND F.mth = C.mth
  5  ;

MTH    JUST_DATA
------ ------------------------------------------------------------------------------------------------------------------------
M01    C6537FE410CFA617AFE7F17E6DD72BD9A6EF9ED08CA1216A811320A31A1FE0F9E57D832061B1A7EAA3534D8473098CBF

1 row selected.

SQL>
SQL> exec Parameters_PKG.Set_Client_ID('wrong_value_to_have_0_rows_returned')

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
  4  AND F.mth = C.mth
  5  ;

no rows selected

SQL>
SQL>

SQL> CREATE TABLE factS
  2  ( mth VARCHAR2(6 BYTE)    NOT NULL
  3  , just_data VARCHAR2(120 BYTE)  NOT NULL
  4  )
  5  PARTITION BY LIST (mth)
  6  (
  7     PARTITION M01 VALUES ('M01')
  8  ,  PARTITION M02 VALUES ('M02')
  9  ,  PARTITION M03 VALUES ('M03')
 10  ,  PARTITION M04 VALUES ('M04')
 11  ,  PARTITION M05 VALUES ('M05')
 12  ,  PARTITION MAX_VALUE VALUES (DEFAULT)
 13      LOGGING
 14      ROW STORE COMPRESS BASIC
 15  )
 16  ;

Table created.

SQL> INSERT INTO facts SELECT 'M' || TO_CHAR(LEVEL, 'FM09'), STANDARD_HASH(LEVEL, 'SHA384' ) FROM dual connect BY LEVEL < 4;

3 rows created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> CREATE TABLE DIM_CALENDAR
  2  (
  3    CLIENT_ID  VARCHAR2(10 BYTE)              NOT NULL,
  4    mth        VARCHAR2(6 BYTE)               NOT NULL
  5  )
  6  ;

Table created.

SQL> INSERT INTO DIM_CALENDAR SELECT 'TEST', 'M' || TO_CHAR(LEVEL, 'FM09') FROM dual connect BY LEVEL < 2;

1 row created.

SQL> INSERT INTO DIM_CALENDAR SELECT 'OTHER', 'M' || TO_CHAR(LEVEL, 'FM009') FROM dual connect BY LEVEL < 10;

9 rows created.

SQL> INSERT INTO DIM_CALENDAR SELECT 'ANOTHER', 'M' || TO_CHAR(LEVEL, 'FM09') FROM dual connect BY LEVEL < 2;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> create context my_context using Parameters_PKG;

Context created.

SQL> CREATE OR REPLACE PACKAGE Parameters_PKG AS
  2     PROCEDURE Set_Client_ID (p_Client_ID     VARCHAR2);
  3  END Parameters_PKG;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Parameters_PKG AS
  2
  3  PROCEDURE Set_Client_ID (P_Client_ID VARCHAR2)
  4  IS
  5  BEGIN
  6     dbms_session.set_context('MY_CONTEXT','CLIENT_ID',UPPER(TRIM(p_Client_ID)));
  7  END Set_Client_ID;
  8  END Parameters_PKG;
  9  /

Package body created.

SQL> exec Parameters_PKG.Set_Client_ID('TEST')

PL/SQL procedure successfully completed.

SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = sys_context('MY_CONTEXT','CLIENT_ID')
  4  AND F.mth = C.mth
  5  ;

MTH
------
JUST_DATA
----------------------------------------------------------------------------------------------------
M01
C6537FE410CFA617AFE7F17E6DD72BD9A6EF9ED08CA1216A811320A31A1FE0F9E57D832061B1A7EAA3534D8473098CBF


1 row selected.

SQL>
SQL> exec Parameters_PKG.Set_Client_ID('wrong_value_to_have_0_rows_returned')

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = sys_context('MY_CONTEXT','CLIENT_ID')
  4  AND F.mth = C.mth
  5  ;

no rows selected

SQL>