• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 958
  • Last Modified:

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"."SHAPE")                                            
       filter("F"."FID"="B"."SHAPE")                                            
                                                                               
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@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)
0
dannykrouk
Asked:
dannykrouk
  • 5
  • 4
  • 3
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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?
0
 
dannykroukAuthor Commented:
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.

0
 
slightwv (䄆 Netminder) Commented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
slightwv (䄆 Netminder) Commented:
Just had another thought as soon as I clicked Submit:

Since a local view of the remote table works, how about trying an inline view:

Just typed in.  Not sure if the sytax will be right:
--------------------------------
select f.numofpts, b.objectid
from (select fid, numofpts from joe.f33@mylink) f
inner join (select shape, objectid from joe.states@mylink) b
on f.fid = b.shape;
0
 
dannykroukAuthor Commented:
Ha!  That seems to work.  Unbelievable.  Thanks.
0
 
paquicubaCommented:
As an extra input.

I had some issues when joining tables contining LONGs using the 1999 ANSI syntax.

Can you try this and let me know please:

select f.numofpts, b.objectid
from joe.f33@mylink f, joe.states@mylink b
where f.fid = b.shape;


0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
dannykroukAuthor Commented:
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.
0
 
paquicubaCommented:
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.
0
 
paquicubaCommented:
Good it worked!. As slightwv pointed out: Check those patches.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
paquicubaCommented:
Thanks slightwv!

It makes sense.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now