Home » RDBMS Server » Performance Tuning » Clob column on remote database (Oracle 9i)
Clob column on remote database [message #361728] Thu, 27 November 2008 17:48 Go to next message
Hidden
Messages: 4
Registered: October 2008
Location: Poland
Junior Member
Hello,

I'm trying to create a view linking two identical tables located on two remote databases. My tables looks like this:

CREATE TABLE post (
   postID INTEGER,
   postURL VARCHAR2(200),
   postDate VARCHAR2(19),
   posTitle VARCHAR2(200),
   postContent CLOB
);


I found some solution:

on remote site:
CREATE VIEW post_remote AS
   SELECT postID, postURL, postDate, postTitle,
   dbms_lob.getlength(postContent) AS postlength FROM post;


on local site:

CREATE VIEW post_local AS
   SELECT postID, postURL, postDate, postTitle,
   dbms_lob.getlength(postContent) AS postlength FROM post
UNION
   SELECT postID, postURL, postDate, postTitle,
   postlength FROM post_remote@remote;


Now trying to run a perl script, the connection is established (earlier I always got this error that it's impossible to select a clob column from the remote site), but even the smallest query takes for example 250 seconds to finish (both sites are located in local network so a brandwith shouldn't be problem, but still the content of the tables is around 1.4gb).

Is there any easier way to link both tables and get acceptable response for following query:

SELECT postID FROM post_local WHERE postLength >=4000 AND
    ROWNUM <=10 ORDER BY postLength DESC


I would prefere no to declare dbms_lob.getlength() but when I tried to use dbms_lob.substr() there was a problem with varchar2 type.

please help
Re: Clob column on remote database [message #361811 is a reply to message #361728] Fri, 28 November 2008 02:32 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Can you use UNION ALL instead of UNION?
Re: Clob column on remote database [message #361855 is a reply to message #361728] Fri, 28 November 2008 04:57 Go to previous message
Hidden
Messages: 4
Registered: October 2008
Location: Poland
Junior Member
Currently I'm trying to optimize the query, following the hints mentioned in Admin's Guide for 9i and I just found some tips about UNION ALL. But there is a restriction not to use ROWNUM is SELECT query then using UNION ALL. To be honest this is not an option for me, because I've got to select the accurate quantity of rows from the local and remote database.

For example, if the UNION ALL statement would look this:

CREATE VIEW post_view AS
SELECT * FROM post WHERE postID < 100000
UNION ALL
SELECT * FROM post@remote WHERE postID >= 100000;


Can I then execute the following query?:

SELECT postID FROM post_view WHERE LENGTH(postContent) <= 500 AND ROWNUM <= 100 ORDER BY LENGTH(postContent);


And again what about the CLOB column, there is still the need to use dbms_lob. (getlength() for example) instead of postContent but according to Admin's Guide I have to select * from local statement instead of listing all columns out. What is more the columns names and columns type must be the same, so should I create another view in the local database to hide dbms_lob function on the local table?

That's to many problems and there is no guaranty that it's gonna work...

[Updated on: Fri, 28 November 2008 04:59]

Report message to a moderator

Previous Topic: 'is Null' versus '='
Next Topic: ORACLE V$SQL issue ....
Goto Forum:
  


Current Time: Mon Dec 05 02:37:40 CST 2016

Total time taken to generate the page: 0.04149 seconds