Ny point in undersizing VARCHAR columns

oracleperformancevarchar

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not.

I would like to give the question of VARCHAR size a little twist and hope to get some insight in this:

Given (multiline) free-text fields (not short stuff like names) that you want to store in an (Oracle) database, is there any point (wrt. performance or otherwise) in not maxing out the VARCHAR capacity (VARCHAR2(4000) on Oracle) but choosing a smaller value such as 1024 or 512 because that will likely be sufficient in 98% of the cases anyway.

Best Answer

It does impact memory usage, especially when a client program has to allocate enough memory to receive a dataset.

Bear in mind that a lot of apps (especially web apps) use UTF-8 which is a multi-byte character set. As such, you should really consider characters rather than bytes.

If I was expecting over a thousand characters, then I'd actively consider a CLOB. I'd be thinking about whether it will store plain text or some form of markup (wiki / html ?), usage with non-Euro languages. The Questions and Answers here, for example, would be CLOB, but comments can fit in a VARCHAR.

If you max out a VARCHAR, then in six months someone will want to make it bigger again, and you'd be kicking yourself for not using a CLOB.

Related Solutions

Oracle SQL count occurances of character in VARCHAR

What version of Oracle?

If you're using Oracle 11.1 or later, you can use REGEXP_COUNT

  1* select regexp_count( '11001110', '1' ) from dual
SQL> /

REGEXP_COUNT('11001110','1')
----------------------------
                           5

In earlier versions, there are various less elegant options such as

SQL> select length( '11001110' ) - length( replace( '11001110', '1' ) ) cnt
  2    from dual;

       CNT
----------
         5

Resolving frequent LGWR switch events recorded in Event log with heavy dblink/materialized view activity – Oracle 11g XE

Close, I think you should be concerned with the redo itself rather than the log switches it is causing.
I think this is because fast_start_mttr_target is set to zero, but again this should not be the issue to focus on.
Here is the heart of the matter:

Since you can't change the source database to have a materialized view log, every MV refresh has to delete all the records and re-insert them all, which creates your excessive redo. There are many different approaches you could take to this issue. Here are some.

Simply increase the redo log size. This will decrease the impact of frequent switches, but the redo will still be generated.
Roll your own Materialized View. This is more work, but would probably generate significantly less redo.
- Your refresh could Truncate the table and do an insert with the Append hint if you can tolerate the absence of data during the refresh.
- Drop the table and do a CTAS from the source with the append hint is another option that sacrifices availability for speed and reduced redo.
- If you need the data to be available you could selectively update and insert data based on what actually changed. This would introduce a bit more load on the source database, but would probably decrease your redo significantly.
- Depending on your data needs you might get away with only handling inserts above the last known sequence number, and/or updates when only certain significant columns have changes. The more custom the solution the less redo, but also the more work to maintain.
A more drastic approach would be to recover the tablespace involved to your second database periodically, but since you are using XE, that probably isn't an option.

Best Answer

Related Solutions

Oracle SQL count occurances of character in VARCHAR

Resolving frequent LGWR switch events recorded in Event log with heavy dblink/materialized view activity – Oracle 11g XE

Related Question