0

I have an application currently working on the SQL Server 2012 and Microsoft stack.

I have a lot of images and documents which are required to be versioned and stored. Since the database was SQL Server, I was using Filetable & Filestream for storing the images and documents. Storing like is also meant I could work with SQL Server full text search for basic document search.

Now, I need to support Oracle also as a database. I wanted to check what would be the best way to ensure feature parity.

Based on my basic research BFile datatype should provide the similar features for storing the documents (except that the files are not a part of the database backup)

If I were to use BFile,

  • Can the BFile be configured to be stored the documents on any network access share?
  • Can OracleText be used to indexing and searching the content of the documents?

Are there any other database independent alternatives to storing the images and documents?

Since the deployment will be on-premise I cannot use s3 or any other cloud based solution.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sharath Chandra
  • 654
  • 8
  • 26
  • I don't have a lot of experience with lobs, but I do know they can be stored in the database, which I would recommend over any external file mechanism. For the very reason that it gets all of the backup and recovery protection of the database. I'd point you to the Ask Tom article at https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14212348049 – EdStevens Aug 06 '16 at 20:20
  • If the SQL Server version stores the images in the database, why wouldn't you do the same thing in the Oracle version? A `bfile` is just a pointer. Given that Oracle has no way of knowing when the operating system file changes, indexing anything that's not in the database would be problematic. – Justin Cave Aug 07 '16 at 05:36
  • @JustinCave, I am using SQL server's file-table capability. Here the metadata is stored on the database and the file itself is stored on the file system. The entire set of operations are managed by SQL server. Regarding the indexing, since the files are managed by SQL server, SQL provides some in-built iFIlters for files lile pdf which helps in configuring the full text search. I am looking at similar thing on the oracle side. – Sharath Chandra Aug 07 '16 at 13:38
  • @EdStevens, In my understanding of the SQL world, database is suitable for storing images/documents whose size is anywhere b/w 256 - 512 KB. Bigger files, its always better to store the files on the external storage. (https://msdn.microsoft.com/en-us/library/hh461480.aspx) – Sharath Chandra Aug 07 '16 at 13:43
  • @SharathChandra - your understanding of the limits/recommendations for MSSQL are null and void for Oracle. The two databases are profoundly different in more ways than you can imagine. In many, many ways, what constitutes "best practice" in one is "worst practice" in the other. In oracle, the limit on a blob in the database is (4 gig -1) * DB_BLOCK_SIZE (See the Database Reference manual). The standard block size is 8k, but you can go up to 32k blocksize, and you can have different block size for different tablespaces. You do the math.. – EdStevens Aug 07 '16 at 23:03
  • @EdStevens, thanks. Your suggestion is to store the images and documents directly as BLOB datatypes within the database itself? The images are anyway b/w 512 KB - 1 MB and documents 1 MB - 10 MB. – Sharath Chandra Aug 08 '16 at 08:24
  • @SharathChandra - you are asking for my recommendation and I gave it to you in my first post. Yes, store the blobs in the database. The sizes you mention are just peanuts. Just make sure you read the ORACLE documentation on how to do it. Do not try to approach it with a MSSQL mindset or you will -- at best -- come up with a sub-optimall implementation. – EdStevens Aug 08 '16 at 21:39
  • @EdStevens, thanks a lot for the headsup – Sharath Chandra Aug 09 '16 at 14:20

0 Answers0