Re: select from long raw very slow

On 4 Feb 2004 13:43:20 -0800, ewong_at_mail.com (Ed Wong) wrote:

>I am installing a 3rd db that is about 3gb big and with a few tables
>with long raw columns(yeah i know it sucks). After installation, the
>app preforms extremely slow. Upon investigation, it turns out the app
>is selecting a lot of data from a 2gb table with long raw.
>
>I tried doing "select *" on that table from sqlplus. It displays
>about 10 records per 5 seconds (even it only displays one-digit on
>that long raw column) which I think is extremely slow. Is this normal
>for long raw? If I do a "select *" on a table with clob, the display
>is pretty much flying.
>
>Thanks.

long raws are stored inline (ie in the record), CLOBs are stored out of line when bigger than 4k.
Consequently your record with long raws can chain over more than one block. Oracle doesn't take the chaining into account when it determines an access plan.
You can easily determine chaining by analyze table <blah> list chained rows into ... You must have run utlchain.sql connected as sys prior to doing so.

--
Sybrand Bakker, Senior Oracle DBA