In my test below, character comparisons are just as fast as numbers - no armchair computer science needed. Also note, implicit conversion isn't a very big deal.
- Jack
SQL> ------------------------
SQL> -- Create input table 1.
SQL> ------------------------
SQL>
SQL> create table temp_01_tb
2 tablespace x_ts
3 nologging
4 parallel 16
5 as
6 select id as id_num,
7 cast(to_char(id) as varchar2(30)) as id_char
8 from (
9 select trunc(dbms_random.value(100000000,100000000000)) id
10 from large_tb
11 where rownum <= 10000000
12 )
13 ;
Table created.
Elapsed: 00:02:14.39
SQL>
SQL> ---------
SQL> -- Count.
SQL> ---------
SQL>
SQL> select count(*) from temp_01_tb;
COUNT(*)
----------------
10,000,000
Elapsed: 00:00:00.81
SQL>
SQL> -------------------------------------
SQL> -- Freq length of id_num and id_char.
SQL> -------------------------------------
SQL>
SQL> break on report
SQL> compute sum of count(*) on report
SQL>
SQL> select length(id_num),
2 length(id_char),
3 count(*)
4 from temp_01_tb
5 group by length(id_num),
6 length(id_char)
7 order by 1
8 ;
LENGTH(ID_NUM) LENGTH(ID_CHAR) COUNT(*)
---------------- ---------------- ----------------
9 9 89,854
10 10 901,563
11 11 9,008,583
----------------
sum 10,000,000
Elapsed: 00:00:01.41
SQL>
SQL> clear breaks
SQL> clear computes
SQL>
SQL> ----------
SQL> -- Sample.
SQL> ----------
SQL>
SQL> select *
2 from temp_01_tb
3 where rownum <= 10
4 ;
ID_NUM ID_CHAR
---------------- ------------------------------
86,969,995,517 86969995517
57,700,034,749 57700034749
98,820,424,831 98820424831
85,003,779,624 85003779624
9,644,826,478 9644826478
88,393,740,235 88393740235
99,155,106,367 99155106367
26,633,943,599 26633943599
67,287,256,584 67287256584
29,916,845,470 29916845470
10 rows selected.
Elapsed: 00:00:00.17
SQL>
SQL> ------------
SQL> -- Describe.
SQL> ------------
SQL>
SQL> desc temp_01_tb
Name Null? Type
----------------------------------- -------- ------------------------
ID_NUM NUMBER
ID_CHAR VARCHAR2(30)
SQL>
SQL> ------------------------
SQL> -- Create input table 2.
SQL> ------------------------
SQL>
SQL> create table temp_02_tb
2 tablespace x_ts
3 nologging
4 parallel 16
5 as
6 select id as id_num,
7 cast(to_char(id) as varchar2(30)) as id_char
8 from (
9 select trunc(dbms_random.value(100000000,100000000000)) id
10 from large_tb
11 where rownum <= 10000000
12 )
13 ;
Table created.
Elapsed: 00:02:14.14
SQL>
SQL> ---------
SQL> -- Count.
SQL> ---------
SQL>
SQL> select count(*) from temp_02_tb;
COUNT(*)
----------------
10,000,000
Elapsed: 00:00:00.70
SQL>
SQL> -------------------------------------
SQL> -- Freq length of id_num and id_char.
SQL> -------------------------------------
SQL>
SQL> break on report
SQL> compute sum of count(*) on report
SQL>
SQL> select length(id_num),
2 length(id_char),
3 count(*)
4 from temp_02_tb
5 group by length(id_num),
6 length(id_char)
7 order by 1
8 ;
LENGTH(ID_NUM) LENGTH(ID_CHAR) COUNT(*)
---------------- ---------------- ----------------
9 9 90,332
10 10 901,092
11 11 9,008,576
----------------
sum 10,000,000
Elapsed: 00:00:01.41
SQL>
SQL> clear breaks
SQL> clear computes
SQL>
SQL> ----------
SQL> -- Sample.
SQL> ----------
SQL>
SQL> select *
2 from temp_02_tb
3 where rownum <= 10
4 ;
ID_NUM ID_CHAR
---------------- ------------------------------
54,067,209,027 54067209027
5,979,558,060 5979558060
72,269,951,072 72269951072
62,556,776,987 62556776987
16,990,890,984 16990890984
47,303,283,438 47303283438
70,490,427,076 70490427076
49,336,811,237 49336811237
68,728,531,845 68728531845
3,126,925,174 3126925174
10 rows selected.
Elapsed: 00:00:00.16
SQL>
SQL> ------------
SQL> -- Describe.
SQL> ------------
SQL>
SQL> desc temp_02_tb
Name Null? Type
----------------------------------- -------- ------------------------
ID_NUM NUMBER
ID_CHAR VARCHAR2(30)
SQL>
SQL> -----------------------
SQL> -- Part II. Run tests.
SQL> -----------------------
SQL>
SQL> alter table temp_01_tb parallel 1;
Table altered.
Elapsed: 00:00:00.02
SQL> alter table temp_02_tb parallel 1;
Table altered.
Elapsed: 00:00:00.00
SQL>
SQL> --------------------
SQL> -- Test num compare.
SQL> --------------------
SQL>
SQL> select count(*)
2 from temp_01_tb a,
3 temp_02_tb b
4 where a.id_num = b.id_num
5 ;
COUNT(*)
----------------
993
Elapsed: 00:00:29.43
SQL>
SQL> ---------------------
SQL> -- Test char compare.
SQL> ---------------------
SQL>
SQL> select count(*)
2 from temp_01_tb a,
3 temp_02_tb b
4 where a.id_char = b.id_char
5 ;
COUNT(*)
----------------
993
Elapsed: 00:00:29.12
SQL>
SQL> ----------------------------
SQL> -- Test num to char compare.
SQL> ----------------------------
SQL>
SQL> select count(*)
2 from temp_01_tb a,
3 temp_02_tb b
4 where a.id_num = b.id_char
5 ;
COUNT(*)
----------------
993
Elapsed: 00:00:30.98
SQL>
SQL> --------------------------
SQL> -- Test num compare again.
SQL> --------------------------
SQL>
SQL> select count(*)
2 from temp_01_tb a,
3 temp_02_tb b
4 where a.id_num = b.id_num
5 ;
COUNT(*)
----------------
993
Elapsed: 00:00:24.84
SQL>
SQL> ---------------------------
SQL> -- Test char compare again.
SQL> ---------------------------
SQL>
SQL> select count(*)
2 from temp_01_tb a,
3 temp_02_tb b
4 where a.id_char = b.id_char
5 ;
COUNT(*)
----------------
993
Elapsed: 00:00:25.70
SQL>
SQL> ----------------------------------
SQL> -- Test num to char compare again.
SQL> ----------------------------------
SQL>
SQL> select count(*)
2 from temp_01_tb a,
3 temp_02_tb b
4 where a.id_num = b.id_char
5 ;
COUNT(*)
----------------
993
Elapsed: 00:00:27.99
SQL>
SQL> spool off
October 27, 2009 - 12:19 pm UTC
... Also note, implicit conversion isn't a very
big deal.
...
there you are wrong. If you compare a string to string 1,000,000 times or a number to a number 1,000,000 times - that'll take about the same time.
But compare a string that has to be converted into a number 1,000,000 times and forget it. Very different.
Your example has a quirk in it that you did a hash join, the actual thing you were testing was such a small contributor to the overall runtime that you cannot see that.
so, just testing what we want to test:
ops$tkyte%ORA10GR2> create table t
2 as
3 select rownum num, to_char(rownum) str
4 from dual
5 connect by level <= 1000000
6 /
Table created.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte%ORA10GR2> select count(*) from t where num = 0;
COUNT(*)
----------
0
ops$tkyte%ORA10GR2> select count(*) from t where str = '0';
COUNT(*)
----------
0
ops$tkyte%ORA10GR2> select count(*) from t where num = '0';
COUNT(*)
----------
0
ops$tkyte%ORA10GR2> select count(*) from t where str = 0;
COUNT(*)
----------
0
select count(*) from t where num = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.05 0 2342 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.05 0 2342 0 1
********************************************************************************
select count(*) from t where str = '0'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.03 0 2342 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 0 2342 0 1
********************************************************************************
select count(*) from t where num = '0'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.03 0 2342 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 0 2342 0 1
********************************************************************************
select count(*) from t where str = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.28 0.27 0 2342 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.28 0.27 0 2342 0 1
In the last query, the implicit conversion was of the column to a number - 1,000,000 times and that is expensive.