Re: Filling in "gaps" in data

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 31 Oct 2008 17:56:16 -0700 (PDT)
Message-ID: <178602ec-bfc2-4f99-b2e9-175941a4a2e5@n1g2000prb.googlegroups.com>

On Oct 31, 4:37�pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Oct 31, 11:22�am, Charles Hooper <hooperc2..._at_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
> > � DT.DATE_TIME,
> > � NVL(COUNT(*)) HIT_COUNT
> > FROM
> > � (SELECT
> > � � TO_DATE('31-OCT-2008','DD-MON-YYYY') + (LEVEL/1440) DATE_TIME
> > � FROM
> > � � DUAL
> > � CONNECT BY
> > � � LEVEL<=1000) DT,
> > � HITS
> > WHERE
> > � DT.DATE_TIME=HITS.DATE_TIME(+)
> > GROUP BY
> > � 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. �This 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=100:11:0::::P11_QUESTION_ID:906593600346091624

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<=1000000;

COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1') ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; SELECT
  ROWNUM COUNTER
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

SELECT
  ROWNUM
FROM
  INVENTORY_TRANS
WHERE
  ROWNUM<=1000000;

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

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<=1000000

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=0,e=798,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=173195416371 EXEC #8:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=173195421821 FETCH
#8:c=982806,e=979081,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173196401970 FETCH
#8:c=904806,e=914328,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173197888861 FETCH
#8:c=889206,e=943805,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173199421758 FETCH
#8:c=889206,e=1037772,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173201068951 FETCH
#8:c=920405,e=1393510,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173203052512 FETCH
#8:c=889206,e=930261,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173204571047 FETCH
#8:c=904806,e=1326808,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173206485772 FETCH
#8:c=920406,e=920646,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173208784741 FETCH
#8:c=904806,e=917949,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173210289328 FETCH
#8:c=889205,e=915164,p=0,cr=0,cu=0,mis=0,r=100000,dep=0,og=1,tim=173211790691 FETCH
#8:c=140401,e=138735,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=173212516501

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<=1000000

Plan hash value: 3836375644

|   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<=1000000)

PARSE
#8:c=0,e=22870,p=0,cr=11,cu=0,mis=1,r=0,dep=0,og=1,tim=174586284770 EXEC #8:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=174586289856 FETCH
#8:c=920406,e=921052,p=0,cr=141,cu=0,mis=0,r=100000,dep=0,og=1,tim=174587212022 FETCH
#8:c=889206,e=1522366,p=1,cr=137,cu=0,mis=0,r=100000,dep=0,og=1,tim=174589309337 FETCH
#8:c=889205,e=996714,p=0,cr=137,cu=0,mis=0,r=100000,dep=0,og=1,tim=174590895840 FETCH
#8:c=842406,e=1032087,p=0,cr=137,cu=0,mis=0,r=100000,dep=0,og=1,tim=174592519029 FETCH
#8:c=873605,e=934658,p=1,cr=139,cu=0,mis=0,r=100000,dep=0,og=1,tim=174594040940 FETCH
#8:c=889206,e=2998915,p=60,cr=137,cu=0,mis=0,r=100000,dep=0,og=1,tim=174597624729 FETCH
#8:c=904806,e=2258202,p=101,cr=137,cu=0,mis=0,r=100000,dep=0,og=1,tim=174600471249 FETCH
#8:c=904806,e=2037804,p=103,cr=137,cu=0,mis=0,r=100000,dep=0,og=1,tim=174603098647 FETCH
#8:c=873605,e=1493508,p=19,cr=138,cu=0,mis=0,r=100000,dep=0,og=1,tim=174605180433 FETCH
#8:c=842406,e=895755,p=0,cr=137,cu=0,mis=0,r=100000,dep=0,og=1,tim=174606667248 FETCH #8:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=174607254191

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. Received on Fri Oct 31 2008 - 19:56:16 CDT

This message: [ Message body ]
Previous message: Tim X: "Re: Puzzeling error with package"
In reply to: Mark D Powell: "Re: Filling in "gaps" in data"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message