[20180926]神奇的規避ORA-01795方法.txt

[20180926]神奇的規避ORA-01795方法.txt

--//大家知道in裡面的值限制1000個值,如果超出報ORA-01795錯誤.

D:\> ooerr 01795
01795, 00000, "maximum number of expressions in a list is 1000"

D:\> oerr ora 01795
01795, 00000, "maximum number of expressions in a list is 1000"
// *Cause:    Number of expressions in the query exceeded than 1000.
//            Note that unused column/expressions are also counted
//            Maximum number of expressions that are allowed are 1000.
// *Action:   Reduce the number of expressions in the list and resubmit.

--//別人的awr報表我仔細查看,發現對方規避ora-01795的方法,使用集合的概念,仔細一想難道集合就沒有1000個的限制嗎?
--//我仔細看對方程式是寫死的,2000個集合,明顯這樣是沒有問題的.
--//有時候還是給佩服開發的想象力,我還是做一些例子說明問題.

1.環境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t as select * from all_objects;
Table created.

SCOTT@test01p> create unique index i_t_object_id on t(object_id);
Index created.

--//分析表略.

2.測試1:
variable b1 number;
variable b2 number;
exec :b1 :=42;
exec :b2 :=47;

SCOTT@test01p> select * from t where (1,object_id) in ((1,:b1),(1,:b2));
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE          CREATED             LAST_DDL_TIME       TIMESTAMP
----- ----------- -------------- --------- -------------- -------------------- ------------------- ------------------- -------------------
SYS   I_ICOL1                           42             42 INDEX                2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29
SYS   I_USER2                           47             47 INDEX                2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29

--//OK沒有問題,裡面僅僅包含2個集合,如果更多呢?

3.測試2:
select 'variable b'||to_char(level)||' number;' txt from dual connect by level<=1001
union all
select 'exec :b'||to_char(level)||' :='|| to_char(level) txt from dual connect by level<=1001
union all
select 'select * from t where (1,object_id) in (' txt from dual
union all
select '(1,:b'||to_char(level)||'),' txt from dual connect by level<=1000
union all
select '(1,:b'||to_char(1001)||'));' txt from dual ;

--//將輸出保存1個腳本,適當編輯執行.ok,沒有任何問題.執行計劃如下:
Plan hash value: 3525592940

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |       |   196 (100)|          |    891 |00:00:00.01 |      40 |
|   1 |  INLIST ITERATOR             |               |      1 |        |       |            |          |    891 |00:00:00.01 |      40 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T             |   1001 |   1001 |   112K|   196   (0)| 00:00:01 |    891 |00:00:00.01 |      40 |
|*  3 |    INDEX UNIQUE SCAN         | I_T_OBJECT_ID |   1001 |   1001 |       |   178   (0)| 00:00:01 |    891 |00:00:00.01 |      22 |
----------------------------------------------------------------------------------------------------------------------------------------

--//1001個集合沒有任何問題.
--//修改b200-b999變數等於0,測試看看:
Plan hash value: 3525592940

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |       |   196 (100)|          |    193 |00:00:00.01 |      16 |
|   1 |  INLIST ITERATOR             |               |      1 |        |       |            |          |    193 |00:00:00.01 |      16 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T             |    203 |   1001 |   112K|   196   (0)| 00:00:01 |    193 |00:00:00.01 |      16 |
|*  3 |    INDEX UNIQUE SCAN         | I_T_OBJECT_ID |    203 |   1001 |       |   178   (0)| 00:00:01 |    193 |00:00:00.01 |      12 |
----------------------------------------------------------------------------------------------------------------------------------------

--//可以發現starts=203,重覆值不會掃描的.

4.我的估計:
--//對方2000個集合寫死的,我估計可能是一個數組,初始賦值0(或者查詢不到的值),然後賦值.
--//通過這樣的方式實現綁定變數.
--//我個人還是趨向這樣的方式使用臨時表更加合理簡單一些.