Path: g2news2.google.com!postnews.google.com!n1g2000prb.googlegroups.com!not-for-mail
From: Charles Hooper <hooperc2...@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Filling in "gaps" in data
Date: Fri, 31 Oct 2008 17:56:16 -0700 (PDT)
Organization: http://groups.google.com
Lines: 251
Message-ID: <178602ec-bfc2-4f99-b2e9-175941a4a2e5@n1g2000prb.googlegroups.com>
References: <MPG.23752a7f359204d6989698@News.Individual.NET> 
	<37d61e27-c8a2-4724-8b0b-55f925d5b167@t39g2000prh.googlegroups.com> 
	<5971087d-b65d-496d-af59-30a8a49ed79e@x1g2000prh.googlegroups.com>
NNTP-Posting-Host: 205.208.133.101
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1225500976 1470 127.0.0.1 (1 Nov 2008 00:56:16 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 1 Nov 2008 00:56:16 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: n1g2000prb.googlegroups.com; posting-host=205.208.133.101; 
	posting-account=xVXeFwkAAAAz3xgWc6VZyjXxx1jx4jb4
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; 
	.NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506),gzip(gfe),gzip(gfe)

On Oct 31, 4:37=A0pm, Mark D Powell <Mark.Pow...@eds.com> wrote:
> On Oct 31, 11:22=A0am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > Once the rows with the missing time elements are available, you could
> > outer join your table to this inline view, something like this:
> > SELECT
> > =A0 DT.DATE_TIME,
> > =A0 NVL(COUNT(*)) HIT_COUNT
> > FROM
> > =A0 (SELECT
> > =A0 =A0 TO_DATE('31-OCT-2008','DD-MON-YYYY') + (LEVEL/1440) DATE_TIME
> > =A0 FROM
> > =A0 =A0 DUAL
> > =A0 CONNECT BY
> > =A0 =A0 LEVEL<=3D1000) DT,
> > =A0 HITS
> > WHERE
> > =A0 DT.DATE_TIME=3DHITS.DATE_TIME(+)
> > GROUP BY
> > =A0 DT.DATE_TIME;
>
> > There are of course more than 1000 minutes in a day (right around
> > 1440), so some adjustment will be necessary.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.

> Charles have you ever found an article or any form of documentation
> that explains how connect by level can be used on dual with 10g+ to
> create a series. =A0This does not work on 9.2 and below and I have never
> found any useful information on this technique.
>
> -- Mark D Powell

Hi Mark,

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   If we are
able to consider that what Tom Kyte writes is an extension of the
Oracle documentation, the method is documented to an extent:
http://www.oracle.com/technology/oramag/oracle/07-mar/o27asktom.html
"If you are in Oracle9i Release 2 and above. You can skip the function
altogether and just use DUAL to generate rows and parse the string."

http://tkyte.blogspot.com/2007/02/what-is-your-fizzbuzz-factor.html

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

http://asktom.oracle.com/pls/asktom/f?p=3D100:11:0::::P11_QUESTION_ID:90659=
3600346091624

There are cautions with using connect by level, as it may lead to
excessive CPU consumption.  I noticed this problem during testing some
time ago, and attempted to reproduce a test case which demonstrates
the problem.  In my test, the excessive CPU consumption was not as
severe as I expected.

Test case, first with connect by level, and then by using a narrow
table containing 1,000,000 rows:
CREATE TABLE T1 (C1 NUMBER) PCTFREE 0;

INSERT INTO
  T1
SELECT
  ROWNUM
FROM
  INVENTORY_TRANS
WHERE
  ROWNUM<=3D1000000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=3D>USER,TABNAME=3D>'T1')

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT
  ROWNUM COUNTER
FROM
  DUAL
CONNECT BY
  LEVEL<=3D1000000;

SELECT
  ROWNUM
FROM
  INVENTORY_TRANS
WHERE
  ROWNUM<=3D1000000;

SELECT
  COUNT(*)
FROM
(SELECT
  ROWNUM
FROM
  T1
WHERE
  ROWNUM<=3D1000000);

SELECT
  SYSDATE
FROM
  DUAL;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

I used a custom program to execute the queries fetching 100,000 rows
at a time while generating a typical DBMS Xplan and a 10046 trace at
level 8.  The highlights of the test results:
SQL_ID  85b6yxsrp1c74, child number 0
-------------------------------------
SELECT    ROWNUM COUNTER  FROM    DUAL  CONNECT BY    LEVEL<=3D1000000

Plan hash value: 1731520519

---------------------------------------------------------------------------=
---
| Id  | Operation                     | Name | Rows  | Cost (%CPU)|
Time     |
---------------------------------------------------------------------------=
---
|   0 | SELECT STATEMENT              |      |       |     2
(100)|          |
|   1 |  COUNT                        |      |       |
|          |
|   2 |   CONNECT BY WITHOUT FILTERING|      |       |
|          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)|
00:00:01 |
---------------------------------------------------------------------------=
---

