Oracle Scratchpad

June 4, 2013


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

	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

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 – this may vary with version of Oracle):

	A.DUMMY QCSJ_C000000000600000,
	from$_subquery$_007.DUMMY_0 QCSJ_C000000000600001,
	from$_subquery$_007.ROWID_1 ROWID 
		FROM	(
				from$_subquery$_005.QCSJ_C000000000500001_1 DUMMY,
				from$_subquery$_005.QCSJ_C000000000500003_2 ROWID 
			FROM	(
					B.DUMMY QCSJ_C000000000500000,
					C.DUMMY QCSJ_C000000000500001_1,
					B.ROWID QCSJ_C000000000500003_2 
					SYS.DUAL B,
					SYS.DUAL C 
				) from$_subquery$_005
			) D 
	)(+) 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.

May 11, 2011

dbms_xplan reprise

Filed under: dbms_xplan,lateral view,Oracle — Jonathan Lewis @ 5:28 pm BST May 11,2011

One of the questions that pops up on the internet from time to time is the one about finding SQL that’s doing full tablescans.

Since the appearance of the dynamic performance view v$sql_plan in Oracle 9i this has been quite easy – provided you can check memory soon enough. A query like the following will give you the sql_id (hash_value, if you’re still on 9i) and child_number of any query that has a full tablescan (or index fast full scan) in its execution path.


January 31, 2011

ANSI Outer

Filed under: ANSI Standard,CBO,Execution plans,lateral view,Oracle — Jonathan Lewis @ 6:59 pm BST Jan 31,2011

Here’s an example of ANSI SQL that does something in a fashion that arguably looks a little tidier than the strategy you have to adopt in Oracle. As so often when I compare Oracle syntax and ANSI syntax it’s an example that relates to an outer join. We start with two tables – as usual I have locally managed tablespaces, 8KB blocks, 1MB uniform extents and freelist management. I’ve also disabled system statistics (CPU costing):


May 5, 2009

Dependent Plans

Filed under: dbms_xplan,Execution plans,lateral view — Jonathan Lewis @ 6:09 pm BST May 5,2009

I’ve written several posts about dbms_xplan, and the display_cursor function in 10g. One of the nice feature of this function is that it is a “pipelined” function – which means that you can treat the call to the function as a “virtual table”. Here’s an example (run on of what this allows us to do: (more…)

November 19, 2008

Lateral LOBs

Filed under: Infrastructure,lateral view,LOBs,Oracle — Jonathan Lewis @ 10:20 pm BST Nov 19,2008

The following request appeared on the newsgroup a few days ago:

Is it possible to create a view that will split single CLOB column into multiple chunks of VARCHAR2 something like this:

ID              INTEGER
DATA            CLOB

ID              INTEGER
DATA            VARCHAR(1000 char)


The Rubric Theme Blog at


Get every new post delivered to your Inbox.

Join 3,507 other followers