Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Abraham.

Asked: November 24, 2016 - 9:53 pm UTC

Last updated: June 13, 2018 - 1:50 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hello.

I've seen this code 
"sysdate@!"
used in a program, and i became curios, as I couldn't find any documentation of it.
From what I saw, both give the same result:

select sysdate@!, sysdate
from dual;


So my question is: What is this function? Does it have any features that are different from SYSDATE?



and Connor said...

Yes, it is quite different.

xxx@!

"use xxx from the database I came from"

So if I was doing something along the lines of:

select sysdate, ... from some_object@remote_db_link;

then on the *remote* database, you might see in the V$SQL table, a reference to "sysdate@!", which is saying "I want the sysdate from the database I came from"

So it's an internal facility - you would never need to code it in your own applications.

Rating

  (4 ratings)

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

Comments

A reader, November 25, 2016 - 10:27 am UTC


Abraham Olsen, November 25, 2016 - 10:38 am UTC


Why there is INTERNAL_FUNCTION (implicit datatype conversion)

Samir, June 06, 2018 - 8:15 am UTC

In 11gr2, In predicates information of one of my query I can see "INTERNAL_FUNCTION" is invoked on column CREATE_TIME. Datatype of column CREATE_TIME is date. "INTERNAL_FUNCTION" is because of "SYSDATE@!" ? If I create index on column CREATE_TIME, will it be used(doubt is because of INTERNAL_FUNCTION)


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1830571993

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    31 |  1261K  (1)| 04:12:18 |
|   1 |  SORT AGGREGATE     |          |     1 |    31 |            |          |
|   2 |   NESTED LOOPS      |          | 38255 |  1158K|  1261K  (1)| 04:12:18 |
|*  3 |    TABLE ACCESS FULL| TABLE_A  | 38255 |   933K|  1255K  (1)| 04:11:09 |
|*  4 |    INDEX RANGE SCAN | IND_TABLE_B |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   3 - filter("A"."STATUS"=U'SUC' AND
              TRUNC(INTERNAL_FUNCTION("A"."CREATE_TIME"))=TRUNC(SYSDATE@!-1))
   4 - access("A"."ID"="B"."ID")

18 rows selected.

Connor McDonald
June 07, 2018 - 1:50 am UTC

I'm not convinced the data type is date. We will try to align the datatypes for comparison if they do not match, eg

SQL> create table t
  2    ( c1 date,
  3      c2 timestamp,
  4      c3 timestamp with time zone ,
  5      c4 varchar2(30)
  6      );

Table created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where c1 = trunc(sysdate-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    54 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    54 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C1"=TRUNC(SYSDATE@!-1))

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

SQL> select * from t where c2 = trunc(sysdate-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    54 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    54 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C2"=TRUNC(SYSDATE@!-1))

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

SQL> select * from t where c3 = trunc(sysdate-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    54 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    54 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(SYS_EXTRACT_UTC("C3")=SYS_EXTRACT_UTC(TRUNC(SYSDATE@!-1)))

SQL> select * from t where c4 = trunc(sysdate-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    54 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    54 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(INTERNAL_FUNCTION("C4")=TRUNC(SYSDATE@!-1))

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


Why there is INTERNAL_FUNCTION (implicit datatype conversion)

Samir, June 12, 2018 - 8:02 am UTC

Thanks Connor for the simple test case. I am using the same test case to explain my concern. I am using trunc function on column c1/c2/c3 as per my original query. trunc will give an error for c4 as datatype is varchar2 which implies that datatype of column CREATE_TIME in my original query is Date.

SQL> create table t
   ( c1 date,
       c2 timestamp,
       c3 timestamp with time zone ,
       c4 varchar2(30)
      );  2    3    4    5    6

Table created.

SQL> set autotrace traceonly explain
SQL> select * from t where trunc(c1)= trunc(sysdate-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    54 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    54 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TRUNC(INTERNAL_FUNCTION("C1"))=TRUNC(SYSDATE@!-1))

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>
  2
SQL> select * from t where trunc(c2)= trunc(sysdate-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    54 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    54 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TRUNC(INTERNAL_FUNCTION("C2"))=TRUNC(SYSDATE@!-1))

Note
-----
   - dynamic sampling used for this statement (level=2)


SQL> select * from t where trunc(c3)= trunc(sysdate-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    54 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    54 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TRUNC(INTERNAL_FUNCTION("C3"))=TRUNC(SYSDATE@!-1))

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> select * from t where trunc(c4)= trunc(sysdate-1);
select * from t where trunc(c4)= trunc(sysdate-1)
                               *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE


Afer this this test case, again my concern, Why there is INTERNAL_FUNCTION (implicit datatype conversion)? If I create index on column CREATE_TIME, will it be used(doubt is because of INTERNAL_FUNCTION)
Connor McDonald
June 13, 2018 - 1:50 am UTC

TRUNC will often lead to (silent) conversions, eg

SQL> create table t ( c timestamp with time zone );

Table created.

SQL> insert into t values ( systimestamp );

1 row created.

SQL> commit;

Commit complete.

SQL> select dump(c) from t;

DUMP(C)
--------------------------------------------------------
Typ=181 Len=13: 120,118,6,13,2,44,53,59,78,126,192,28,60

1 row selected.

SQL> select dump(trunc(c)) from t;

DUMP(TRUNC(C))
--------------------------------------------------------
Typ=13 Len=8: 226,7,6,13,0,0,0,0

1 row selected.


which is why you're seeing "internal_function".

In all cases, I don't like using TRUNC at all. For me, I would phrase the queries as:

where date_col >= trunc(sysdate-1)
and   date_col <  trunc(sysdate)


and for timestamps

where ts_col >= cast(trunc(sysdate-1) as timestamp)
and   ts_col <  cast(trunc(sysdate) as timestamp)



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.