Hello,

we are planning to move our mails from local clients (like Thunderbird)
into the database.

We experienced long search times with the local clients doing fulltext
search and I guess this would be same if we store the messages as simple
varchar() records.

Does oracle come with a special email record type that includes
attachments and fulltext index? How would you recommend to store our
mails into the database?

Regards
Marten

Marten Lehmann wrote:
I do hope that would be varchar2...

And no - Oracle is an RDBMS, not an email server, and does not
know email record types.
I would think oracle stores the email in CLOBs (Character Large
OBjects), attachments in BLOBs (Binary Large Objs). Both
can be indexed, using OracleText.

Of course, you could buy an Oracle Collaboration Suite licence,
which includes Oracle's email implementation - better than Exchange,
so they say.
From what I hear, OCS is remarkably cheap for the product. Not sure
if OracleText is included. Just give your Oracle Rep a call.

Oh - and varchar2 objects can be text indexed as well; guess
the majority of mails fits in 4000 characters (using a decent
mail program, that produces plain text), like TBird
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Hello,

well, I just thought that some databases have special record types for
objects, 3d coordinates, barcodes etc.; so maybe some database has
RFC822 as well.

There is another point: MOst emails are indeed pure text, so there the
size could be reduced very much using compression. But I don't know if
it is possible to compress the mail on the one hand and have a fulltext
index on the other.

Well, we don't need Exchange or a Collaboration Suite as we are
developing an own special collab/ticket software customized to our needs
which integrates perfectly into our own CRM software.

I wouldn't swear that all messages have a size of less than 4000
characters. Surely, most messages are smaller, but what shall I do with
messages greater than that? Is the maximum limit of varchar2 really 4k?
I cannot imagine that. I've seen much greater values at MySQL,
Postgresql or Sybase.

Regards
Marten

On Nov 27, 2:38 pm, Marten Lehmann <lehmannmap... (AT) cnm (DOT) de> wrote:

Limits are documented: http://download.oracle.com/docs/cd/B...htm#sthref4180

From http://dev.mysql.com/doc/refman/6.0/en/char.html :

"The effective maximum length of a VARCHAR is subject to the maximum
row size (65,535 bytes, which is shared among all columns) and the
character set used."

Perhaps we can chalk this up to "the simpler tool is easier to use,
until it isn't."

jg
--
@home.com is bogus.
Yo mama! http://www.guardian.co.uk/g2/story/0...eed=technology

Marten Lehmann wrote:
3d coordinates are just numbers.
barcodes are just strings - your print font is off

Add a line# column; anything less than 4k: line 1, less than 8k,
two lines, etc etc.
That's why Oracle has CLOBs - would 4GB/column be sufficient?

Oh – this reply counts 1108 characters
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...