Hello All,
I have a query that selects data based on a date context. I have created a user-defines function that reads the value from sys-context and returns the date set. The query then returns all records that are less than this date value.
A strange thing I noticed is that if I use a literal then I get an accurate cardinality, if I use SYSDATE then the query plan shows the cardinality very close to actual. However, when I used my function it does not show the right cardinality.
How is this possible? Isin't SYSDATE also a function. Can I make my function work the same way as SYSDATE?
With Literal (exact plan)
==================================
SQL> select count(*) from pe_v where '02-FEB-2012' between version_valid_from an
d version_valid_until;
10833
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 342rn8gypb93a, child number 0
-------------------------------------
select count(*) from pe_v where '02-FEB-2012' between
version_valid_from and version_valid_until
Plan hash value: 3877472449
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| PE_V | 10833 | 169K| 9 (0)| 00:01:51 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("VERSION_VALID_UNTIL">='02-FEB-2012' AND
"VERSION_VALID_FROM"<='02-FEB-2012'))
21 rows selected.
With SYSDATE (the ROWS are close to the count)
==========================================================
SQL> select count(*) from pe_v where sysdate between version_valid_from and vers
ion_valid_until;
10832
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 48trqfu5262pt, child number 0
-------------------------------------
select count(*) from pe_v where sysdate between version_valid_from and
version_valid_until
Plan hash value: 3877472449
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| PE_V | 10792 | 168K| 9 (0)| 00:01:51 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("VERSION_VALID_UNTIL">=SYSDATE@! AND
"VERSION_VALID_FROM"<=SYSDATE@!))
21 rows selected.
With user-defined function (incorrect cardinality)
===========================================================
CREATE OR REPLACE
FUNCTION f_business_date
RETURN DATE PARALLEL_ENABLE DETERMINISTIC
IS
BEGIN
RETURN TO_DATE(SYS_CONTEXT ('APP_USR_CTX', 'BUSINESS_DATE'),'DD.MM.YYYY HH24:MI:SS');
END f_business_date;
/
SQL> select count(*) from pe_v where f_business_date between version_valid_from
and version_valid_until;
10832
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID f0t0af00ft6m7, child number 0
-------------------------------------
select count(*) from pe_v where f_business_date between
version_valid_from and version_valid_until
Plan hash value: 3877472449
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| PE_V | 27 | 432 | 9 (0)| 00:01:51 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("VERSION_VALID_FROM"<="F_BUSINESS_DATE"() AND
"VERSION_VALID_UNTIL">="F_BUSINESS_DATE"()))
21 rows selected.