I should have noticed you were using long raws. I would definitely move to BLOBS if your system supports it. The lob segment should be in a different tablespace than the data segment. This also allows you to name the lob segment which is quite useful. I would suggest a 4MB uniform extent for the lob segment tablespace. Remember Oracle does not read data according to the extent size. You may want to use a 32k chunk size. More data is written/read that way, but it there is more wasted space. But I cannot say for sure.
Do talk to the Vendor about using BLOB's. They may be able to suggest the best lob storarge parameters for your situation. What was the problem when you tried to use a BLOB? Were you receiving Oracle errors? I am very new at using BLOBS as well. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -----Original Message----- Sent: Thursday, March 07, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Sorry about being vague about what I am trying to do, I will try to explain it better. We are implementing a GIS system called Arc/Info, thats allows us to store,manipulate spatially referenced data, one of the "layers" of information is orthophoto's, these are rectified aerial photographs that are scalable. Each ortho covers an area of 1600m by 1000m on the ground with a pixel resolution of .25m, each source tiff image is approx 25Mb in size. During my initial testing of loading one image at a time, it appeared to created table size of approx 32Mb, hence my decision to use a uniform extent of 32Mb. Arc/Info creates one table per image, and the "raster" data is stored in the "long raw" column. It appears that the table can have anywhere from 8000 to 9000 rows, with an avg_row_len of 3467 Bytes, I am assuming as with lobs, Arc/Info has the ability to merge all your images together into one "layer" giving you a seamless layer, it also creates a series of "pyramids" which are resampled versions of the base layer at a larger pixel resolution. This helps speed up the drawing on the screen when you are at a smaller scale (1:1,000,000). If you have 4 source images, they are merged into a single table, with multiple rows, the avg_row_len becomes 13261. The table that the information is stored in has the following structure : RASTERBAND_ID NOT NULL NUMBER(38) RRD_FACTOR NOT NULL NUMBER(38) ROW_NBR NOT NULL NUMBER(38) COL_NBR NOT NULL NUMBER(38) BLOCK_DATA NOT NULL LONG RAW As you can see, the block_data field is currently defined as a "long raw". It does have the option of using a LOB, which prompts the question what is better long raw or LOB ? will have to some RTFM on that. Unfortunately I cannot get the LOB option working, time to phone support on that one. Where I am stuck, is figuring out is how to best configure my tablespace, I realize there is no "right" way to do it. I think 32Mb is incorrect, as this represents an image with the pyramids built, I will have to test loading an image that has no pyramids. Should I set an extent that represents an average of the orthophotos, say 25Mb, or some lesser value, and have more extents. From your example, I am assuming I should use a smaller extent size, maybe 5Mb, use the LOB option (Once I can get it working!) , and have multiple tablespaces. Darren -----Original Message----- Sent: March 6, 2002 6:53 PM To: Multiple recipients of list ORACLE-L I'm trying to understand this. How many records are in the table? Is it four? If so 115,437,005 / 4 gives an avg_row_len of about 27.5 MB. AVG_ROW_LEN for a table does not include the lob segment; the number is terribly high. What is your table definition including the definition of your lob segment? Here's one of mine, slightly altered. The database version is 8.1.6.3 CREATE TABLE x (FILE_ID NUMBER(7,0) NOT NULL ,FILE_TYPE VARCHAR2(1) NOT NULL ,FILE_FORMAT VARCHAR2(1) NOT NULL ,FILE_NAME VARCHAR2(80) NOT NULL ,FILE_PRIVATE VARCHAR2(1) NOT NULL ,COMMENTS VARCHAR2(240) ,FILE_DATA BLOB ,DATE_CREATED DATE NOT NULL ,CREATED_BY VARCHAR2(30) NOT NULL ,DATE_UPDATED DATE ,UPDATED_BY VARCHAR2(30) ,CONTENT_TYPE VARCHAR2(240) ,CONTENT_DISPOSITION VARCHAR2(240) Lob(FILE_DATA) STORE AS loby (TABLESPACE zzzzzzz PCTVERSION 5 ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING) The bp_files table is in a tablespace with locally managed extents with a uniform size 0f 128K. The lob segment is in a locally managed tablespace with 4 MB uniform extents. Sizes of the individual lobs vary with the largest one being just over 15 MB The avg_row_len of bp_files is 201 bytes. The size of the lob segment according to debasements is 742,391,808 bytes whereas SELECT SUM(dbms_lob.getlength(file_data) from x is 729,736,842. The difference between these numbers averages less than 7 KB per BLOB. It appears you are trying to apply table physical attribute parameters to the lob segment. Take a look at pctversion defined in the docs as .... The maximum percentage of overall LOB storage space used for creating new versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used. You would set your pctversion to 0 if no updates will take place. Ian -----Original Message----- Sent: Wednesday, March 06, 2002 9:54 AM To: Multiple recipients of list ORACLE-L I am in the process of storing 32Mb image files into a 8.1.7 database, I have set pct_free to 0, but I left pct_used at 90 and the tablespace is locally managed with a uniform extent of 32Mb. The database has a db_block_size of 8k. I am considering moving it to 16k, as opposed to 32k, as it has a mix of smaller files ( > 8k, < 7Mb) and then the larger tables ( 32 Mb). When I loaded 4 images (to create one image), and analyzed the tables, I received the following output. Size (numrow*avg_row_len) = 115,437,005 Bytes (Segments) = 167,772,160 Blocks = 20,480 Extents = 5 As you can see, I am wasting 50Mbs of space for 4 images. I realize that if I add another image to the larger one, I most likely only waste 18 Mbs. I have figured out that this wastage is due to the pct_used parameter. After re-re-re-reading the documents, I should have set the pct_used higher then 90. Question : Should I set pct_used to 100 or 99 ? Once these images are loaded,the tablespace will be set to READONLY. No updates will occur. Thanks Darren ---------------------------------------------------------------------------- ---------------------------------------------- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] ---------------------------------------------------------------------------- ----------------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
