On Favorites and CONNECT BY

Our technologist picks, expands, loads, and dates.

May/June 2005

What is your favorite Oracle Database feature, irrespective of version?

This is actually very easy to answer. Before I came to Oracle, the feature that turned me on to Oracle and made me drop the others was quite simply the concurrency model, as described eloquently in Oracle Database Concepts.

I cannot imagine doing database work without Oracle's data concurrency and consistency model. In a nutshell, Oracle's data concurrency allows us to have reads that do not block writes and writes that do not block reads.

For people running queries, it is as if they are the sole users of the database—there is no blocking or locking. And data consistency gives us the "read consistent" attribute, which I also refer to as "the correct answer attribute." I strongly encourage everyone to read that referenced link to gain a better understanding of this fundamental Oracle difference.

For a top 10 list of favorite features for Oracle Database 10g, you can see my 2004 Oracle OpenWorld presentation (available at asktom.oracle.com under the Files tab). In the presentation, I discuss my 10 favorite new things in Oracle Database 10g Release 1:

• Flashing back

• Faster PL/SQL

• Oracle HTML DB

• Oracle Automatic Storage Management (ASM)

• DBMS_ADVANCED_REWRITE

• SQL profiles

• Easier online redefinitions

• Case-insensitive searching

• Fewer invalidations

• Online segment shrinking

I have a table containing a simple hierarchy:

SQL> DESC test_table;
 Name        Null?      Type
 ------      ------     --------------
 A                      NUMBER(38)
 B                      NUMBER(38)

With the following data:

SQL> SELECT * FROM test_table;

          A              B
-----------     ----------
          1             -1
          2              1
          3              1
          4              2
          5              2
          6              4
          7              4
          8              5
          9              5
         10              3
         11              3

11 rows selected.

I want a query that will give me each node and all its ancestors (parents). That is, I need to have as part of the output:

          A              B
-----------     ----------
...
          9              9
          9              5
          9              2
          9              1
...

I need this because 9 relates to 9, 9 relates to 5 (from the original table), 9 indirectly relates to 2 (by 5), and so on. How can I achieve this in a query?

At first glance, this seemed really hard, but it is pretty easy in Oracle9i Database and even easier in Oracle Database 10g. We can get the entire hierarchy easily:

SQL> SELECT a
  2    FROM test_table
  3    CONNECT BY PRIOR b=a
  4  /

That actually gets your column B in the desired output. Now we need to get the root node of each of the rows in this hierarchy. Using the somewhat new SYS_CONNECT_BY_PATH function (Oracle9i Database and later), we can get each root node. Using the following query, we can see what the SYS_CONNECT_BY_PATH function returns:

SQL> SELECT a,
  2    SYS_CONNECT_BY_PATH (a,'.') scbp
  3    FROM test_table
  4    CONNECT BY PRIOR b=a
  5    ORDER BY 2
  6  /

         A    SCBP
----------     ---------
...
         9    .9
         5    .9.5
         2    .9.5.2
         1    .9.5.2.1
	    ...

As you can see, we are starting to get what we need—the front of each value of SCBP is the root of the hierarchy, and column A is the tail. Now we work a little substring ( SUBSTR ) magic:

SQL> SELECT a,
  2     TO_NUMBER(
  3      SUBSTR(scbp,1,
  4             INSTR(scbp,'.')-1)
  5      ) b
  6    FROM (
  7  SELECT a,
  8    LTRIM(
  9      SYS_CONNECT_BY_PATH(a,'.'),
 10         '.') ||'.' scbp
 11    FROM test_table
 12   CONNECT BY PRIOR b=a
 13         )
 14   ORDER BY 2
 15 /

          A              B
-----------     ----------
...
          9              9
          9              5
          9              2
          9              1
...

And there we go. You'll be happy to know that in Oracle Database 10g, this is even easier. There are a bunch of new functions for CONNECT BY queries, such as:

• CONNECT_BY_ROOT —returns the root of the hierarchy for the current row; this greatly simplifies our query. (See below for an example).

• CONNECT_BY_ISLEAF —is a flag to tell you if the current row has child rows.

• CONNECT_BY_ISCYCLE —is a flag to tell you if the current row is the beginning of an infinite loop in your hierarchy. For example, if A is the parent of B, B is the parent of C, and C is the parent of A, you would have an infinite loop. You can use this flag to see which row or rows are the beginning of an infinite loop in your data.

• NOCYCLE —lets the CONNECT BY query recognize that an infinite loop is occurring and stop without error (instead of returning a CONNECT BY loop error).

For this question, the first new function, CONNECT_BY_ROOT , is relevant. The following query does the work for us in Oracle Database 10g:

