BLOB select

[deulyd]

Hi,

I have a the folowing table :

FOLDERSATTACH
----------------------------------------------
ORIGREC NUMBER (8) PK
DESC VARCHAR2 (255)
FILENAME VARCHAR2 (255)
FOLDERNO NUMBER (8)
ATTACH BLOB (4000)

It contains around 200 records with files in the BLOB field around 1 Mb.

The select on that table are REALLY slow! Even if I dont refer to the BLOB field (see examples)... Any clues?

Thanks

Daniel


Examples :

SELECT ORIGREC
FROM FOLDERSATTACH
----------------------------
< 1 second (this one is fast)


SELECT ORIGREC
FROM FOLDERSATTACH
WHERE FOLDERNO > 3000
-----------------------------
around 6 seconds


SELECT COUNT(ORIGREC)
FROM FOLDERSATTACH
----------------------------
around 7 seconds


SELECT ORIGREC
FROM FOLDERSATTACH
ORDER BY ORIGREC
----------------------------
around 10 seconds

 

[deulyd]

Ok I solved the problem.

Before being a BLOB field, the ATTACH field was a LONG RAW with lot of data. The table was totally fragmented.

1. I backed up all the data
2. Drop and recreate the table
3. Restore the information.

all the select mentionned in the previous post run in less than a second!

Daniel