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” syntax. 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:

ANSI Outer 2

A comment on a recent post of mine pointed me to a question on the OTN SQL and PL/SQL Forum where someone had presented a well-written test case of an odd pattern of behaviour in “ANSI” SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables (plus a few extras) are all available on OTN. If you create only the four tables needed and all their indexes you will need about 1.3GB of space.

The core of the problem is this: there is a three table join which does a hash join involving an index fast full scan on a particular index; but when you add a fourth table to the join this index fast full scan turns into a full tablescan for no obvious reason.

Here are the queries, with the plans that I got when running 10.2.0.3. (My final plan is slightly different from the plan shown on OTN – I have a right outer hash join to the last table where the OP had a nested loop outer – but the difference is not significant). The queries, with their execution plans, are below- the three table join first:

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.

ANSI Outer

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):

Dependent Plans

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 10.2.0.3) of what this allows us to do:

rem
rem     Script:         multiple_xplan.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2009
rem
select
        t.plan_table_output
from    (
        select
                sql_id, child_number
        from
                v$sql
        where
                hash_value in (
                        select  from_hash
                        from    v$object_dependency
                        where   to_name = 'XXXXX'
                )
        ) v,
        table(dbms_xplan.display_cursor(v.sql_id, v.child_number)) t
;

In this example, I’ve joined an inline view to a table – but the “table” is the result set from a call to dbms_xplan.display_cursor, and the join is a variation of the lateral join mechanism which takes as its inputs some values from an earlier object in the from clause.

The output is a list of the execution plans for all the queries currently in the library cache that are dependent on the object I’ve named in the inline query against v$object_dependency.

This could be very useful information when you’re trying to work out the possible side effects of (for example) changing the indexes on a table.  Be very cautious with queries like this, though, as they’re likely to cause a lot of contention on library and shared pool latches – but just occasionally the cost may be worth it.)

Footnote: ideally I would like to introduce an order by clause that ensures the execution plan output (column plan_table_output from dbms_xplan calls) appears in the right order – but there is no available column, and the nature of pipelined functions probably ensures that the data will appear in the correct order … in this case.

Lateral LOBs

The following request appeared on the comp.databases.oracle.server 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:

TABLE:
---------------------------
ID              INTEGER
DATA            CLOB

VIEW:
--------------------------------------------------
ID              INTEGER
CHUNK_ID        INTEGER
DATA            VARCHAR(1000 char)