?
Solved

Accessing non-long raw columns from table with long raw over dblink

Posted on 2006-05-12
12
Medium Priority
?
914 Views
Last Modified: 2010-07-27
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
Comment
Question by:dannykrouk
  • 5
  • 4
  • 3
12 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16669252
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
 

Author Comment

by:dannykrouk
ID: 16669476
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16669606
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 16669628
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
 

Author Comment

by:dannykrouk
ID: 16669675
Ha!  That seems to work.  Unbelievable.  Thanks.
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 1000 total points
ID: 16669784
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16670209
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
 

Author Comment

by:dannykrouk
ID: 16670335
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 16670390
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 16670568
Good it worked!. As slightwv pointed out: Check those patches.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16681718
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 16682849
Thanks slightwv!

It makes sense.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses
Course of the Month17 days, 13 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question