ThatJeffSmith

SQL Developer Data Modeler Quick Tip: Preferred Data Types

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 eighty percent of the time.

Here is what the ‘TYPE’ control looks like in the modeler when defining a column:

Choice is great, but it shouldn't paralyze you.

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.

A few clicks can save you a lot of scrolling!

So now when I go to create or modify my attributes, I only see this when the ‘Preferred’ check box is ticked:

Now I can blaze through my table definitions