Hi,
Referring to the documentation of ROWIDTOCHAR https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions138.htm
I feel that "cast" does the trick of converting rowid into varchar2 type, if so I would like to learn as to why did Oracle create a separate function for this?
Apart from this please point to any specific examples where this function is used.
--Oracle Version 11.2.0.4
select cast (rowid as varchar2(18)) from dual
union
select rowidtochar (rowid) from dual; --- results in single row (as expected both are same.)
----> Followup :
Did some performance testing on huge table :
And interesting to see that ROWIDTOCHAR performs better than cast..
---Tested on Exdata Oracle version : 11.2.0.4
SET TIMING ON
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH shared_pool;
------
SELECT COUNT (CAST (ROWID AS VARCHAR2 (18)))
FROM BIG_TABLE r
WHERE ROWNUM <= 100000000000;
-----
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH shared_pool;
-----
SELECT COUNT (ROWIDTOCHAR(rowid))
FROM BIG_TABLE r
WHERE ROWNUM <= 100000000000;
Results :
---------------
System altered.
Elapsed: 00:00:00.20
System altered.
Elapsed: 00:00:00.19
COUNT(CAST(ROWIDASVARCHAR2(18)))
--------------------------------
10471775
Elapsed: 00:00:03.51
System altered.
Elapsed: 00:00:00.21
System altered.
Elapsed: 00:00:00.19
COUNT(ROWIDTOCHAR(ROWID))
-------------------------
10471775
Elapsed: 00:00:02.21
SQL>
May be because of this ? (if someone throws some light on this I would close this thread, thanks!)
Cheers,
Manik.
Message was edited by: Manik Added followup based on certain observations.