| Clob column on remote database [message #361728] |
Thu, 27 November 2008 17:48  |
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 #361855 is a reply to message #361728] |
Fri, 28 November 2008 04:57  |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
