OS & Oracle

At least, there will be two ways to handle left outer join, one is the traditional way to handle the case such as t1.x = t2.x (+), and the new way we saw in last article to handle the new ANSI syntax. So we will begin with such two execution plans.

cbo@ORCL> select * from t1 left outer join t2 on (t1.x = t2.x);

Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     6 |    78 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     6 |    78 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     5 |    30 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     8 |    56 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."X"="T2"."X"(+))

cbo@ORCL> select * from t1 left outer join t2 on (t1.x = t2.x and t1.c = 11);

Execution Plan
----------------------------------------------------------
Plan hash value: 2394896828

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |   225 |    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER |      |     5 |   225 |    12   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T1   |     5 |    30 |     2   (0)| 00:00:01 |
|   3 |   VIEW              |      |     1 |    39 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     7 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T1"."X"="T2"."X" AND "T1"."C"=11)

The output of the first sample should be like the output of

select * from t1,t2 where t1.x = t2.x(+);

For the second sample, we have no a simple corresponding  (+) expression. But from the execution plan, we can guess one possibility, the outline is an left outer join, composite of one table and one view, the view come from t2, and have predicates from t1.c:

select *
  from t1,
       (select t2.*
          from t1, t2
         where t1.x = t2.x
           and c = 11) t2
 where t1.x = t2.x(+);

and corresponding execution plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 1247508653

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     5 |   225 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER     |      |     5 |   225 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T1   |     5 |    30 |     2   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    39 |     5  (20)| 00:00:01 |
|*  4 |    HASH JOIN         |      |     1 |    13 |     5  (20)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T1   |     1 |     6 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |     8 |    56 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."X"="T2"."X"(+))
   4 - access("T1"."X"="T2"."X")
   5 - filter("C"=13)

This is complicated than the sample output, what Oracle really does to this type of left outer join can be investigated through the 10053 trace, below is the transformed SQL.

SELECT T1.C                    C,
       T1.X                    X,
       T1.Y                    Y,
       lv.D_0 D,
       lv.X_1 X,
       lv.Y_2 Y
  FROM CBO.T1 T1,
       LATERAL((SELECT T2.D D_0, T2.X X_1, T2.Y Y_2
                 FROM CBO.T2 T2
                WHERE T1.X = T2.X
                  AND T1.C = 11))(+) lv

A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause. 

Like the way I show above, we can analyze the statement below, and translate them into the  (+) way.

select t1.*,t2.*
from t1 left outer join t2
on (t1.x=t2.x and (t2.d>23 or t2.d<23));

Another different issue is translating the statement below:

select t1.*,t2.* from t1 left outer join t2 on (t1.x=t2.x or t1.y = t2.y);

Oracle still use the Lateral View to generate the execution plan, but I failed to find an suitable (+) way to solve this problem, what I found is another way:

select *
  from t1, t2
 where t1.x = t2.x
    or t1.y = t2.y
union all
select t1.*, null, null, null
  from t1
 where not exists (select 1
          from t2
         where t2.x = t1.x
            or t1.y = t2.y);

This method is similar to the issue about “full outer join”.

SELECT T1.c, T2.d
FROM T1 FULL OUTER JOIN T2
ON T1.x = T2.y;

will be translated to

SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x = T2.y (+)
UNION ALL
SELECT NULL, T2.d
FROM T2
WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE T1.x = T2.y);

The general rule is that degrade left outer join to equi-join, and degrade full outer join to left outer join.

Reference:

The articles below would be useful to understand this topic:

OuterJoins in Oracle from http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html

ANSI Outer Joins And Lateral Views from http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/

What’s in a Condition? from http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html

Oracle guys usually use Oracle native left outer join syntax to do left outer join, such as

cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+);

         C          X          Y          D          X          Y
---------- ---------- ---------- ---------- ---------- ----------
        11          1         51         21          1         51
        13          3         53         23          3         40
        15          5         55         25          5         54
        14          4         54
        12          2         52

An more advanced way is:

cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+) and t2.y(+)>50;

         C          X          Y          D          X          Y
---------- ---------- ---------- ---------- ---------- ----------
        11          1         51         21          1         51
        15          5         55         25          5         54
        14          4         54
        13          3         53
        12          2         52

Be note, this is different from:

cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+) and t2.y>50;

         C          X          Y          D          X          Y
---------- ---------- ---------- ---------- ---------- ----------
        11          1         51         21          1         51
        15          5         55         25          5         54

The t2.y(+) > 50, as a join condition, is applied at join time, but t2.y>50 is applied after the result set is return from joining. Also note that, as t2 > 50 make sure that the rows in t2 is not null, so the SQL statement will become an  normal equi-join. This is clear in the execution plans:

cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+) and t2.y(+)>50;

Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    65 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |    65 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     5 |    30 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     7 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."X"="T2"."X"(+))
   3 - filter("T2"."Y"(+)>50)

cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+) and t2.y>50;

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    13 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |     7 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |     5 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."X"="T2"."X")
   2 - filter("T2"."Y">50)

But this method have its own limitations. The SQL Reference list such limitations:

1. You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
2. The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
3. If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
4. The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
5. You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:
   – The following statement is not valid:
   SELECT employee_id, manager_id
      FROM employees
      WHERE employees.manager_id(+) = employees.employee_id;
   However, the following self join is valid:SELECT e1.employee_id, e1.manager_id, e2.employee_id
      FROM employees e1, employees e2
      WHERE e1.manager_id(+) = e2.employee_id
      ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;
6. The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
7. A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
8. A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.

It’s a little confused to read these statements, for the limitation I highlighted, there are two variations:

1. I can’t add OR operand predicates on the right table at join time, such the one below expressed in ANSI-SQL.

select t1.c,t2.d from t1 left outer join t2 on (t1.x = t2.x and (t2.y>23 or t2.y<23) );

2. I can’t combine OR operands as left outer join condition. such the one below expressed in ANSI-SQL.

select t1.c,t2.d from t1 left outer join t2 on (t1.x = t2.x or t1.y = t2.y);

Another limitation is that, I can’t add predicate on the left table at join time, such as the one below expressed in ANSI-SQL.

select * from t1 left outer join t2 on (t1.x = t2.x and t1.c = 11);

Clearly, Oracle recommend use ANSI-SQL style left outer join syntax, just as the examples above.

But another question comes, how Oracle implement ANSI-SQL left outer join syntax, and how to rewrite these unsupported statements in native/complex ways?

Compiling from source is not an productive way to install software, the binary distribution will cease a lot of trouble when installing. It will be perfect will one distribution vendor bundle all of the necessary development software together and make the common configuration and optimization, and make the management easy to combine some management tools.

Zend Core for Oracle come out! "Zend Core is a Certified PHP distribution that includes a stable and reliable environment for managing PHP. This environment improves PHP development by removing the current hardship in manually building and setting up an entire PHP environment. Zend Core also acts as an "efficiency facilitator" to improve the PHP implementation and management process for managing PHP development and production environments.
Zend Core does not require any complicated configurations or settings in order to run. After installation, Zend Core is ready to run. The only thing left to do is to login to the application with the password that was created in the installation process."

The URL to download the latest version: http://www.oracle.com/technology/tech/php/zendcore/index.html

Just download it and execute the install scripts, remember to install the additional components, including Zend Framework, Mysql, PHPMyAdmin.

After installing, the directory layout(in /usr/local/Zend) is like:

|– Core
|   |– GUI
|   |– bin
|   |– cgi-bin
|   |– etc
|   |– include
|   |– lib
|   |– logs
|   |– modules
|   |– mysql
|   |– sbin
|   |– setup
|   `– share
|– ZendFramework
|   |– INSTALL.txt
|   |– LICENSE.txt
|   |– NEWS.txt
|   |– README.txt
|   |– VERSION.txt
|   |– demos
|   |– library
|   `– tests
`– apache2
    |– LICENSE
    |– bin
    |– build
    |– cgi-bin
    |– conf
    |– error
    |– htdocs
    |– icons
    |– include
    |– lib
    |– logs
    |– man
    |– manual
    `– modules

