Re: Filling in "gaps" in data
Date: Wed, 5 Nov 2008 08:00:08 -0800 (PST)
Message-ID: <0e23ce69-269a-4269-b34a-8e213d12e67f@s1g2000prg.googlegroups.com>
On Nov 4, 2:27 am, William Robertson <williamr2..._at_googlemail.com>
wrote:
> On Nov 1, 12:56 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > It is a bit interesting that a search of the Oracle documentation for
> > "connect by level" returns 0 results. A Metalink search for the same
> > phrase only returns a couple hits, most of which report bugs. I first
> > saw the method of using connect by level used in this Usenet forum,
> > and I recall having difficulty understanding why it worked
>
> There is no special CONNECT BY LEVEL syntax, which is why this exact
> phrase is not documented. There is just "CONNECT BY [condition]". The
> documentation then links to "Conditions" ("A condition specifies a
> combination of one or more expressions and logical (Boolean) operators
> and returns a value of TRUE, FALSE, or UNKNOWN.") I personally prefer
> ROWNUM <= n, but it could be any condition you like that is true until
> you want it to be false, e.g.
>
> SELECT ROWNUM FROM dual CONNECT BY DBMS_RANDOM.VALUE(0,1) > 0.5
William,
Your explanation and example make perfect sense, if you read the above as connect child rows to parent rows until the specified condition is false. The documentation does not seem to be quite as clear: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm "CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy."
Note the term "relationship", which seems to indicate that the relationship between child rows and parent rows must be defined.
The samples in the documentation all include the "PRIOR" syntax, which might be part of the source of the confusion (at least it was for me as I was first trying to understand this syntax). From the same link: "PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error."
The above hints at the possibility of using something other than an equal sign in the connect by syntax.
Regardless of how clear the documentation is, your explanation describes how it actually works. Thanks for the reply.
Correcting a couple editing problems within my posts of this thread:
NVL(COUNT(*)) HIT_COUNT
Should be:
NVL(COUNT(*),0) HIT_COUNT
SELECT
COUNT(*)
FROM
(SELECT
ROWNUM
FROM
T1
WHERE
ROWNUM<=1000000);
Actual SQL statement which was executed during testing:
SELECT
ROWNUM
FROM
T1
WHERE
ROWNUM<=1000000;
SELECT
ROWNUM
FROM
INVENTORY_TRANS
WHERE
ROWNUM<=1000000;
The above was for a timed test using an existing table in the database with at least 1,000,000 rows. The time required for that test to complete was so significantly long, that I decided not to post the results.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 05 2008 - 10:00:08 CST