Oracle internal_function tips

Oracle Database Tips by Donald BurlesonJanuary 28, 2015

Question: I see a strange item in my SQL execution plan, something called internal_function:

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("R"=1)

2 - filter(RANK() OVER ( PARTITION BY "DEPARTMENT_ID" ORDER BY

INTERNAL_FUNCTION("SALARY") DESC )<=1)

What is this internal_function? Is the internal_function important for SQL tuning?

Answer: I have seen the internal_function when Oracle SQL must do an implicit data type conversion, like changing a character to a number. It is not a significant overhead in SQL execution performance.

While Oracle does not reveal their internals, the Oracle documentation notes that internal_function is used in datatype conversions and gives this example of internal_function when converting a timestamp datatype:

EXPLAIN PLAN FOR
SELECT SUM(quantity_sold)
FROM sales
WHERE time_id = TO_TIMESTAMP('1-jan'2000', 'dd-mon-yyyy');

Because time_id is of type DATE and Oracle needs to promote it to the TIMESTAMP type to get the same datatype, this predicate is internally rewritten as:

TO_TIMESTAMP(time_id) = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')

The explain plan for this statement shows the internal_function::

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(INTERNAL_FUNCTION("TIME_ID")=TO_TIMESTAMP('1-jan-2000',:B1))

Get the Complete
Oracle SQL Tuning Information

The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

��