Re: Case for Index Organized table?

From: Jeremy Paul Schneider <jeremy.schneider_at_ardentperf.com>
Date: Mon, 23 Apr 2007 17:02:21 -0500
Message-ID: <18be0f260704231502o9f6b4d7m834111afe666159a@mail.gmail.com>

That's a really interesting question. I bounced an email to a few other DBA's at ITC to see what they'd say...

I also started thinking right away that you could do something with SQL analytics. I was in the middle of working up an example when Cesar Lopez, another DBA at ITC, dropped me an email almost letter-for-letter what I was doing - but he beat me to the finish line so I'll just copy his example here. Also, Dan Norris here at ITC commented that in data warehouses it's not so uncommon to build a dimension table as you're suggesting, or possibly an inline view...

Analytics are a cool and very fast solution but will only show you gaps, and will not show each individual missing time of more than one is missing in a row. Here's an example:

alter session set nls_date_format='mi:ss';

create table t1 (a date,b number);

insert into t1 values(to_date('12:00','mi:ss'),69);
insert into t1 values(to_date('12:01','mi:ss'),69);
insert into t1 values(to_date('12:02','mi:ss'),69);
insert into t1 values(to_date('12:03','mi:ss'),69);
insert into t1 values(to_date('12:05','mi:ss'),69);
insert into t1 values(to_date('12:06','mi:ss'),69);
insert into t1 values(to_date('12:08','mi:ss'),69);
insert into t1 values(to_date('12:09','mi:ss'),69);
insert into t1 values(to_date('12:10','mi:ss'),69);

commit;

select a-1/24/3600 as missing from
(
select a,b,(a-lag(a) over (order by a,b))*3600*24 as TIME from t1
)
where TIME>1
;

MISSI

12:04
12:07

delete from t1 where a='12:06';
commit;
select a-1/24/3600 as missing from
(
select a,b,(a-lag(a) over (order by a,b))*3600*24 as TIME from t1
)
where TIME>1
;

MISSI

12:04
12:07

On 4/23/07, Brady, Mark <Mark.Brady_at_constellation.com> wrote:
>
>
>
> Example:
> I have an instrument that measures something (furnace temp, motor speed,
> etc) every second. Occasionally it fails to report the value. Find each
> second that it didn't report between date_x and date_y. What's the best
> way to answer that question?
>
>
>
>
>
> >>> This e-mail and any attachments are confidential, may contain legal,
> professional or other privileged information, and are intended solely for
> the addressee. If you are not the intended recipient, do not use the
> information in this e-mail in any way, delete this e-mail and notify the
> sender. CEG-IP1
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Jeremy Schneider
Chicago, IL
http://www.ardentperf.com/category/technical

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 23 2007 - 17:02:21 CDT