SQL> SELECT CONNECT_BY_ROOT a cbr,
  2         a b
  3     FROM test_table
  4   CONNECT BY PRIOR b=a
  5   ORDER BY 1
  6  /

       CBR            B
-----------     ----------
...
         9            9
         9            5
         9            2
         9            1
...

I have a table in which there are two columns: Date of Birth as DOB and Date of Death as DOD. Both are dates. I want to create a view, and I want the difference of the dates in exact years and days. For example, the difference between the two dates 25-JAN-1910 and 17-JAN-2005 is 94 years and 358 days.

This is pretty easy to do—especially if you know which of the two dates is always going to be greater than the other, as you seem to (DOB should always be less than DOD). In this case, you can use the following:

SQL> SELECT DOB, DOD,
  2  years,
  3  DOD-ADD_MONTHS(DOB,years*12) days
  4    FROM
  5    (
  6  SELECT DOB, DOD,
  7  TRUNC(MONTHS_BETWEEN(DOD,DOB)
/12)
  8            years
  9    FROM t
 10    )
 11  /

DOB             DOD             YEARS             DAYS
------------    -----------     ------            -----
25-JAN-10       17-JAN-05        94               358

The inline view, on line 7, computes the total years between the two dates. We do that by getting the months between the two dates and dividing by 12. (There are always 12 months in a year, and because the number of days in a year varies, we need to use months.) Once we have the years between the dates, computing the days remaining is rather simple. We just add that many years to the lesser of the two columns (DOB) and subtract that resulting date from the greater of the two columns (DOD). That is what we did on line 3. Then we have the years and days between two dates.

Now, if we didn't know which date would be greater than the other in all cases, a quick fix is to use the built-in GREATEST and LEAST functions:

SQL> SELECT DOB, DOD,
  2    ABS(years) years,
  3   GREATEST(DOB,DOD)
  4    - ADD_MONTHS(LEAST(DOB,DOD),
  5               ABS(12*years) ) days
  6    FROM
  7    (
  8  SELECT DOB, DOD,
  9  TRUNC(MONTHS_BETWEEN(DOB,DOD)
/12)
 10           years
 11    FROM t
 12    )
 13  /

This query returns the same result, but it works with any pair of dates, regardless of which is larger.

Is there any way to invoke SQL Loader from within a PL/SQL stored procedure, rather than running it from UNIX?

This used to be a dreaded question in the days of old. In Oracle8i Database and earlier, the answer was long and involved and would include using Java stored procedures or perhaps a C-based external procedure. In any case, it was hard.

In Oracle9i Database and later, this becomes very easy. We would use an external table, which gives us the ability to query a file as if it were a database table. So, a call to SQLLDR from SQL or PL/SQL becomes simply INSERT INTO table SELECT * FROM external_table . That is, we just use SQL.

A quick and easy way to get started with external tables is to take some of your old-fashioned SQL Loader control (CTL) files and convert them into external table definitions. This will help us understand the syntax and get CREATE TABLE statements that work the first time without a lot of effort. For example, using the supplied demonstration control files in $ORACLE_HOME/rdbms/demo, we can issue from the command line SQLLDR SCOTT/TIGER ulcase1.ctl external_table=generate_only . That will create a file ulcase1.log (it won't actually load the data; we asked SQL Loader to generate only here). In that log file, we'll find:

• A CREATE DIRECTORY statement. External tables require a database directory object.

• A CREATE TABLE statement for the external table. This will have your converted SQL Loader script embedded in it.

• An INSERT statement that does the loading.

• A pair of DROP statements to clean up.

You are most interested in the CREATE TABLE statement. Listing 1 shows you what the CREATE TABLE statement in the ulcase1.ctl file would produce. You would edit this CREATE TABLE statement to give the external table whatever name you like, change the directories, and so on. All of the hard work has been done for you, though.

Code Listing 1: Generated external table CREATE TABLE statement

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
  DEPTNO NUMBER(2),
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ulcase1.bad'
    LOGFILE 'ulcase1.log_xt'
    READSIZE 1048576
    SKIP 20
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      DEPTNO CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      DNAME CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      LOC CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    'ulcase1.ctl'
  )
)REJECT LIMIT UNLIMITED

With external tables, a conventional path SQL load becomes INSERT INTO table SELECT * FROM external_table . A direct path load becomes INSERT /*+ APPEND */ INTO table.... Even better, a parallel direct path load is as easy as ALTER TABLE external_table PARALLEL and CREATE TABLE new_table PARALLEL AS SELECT * FROM external_table . Additionally, you have the complete power of SQL to process the data, apply complex WHERE clauses, and so on. It is better than SQL Loader.

I wrote a stored procedure, DISABLECONS, which dynamically disables constraints. It creates an ALTER TABLE tablename DISABLE CONSTRAINT constraint_name statement and, using EXECUTE IMMEDIATE, executes it.

