Re: How to pass bind variable value into a view
Date: Fri, 9 Oct 2009 08:13:51 -0700 (PDT)
Message-ID: <561aabaa-70e3-4e43-8d46-9a13e2a5692d_at_r36g2000vbn.googlegroups.com>
On Oct 8, 2:54 pm, UXDBA <unixdb..._at_googlemail.com> wrote:
> All, > > RDBMS : 9.2.0.8 > (snip) > question: > > a) Is there any way we can pass bind variable to the inner query with > using view v1 ( i know BIND > var cannot be passed to DDL)but any other way you would suggest to > achieve the same result. > > Regards
I think that we need a full test case to verify what you are seeing.
I had to change the table names slightly due to existing objects with
those names:
CREATE TABLE TT1 AS
SELECT
ROWNUM COL1,
CEIL(ROWNUM/2000) COL2,
LPAD('A',200,'A') COL3
FROM
DUAL
CONNECT BY
LEVEL<=10000000;
CREATE TABLE TT2 AS
SELECT
ROWNUM COL1,
CEIL(ROWNUM/2000) COL2,
LPAD('A',200,'A') COL3
FROM
DUAL
CONNECT BY
LEVEL<=10000000;
CREATE UNIQUE INDEX IND_TT1 ON TT1(COL1);
CREATE INDEX IND_TT2 ON TT2(COL2);
EXEC DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME=>USER,TABNAME=>'TT1',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME=>USER,TABNAME=>'TT2',CASCADE=>TRUE)
CREATE OR REPLACE VIEW VV1 (
VV1_COL1,
VV1_COL2)
AS
SELECT
TT1.COL1,
TT2.COL2
FROM
TT1,
TT2
WHERE
TT1.COL1=TT2.COL2;
VARIABLE N1 NUMBER
EXEC :N1:=1000
SELECT
*
FROM
VV1
WHERE
VV1.COL1=:N1;
ERROR at line 6:
ORA-00904: "VV1"."COL1": invalid identifier
Notice that Oracle returned an error as the columns have been aliased by the view.
SELECT
*
FROM
VV1
WHERE
VV1.VV1_COL1=:N1;
VV1_COL1 VV1_COL2
---------- ----------
1000 1000
1000 1000
1000 1000
...
2000 rows selected.
SET LINESIZE 120
SET PAGESIZE 2000
SPOOL C:\VV1_PLAN1024.TXT
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));
SPOOL OFF
Plan hash value: 2691474176
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |00:00:01 |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| | | 1 | NESTED LOOPS | | 1986 | 19860 | 8 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| IND_TT1 | 1 | 6 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_TT2 | 1986 | 7944 | 6 (0)|
Predicate Information (identified by operation id):
2 - access("TT1"."COL1"=:N1)
3 - access("TT2"."COL2"=:N1)
The Predicate Information shows that Oracle did push in the value of the N1 bind variable into the view.
SHOW PARAMETER OPTIMIZER
NAME TYPE VALUE ------------------------------------ ----------- -------- optimizer_dynamic_sampling integer 2 optimizer_features_enable string 10.2.0.4 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='9.2.0';
SELECT
*
FROM
VV1
WHERE
VV1.VV1_COL1=:N1;
VV1_COL1 VV1_COL2
---------- ----------
1000 1000
1000 1000
1000 1000
...
2000 rows selected.
SPOOL C:\VV1_PLAN920.TXT SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL)); SPOOL OFF Plan hash value: 2691474176
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |00:00:01 |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| | | 1 | NESTED LOOPS | | 1986 | 19860 | 9 (12)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| IND_TT1 | 1 | 6 | 3 (34)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_TT2 | 1986 | 7944 | 7 (15)|
Predicate Information (identified by operation id):
2 - access("TT1"."COL1"=:N1)
3 - access("TT2"."COL2"=:N1)
Notice that the plan is identical when the optimizer is pretending to be compatible with Oracle 9.2.0.x.
SHOW PARAMETER OPTIMIZER
NAME TYPE VALUE ------------------------------------ ----------- ------ optimizer_dynamic_sampling integer 1 optimizer_features_enable string 9.2.0 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string CHOOSE optimizer_secure_view_merging boolean TRUE
Now for the other version of the query:
SELECT
*
FROM
(SELECT
TT1.COL1,
TT2.COL2
FROM
TT1,
TT2
WHERE
TT1.COL1=TT2.COL2
AND TT1.COL1=:N1);
COL1 COL2
---------- ----------
1000 1000
1000 1000
1000 1000
...
2000 rows selected.
SPOOL C:\VV1_PLAN920-2.TXT SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL)); SPOOL OFF Plan hash value: 2691474176
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |00:00:01 |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| | | 1 | NESTED LOOPS | | 1986 | 19860 | 9 (12)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| IND_TT1 | 1 | 6 | 3 (34)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_TT2 | 1986 | 7944 | 7 (15)|
Predicate Information (identified by operation id):
2 - access("TT1"."COL1"=:N1)
3 - access("TT2"."COL2"=:N1)
Same plan as was seen for the other SQL statement. Let's check the
optimizer at release 10.2.0.4:
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.4';
SELECT
*
FROM
(SELECT
TT1.COL1,
TT2.COL2
FROM
TT1,
TT2
WHERE
TT1.COL1=TT2.COL2
AND TT1.COL1=:N1);
COL1 COL2
---------- ----------
1000 1000
1000 1000
1000 1000
...
2000 rows selected.
SPOOL C:\VV1_PLAN1024-2.TXT SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL)); SPOOL OFF Plan hash value: 2691474176
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |00:00:01 |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| | | 1 | NESTED LOOPS | | 1986 | 19860 | 8 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| IND_TT1 | 1 | 6 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_TT2 | 1986 | 7944 | 6 (0)|
Predicate Information (identified by operation id):
2 - access("TT1"."COL1"=:N1)
3 - access("TT2"."COL2"=:N1)
Same plan again. The above DBMS_XPLAN.DISPLAY_CURSOR function
requires a minimum of Oracle 10.1. For 9.2.0.x you should be able to
used the following method:
EXPLAIN PLAN FOR
SELECT
*
FROM
(SELECT
TT1.COL1,
TT2.COL2
FROM
TT1,
TT2
WHERE
TT1.COL1=TT2.COL2
AND TT1.COL1=:N1);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2691474176
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |00:00:01 |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1986 | 19860 | 8 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1986 | 19860 | 8 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| IND_TT1 | 1 | 6 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_TT2 | 1986 | 7944 | 6 (0)|
Predicate Information (identified by operation id):
2 - access("TT1"."COL1"=TO_NUMBER(:N1)) 3 - access("TT2"."COL2"=TO_NUMBER(:N1))
Notice the problem in the Predicate Information above with explain plan when numeric bind variables are used, which may lead to inaccurate execution plans. The DBMS_XPLAN.DISPLAY_CURSOR function pulls the actual execution plan from the library cache.
EXPLAIN PLAN FOR
SELECT
*
FROM
VV1
WHERE
VV1.VV1_COL1=:N1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2691474176
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |00:00:01 |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1986 | 19860 | 8 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1986 | 19860 | 8 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| IND_TT1 | 1 | 6 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_TT2 | 1986 | 7944 | 6 (0)|
Predicate Information (identified by operation id):
2 - access("TT1"."COL1"=TO_NUMBER(:N1)) 3 - access("TT2"."COL2"=TO_NUMBER(:N1))
Same plan again, even when testing with
OPTIMIZER_FEATURES_ENABLE='9.2.0'.
It might be helpful to verify that you have not made an error in the
analysis due to table and column names. Try the above test case to
see if the same plan in received in all cases.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Oct 09 2009 - 10:13:51 CDT