Updated: July 16, 2020
There are many data types to choose from when defining your table columns. The eighty percent rule tells us we can probably get away with a smaller subset of data types, 8 times out of 10.
Here is what the ‘TYPE’ control looks like in the modeler when defining a column:
I was curious to see what data types were used most in my 11.2.0.3 database, so I fired off a quick query:
SELECT count(*), data_type FROM dba_tab_cols
WHERE data_type NOT LIKE ('%$%')
and owner not in ('SYS', 'SYSTEM')
GROUP BY data_type
order by 1 desc;
COUNT DATA_TYPE +++++ ++++++++++++++++++++++++++ 30089 VARCHAR2 11562 NUMBER 2553 DATE 2310 RAW 509 CLOB 176 CHAR 170 BLOB 161 XMLTYPE 160 TIMESTAMP(6) 68 XMLTYPEPI 54 NVARCHAR2 50 TIMESTAMP(6) WITH TIME ZONE 39 ROWID 34 XMLTYPEEXTRA 23 SharedValueType754_T 23 SDO_GEOMETRY 22 ANYDATA 21 STRINGLIST 17 MGMT_JOB_VECTOR_PARAMS 15 FLOAT
Want to learn how to copy result sets out of SQL Developer WITH the column headers?
Instead of seeing all 100+ types when designing your model, what if you could…
Create a ‘Favorites’ list of data types
Yes you can!
In the preferences for v3.1 you can establish your ‘preferred’ data types.
So now when I go to create or modify my attributes, I only see this when the ‘Preferred’ check box is ticked:
While you’re here, let’s save you ONE Extra Click PER New Column Definition!
Most of you aren’t using DOMAINs, yet. So in the meantime, tell the Modeler to switch you over to Logical types by default.
5 Comments
Hi Jeff – this did work and I’m working on a new project and all my data types on a new model have become unknown today. I’ve gone in and I no longer have ANY data types to add in the preferred selection column above (20.3)
HELP!
Please post your issue/scenario to the Modeler forums.
Hello to every one, it’s actually a nice for me to pay a quick visit this website, it
contains precious Information.
In data modeler is it possible to change the Data Type of multiple columns at once? I.e. From a table high light 10 columns and change all of the to varchar2(50) (from varchar2(25))?
No.
But you can still do what you want – your answer is at the very bottom.