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:
June 4, 2013
May 11, 2011
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.
January 31, 2011
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):
May 5, 2009
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: (more…)
November 19, 2008
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)
