Oracle Scratchpad

June 4, 2013

ROWID

Filed under: ANSI Standard,Bugs,lateral view,Oracle — Jonathan Lewis @ 9:09 am BST Jun 4,2013

Here’s a suggestion to help you avoid wasting time. If you ever include the rowid in a query – not that that should happen very commonly – make sure you give it an alias, especially if you’re using ANSI SQL. If you don’t, you may find yourself struggling to work out why you’re getting an irrational error message. Here’s an example that appeared recently on the OTN forum, with the output cut-n-pasted from a system running 11.1.0.7:

select
	'1'
from
	dual a
left join
	(
	select	c.dummy, b.rowid
	from	dual b
	join	dual c
	on b.dummy = c.dummy
	) d
on 	a.dummy = d.dummy
;

select
*
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

The error doesn’t really seem to fit the query, does it?
If you want to bypass the problem all you have to do is give b.rowid (line 7) an alias like rid.

As far as I can tell, the problem arises from a defect in the code that Oracle uses to transform the query before optimising it. Given the nature of the transformation it’s possible that you’re only going to see the problem manifest like this if the SQL uses ANSI forms; in this particular case changing from the ANSI left outer join syntax to Oracle’s standard (+) syntax also bypasses the problem. To narrow down the cause, I simply enabled event 10053 (the optimizer trace) and got lucky. The trace file showed an interesting “unparsed SQL” statement which, stripped of double-quote marks and re-formatted, was as follows (in 11.1.0.7 – this may vary with version of Oracle):

SELECT
	A.DUMMY QCSJ_C000000000600000,
	from$_subquery$_007.DUMMY_0 QCSJ_C000000000600001,
	from$_subquery$_007.ROWID_1 ROWID
FROM
	SYS.DUAL A,
	LATERAL(
		(
		SELECT
			D.DUMMY DUMMY_0,
			D.ROWID ROWID_1
		FROM	(
			SELECT
				from$_subquery$_005.QCSJ_C000000000500001_1 DUMMY,
				from$_subquery$_005.QCSJ_C000000000500003_2 ROWID
			FROM	(
				SELECT
					B.DUMMY QCSJ_C000000000500000,
					C.DUMMY QCSJ_C000000000500001_1,
					B.ROWID QCSJ_C000000000500003_2
				FROM
					SYS.DUAL B,
					SYS.DUAL C
				WHERE
					B.DUMMY=C.DUMMY
				) from$_subquery$_005
			) D
		WHERE
			A.DUMMY=D.DUMMY
		)
	)(+) from$_subquery$_007

Note the alias of ROWID appearing in lines 4 and 15. If you tried to run this SQL from the command line (after fiddling the event to enable lateral() views), or even just the simple select running from lines 13 to 26, you would get error ORA-00923: FROM keyword not found where expected. My guess is that the context in which the optimisation takes place means that this error is re-raised as the error ORA-01445 that we ultimately see.

4 Comments »

  1. Interesting.
    I first tried to recreate this on a rdbms 11.2.0.2. There I get:

    ERROR at line 1:
    ORA-00918: column ambiguously defined

    On 11.2.0.1 software I see:
    ERROR at line 1:
    ORA-01445: cannot select ROWID from, or sample, a join view without a
    key-preserved table

    Comment by Martijn — June 4, 2013 @ 9:44 am BST Jun 4,2013 | Reply

    • can be reproduced by me

      sokrates@11.2 > select * from v$version where rownum=1;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      
      sokrates@11.2 > select
        2      '1'
        3  from
        4      dual a
        5  left join
        6      (
        7      select  c.dummy, b.rowid
        8      from    dual b
        9      join    dual c
       10      on b.dummy = c.dummy
       11      ) d
       12  on  a.dummy = d.dummy
       13  ;
      select
      *
      ERROR at line 1:
      ORA-00918: column ambiguously defined
      

      Comment by Matthias Rogel — June 4, 2013 @ 2:42 pm BST Jun 4,2013 | Reply

  2. Just reacted to soon.

    On an other 11.2.0.2 installation I also see ORA 01445.

    Sorry for the confusion.

    Comment by Martijn — June 4, 2013 @ 10:11 am BST Jun 4,2013 | Reply

  3. “…The error doesn’t really seem to fit the query, does it? …”

    true, it doesn’t.
    But that happens quite often.
    For example

    sokrates@11.2 > select * from dual where dummy(+)=dummy;
    select * from dual where dummy(+)=dummy
                                     *
    ERROR at line 1:
    ORA-01416: two tables cannot be outer-joined to each other
    

    ?????

    Comment by Matthias Rogel — June 4, 2013 @ 2:44 pm BST Jun 4,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,003 other followers