Skip to Main Content
  • Questions

  • Function in predicate produces different execution plan

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Gary.

Asked: December 10, 2007 - 4:29 pm UTC

Last updated: December 12, 2007 - 12:08 pm UTC

Version: 9.2.0.4

Viewed 1000+ times

You Asked

Hi Tom

Greetings from the great state of Indiana home of the Indiana Oracle Users Group (INOUG), you need to come back and visit.

DB Version: 9.2.0.4 on UNIX

I have a simple query that uses what turns out to be a constant on the right side of the predicate. In one case the constant is hard coded as '107337' and in the other it is derived using a function that converts an Oracle date to a number. The number represents a quasi Julian date and the conversion function does little more than a TO_NUMBER( TO_CHAR( <Oracle Date>, '<format>' ) ).

The table being queried is quite large (300+ million rows), hence the desire to limit the query with the date selection.

I am seeing a very different execution plan from the two versions of the query. The first one with the hard coded number shows a Bitmap Index Range Scan and then a table access by ROWID. This makes sense and the query has a good cost and runs quickly.

The other query that uses the conversion function to create the number does a FULL TABLE SCAN on the very large table.

There is a bitmap index on the date column (SDUPMJ) and my query goes back against the table so I could see the table access method. Both the table and the index have had their statistics updated recently.

We have several other table access that use this same function to convert the Oracle date to the quasi Julian date and they also have an index on their date columns. The other queries seem to work fine and do the same index scan and table access by ROWID as I would expect this one to do. However ...

Here is the output from the two (2) runs showing the Explain Plan output.

Any comments on why the plans are different and / or why the other scripts that use the same logic work as expected would be greatly appreciated.

=========

indgww@jdeprod> host more f42119_hard.sql
explain plan for
select count(distinct sduorg) cnt
from proddta.f42119
where sdupmj > 107337

/

indgww@jdeprod> @f42119_hard.sql

Explained.

indgww@jdeprod> @$ORACLE_HOME/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4415 (0)|
| 1 | SORT GROUP BY | | 1 | 9 | |
| 2 | TABLE ACCESS BY INDEX ROWID | F42119 | 22101 | 194K| 4415 (0)|
| 3 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 4 | BITMAP INDEX RANGE SCAN | F42119_SDUPMJ_BM | | | |
----------------------------------------------------------------------------------------

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

4 - access("F42119"."SDUPMJ">107337)
filter("F42119"."SDUPMJ">107337)

16 rows selected.

indgww@jdeprod>


=======================================

indgww@jdeprod> host more f42119_func.sql
explain plan for
select count(distinct sduorg) cnt
from proddta.f42119
where sdupmj > proddta.dcdttoju( sysdate -3 )

/

indgww@jdeprod> @f42119_func.sql

Explained.

indgww@jdeprod> @$ORACLE_HOME/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 165K|
| 1 | SORT GROUP BY | | 1 | 9 | |
|* 2 | TABLE ACCESS FULL | F42119 | 516K| 4535K| 165K|
--------------------------------------------------------------------

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

2 - filter("F42119"."SDUPMJ">"PRODDTA"."DCDTTOJU"(SYSDATE@!-3))

Note: cpu costing is off

15 rows selected.

indgww@jdeprod>


=======================================

indgww@jdeprod> 
select proddta.dcdttoju( sysdate -3 ) from dual;


PRODDTA.DCDTTOJU(SYSDATE-3)
---------------------------
107337

1 row selected.

indgww@jdeprod>


=======================================

indgww@jdeprod> 
select table_name, num_rows, last_analyzed
  2  from all_tables
  3  where owner = 'PRODDTA'
  4    and table_name = 'F42119';


TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
F42119 10321280 01-DEC-07


===============

Thanks very much for your help.

-gary

Doh! I was in too big a hurry to see how you are using the tags. Sorry.

and Tom said...

Ok, this is pretty easy to diagnose -

you have a pair of predicates:

where sdupmj > 107337

| 2 | TABLE ACCESS BY INDEX ROWID | F42119 | 22101 | 194K| 4415 (0)|

where sdupmj > proddta.dcdttoju( sysdate -3 )

|* 2 | TABLE ACCESS FULL | F42119 | 516K| 4535K| 165K|