This procedure is designed to be used by multiple schemas that have similar table structures. The user provides the table name and the constraint name—everything else is automatic for the schema. When I run this procedure in different schemas, I get the following error:

SQL> EXECUTE common.disablecons('BCBS');
BEGIN common.disablecons('BCBS'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "COMMON.DISABLECONS",
line 24
ORA-06512: at line 1

However, when I run the command directly, I do not get the error. Any idea why this is happening?

This comes up so frequently that I have a FAQ page about it on my site. The bottom line is this: Stored procedures execute, by default, with the base privilege of the definer of the routine (and hence they are known as definer-rights routines). The base set of privileges is only those privileges granted directly to the definer of the routine, as well as anything granted directly to PUBLIC . The page referenced above shows how to test if your procedure can run by issuing SET ROLE NONE in SQL*Plus.

That aside, you probably want this to be an invoker-rights routine (using AUTHID current_user ). What that means is that if GEORGE runs this, it'll run with his privileges (roles and all), and if MARY runs it, it'll run with her privileges (roles and all). Otherwise, you (the owner of this procedure) will need the extremely powerful ALL types privileges granted directly to you so you can disable any constraint anywhere in the system. If you have Expert One-on-One Oracle , you'll see that I go into great detail on this topic of invoker and definer rights and when to use each. Additionally, the PL/SQL User's Guide and Reference, has a good section titled "Invoker Rights Versus Definer Rights."

Is there any way to return errors on floating-point numbers going into number types? Oracle automatically rounds them. Is there a way to cause a constraint violation instead?

The following code demonstrates the problem to which you refer:

SQL> CREATE TABLE t
  2  ( X NUMBER (3)
  3  );
Table created.

SQL> INSERT INTO t VALUES ( 134 );
1 row created.

SQL> INSERT INTO t VALUES ( 134.1 );
1 row created.

SQL> SELECT * FROM t;

          X
 ----------
       134
       134

By default, if you define something as, say, NUMBER(3) , Oracle Database will round the value, if possible, to make the data fit. Only if the data cannot fit after rounding will it be rejected.

Now, if we enforce the NUMBER(3) not as a data definition but rather as a constraint, we can, in fact, change that:

SQL> CREATE TABLE t
  2  ( X NUMBER
  3    CHECK
  4    (CAST(X AS NUMBER(3)) = x)
  5  );
Table created.

SQL> INSERT INTO t VALUES ( 134 );
1 row created.

SQL> INSERT INTO t VALUES ( 134.1 );
insert into t values ( 134.1 )
*
ERROR at line 1:
ORA-02290: check constraint
(X.SYS_C005627) violated

This approach uses the built-in CAST function to convert X into a NUMBER(3) and apply the rounding. When we compare the rounded value to the non-rounded value, they must be identical, or else we get a constraint violation. And if we attempt to insert a number that cannot fit into a NUMBER(3) after rounding, we get the expected error:

SQL> INSERT INTO t VALUES ( 1234 );
insert into t values ( 1234 )
     *
ERROR at line 1:
ORA-01438: value larger than
specified precision allows for
this column

The number is just too large.

I need to prepare a document that talks about materialized views and summary tables (user-created). I would like to know the pros and cons of both of these.

There is no such thing as a user-created summary table; there are just user-created tables with no metadata about them, no control, no management, and no administration. I mean, a table T created as a SELECT FROM 14 tables is just a table T . You don't know the defining query (gone are those details), you don't know if the information contained in T is even relatively current (gone is that detail), your end user has to understand the fine art of "indexing intelligently," and so on. I see virtually nothing positive in letting end users create tables like that.

Materialized views (MVs), on the other hand,

• Behave like indexes for a data warehouse. They are used automatically when useful, and end users don't have to be trained to use them.

• Can be recommended by the system (as it watches you run queries, it can say, "You know, if you had this MV in place....").

• Contain the metadata about from whence they came (the defining queries and the dependencies).

• Know when they are stale.

• Know how to incrementally refresh themselves.

• Can be administered professionally (scheduled for refreshes and maintained correctly).

• Can be incrementally refreshed even better with partitioning (partition change tracking).

See the Oracle9i Data Warehousing Guide, and get as many reasons to use MVs as you like. That document has a very comprehensive section on this technology.

As an administrator, I see zero advantages to end users creating and maintaining tables. As an end user, I see no advantages—I just become another DBA if I start doing that.

ASK Tom
Oracle Vice President Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.

READ
more on the concurrency model
Oracle Database Concepts

more on Tom's Oracle 10g Top 10

 more on invoker and definer rights

more on materialized views
Oracle9i Data Warehousing Guide

DOWNLOAD
Oracle Database 10g