create table t1 (id integer, c_char char(10), c_varchar varchar2(10), c_clob clob);
insert into t1 values (1,'a','a','a');
insert into t1 values(2,'a ','a ','a ');
commit;
SQL> select id,c_char||'*' c_char, c_varchar||'*' c_varchar, c_clob||'*' c_clob from t1;
ID C_CHAR C_VARCHAR C_CLOB
_____ ______________ ____________ _________
1 a * a* a*
2 a * a * a *
exec dbms_stats.gather_table_stats('ADMIN','T1');
SQL> set autotrace on explain;
-- Test Case 1: Comparing char with char
SQL> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_char;
AID BID
______ ______
1 1
2 1
1 2
2 2
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID 75cmkzxq4wns2, child number 0
-------------------------------------
select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_char
Plan hash value: 1734879205
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 4 | 1610K| 1610K| 658K (0)|
| 2 | TABLE ACCESS FULL| T1 | 2 | | | |
| 3 | TABLE ACCESS FULL| T1 | 2 | | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."C_CHAR"="B"."C_CHAR")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
-- Test Case 2: Comparing char with varchar
SQL> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_varchar;
no rows selected
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID aqc24h6yv1ht1, child number 0
-------------------------------------
select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_varchar
Plan hash value: 1734879205
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 2 | 1610K| 1610K| 672K (0)|
| 2 | TABLE ACCESS FULL| T1 | 2 | | | |
| 3 | TABLE ACCESS FULL| T1 | 2 | | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."C_CHAR"="B"."C_VARCHAR")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
-- Test Case 3: Comparing char with clob
SQL> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_clob;
Error starting at line : 1 in command -
select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_clob
Error at Command Line : 1 Column : 57
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
SQL> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=to_char(b.c_clob);
no rows selected
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID 3kjn2rsrs04hc, child number 0
-------------------------------------
select a.id aid,b.id bid from t1 a, t1 b where
a.c_char=to_char(b.c_clob)
Plan hash value: 1734879205
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 4 | 1538K| 1538K| 758K (0)|
| 2 | TABLE ACCESS FULL| T1 | 2 | | | |
| 3 | TABLE ACCESS FULL| T1 | 2 | | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."C_CHAR"=TO_CHAR("B"."C_CLOB" /*+ LOB_BY_VALUE */ ))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
-- Test Case 4: Comparing varchar with varchar
SQL> select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=b.c_varchar;
AID BID
______ ______
1 1
2 2
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID 5yadtjau6uf6m, child number 0
-------------------------------------
select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=b.c_varchar
Plan hash value: 1734879205
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 2 | 2078K| 2078K| 671K (0)|
| 2 | TABLE ACCESS FULL| T1 | 2 | | | |
| 3 | TABLE ACCESS FULL| T1 | 2 | | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."C_VARCHAR"="B"."C_VARCHAR")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
-- Test Case 5: Comparing varchar with clob
SQL> select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=b.c_clob;
Error starting at line : 1 in command -
select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=b.c_clob
Error at Command Line : 1 Column : 60
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
SQL> select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=to_char(b.c_clob);
AID BID
______ ______
1 1
2 2
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID 3p9m03usr0prm, child number 0
-------------------------------------
select a.id aid,b.id bid from t1 a, t1 b where
a.c_varchar=to_char(b.c_clob)
Plan hash value: 1734879205
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 2 | 1922K| 1922K| 727K (0)|
| 2 | TABLE ACCESS FULL| T1 | 2 | | | |
| 3 | TABLE ACCESS FULL| T1 | 2 | | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."C_VARCHAR"=TO_CHAR("B"."C_CLOB" /*+ LOB_BY_VALUE */
))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
-- Test Case 6: Comparing clob with clob
SQL> select a.id aid,b.id bid from t1 a, t1 b where a.c_clob=b.c_clob;
Error starting at line : 1 in command -
select a.id aid,b.id bid from t1 a, t1 b where a.c_clob=b.c_clob
Error at Command Line : 1 Column : 48
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
SQL> select a.id aid,b.id bid from t1 a, t1 b where to_char(a.c_clob)=to_char(b.c_clob);
AID BID
______ ______
1 1
2 2
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID 5bnzyaqqdhfbu, child number 0
-------------------------------------
select a.id aid,b.id bid from t1 a, t1 b where
to_char(a.c_clob)=to_char(b.c_clob)
Plan hash value: 1734879205
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 1 | 1090K| 1090K| 410K (0)|
| 2 | TABLE ACCESS FULL| T1 | 2 | | | |
| 3 | TABLE ACCESS FULL| T1 | 2 | | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(TO_CHAR("A"."C_CLOB" /*+ LOB_BY_VALUE */
)=TO_CHAR("B"."C_CLOB" /*+ LOB_BY_VALUE */ ))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
No comments:
Post a Comment