Feed aggregator


Ayyappa Yelburgi - Sun, 2007-12-23 04:03
--1 Move index from one tablespace to anotheralter index &OWNER.&INDEX_NAME rebuild tablespace &NEW_TS_NAME;--2 Moving index partition from one tablespace to anotheralter index &OWNER.&INDEX_NAMErebuild partition &IND_PART_NAME tablespace &NEW_TS_NAME;--3 Moving all index subpartitions from one tablespace to anotheralter index &OWNER.&INDEX_NAMErebuild subpartition &IND_SUBPART_NAME tablespace &ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com31


Ayyu's Blog - Sun, 2007-12-23 04:03
Categories: DBA Blogs


Ayyappa Yelburgi - Sun, 2007-12-23 04:01
--Long operationsselect sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,time_remaining remaning,elapsed_seconds elapsed,last_update_time last_timefrom v&session_longops --where sid=73order by last_update_time desc;--All active sessionsselect * from v&session where status='ACTIVE'--and sid in (37,43)order by sid;--Find session's sid or process id by it's sid or process ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com6


Ayyu's Blog - Sun, 2007-12-23 04:01
Categories: DBA Blogs


Ayyappa Yelburgi - Sun, 2007-12-23 04:00
-1 Move table from one tablespace to another-- (check for unusable indexes after that).alter table $OWNER.$TABLE_NAME move tablespace $NEW_TS_NAME;--2 Move table partition from one tablespace to another-- (check for unusable indexes and partitoned indexes after that).alter table $OWNER.$TABLE_NAMEmove partition $TAB_PART_NAME tablespace $NEW_TS_NAME;--3 Move table subpartition from one tablespaceayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com20


Ayyu's Blog - Sun, 2007-12-23 04:00
Categories: DBA Blogs

Query result cache in oracle 11g

Adrian Billington - Sat, 2007-12-22 02:00
Oracle adds a new cache for storing the results of queries. December 2007

Solaris 10 on Dell PowerEdge 1950 and 2950

Hampus Linden - Thu, 2007-12-20 11:58
I probably get an email every couple a weeks about this one, not sure why.
E-mails regarding something in Solaris not working on Dell PowerEdge 9th gen servers.

For any device driver related issues with Solaris on x86, the first resource to check if you run in to trouble is the Sun HCL, the 'hardware compatability list'.
Solaris 10 works fine on most Dell boxes, but some need NIC or HBA drivers.

For a Dell 2950 that tells us that Solaris works, from release 11/06 upwards, it also tells us to download the ethernet driver here and the MegaRAID SAS driver here.

NB, the Sun HCL does not mention the new 'III' series 9-gen server yet. I would guess they are working on as Sun and Dell recently partenered to provide better Solaris support. The PERC6 card may need some new drivers.

Agile PLM - Part I

Aviad Elbaz - Thu, 2007-12-20 02:27
In May this year, Oracle has acquired Agile, a leading provider of Product Lifecycle Management (PLM) software solutions.

In this post I will explain about the various components of Agile PLM system, and the following posts will be dedicated to Agile PLM installation.

The components of Agile PLM application are:
1) Agile Database
2) Agile Application Server
3) Agile File Manager
4) Agile Web Proxies
These components should be installed in the above order.

Agile Database
Used to hold all Agile data.
It is recommended to install the database and application server on separated machines.

Agile Application Server
The Agile Application Server can be run on Oracle Application Server 10g or BEA WebLogic Server.
The Application Server is the main component of the Agile system, all services and business logic reside on it.
The Agile application deployed on the Application Server.
All users (Java/Web clients) connect to the Application Server in 2 possible ways:
- Directly
- Indirectly, via Wev proxy Server.

Agile File Manager
The File Manager manages all Agile files in the file system (File Vault).
The File Manager runs on Tomcat.

Agile Web Proxies Server
The Agile web client allows connection to both internal and external users.
Agile web client uses Microsoft IIS (Internet Information Services) or Apache web server.
The Agile web client connects to an Agile Application Server the same way like other Agile Application Server clients.

Next post I will show, step by step, how to install Agile PLM 9.2.21 on two nodes configuration.

Categories: APPS Blogs

Need to Kill j000 process to shutdown the database after upgrade from to

