Skip to Main Content
  • Questions

  • PLW-07204: conversion away from column type may result in sub-optimal query plan

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dieter.

Asked: April 30, 2024 - 6:59 am UTC

Last updated: May 09, 2024 - 10:14 am UTC

Version: Oracle Database 19c

Viewed 1000+ times

You Asked

I have been wondering for a long time why a select like "SELECT COUNT(*) INTO l_count FROM foo WHERE c_now >= ts + 1" in the following example issues a PLW-07204 warning but for example "SELECT COUNT(*) INTO l_count FROM foo WHERE ts < c_now - 1" does not.

CREATE TABLE foo (ts DATE, tx VARCHAR2(30));

INSERT INTO foo VALUES (SYSDATE - 1, 'foo');
INSERT INTO foo VALUES (SYSDATE - 2, 'bar');
COMMIT;

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:7204';

CREATE OR REPLACE
FUNCTION new_foo RETURN NUMBER
IS
 c_now CONSTANT DATE := SYSDATE;
 l_count NUMBER;
BEGIN
 SELECT COUNT(*) INTO l_count FROM foo WHERE c_now >= ts + 1;
-- SELECT COUNT(*) INTO l_count FROM foo WHERE ts < c_now - 1;
 RETURN l_count;
END;
/
SELECT * FROM user_errors WHERE name = 'NEW_FOO';


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/bnxphyqabg1r03o4q7epgrhfp

and Connor said...

Easiest way is to see how these queries would run in isolation outside of PLSQL

SQL> CREATE TABLE foo (ts DATE, tx VARCHAR2(30));

Table created.

SQL>
SQL> INSERT INTO foo VALUES (SYSDATE - 1, 'foo');

1 row created.

SQL> INSERT INTO foo VALUES (SYSDATE - 2, 'bar');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> create index foo_ix on foo (ts);

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> SELECT COUNT(*) FROM foo WHERE sysdate >= ts + 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1342139204

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| FOO  |     2 |    18 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter(INTERNAL_FUNCTION("TS")+1<=SYSDATE@!)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> SELECT COUNT(*) FROM foo WHERE ts < sysdate - 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3578467503

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     9 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |        |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| FOO_IX |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("TS"<SYSDATE@!-1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Notice that one of them prohibits the use of an index because of the expression (+1) on the column side.

Rating

  (5 ratings)

Comments

Warning misleading

Dieter Oberkofler, May 07, 2024 - 8:14 am UTC

Thank you for the feedback but I'm still confused:

1) So the warning message "conversion away from column type" is basically wrong (or at least misleading) when suggesting that the column type has changed?
2) What is the suggested resolution when this kind of warning is shown?

Chris Saxon
May 08, 2024 - 3:19 pm UTC

See mathguy's replies and my response.

Conversion away from data type is not the same thing as applying a calculation

mathguy, May 08, 2024 - 6:10 am UTC

It is true that applying a calculation (like adding 1, meaning 1 day in this case) to an indexed column will cause the index on that column to not be used. (More generally, applying any kind of transformation - for example a function, like TRUNC.)

However, the warning talks explicitly about conversion away from column data type, which is not what happens here (or if we apply TRUNC). It would be really odd if Oracle wrote something explicitly about "data type" in a warning supposed to apply to all expressions a column may appear in (making an index on that column irrelevant).

Indeed, change the example so that the TS column is number data type, with the needed changes to the INSERT statements and the function code; keep TS + 1 on the right-hand side of the condition in SELECT. No error about "conversion away" appears anymore, even though an index on TS can't be used - for the same reason as for dates.

A data type conversion (implicit or explicit) applied to a column is one type of "transformation" that won't allow an index on that column to be used; but it is only one type. The warning is not thrown for other types of transformation; indeed, in the example using number data type (instead of date), no warning is thrown at all!

Dieter - regarding your "what can be done" question, whenever possible (and relevant), filters in SELECT queries (WHERE clause, join conditions, etc.) should be written so that columns that may be indexed appear unmodified on one side of an equality or inequality, rather than as part of an expression. Your re-writing of the condition, moving the + 1 part to the other side of the inequality, where it no longer affects the column reference, is exactly what is needed. Note that sometimes this is not possible, and other times it doesn't matter (if there isn't too much data so speed is not relevant, or if a column isn't indexed in the first place, etc.)
Chris Saxon
May 09, 2024 - 10:07 am UTC

It's not obvious from the example, but there is a data type conversion!

