Types of columns

Just participated C.J.Date two day seminar in 10 hours today. Thanks to the ash from Iceland. Monday was delayed.

Just a small thing picked up from the massive amount of information. To avoid type conversions behind the scenas while doing natural joins avoid using different types for columns named similarily. Just checking that

select * from (
select count(distinct data_type
||'|'||cast(data_length as varchar2(30))
||'|'||cast(data_precision as varchar2(30))
||'|'||cast(data_scale as varchar2(30))
) over (partition by column_name) as dis
, table_name
, column_name
, data_type
, data_length
, data_precision
, data_scale
from user_tab_columns
where dis > 1

Well on the other hand do not use select *. As an analogy I would suggess not to use natural joins. Mr Date suggested to use views on to of base tables. That makes sense. And a good thing here user_tab_columns has also the columns in views included.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.