Madhu Thatamsetty - Wed, 2007-12-19 22:20
This is a common problem after the upgrade. One important point to remember is to disable all the schedules for dbms jobs before kicking off the upgrade and enable the schedule after the uprade is over. Alternatively job_queue_processes parameter can be set to '0'.You see following symptoms in the alert log.-- Clip --Active call for process 21244 user 'oracle' program 'oracle@hostname (J000)'Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com1

Oracle EBS R12 beta exam are you ready

Fadi Hasweh - Wed, 2007-12-19 03:18
I received the following email from oracle certification program
“Oracle University is pleased to announce the availability of the following new Oracle E-Business Suite (R12) certification exam in beta version:
Oracle EBS R12: Install, Patch and Maintain Applications [1Z0-238]
This new exam is required as part of earning the new Oracle EBS R12 Applications Database Administrator Certified Professional certification.
By participating in this beta exam, you have the opportunity to provide the Oracle Certification program with feedback about exam content which is integral to the process of exam development. We depend on the contributions of experienced professionals and developers as we continually improve exam content and maintain the value of Oracle certifications.

Beta exam period runs:
January 03 – April 04, 2008
These beta periods are subject to change, please check the exam pages for the most up-to-date information

Good luck with your exams

Regular Expressions: REGEXP_COUNT (New SQL Snippets Tutorial)

Joe Fuda - Sun, 2007-12-16 14:00
Introduced in Oracle 11g the REGEXP_COUNT function greatly simplifies counting the number of times a pattern appears inside a string. The following examples demonstrate how to use REGEXP_COUNT with some simple patterns. For versions prior to 11g alternative logic that provides similar functionality is also presented.

Mac OSX, Bootcamp and a Missing Hash Key

Duncan Mein - Sun, 2007-12-16 12:58
I bought a Mac Book Pro about 8 months ago as my main business Laptop. Coupled with a copy of Parallels, I built my Oracle Server (Database and Apps Server) on a Windows VM environment which left Mac OSX free for Development using SQL Developer, Dreamweaver etc

A couple of weeks back I decided to upgrade to OSX Leopard and install windows natively using Bootcamp to utilise both core's on the CPU and all 3 Gig of memory. All well and good until i tried to use the Hask key (Alt + 3 in OSX) when working on some APEX templates. After much research on the web, it appears that most OSX key mappings are installed when using bootcamp but in order to print the hash (#) symbol, you must use Ctrl + Alt + 3

Simple when you know how

Autopatch Timing Report. Useful information that helps you to plan your Production Downtime.