see the different estimated cardinalities, with the hard coded value - it is guessing 22,101 rows. With the function - which is a big unknown - at parse time, we have NO insight into what it might return at all - we have 516k (which is amazingly enough - 5% of 10,321,280 (516,064 is 5%). It *guessed*, it had to guess, it had no option but to guess.

And, given your data, it said "for 5% of the rows, no way do I want to have the overhead of the index"

I would suggest losing the PLSQL call in there - for the query may well invoke that plsql function millions of times and that would be "expensive", either

where column >= ( select f(x) from dual )

to let scalar subquery caching kick in or, my preference, just get rid of the plsql call altogether and do the to_char right inline.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A few more examples

Gary Wicke, December 11, 2007 - 9:17 am UTC

Hi Tom

Thanks for pointing me to the obvious, number of rows expected in the Explain Plan, flat overlooked it.

I ran a couple more examples using different predicate methods and am a little confused by what I'm seeing. I tried the same SQL with:
1) a scalar query from DUAL calling the same date conversion procedure
2) a combination of TO_NUMBER and TO_CHAR to simulate the PL/SQL procedure

From the output of the Explain Plan (shown below) it appears both methods are expecting the same 517,000 rows (5% of the table) being returned as when calling the PL/SQL procedure. However they both are using the index (F42119_SDUPMJ_BM) to access the table. Here are the SQL statements and the Explain Plan output:

1)explain plan for
select count(distinct sduorg) cnt
from proddta.f42119
where sdupmj > (select proddta.dcdttoju(sysdate -3) from dual)

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 97147 (0)|
| 1 | SORT GROUP BY | | 1 | 9 | |
| 2 | TABLE ACCESS BY INDEX ROWID | F42119 | 517K| 4549K| 97147 (0)|
| 3 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 4 | BITMAP INDEX RANGE SCAN | F42119_SDUPMJ_BM | | | |
| 5 | TABLE ACCESS FULL | DUAL | 8168 | | 11 (0)|
----------------------------------------------------------------------------------------


2)explain plan for
select count(distinct sduorg) cnt
from proddta.f42119
where sdupmj > to_number('1' || to_char(sysdate -3, 'RRDDD'))


----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 97147 (0)|
| 1 | SORT GROUP BY | | 1 | 9 | |
| 2 | TABLE ACCESS BY INDEX ROWID | F42119 | 517K| 4549K| 97147 (0)|
| 3 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 4 | BITMAP INDEX RANGE SCAN | F42119_SDUPMJ_BM | | | |
----------------------------------------------------------------------------------------


Any other comments are greatly appreciated.

-gary
Tom Kyte
December 11, 2007 - 9:37 am UTC

it would be of interest to see the output from dbms_xplan for the other query too - utlxplp is not showing us stuff.


use dbms_xplan for the other one.

I'm missing something with DBMS_XPLAN

Gary Wicke, December 11, 2007 - 2:23 pm UTC

Tom

I tried running several versions of DBMS_XPLAN.DISPLAY with different values for the 'format' parameter to try and get more detail but all I could muster was the same output as from utlxplp.sql. What am I missing?

-gary
Tom Kyte
December 11, 2007 - 9:31 pm UTC

don't use any format. 

ops$tkyte%ORA9IR2> @plan "select * from scott.emp"
ops$tkyte%ORA9IR2> delete from plan_table;

2 rows deleted.

ops$tkyte%ORA9IR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select * from scott.emp

Explained.

ops$tkyte%ORA9IR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   448 |     2 |
|   1 |  TABLE ACCESS FULL   | EMP         |    14 |   448 |     2 |
--------------------------------------------------------------------

Note: cpu costing is off

9 rows selected.

Output using DBMS_XPLAN

Gary Wicke, December 12, 2007 - 8:07 am UTC

Hi Tom

Here is the output using DBMS_XPLAN for the four (4) test cases I've been running.

Many thanks for your assistance.

-gary

==========================================

Run using the function directly:

indgww@jdeprod> host more f42119_func.sql
explain plan for
select count(distinct sduorg) cnt
from proddta.f42119
where sdupmj > proddta.dcdttoju( sysdate -3 )

/

indgww@jdeprod> @f42119_func.sql

Explained.

indgww@jdeprod> 
select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 166K|
| 1 | SORT GROUP BY | | 1 | 9 | |
|* 2 | TABLE ACCESS FULL | F42119 | 517K| 4549K| 166K|
--------------------------------------------------------------------

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

2 - filter("F42119"."SDUPMJ">"PRODDTA"."DCDTTOJU"(SYSDATE@!-3))

Note: cpu costing is off

15 rows selected.

==========================================

Run using the hard coded number:

indgww@jdeprod> host more f42119_hard.sql
explain plan for
select count(distinct sduorg) cnt
from proddta.f42119
where sdupmj > 107337

/

indgww@jdeprod> @f42119_hard.sql

Explained.

