-
We have huge table in production with a LONG column in it. We are having some performance issues around this (due the volume and access frequencies). There are some storage concerns too. We are running Oracle 8.1.5.1 on Solaris 2.6. Here are my questions:
1. Oracle recommends that LONG column be converted to LOBs. What would be the performance impact of this? Are we better off staying on LONGs till they are de-supported?
2. There were some posting on Metalink siting problems with conversions of LONGs to LOBs resulting in 2 to 3 times more storage. Is this true? DO we have to use OCI programs for the conversion to be space efficient?
3. Does the access path to LONG and LOB data by oracle involve the same costs (rows/fetch, fetch/row etc)?
4. Do LONGs cols go through the buffer cache and if so what re the merits/demerits.
SOrry to cram in so many questions - but the bottom line is that it would be great to know what the issues might be if we convert from LONG to LOB. Thanks a lot !!
Reg
-
1. Oracle recommends that LONG column be converted to LOBs. What would be the performance impact of this? Are we better off staying on LONGs till they are de-supported?
Even though Oracle recommends LOB in place of LONG, I saw many 3rd applications still use LONG. Even Oracle uses LONG data type in the SYS Schema (8.1.7.4).
Performance impact can be measured at your environment only. You will be better of with LONG data type as long as it is supported. You might switch to LOB when you do not find a single LONG column in the SYS schema.
2. There were some posting on Metalink siting problems with conversions of LONGs to LOBs resulting in 2 to 3 times more storage. Is this true? DO we have to use OCI programs for the conversion to be space efficient?
LOB requires more storage. Oracle manual clearly says how to convert LONG into LOB. I do not find any problem.
3. Does the access path to LONG and LOB data by oracle involve the same costs (rows/fetch, fetch/row etc)?
This is the amount of data stored in the column. For better performance, increase DB_BLOCK_SIZE or maintain a separate tablespace for LOB columns and also consider chunk size while creating the object.
A separate tablespace for LOB reduces I/O calls since all the SQLs may not require LOB columns data.
4. Do LONGs cols go through the buffer cache and if so what re the merits/demerits.
Yes. LONG data also goes through buffer cache.