Madhu Thatamsetty - Fri, 2007-12-14 23:24
I was all set to write a shell script that will parse the patch log file and arrive at top time consuming sqls that will help me know the time taken by the sqls for the upgrades. After constantly watching the Maintenance Pack ( 3480000 ) session for 30+ hours. The following lines of the autopatch session caught my attention.-- Clip --sqlplus -s APPS/***** @/xxxx/xxxx/xxxxappl/ad/11.5.0/admin/sql/Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com2

My Experience with Oracle Service Contracts (OKS) Rule Migration Metalink Note:265048.1

Madhu Thatamsetty - Fri, 2007-12-14 18:28
Does this Note:265048.1 sound familiar ? At least I can't forget this Note#. I can tell this Note# even if someone wakes me up in the middle of the night. This is because I have spent so much of time and iterations doing Service Contracts Rule Migration before upgrade to cut down the time the Maintenace Pack (Patch# 3480000) takes. Coming to the technical aspect of this Note#, Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Tahiti Rising

Mary Ann Davidson - Fri, 2007-12-14 10:55

As is obvious from my previous blog entries, I have a great appreciation for nā mea 'apau Hawai'i - all things Hawai'i Since Polynesian culture is not, in general, something a lot of people know anything about, I enjoy opportunities to talk about it to friends. A few weeks ago, when I was at the Army-Navy* game with an "infidel" friend (I mean, a friend who had gone to West Point instead of the Naval Academy), we talked about our relative experiences in the military, specifically, the differences between finding your way on land and finding your way on the sea. I had a chance to talk about Polynesia in the context of navigation. (I skipped over the fact that I was singularly lousy at seamanship, one of many reasons the Navy in its infinite wisdom did not make me a navigator.)


The traditional (Western) history of the world ascribes amazing feats of navigation to Ferdinand Magellan, who was the first to circumnavigate the globe. Personally, I'm not all that impressed, since Magellan had a compass and a quadrant at his disposal, and the rest was testosterone and favorable winds. OK, that probably isn't fair. What I mean to say is that even if Magellan deserves some accolades as a mariner, he had mechanical help (the aforementioned compass and quadrant), and he certainly wasn't the first to make long voyages in the Pacific.


The true master mariners, in my opinion, were the Polynesians, who traveled thousands of miles in the Pacific, between New Zealand, Tahiti, Hawai'i and the Marquesas, even Easter Island (Rapa Nui). They were able to do so using traditional Polynesian wayfinding, which considers the wind, the waters, the stars, even the birds. No mechanical assistance, in other words, which makes their navigational skills truly extraordinary. (Lewis and Clark carved a way through the wilderness; today, people can drive an RV from St. Louis, Missouri to Astoria, Oregon, with all the creature comforts of home, the interstate highway network and a GPS system. It's hardly comparable, is it?) The Polynesians navigated the Pacific long before Magellan, because they were an island people. To expand their territory, they had to become voyagers.


By the 1970s, the art of Polynesian wayfinding had all but died out. One of the last practitioners was a master navigator from Micronesia named Mau Piailug. He came to Hawai'i to teach Nainoa Thompson, a Hawaiian whose dream it was to sail a double-hulled voyaging canoe called the Hōkūle'a from Hawai'i to Tahiti using Polynesian wayfinding.


The story is told that one night, Mau stood under a star-filled sky and asked Nainoa to point to Tahiti. Nainoa pointed to the star (hōkū) under which Tahiti lay. Then Mau asked him, "Do you see Tahiti?" Such a strange question; how could Nainoa possibly see an island 2700 miles away? Finally, after much thought, Nainoa said, "I see the island in my mind," to which Mau replied that Nainoa must never forget what he was seeing or he would be lost.


The day came when the Hōkūle'a set sail with Nainoa as the navigator. Nainoa could see Tahiti in his mind, until the day came when Hōkūle'a was approaching the equator, and with it a great cloud of rain. Nainoa feared that he would not be able to steer the ship without the benefit of the stars to guide him. Great fear and restlessness over came him, until he remembered Mau telling him that he needed to look inside, that he would be lost if he tried to see with his eyes. Suddenly, Nainoa had a feeling of deep relaxation, and he felt the moon over his shoulder, despite the fact it could not be seen in the cloudy sky. He was able to navigate by a sense of knowing where Tahiti was. Days later, he turned towards the horizon and saw Tahiti rising, as he had hoped and dreamed.


Because of the voyages of Hōkūle'a and other Polynesian voyaging canoes over the last 30 years, the Hawaiian people have come to have great pride -  a resurgence of pride - in their culture. In fact, Hawaiian culture has had a sort of renaissance in all areas - language, music, dance. At one point, there were less than 50 native speakers of Hawaiian under the age of 18. Now, there are multiple immersion schools in the Hawaiian language and many young people speak the language. The story of Hōkūle'a does not end with a single voyage. One man's willingness to see Tahiti rising and keep after it affected an entire culture.


Something else I find interesting about this story is that it challenges our assumptions that in the steady march of progress,  new technology is inevitably more sophisticated and better than knowledge built up over hundreds or thousands of years. Maybe it isn't -  maybe we are just lazier than we used to be and nobody wants to put in the time to learn anything of substance anymore.


I recently read a book about the impending extinction of so many of the world's languages. Loss of a language also means losing the knowledge encompassed in that language. A Siberian language (Tuvan) can use a single word to describe all the attributes of a reindeer (age, health, disposition) that are useful if you want to ride him. In English, the linguistic encapsulation of "male reindeer, 5 years old, excellent health and easy to ride" takes 11 times as many words as in the Siberian language.


There is no such thing as a living language with no native speakers. When a language dies - because there are no longer native speakers - the culture dies and most of the knowledge embedded in the culture dies with it. Only today, I read that pharmaceutical companies may be reaching the upper limit of what they can do with purely chemical treatments of disease: they are shifting their investments to biologically-based treatments. Of course, many indigenous peoples - many of whose languages are facing extinction - use plant and animal-based medicines, the use and utility of which is incorporated into their language. If the language dies, so does that knowledge.


In a strange way, we may become collectively more ignorant when we increase our reliance on technology and forget (or don't learn) what our fathers and mothers knew, however simple. How many of us have been in a checkout line when the clerk could not do basic addition and subtraction (to make change) unless the cash register was able to do it for him? I confess, I once had a perfectly lovely financial calculator that could amortize a bond for you. Unfortunately, I was able to get the right answer in some of my MBA classes while in many respects not grokking the basics of finance (one of many reasons I don't work in finance). 


How many people can't actually read a map any more? (I've had some spectacular failures with mapquest.com and just lived through one with a GPS system.) I cringe when I hear about people going hiking or skiing in the backcountry without basic equipment and survival skills (including a compass and knowledge of how to use it). Too many assume that they can use a cell phone to call for help if they get lost. (Not if there is no cel tower in range or your battery dies.) 


We've become so removed from nature that many of us have lost respect for it. A couple of years ago, some (allegedly expert) climbers decided to ascend Mount Hood in advance of a storm (I am told by my climbing friends in Idaho that you should never, ever climb thinking you will "beat" the weather. If there is a better definition of "hubris" than that I don't know what it is). The climbers died. The tragedy was that their deaths were avoidable if only they had respected the weather report. If you live in a wilderness area, you learn to respect it. You don't "tame" it anymore than you "tame" a grizzly bear.


A concomitant of busy lives and "more technology" is that too many of us spend too much time with gadgets and gizmos and not enough time experiencing the natural world. Dare I say, "the real world." A particularly pathetic example of this was a wealthy real estate developer who installed a state-of-the-art golf simulator in his basement. He bragged on camera (one of those home improvement shows on HGTV) that he had "played"  all the best courses in the world - from his basement. The reality is that he is so busy with work that he does not have time to actually play golf. Real golf. Playing virtual golf and saying you've played St. Andrews is like reading the Cliff Notes and saying you read Dickens. It isn't the same - not even close. It's not about your golf score, it is about being there, because the game isn't going to show you the elk that peek out on the course, the geese that fly overhead, or the particularly beautiful late afternoon lighting on the water.


I am sure a lot of people will read the story of Nainoa Thompson and conclude that there are lots of faster and easier ways to get to Tahiti than recreating a Polynesian voyaging canoe and learning the art of wayfaring (so, they think, "why bother?"). Just like you can play a really great round of golf in your basement, without bugs, divots, wind or other annoyances. It's only when you look at a dream of Tahiti rising that the real value of the trip becomes apparent.


Several years ago, I saw the Hōkūle'a entering San Francisco Bay, and it gave me chicken skin, as the Hawaiians say. I can say this after years of seeing some of the great warships of history, and great warships of today's modern Navy: the feeling I got when I saw the Hōkūle'a was unlike anything, because I knew what it meant to the Hawaiian people. Respect. Pride in their culture. Now, people who will never sail a double-hulled voyaging canoe can nonetheless see Tahiti in their mind, because one man could. As Antoine de Ste. Exupery said, "It is only with the heart that one can see rightly; what is essential is invisible to the eye."


My sister and I are writing a book together, an IT murder mystery. This is the main reason I have not been blogging as much -  my writing energies are going into the book. We have gone from chatting about "we ought to write a book together" to an outline, drafts, character sketches, and entire chapters. My sister has become the book navigator - she sees where she wants it to go, and plots the course in her mind. She is like Nainoa Thompson, being disciplined about spending time learning her craft, and always, she sees Tahiti rising. A couple of months ago, she said she wanted us to have a draft finished by the end of the year, and now, we have.


I can write, but I have always lacked discipline and a vision greater than "I ought to write more." I was like the guy with the golf simulator: I dabbled at something I loved but did not make time for the real deal. I needed a navigator, and my sister stepped up to the helm. I did not envision the book, or know that the moon was over my shoulder: my sister did that. And because of her, now I can see Tahiti on the horizon. For once, I will have finished a writing project I mused about and thought about but never showed the discipline to accomplish. And like the Hōkūle'a, who knows what other adventures will come from that first dream of Tahiti? It seems fitting that the culture of Polynesia figures prominently in our book. Nainoa Thompson inspires me, too, because of all the dreams of Tahiti rising in my mind, one of which is to some day live in Hawai'i, where I already feel at home.


As we come to the end of another year, it is a good time for all of us to imagine what is possible if you have a star to steer by, or one you hold in your heart that you follow to a place of your imagining. Two thousand years ago, wise men followed a star to Judea, asking the question, "Where is the one who has been born king of the Jews? We saw his star in the east and have come to worship him." (Matthew 2: 2)  They were not the first - and they have not been the last - to follow a star and find more illumination than their hearts could hold.


* Navy won, 38-3, the 6th straight win over Army. And, the Navy quarterback is Hawaiian, how cool is that?


For more information:


Book of the week: When Languages Die: The Extinction of the World's Languages and the Erosion of Human Knowledge by K. David Harrison


In all the hoopla about endangered species, people forget that a substantial percentage of the world's languages are in danger of becoming extinct. Lose a language, lose a culture, and all the priceless knowledge that goes with it.  You will never look at linguists the same after reading this book.




Nathan Aweau (nui kona mana!) has a new CD out (Kāne’ohe) which is absolutely kamaha'o (amazing). The very first song is about seafaring voyagers (E Pi'i Mai Ke Kai), and the Hōkūle’a is in the lyric, too. Read about it or order it at:




About the Polynesian Voyaging Society




About Chicken Soup from the Soul of Hawai'i (the source of the story about Nainoa Thompson):




About wayfinding:





More on Nainoa Thompson:




Herb Kawaianui Kane's amazing art of a navigator and a voyaging canoe:





And Herb Kawaianui Kane's imagining of the discovery of Hawai'i:




About the Tuvan language:



Integrating the Google Chart API in APEX

Jornica - Thu, 2007-12-13 16:13

A few days ago, Google released the Google Chart API. By calling a special crafted URL Google returns a image containing a chart derived from the parameters included in the URL. For instance


will give the image shown left. At the right the Flash equivalent generated by APEX is shown.

To include this dynamic Google chart based on a query on a page, define a PL/SQL dynamic content region with the following PL/SQL source:

l_url VARCHAR2(2000) := 'http://chart.apis.google.com/chart?cht=p3&chs=200x100&chco=ff0000,00ff00,0000ff,000000';
l_chd VARCHAR2(2000);
l_chl VARCHAR2(2000);
FOR rec IN
(SELECT d.deptno LABEL,
COUNT(e.empno) VALUE
FROM dept d,
emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.deptno)
l_chd := l_chd || ',' || rec.VALUE;
l_chl := l_chl || '|' || rec.LABEL;
l_url := l_url || 'chd=t:' || SUBSTR(l_chd, 2) || '&chl=' || SUBSTR(l_chl, 2);
htp.p('<img src="' || l_url || '"/>');

If you do not want to use the built-in Flash chart functionality, the Google Chart API is a simple alternative.

Revisting My Previous Post "DBUA FAILS WITH unable to extend rollback segment ODMA_RBS01"

Madhu Thatamsetty - Wed, 2007-12-12 04:15
I thought i'll add in more details to the post "DBUA FAILS WITH unable to extend rollback segment ODMA_RBS01".Just changing the entry " InNewTablespace name="ODMA_RBS" size="50" unit="MB"" in $ORACLE_HOME/assistants/dbma/mep.cfg is not sufficient. There are couple of more tags that you need to play with in mep.cfg. Refer to "CreateRollbackSegmentsODMA_RBS01" revert="true" " section in the Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Agent Update Packs

Mark Vakoc - Tue, 2007-12-11 20:49
If you navigate to the 'Managed Software' page in the quick links you'll see a listing of all the tools releases and JDBC drivers that have been uploaded to the management console. These packages, obtained from the Update Center, are used to stage tools releases prior to installing or upgrading them.

You may notice an entry named 'Agent Install Pack 7' of type 'Management Agent Installer Bundle'.

What is this? Well, in a previous post I mentioned that we have two separate downloads for Server Manager for each tools release.
  • A large (~1 GB) Installer, and
  • A much smaller (< 50 MB) console update
The large installer is used the first time you install SM. This includes the J2EE container used to run the web application, the SM management console application, and the corresponding agent install pack. The SM update contains only updated SM management console (and corresponding management agent code). Installing a new SM application and later applying an update is functionally equivalent to initially installing the later release using the installer. Our goal here was to keep updates small and easy to apply.

There's actually a third possible download from the update center: a new agent update pack. This contains items that are rarely going to change with each tools release update. Specifically it contains the platform specific managed home installer for all the platforms we support and the platform specific Oracle Configuration Manager agent that is installed along with the managed home.

These items are rarely updated because
  1. The managed home agent installer obtains the actual agent codebase from the management console, thus installing whatever is current with the console application
  2. The Oracle Configuration Manager agent will automatically update with the latest version provided by Oracle, therefore not requiring OCM updates with Server Manager
So why the potential separate download for the management agent pack? In the event we need to update either the managed home installer or OCM agent that is initially installed we will create a new management agent pack that can be downloaded from the Update Center. Why would we do this? It won't be typical, but an example in which we might need to update the pack is if we added support for a new platform that isn't currently available or need to deploy code fixes to the managed home installer. As of this posting we have no foreseeable need to update the agent installer pack.

How do I know if I need a new agent installer pack? Should we need to update the agent installer pack we will create and add the package to the Update Center. Navigating to the 'Management Agents' page will tell you if a new agent installer pack is required. For example, assume:
  1. We updated the agent install pack with a particular tools release of Server Manager
  2. You have applied that SM update to an existing SM installation
  3. You want to install a new managed home agent
In this case you will be instructed to download the corresponding agent installer pack and add it to the SM software repository.

What if I delete the existing agent installer pack? This software component is treated just like any other software component; that is you can delete it either through SM or directly from the file system. If you have deleted the agent installer pack and navigate to the 'Management Agents' screen you will be instructed to obtain the installer pack from the Update Center.

Since we have not had the need to rev the installer pack it is not currently available for download from the Update Center. So, in the case you accidentally deleted the existing installer package you may obtain it only by performing a new SM installation and copying/uploading the file from the new install to the existing installation. After doing so you may uninstall the new SM console just installed.

The file will be named something along the lines of agentPackage7.jar and will be located in the directory INSTALL_BASE/components, where INSTALL_BASE refers to the installation path of the SM application.

Outerjoins in Oracle

Oracle Optimizer Team - Tue, 2007-12-11 15:00
Since release 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In 9i, Oracle introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outerjoin. The Oracle syntax for left outerjoin and that of ANSI SQL 92/99 are not equivalent, as the latter is more expressive.

There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntaxes. The following examples explain the equivalences and inequivalences of these two syntaxes.

Oracle-Specific Syntax

Consider query A, which expresses a left outerjoin in the Oracle syntax. Here T1 is the left table whose non-joining rows will be retained and all non-joining rows of T2 will be null appended.

SELECT T1.d, T2.c
WHERE T1.x = T2.x (+);

ANSI Left Outerjoin

In the ANSI outer join syntax, query A can be expressed as query B.

SELECT T1.d, T2.c
ON (T1.x = T2.x);


Consider the following queries. In the Oracle semantics, the presence of (+) on the filter predicate (e.g., T2.y (+) > 5 in query C) indicates that this filter must be applied to the table T2 before the outer join takes place.

SELECT T1.d, T2.c
WHERE T1.x = T2.x (+) and T2.y (+) > 5;

The ANSI left outer join query D is equivalent to C. Applying the filter on the right table in the left outer join is the same as combining the filter with the join condition.

SELECT T1.d, T2.c
ON (T1.x = T2.x and T2.y > 5);

Similarly, the presence of (+) on the filter predicate, T2.y (+) IS NULL, in query E indicates that this filter must be applied to the table T2 before the outer join takes place.

SELECT T1.d, T2.c
WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;

The ANSI left outer join query F is equivalent to E.

SELECT T1.d, T2.c
ON (T1.x = T2.x and T2.y IS NULL);

Consider query G. Oracle will apply the filter, T2.y IS NULL, in query G after the outer join has taken place. G will return only those rows of T2 that failed to join with T1 or those whose T2.y values happen to be null.

SELECT T1.d, T2.c
WHERE T1.x = T2.x (+) and T2.y IS NULL;

The ANSI left outer join query H is equivalent to G, as the WHERE clause in H is applied after the left outer join is performed based on the condition specified in the ON clause.

SELECT T1.d, T2.c
ON (T1.x = T2.x)

Consider query I, where the filter on the left table is applied before or after the outer join takes place.

SELECT T1.d, T2.c
WHERE T1.x = T2.x (+) and T1.Z > 4;

The ANSI left outer join query J is equivalent to query I.

SELECT T1.d, T2.c
ON (T1.x = T2.x)
WHERE T1.Z > 4;

Lateral Views

In Oracle, ANSI left and right outerjoins are internally expressed in terms of left outerjoined lateral views. In many cases, a left outerjoined lateral view can be merged and the ANSI left (or right) outerjoin can be expressed entirely in terms of Oracle native left outerjoin operator. (A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.)

Consider the ANSI left outer join query K, which is first represented internally as L.

SELECT T1.d, T2.c
ON (T1.x = T2.x and T2.k = 5);

WHERE T1.x = T2.x and T2.k = 5)(+) LV;

The lateral view in query L is merged to yield query M.

SELECT T1.d, T2.c
WHERE T1.x = T2.x (+) and T2.k (+)= 5;

Consider query N, which expresses a left outerjoin in the ANSI join syntax. Currently query N cannot be expressed using the Oracle native left outer join operator.

SELECT T1.m, T2.n
ON (T1.h = 11 and T1.y = T2.y)
WHERE T1.q > 3;

The query N is converted into query O with a left outer-joined lateral view. The lateral view in O cannot be merged, since the filter on the left table specified in the ON clause must be part of the left outerjoin condition.

WHERE T1.h = 11 and T1.y = T2.y)(+) LV
WHERE T1.q > 3;

Consider query P with a disjunction in the outer join condition. Currently N cannot be expressed using the Oracle native left outer join operator.

ON (T1.x = T2.x OR T1.Z = T2.Z);

The query P will be converted into Q with a left outer-joined lateral view containing the disjunctive join condition.

WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;

ANSI Full Outerjoin

Before Oracle 11gR1 all ANSI full outerjoins were converted into a UNION ALL query with two branches, where one branch contained a left outerjoined lateral view and the other branch contained a NOT EXISTS subquery. A native support for hash full outerjoin was introduced in 11gR1 to overcome this problem. When the native full outerjoin, cannot be used, Oracle reverts to the pre-11gR1 strategy.

Consider query R, which specifies an ANSI full outerjoin.

SELECT T1.c, T2.d
ON T1.x = T2.y;

Before 11gR1, Oracle would internally convert query R into S.

SELECT T1.c, T2.d
WHERE T1.x = T2.y (+)
(SELECT 1 FROM T1 WHERE T1.x = T2.y);

With the native support of hash full outerjoin, R will be simply expressed as query T, where the view, VFOJ, is considered unmergeable.

WHERE T1.x F=F T2.y) VFOJ;

Conversion of Outerjoin into Inner Join

Consider query U. Here the filter predicate on the outer-joined table T2 does not contain the outerjoin operator (+); thus it will be applied after the left outerjoin has taken place. This will result in the elimination of all null appended rows of T2. Hence, Oracle converts the outer join into an inner join.

SELECT T1.d, T2.c
WHERE T1.x = T2.x (+) and T2.y > 5;

The ANSI left outer join query V is equivalent to query U, as the WHERE clause in V is applied after the left outer join is performed based on the condition specified in the ON clause.

SELECT T1.d, T2.c
ON (T1.x = T2.x)
WHERE T2.y > 5;

Oracle converts the queries U and V into query W with an inner join.

SELECT T1.d, T2.c
WHERE T1.x = T2.x and T2.y > 5;


Q1: I do not understand the queries N and O. What is the difference between
a filter appearing in the ON Clause or a filter appearing in the WHERE

A1: Consider two tables T11 and T22.

A | B
1 | 2
2 | 3
3 | 5

X | Y
7 | 2
8 | 4
9 | 4

The following ANSI left outer join query N’ involving
the tables T11 and T22 will return three rows, since
the filter, which always fails, is part of the join
condition. Although this join condition, which
comprises both the predicates in the ON clause,
always evaluates to FALSE, all the rows of the left
table T11 are retained in the result.

ON (T11.A > 9 and T11.B = T22.Y);

------ ---------- ---------- ---------
1 2
2 3
3 5

However, if the filter, T11.A > 9, is moved to the WHERE clause,
the query will return zero rows.

Q2: Is the outer to inner join conversion a new feature?

A2: No. This feature has been avaliable since Release 7.

Q3: Has native full outer join been made available in
versions prior to 11gR1?

A3: Yes. It is available in and, but not by


Subscribe to Oracle FAQ aggregator