And the Web page is like this:

The Mysql link to PHPMyAdmin:

All is ready for you to explore!

For example, let’s test the OCI functionalities:

create the file conn.php as last article, and output is like this:

Actually, the traditional way is compiling source from scratch. Below is information from OTN:
Installing Apache, PHP, JDeveloper, and the PHP Extension on Linux

Apache 1.3

1. Login as Oracle user.
2. Copy/download apache_1.3.31.tar.gz
3. tar -zxf apache_1.3.31.tar.gz
4. cd apache_1.3.31
5. Configure:

   ./configure --enable-module=so --prefix=$HOME/apache --with-port=8888

6. Build: make
7. Install: make install
8. Start Apache: $HOME/apache/bin/apachectl start
9. Go to a browser and check that http://localhost:8888/ returns the default "Powered by Apache" page.
10. Stop Apache: $HOME/apache/bin/apachectl stop

PHP 4.3

Before you proceed, make sure that the packages "bison" and "flex" are installed, because PHP needs them.

1. Copy/download php-4.3.9.tar.gz
2. tar -zxf php-4.3.9.tar.gz
3. Set environment variable ORACLE_SID
4. Set environment variable ORACLE_HOME
5. Set environment variable LD_LIBRARY_PATH to $ORACLE_HOME/lib:${LD_LIBRARY_PATH}
6. Configure (make sure to enter this as one long line):

   ./configure --prefix=$HOME/php --with-apxs=$HOME/apache/bin/apxs
    --with-config-file-path=$HOME/apache/conf
    --with-oci8=$ORACLE_HOME --enable-sigchild

7. Build: make
8. Install: Make install
9. cp php.ini-recommended $HOME/apache/conf/php.ini
10. Edit $HOME/apache/conf/httpd.conf and add:

    AddType application/x-httpd-php        .php
    AddType application/x-httpd-php-source .phps

11. Restart Apache
12. create $HOME/apache/htdocs/phpinfo.php as:

       1: <?php

       2:   phpinfo();

       3: ?>

13. In a browser load http://localhost:8888/phpinfo.php. Check that the OCI module is listed.

Following exactly the steps above, you will get PHP + Apache running on Linux.

NOTES:–with-apxs in step 6 when installing PHP is important, this let the installation process to compile the OCI library for the apache and make it available to Apache.

For testing the OCI support in PHP, create $HOME/apache/htdocs/connect.php as:

   1: <?php 

   2:  

   3: define('ORA_CON_UN', 'scott');

   4: define('ORA_CON_PW', 'tiger');

   5: define('ORA_CON_DB', 'MYSID');

   6:  

   7: $conn = OCILogon(ORA_CON_UN, ORA_CON_PW , ORA_CON_DB );

   8:  

   9: if (!$conn) {

  10: exit;

  11:   }

  12:  

  13: echo OCIServerVersion($conn) ."<br>\n";

  14: echo "Connected as ".ORA_CON_UN."</br>\n";

  15: echo date('Y-m-d H:i:s')."<br><br>\n";

  16:  

  17: $query = 'select * from emp';

  18:  

  19: $stid = OCIParse($conn, $query);

  20: OCIExecute($stid, OCI_DEFAULT);

  21: print "<table border='1'>\n";

  22: while ($succ = OCIFetchInto($stid, $row, OCI_RETURN_NULLS)) {

  23: print "<tr>\n";

  24: foreach ($row as $item) {

  25: print '<td>';

  26: print isset($item)?htmlentities($item):'?';

  27: print "</td>\n";

  28:     }

  29: print "</tr>\n";

  30:   }

  31: print "</table>\n";

  32:  

  33: OCILogoff($conn);

  34: ?>

In a browser load http://localhost:8888/connect.php. Check that the OCI driver works.