This is because there are two date representations in Oracle Database:

SELECT DUMP(TS), DUMP(TS+1), DUMP(TO_DATE(TS)) FROM foo WHERE sysdate >= ts + 1;

DUMP(TS)                              DUMP(TS+1)                            DUMP(TO_DATE(TS))                     
Typ=12 Len=7: 120,124,5,7,16,16,48    Typ=13 Len=8: 232,7,5,8,15,15,47,0    Typ=13 Len=8: 232,7,5,7,15,15,47,0    
Typ=12 Len=7: 120,124,5,6,16,16,48    Typ=13 Len=8: 232,7,5,7,15,15,47,0    Typ=13 Len=8: 232,7,5,6,15,15,47,0


Notice that the column is type 12, the column + 1 is type 13.

MOS note 69028.1 explains these in detail.

Better example (involving just an implicit conversion)

mathguy, May 08, 2024 - 7:07 am UTC

An example of what the warning was probably meant to be for:

 SELECT COUNT(*) INTO l_count FROM foo WHERE timestamp '2024-01-01 00:00:00' >= ts;


(replace the corresponding line in your code with this line)

The warning is emitted, even though the column reference TS appears unmodified on one side of the condition. This is because the comparison is between a date (TS) and a timestamp (on the other side of the inequality). This type of comparison requires that the date be re-cast as timestamp, so that there is no loss of information. So there is an implicit (and therefore not "visible") conversion of TS to another data type - exactly what the warning is about. **

In a case like this, if possible, the comparison of TS should be made against another date, not a timestamp. How it can be done in this particular example is irrelevant (it's easy), but the principle should be clear.

** Why the PL/SQL parser thinks this is happening in the example presented in the original post is unclear (probably a bug).

most interesting but still not 100% clear

Dieter Oberkofler, May 08, 2024 - 5:13 pm UTC

If I understood the latest comments the warning does actually not originate from a column that that cannot be used when indexed but rather an actual data type mismatch.
The best practices on how to use indices are clear and in my original example i did not use an index on purpose.
What is still a bit unclear, is why exactly Oracle implicitly converts the DATE columns type to a TIMESTAMP when adding 1 to the DATE column and by therefore causing this warning?
Chris Saxon
May 09, 2024 - 10:14 am UTC

Two things are going on:

COL + 1 => the optimizer can't use an index on COL; this happens whether the COL is a number, date or timestamp

DATE_COL + 1 => changes the DATE from type 12 to type 13. These are both still DATEs, not TIMESTAMPs. This is the type conversion warning you're getting - notice the INTERAL_FUNCTION call in the Predicate section of the plan.

Either way, the solution's the same: rearrange the formula so there are no functions applied to the column.

Different internal representations of DATE data type

mathguy, May 09, 2024 - 6:36 pm UTC

It's not obvious from the example, but there is a data type conversion!
This is because there are two date representations in Oracle Database:

OK, this explains the warning.

The "purist" objection to this is that "internal representation" is not the programmer's business; the column DATA TYPE (which is the programmer's business) is not being "converted away from". But, of course, in practice what matters to the optimizer is the physical representation of data, not its abstract meaning. And, to be completely fair, the warning talks about converting away from "column type", it doesn't explicitly say "data type". Now we can understand that too!

This raises an interesting question though - I didn't see it addressed in the documentation and I can't tell from the simplistic example I looked at. Perhaps it's in the MOS note (to which I don't have access).

Namely: suppose we have a comparison (equality, or inequality) where on the left-hand side we have a column reference for DATE data type (therefore "type 12"), and on the right-hand side we have something like SYSDATE, or a DATE literal (which the parser translates to a call to TO_DATE), or some other kind of expression (therefore "type 13"). Before a comparison is possible, one of the two must be "converted" to the "type" (internal representation) of the other.

When "stored" data type (internal presentation, "type 12") is "converted" to "type 13", we see the INTERNAL_FUNCTION invocation in the predicate section.

If we run something like 

select * from scott.emp where hiredate < to_date('2024-01-01', 'yyyy-mm-dd');


the predicate is shown as

HIREDATE<TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')


Note that here no "internal function" is applied to EITHER SIDE! This is even though the LHS is type 12 and the RHS is type 13.

I assume type 13 is converted to type 12 here (otherwise we would likely get the "conversion away from column type" warning again), but the predicate shows no indication of that - there is nothing similar to the application of INTERNAL_FUNCTION before an expression is calculated, when converting happens in the opposite direction.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library