indgww@jdeprod> 
select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 12021 (0)|
| 1 | SORT GROUP BY | | 1 | 9 | |
| 2 | TABLE ACCESS BY INDEX ROWID | F42119 | 60396 | 530K| 12021 (0)|
| 3 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 4 | BITMAP INDEX RANGE SCAN | F42119_SDUPMJ_BM | | | |
----------------------------------------------------------------------------------------

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

4 - access("F42119"."SDUPMJ">107337)
filter("F42119"."SDUPMJ">107337)

16 rows selected.

==========================================

Run using the TO_NUMBER( TO_CHAR( <date> )):

indgww@jdeprod> host more f42119_tochar.sql
explain plan for
select count(distinct sduorg) cnt
from proddta.f42119
where sdupmj > to_number('1' || to_char(sysdate -3, 'RRDDD'))

/

indgww@jdeprod> @f42119_tochar.sql

Explained.

indgww@jdeprod> 
select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 97147 (0)|
| 1 | SORT GROUP BY | | 1 | 9 | |
| 2 | TABLE ACCESS BY INDEX ROWID | F42119 | 517K| 4549K| 97147 (0)|
| 3 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 4 | BITMAP INDEX RANGE SCAN | F42119_SDUPMJ_BM | | | |
----------------------------------------------------------------------------------------

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

4 - access("F42119"."SDUPMJ">TO_NUMBER('1'||TO_CHAR(SYSDATE@!-3,'RRDDD')))
filter("F42119"."SDUPMJ">TO_NUMBER('1'||TO_CHAR(SYSDATE@!-3,'RRDDD')))

16 rows selected.

==========================================

Run using the function in a scalar query:

indgww@jdeprod> host more f42119_inline.sql
explain plan for
select count(distinct sduorg) cnt
from proddta.f42119
where sdupmj > (select proddta.dcdttoju(sysdate -3) from dual)

/

indgww@jdeprod> @f42119_inline.sql

Explained.

indgww@jdeprod> 
select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 97147 (0)|
| 1 | SORT GROUP BY | | 1 | 9 | |
| 2 | TABLE ACCESS BY INDEX ROWID | F42119 | 517K| 4549K| 97147 (0)|
| 3 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 4 | BITMAP INDEX RANGE SCAN | F42119_SDUPMJ_BM | | | |
| 5 | TABLE ACCESS FULL | DUAL | 8168 | | 11 (0)|
----------------------------------------------------------------------------------------

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

4 - access("F42119"."SDUPMJ"> (SELECT /*+ */ "PRODDTA"."DCDTTOJU"(SYSDATE@!-3) FROM
"SYS"."DUAL" "DUAL"))
filter("F42119"."SDUPMJ"> (SELECT /*+ */ "PRODDTA"."DCDTTOJU"(SYSDATE@!-3) FROM
"SYS"."DUAL" "DUAL"))

19 rows selected.


Tom Kyte
December 12, 2007 - 9:19 am UTC

I really wish the CODE button would be used :( the explain plan output is virtually unreadable.


the difference from the above is that CPU costing was disabled in the first query

where column > plsql_function


when cpu costing is on, it used the bitmap.


That said, there is NO WAY you want to use a plsql function here, or if you do, you definitely want to use the scalar subquery always (to enable scalar subquery caching - in 11g, you'll be able to use a cache directive when compiling your plsql to achieve even better results and forgo the scalar subquery)


My question to you however is why cpu costing is even being used in the first place - you have non-default optimizer parameters set? That makes it hard for anyone outside of your database to say "why" things happen.


but in all cases - use the scalar subquery.

CPU Costing - followup

Gary Wicke, December 12, 2007 - 9:46 am UTC

Hi Tom

Sorry about the CODE tags. I was so buried in the trees of this issue I didn't take the time to really see how you were using them. From now on ... ;-)>

I am a little confused by the CPU costing issue and I will go talk to the DBA in charge of this system. I did read the Performance Tuning Guide but couldn't find much on cpu costing. I'll check both your books and Jonathan's when I get home tonight.

I ran the four (4) scripts above one after the other with no changes to the system behind them.

1- Why was cpu-costing turned off for only the version that used the PL/SQL function directly? (I'm assuming the CBO decided to not use cpu costing)

2- How would the DBA have set the cpu costing parameter away from the default?

3- What is the default setting anyway? :-)

I will take steps to use either the direct in-line TO_NUMBER( TO_CHAR()) functions or the scalar sub-query in the Production versions of these.

Thanks as always for your assistance.

-gary
Tom Kyte
December 12, 2007 - 12:08 pm UTC

in 9i, it would be unusual to see cpu costing
in 10g, it is the default

but even in 10g, 11g - the results were the same.

when using plsql like that - ALWAYS use the scalar subquery approach.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.