I have a problem saving images into an Oracle 8 database. I saved images as LONGRAW datatype, but it needs a long time to retrieve the image from my archiving system. What is the best way to make this faster?
Instead of using LONGRAW, use BLOB. LOB datatypes (BLOB, LOB, BFILE) are more appropriate choices and much more flexible than the older LONGRAW datatype.
For example, suppose you have a table with the following definition:
CREATE TABLE long_tab ( id NUMBER, long_col LONG RAW);Create a new table using the following SQL statement:
CREATE TABLE lob_tab ( id NUMBER, clob_col BLOB);Issue an INSERT statement using the TO_LOB function to insert the data from the table with the LONG datatype into the table with the LOB datatype.
For example, issue the following SQL statement:
INSERT INTO lob_tab SELECT id, TO_LOB(long_col) FROM long_tab;Note: When you create the new table, make sure you preserve the table's schema, including integrity constraints, triggers, grants, and indexes. The TO_LOB function only copies data; it does not preserve the table's schema.
That will get your data into the new table format using a BLOB. Your next step is to read up on how to work with BLOB datatypes. You'll need to get familiar with with DBMS_LOB package for starters. See the Oracle Supplied PL/SQL Packages Reference.
You should get much better overall performance and flexibility.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.