Re: using clob in where clause

On 1 Mar 2006 04:20:42 -0800, gupta.harika@googlemail.com wrote:

>I am a developer working on php with oracle as backend.
>I am facing a problem related with the CLOB data.
>The problem is as follows
>My application uses a table which contains Clob datatype.
>I need to do a search on this table based on the clob data,i.e., I am
>using the column which is a clob datatype in the where clause.
>The query is as follows
>" select * from mytable where (dbms_lob.instr(mybody, 'as') > 0)"
>If the result set has less records then the data gets displayed in the
>front end page of the application...
>If the result set contains more records and the query takes more that
>30 seconds to execute in the backend the data does not get displayed in
>the frontend page of the application.
>I get an error telling
>"Fatal error: Maximum execution time of 30 seconds exceeded."
>Can any one of you please help me out in solving this problem.

 Querying the contents of a CLOB like this involves full table scans, and even
worse, accessing all of each of the CLOBs. This is expensive, and will
inevitably take a long time.

 If you want to query for text within a CLOB more quickly, you should probably
consider using Oracle Text.

 Follow-ups set to comp.databases.oracle.server.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool