Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 03, 2021 - 6:05 am UTC

Last updated: February 05, 2021 - 1:52 pm UTC

Version: 11204

Viewed 100+ times

You Asked

Hi Team ,

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                                |       |       |     4 (100)|          |       |       |
|   1 |  SORT AGGREGATE                        |                                |     1 |    54 |            |          |       |       |
|   2 |   NESTED LOOPS                         |                                |     3 |   162 |     4   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                        |                                |     3 |   162 |     4   (0)| 00:00:01 |       |       |
|   4 |     PARTITION HASH SINGLE              |                                |     3 |   108 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID|  TABLE1                        |     3 |   108 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  6 |       INDEX RANGE SCAN                 | IDX_TABLE1                     |    27 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   7 |     PARTITION HASH ALL                 |                                |     1 |       |     1   (0)| 00:00:01 |     1 |    64 |
|*  8 |      INDEX RANGE SCAN                  | IDX_TABLE2                     |     1 |       |     1   (0)| 00:00:01 |     1 |    64 |
|*  9 |    TABLE ACCESS BY LOCAL INDEX ROWID   | TABLE2                         |     1 |    18 |     1   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(("TABLE1"."PART_T_TYPE"=:SYS_B_3 AND "TABLE1"."DFLG"=:SYS_B_4 AND "TABLE1"."PFLG"=:SYS_B_5))
   6 - access("TABLE1"."AIC"=:1)
   8 - access("TABLE1"."TRIAN_ID"="TABLE2"."TRIAN_ID")
   9 - filter((INTERNAL_FUNCTION("DEL_ID") AND "TABLE2"."COL_ID"=:2))


Need to know cause of internal function for DEL_ID column

select count(*)
from TABLE1 a ,TABLE2 b
 where a.train_id= b.train_id  ===> datatype is same for both joining columns
 and a.aic = 'AaaaaaQWESQ'  
 AND (DEL_ID) in ('OOO','QQQ')====> causing internal function , datatype is varhcar2(10)
 and a.part_t_type ='L' 
 and a.dflg='N'
 and a.pflg='Y'
 and a.col_ID=b.col_ID 
 and a.col_ID= 'QWE'


manual execution : select count(*) from TABLE2 b where (DEL_ID) in ('OOO','QQQ') actual plan shows not internal function
TABLE2 is hash partitioned table b.train_id is partitioning key

and we said...

An internal function is often because there's an implicit conversion in the query somewhere.

What is the data type of DEL_ID? Can you share the DDL for all the tables?

Rating

  (4 ratings)

Comments

Chuck Jolley, February 03, 2021 - 6:55 pm UTC

I'm sure it's been suggested before, but a setting to cause implicit conversions to fail at compile time would be a nice addition.

Chris Saxon
February 03, 2021 - 7:29 pm UTC

Yep, many people have asked for that!

plsql_warnings

Rajeshwaran, Jeyabal, February 04, 2021 - 11:46 am UTC

If this sql is part of PL/SQL code, then plsql compiler can produce warning during compilation for datatype conversions.

demo@ORA11G> create table t
  2  nologging
  3  as
  4  select owner,object_type,to_char(object_id) object_id
  5  from all_objects ;

Table created.

demo@ORA11G> create or replace procedure do_some_work
  2  authid current_user
  3  as
  4  begin
  5      for x in ( select * from t
  6          where object_id = 55)
  7      loop
  8          null;
  9      end loop;
 10  end;
 11  /

Procedure created.

demo@ORA11G> alter procedure do_some_work compile plsql_warnings='enable:all';

SP2-0805: Procedure altered with compilation warnings

demo@ORA11G> show err
Errors for PROCEDURE DO_SOME_WORK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/15     PLW-07204: conversion away from column type may result in
         sub-optimal query plan

demo@ORA11G>


Chris Saxon
February 04, 2021 - 4:25 pm UTC

Good point

Chrishna0007@gmail.com, February 05, 2021 - 3:36 am UTC

Del_id is varchar2 and observer implicit is not because datatype conversion but due to col_id columns
In both table value is passed for table 1 col_id1 but in plan it shows value for colid2 when I has out table1.colid =table2.colid no internal conversion takes place
Chris Saxon
February 05, 2021 - 1:52 pm UTC

I'm unsure what you mean - please clarify.

And please share the DDL for your tables - or a simplified example that shows the same issue.

Chuck Jolley, February 05, 2021 - 12:42 pm UTC

"alter procedure do_some_work compile plsql_warnings='enable:all';"
I had no idea that existed.
Thanks!

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.