dannykrouk
asked on
Accessing non-long raw columns from table with long raw over dblink
I've got a public dblink between two Oracle9i databases. There are two remote tables that I can query individually. However, when I specify a join between the two tables, I get this error: ORA-00997: illegal use of LONG datatype.
One of the tables does have a column defined as a long raw. I do not need that column; none of my queries make reference to it. So, my question is, "How can I accomplish this query, over a dblink, given that one table has a long raw that is not part of my query?"
Here's the offending query:
select f.numofpts, b.objectid
from joe.f33@mylink f
inner join joe.states@mylink b
on f.fid = b.shape;
from joe.f33@mylink f
*
ERROR at line 2:
ORA-00997: illegal use of LONG datatype
The table definitions appear at the bottom of this question.
My theory about what is going on is that the optimizer (set to choose) is deciding to bring the entire table over the link, instead of just the selected columns. In other words, it would be *as if* I had included "f.points" (the long raw column) in column list. However, that theory has not been born-out by my efforts to discover the execution plan.
I created a table idential to joe.f33 called joe.f33_minus_longraw ... which is the same table and data without the long raw column. Then, I traced a join query using this table instead of joe.f33. Needless to say, that query does execute without the error.
However, when I examine the execution plan in the trace, it does not support my theory that the optimizer is bringing the whole table over. The optimizer claims to only refer to the columns that I specify in my query. Witness:
Rows Row Source Operation
------- --------------------------
52 MERGE JOIN (cr=0 r=0 w=0 time=12498 us)
52 SORT JOIN (cr=0 r=0 w=0 time=4214 us)
52 REMOTE (cr=0 r=0 w=0 time=3798 us)
52 SORT JOIN (cr=0 r=0 w=0 time=4230 us)
52 REMOTE (cr=0 r=0 w=0 time=3833 us)
Rows Execution Plan
------- --------------------------
0 SELECT STATEMENT GOAL: CHOOSE
52 MERGE JOIN
52 SORT (JOIN)
52 REMOTE [MYLINK]
SELECT "OBJECTID","SHAPE" FROM "JOE"."STATES"
"B"
52 SORT (JOIN)
52 REMOTE [MYLINK]
SELECT "FID","NUMOFPTS" FROM "JOE"."F33_MINUS_LONGRAW" "F"
Then, I tried requesting a plan when I issue the query. I do not have much experience working with the plan table. And, my attempt to use the canned scripts to get information out resulted in the, somewhat perplexing result, below. The structure is the same as the session trace plan. But, it says that the optimizer mode was "rule". What is confusing about this is that the optimizer is set to "choose". So, I don't know what this is trying to tell me.
PLAN_TABLE_OUTPUT
--------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MERGE JOIN | | | | |
| 2 | SORT JOIN | | | | |
| 3 | REMOTE | | | | |
|* 4 | SORT JOIN | | | | |
| 5 | REMOTE | | | | |
--------------------------
PLAN_TABLE_OUTPUT
--------------------------
Predicate Information (identified by operation id):
--------------------------
4 - access("F"."FID"="B"."SHAP
filter("F"."FID"="B"."SHAP
Note: rule based optimization
TABLE DEFINITIONS:
JOE.F33@MYLINK:
Name Null? Type
--------------------------
FID NOT NULL NUMBER(38)
NUMOFPTS NOT NULL NUMBER(38)
ENTITY NOT NULL NUMBER(38)
EMINX NOT NULL FLOAT(64)
EMINY NOT NULL FLOAT(64)
EMAXX NOT NULL FLOAT(64)
EMAXY NOT NULL FLOAT(64)
EMINZ FLOAT(64)
EMAXZ FLOAT(64)
MIN_MEASURE FLOAT(64)
MAX_MEASURE FLOAT(64)
AREA NOT NULL FLOAT(64)
LEN NOT NULL FLOAT(64)
POINTS LONG RAW
JOE.STATES@MYLINK:
Name Null? Type
--------------------------
OBJECTID NOT NULL NUMBER(38)
AREA NUMBER(38,8)
STATE_NAME VARCHAR2(25)
STATE_FIPS VARCHAR2(2)
SUB_REGION VARCHAR2(7)
STATE_ABBR VARCHAR2(2)
SHAPE NUMBER(38)
JOE.F33_MINUS_LONGRAW@MYLI
Name Null? Type
--------------------------
FID NOT NULL NUMBER(38)
NUMOFPTS NOT NULL NUMBER(38)
ENTITY NOT NULL NUMBER(38)
EMINX NOT NULL FLOAT(64)
EMINY NOT NULL FLOAT(64)
EMAXX NOT NULL FLOAT(64)
EMAXY NOT NULL FLOAT(64)
EMINZ FLOAT(64)
EMAXZ FLOAT(64)
MIN_MEASURE FLOAT(64)
MAX_MEASURE FLOAT(64)
AREA NOT NULL FLOAT(64)
LEN NOT NULL FLOAT(64)
One of the tables does have a column defined as a long raw. I do not need that column; none of my queries make reference to it. So, my question is, "How can I accomplish this query, over a dblink, given that one table has a long raw that is not part of my query?"
Here's the offending query:
select f.numofpts, b.objectid
from joe.f33@mylink f
inner join joe.states@mylink b
on f.fid = b.shape;
from joe.f33@mylink f
*
ERROR at line 2:
ORA-00997: illegal use of LONG datatype
The table definitions appear at the bottom of this question.
My theory about what is going on is that the optimizer (set to choose) is deciding to bring the entire table over the link, instead of just the selected columns. In other words, it would be *as if* I had included "f.points" (the long raw column) in column list. However, that theory has not been born-out by my efforts to discover the execution plan.
I created a table idential to joe.f33 called joe.f33_minus_longraw ... which is the same table and data without the long raw column. Then, I traced a join query using this table instead of joe.f33. Needless to say, that query does execute without the error.
However, when I examine the execution plan in the trace, it does not support my theory that the optimizer is bringing the whole table over. The optimizer claims to only refer to the columns that I specify in my query. Witness:
Rows Row Source Operation
------- --------------------------
52 MERGE JOIN (cr=0 r=0 w=0 time=12498 us)
52 SORT JOIN (cr=0 r=0 w=0 time=4214 us)
52 REMOTE (cr=0 r=0 w=0 time=3798 us)
52 SORT JOIN (cr=0 r=0 w=0 time=4230 us)
52 REMOTE (cr=0 r=0 w=0 time=3833 us)
Rows Execution Plan
------- --------------------------
0 SELECT STATEMENT GOAL: CHOOSE
52 MERGE JOIN
52 SORT (JOIN)
52 REMOTE [MYLINK]
SELECT "OBJECTID","SHAPE" FROM "JOE"."STATES"
"B"
52 SORT (JOIN)
52 REMOTE [MYLINK]
SELECT "FID","NUMOFPTS" FROM "JOE"."F33_MINUS_LONGRAW" "F"
Then, I tried requesting a plan when I issue the query. I do not have much experience working with the plan table. And, my attempt to use the canned scripts to get information out resulted in the, somewhat perplexing result, below. The structure is the same as the session trace plan. But, it says that the optimizer mode was "rule". What is confusing about this is that the optimizer is set to "choose". So, I don't know what this is trying to tell me.
PLAN_TABLE_OUTPUT
--------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MERGE JOIN | | | | |
| 2 | SORT JOIN | | | | |
| 3 | REMOTE | | | | |
|* 4 | SORT JOIN | | | | |
| 5 | REMOTE | | | | |
--------------------------
PLAN_TABLE_OUTPUT
--------------------------
Predicate Information (identified by operation id):
--------------------------
4 - access("F"."FID"="B"."SHAP
filter("F"."FID"="B"."SHAP
Note: rule based optimization
TABLE DEFINITIONS:
JOE.F33@MYLINK:
Name Null? Type
--------------------------
FID NOT NULL NUMBER(38)
NUMOFPTS NOT NULL NUMBER(38)
ENTITY NOT NULL NUMBER(38)
EMINX NOT NULL FLOAT(64)
EMINY NOT NULL FLOAT(64)
EMAXX NOT NULL FLOAT(64)
EMAXY NOT NULL FLOAT(64)
EMINZ FLOAT(64)
EMAXZ FLOAT(64)
MIN_MEASURE FLOAT(64)
MAX_MEASURE FLOAT(64)
AREA NOT NULL FLOAT(64)
LEN NOT NULL FLOAT(64)
POINTS LONG RAW
JOE.STATES@MYLINK:
Name Null? Type
--------------------------
OBJECTID NOT NULL NUMBER(38)
AREA NUMBER(38,8)
STATE_NAME VARCHAR2(25)
STATE_FIPS VARCHAR2(2)
SUB_REGION VARCHAR2(7)
STATE_ABBR VARCHAR2(2)
SHAPE NUMBER(38)
JOE.F33_MINUS_LONGRAW@MYLI
Name Null? Type
--------------------------
FID NOT NULL NUMBER(38)
NUMOFPTS NOT NULL NUMBER(38)
ENTITY NOT NULL NUMBER(38)
EMINX NOT NULL FLOAT(64)
EMINY NOT NULL FLOAT(64)
EMAXX NOT NULL FLOAT(64)
EMAXY NOT NULL FLOAT(64)
EMINZ FLOAT(64)
EMAXZ FLOAT(64)
MIN_MEASURE FLOAT(64)
MAX_MEASURE FLOAT(64)
AREA NOT NULL FLOAT(64)
LEN NOT NULL FLOAT(64)
Not sure why this is happening either. I'm still doing some research on it but wanted to ask if it would be possible to create a view on the base table?
ASKER
A good question. Thanks.
On a purely technical level that would be possible. However, I'm trying to figure out if there is a more generalizable approach to this situation. There are lots of these tables, in lots of different databases, that I hope to be able to query.
Inspired by your question, I see that it is possible to create a view in the local database of the non-long raw columns of the remote table, execute the query, and then drop the view. I would want to drop the view, after the query, to (1) not gum-up the database with arbitrary views that may or may not be used again and (2) reduce the chance of name collisions with other views. So, that is a possible, though slightly icky, way to go.
For the moment, I'm going to hold the points for a more thorough diagnosis of the problem or a solution that is more generalizable, given my circumstances. If a more thorough understanding of the problem shows this as most viable solution, so be it.
On a purely technical level that would be possible. However, I'm trying to figure out if there is a more generalizable approach to this situation. There are lots of these tables, in lots of different databases, that I hope to be able to query.
Inspired by your question, I see that it is possible to create a view in the local database of the non-long raw columns of the remote table, execute the query, and then drop the view. I would want to drop the view, after the query, to (1) not gum-up the database with arbitrary views that may or may not be used again and (2) reduce the chance of name collisions with other views. So, that is a possible, though slightly icky, way to go.
For the moment, I'm going to hold the points for a more thorough diagnosis of the problem or a solution that is more generalizable, given my circumstances. If a more thorough understanding of the problem shows this as most viable solution, so be it.
no problem on the points.
I can't find anything that says why this is happening. What version are you on (include both sides of the link)? I'm thinking this may be a 'feature' that might be solved with a patch.
I can't find anything that says why this is happening. What version are you on (include both sides of the link)? I'm thinking this may be a 'feature' that might be solved with a patch.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ha! That seems to work. Unbelievable. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dannykrouk,
Let us know if paquicuba's post works as well. I don't mind sharing the points with him. If it works, I'll unaccept the question and you can split the points.
Let us know if paquicuba's post works as well. I don't mind sharing the points with him. If it works, I'll unaccept the question and you can split the points.
ASKER
That works, too! Amazing. Thanks!
slightwv, if you'd like to unaccept, I can try splitting the points between you. I've not done that before, but I assume you know it is possible to accomplish. Feel free to include any tips for doing so.
slightwv, if you'd like to unaccept, I can try splitting the points between you. I've not done that before, but I assume you know it is possible to accomplish. Feel free to include any tips for doing so.
slightwv,
I don't want the points. I don't have 9i to test the ANSI syntax but I remember working with 9i and getting those type of errors, which were fixed in R2.
I don't want the points. I don't have 9i to test the ANSI syntax but I remember working with 9i and getting those type of errors, which were fixed in R2.
Good it worked!. As slightwv pointed out: Check those patches.
I have unaccepted the original answer to this question.
paq,
Splitting the points is the proper thing to do. Besides, when others come across this post, showing 2 correct answers would be best.
dannykrouk,
There is a 'Split Points' link directly above the box where you type in your responses. Once you select it you accept 1 response as the accepted answer and type in the point distribution.
paq,
Splitting the points is the proper thing to do. Besides, when others come across this post, showing 2 correct answers would be best.
dannykrouk,
There is a 'Split Points' link directly above the box where you type in your responses. Once you select it you accept 1 response as the accepted answer and type in the point distribution.
Thanks slightwv!
It makes sense.
It makes sense.