Search

Top 60 Oracle Blogs

Recent comments

lateral view

ROWID

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.

dbms_xplan reprise

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. (Note – just because there is a full tablescan in the plan this doesn’t mean that it will definitely happen, and you don’t necessarily know how often it might happen each time the query executes: so finding the SQL isn’t the whole story.)

select
        distinct sql_id /* hash_value */ , child_number
from
        v$sql_plan
where   (operation = 'TABLE ACCESS' and options = 'FULL')
or      (operation = 'INDEX' and options = 'FAST FULL SCAN')
;

Once you have the sql_id (hash_value) and child_number you can always check v$sql (or v$sqlstats in 10g) for the SQL text and any other details you might want. If you’re on 10g, though, there’s an interesting variation on this theme. It’s easy (though resource-intensive on big busy systems) to use dbms_xplan to print the SQL text and execution plans for the suspect queries.

The function dbms_xplan.display_cursor() is a “pipelined function”, which means that it can behave like a table if you apply the table() operator to it; and table() operators can appear in lateral joins in Oracle. This means we can take the previous query, put it into an inline view, and (laterally) join it to dbms_xplan.display_cursor() as follows:

select
        plan_table_output  -- (the column of the pipelined function)
from    (
                select
                        distinct sql_id, child_number
                from
                        v$sql_plan
                where   (operation = 'TABLE ACCESS' and options = 'FULL')
                or      (operation = 'INDEX' and options = 'FAST FULL SCAN')
        ) v,
        table(dbms_xplan.display_cursor(v.sql_id, v.child_number))
;

Notice that the table() operator has to appear after the view that the pipelilned function is referencing, which alluws us to pass columns from the view into the pipelined function. This query gives us the dbms_xplan.display_cursor() output – with all the variations that allows – for every query that’s still in memory that has done a tablescan or index fast full scan.

You may find this a useful thing to run occasionally. But do be cautious – it WILL hammer the library cache for some time, especially if you have a large shared pool and a busy system.