PARSE #8:c=3D0,e=3D798,p=3D0,cr=3D0,cu=3D0,mis=3D1,r=3D0,dep=3D0,og=3D1,tim=
=3D173195416371
EXEC #8:c=3D0,e=3D64,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D1,tim=
=3D173195421821
FETCH
#8:c=3D982806,e=3D979081,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173196401970
FETCH
#8:c=3D904806,e=3D914328,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173197888861
FETCH
#8:c=3D889206,e=3D943805,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173199421758
FETCH
#8:c=3D889206,e=3D1037772,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173201068951
FETCH
#8:c=3D920405,e=3D1393510,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173203052512
FETCH
#8:c=3D889206,e=3D930261,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173204571047
FETCH
#8:c=3D904806,e=3D1326808,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173206485772
FETCH
#8:c=3D920406,e=3D920646,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173208784741
FETCH
#8:c=3D904806,e=3D917949,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173210289328
FETCH
#8:c=3D889205,e=3D915164,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D100000,dep=3D0,og=
=3D1,tim=3D173211790691
FETCH
#8:c=3D140401,e=3D138735,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D1,t=
im=3D173212516501

17.10013 seconds elapsed start to end
9.235259 seconds CPU during fetch

-----------------------------------
SQL_ID  c2c36dfayx56p, child number 0
-------------------------------------
SELECT    ROWNUM  FROM    T1  WHERE    ROWNUM<=3D1000000

Plan hash value: 3836375644

---------------------------------------------------------------------------=
-
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
Inst   |
---------------------------------------------------------------------------=
-
|   0 | SELECT STATEMENT   |      |       |   313 (100)|
|        |
|*  1 |  COUNT STOPKEY     |      |       |            |
|        |
|   2 |   TABLE ACCESS FULL| T1   |  1003K|   313  (13)| 00:00:02
|     LT |
---------------------------------------------------------------------------=
-

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=3D1000000)

PARSE
#8:c=3D0,e=3D22870,p=3D0,cr=3D11,cu=3D0,mis=3D1,r=3D0,dep=3D0,og=3D1,tim=3D=
174586284770
EXEC #8:c=3D0,e=3D42,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D1,tim=
=3D174586289856
FETCH
#8:c=3D920406,e=3D921052,p=3D0,cr=3D141,cu=3D0,mis=3D0,r=3D100000,dep=3D0,o=
g=3D1,tim=3D174587212022
FETCH
#8:c=3D889206,e=3D1522366,p=3D1,cr=3D137,cu=3D0,mis=3D0,r=3D100000,dep=3D0,=
og=3D1,tim=3D174589309337
FETCH
#8:c=3D889205,e=3D996714,p=3D0,cr=3D137,cu=3D0,mis=3D0,r=3D100000,dep=3D0,o=
g=3D1,tim=3D174590895840
FETCH
#8:c=3D842406,e=3D1032087,p=3D0,cr=3D137,cu=3D0,mis=3D0,r=3D100000,dep=3D0,=
og=3D1,tim=3D174592519029
FETCH
#8:c=3D873605,e=3D934658,p=3D1,cr=3D139,cu=3D0,mis=3D0,r=3D100000,dep=3D0,o=
g=3D1,tim=3D174594040940
FETCH
#8:c=3D889206,e=3D2998915,p=3D60,cr=3D137,cu=3D0,mis=3D0,r=3D100000,dep=3D0=
,og=3D1,tim=3D174597624729
FETCH
#8:c=3D904806,e=3D2258202,p=3D101,cr=3D137,cu=3D0,mis=3D0,r=3D100000,dep=3D=
0,og=3D1,tim=3D174600471249
FETCH
#8:c=3D904806,e=3D2037804,p=3D103,cr=3D137,cu=3D0,mis=3D0,r=3D100000,dep=3D=
0,og=3D1,tim=3D174603098647
FETCH
#8:c=3D873605,e=3D1493508,p=3D19,cr=3D138,cu=3D0,mis=3D0,r=3D100000,dep=3D0=
,og=3D1,tim=3D174605180433
FETCH
#8:c=3D842406,e=3D895755,p=3D0,cr=3D137,cu=3D0,mis=3D0,r=3D100000,dep=3D0,o=
g=3D1,tim=3D174606667248
FETCH #8:c=3D0,e=3D18,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D1,tim=
=3D174607254191

20.969421 seconds elapsed start to end
8.829657 seconds CPU during fetch

It is a bit interesting that the connect by level method reported no
consistent reads on 10.2.0.3 and 11.1.0.7.  It is also a bit
interesting to look at the CPU consumption when 0 rows were returned.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.