Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How can we get only numeric value from a column of Varchar2 type

840912May 22 2012 — edited May 23 2012
I have a table, there is a column in a table, the data type of this column is varchar2 but i want only numeric values from the column


SQL> Create table t1 ( col1 varchar2(10)) ;

Table created.

SQL> insert into t1 values('ZAHIR') ;

1 row created.

SQL> insert into t1 values('08831') ;

1 row created.

SQL> insert into t1 values('07066') ;

1 row created.

SQL> insert into t1 values('48331') ;

1 row created.

SQL> insert into t1 values('DEEN') ;

1 row created.

SQL>
SQL> commit;

Commit complete.


I want the output

08831
07066
48331

Comments

cd_2
1) Analytic function count not supported distinct
count over WINDOW
That'd be new. COUNT(DISTINCT column) OVER ... works
perfectly unter 10g2

C.
Aketi Jyuuzou

If we use distinct option in count function of OLAP,
we cannot use order by clause.

for instance in Oracle 10.2.0.1.0

SQL> select SortKey,Val,
  2  count(distinct Val) over() as disticntCount
  3  from (select 1 as SortKey,'AAA' as Val from dual
  4  union select 2,'AAA' from dual
  5  union select 3,'BBB' from dual
  6  union select 4,'CCC' from dual
  7  union select 5,'CCC' from dual
  8  union select 6,'AAA' from dual
  9  union select 7,'AAA' from dual);

SortKey  Val  disticntCount
-------  ---  -------------
      1  AAA              3
      2  AAA              3
      7  AAA              3
      6  AAA              3
      3  BBB              3
      4  CCC              3
      5  CCC              3

SQL> select SortKey,Val,
  2  count(Val) over(order by SortKey) as disticntCount
  3  from (select 1 as SortKey,'AAA' as Val from dual
  4  union select 2,'AAA' from dual
  5  union select 3,'BBB' from dual
  6  union select 4,'CCC' from dual
  7  union select 5,'CCC' from dual
  8  union select 6,'AAA' from dual
  9  union select 7,'AAA' from dual);

SortKey  Val  disticntCount
-------  ---  -------------
      1  AAA              1
      2  AAA              2
      3  BBB              3
      4  CCC              4
      5  CCC              5
      6  AAA              6
      7  AAA              7

SQL> select SortKey,Val,
  2  count(distinct Val) over(order by SortKey) as disticntCount
  3  from (select 1 as SortKey,'AAA' as Val from dual
  4  union select 2,'AAA' from dual
  5  union select 3,'BBB' from dual
  6  union select 4,'CCC' from dual
  7  union select 5,'CCC' from dual
  8  union select 6,'AAA' from dual
  9  union select 7,'AAA' from dual);
count(distinct Val) over(order by SortKey) as disticntCount
                         *
ORA-30487: ORDER BY not allowed here

However,
we can emulate count function of OLAP with distinct option and order by clause.

SQL> select SortKey,Val,sum(WillSum) over(order by SortKey) as disticntCount
  2  from (select SortKey,Val,
  3        case Row_Number() over(partition by Val order by SortKey)
  4        when 1 then 1 else 0 end as WillSum
  5          from (select 1 as SortKey,'AAA' as Val from dual
  6          union select 2,'AAA' from dual
  7          union select 3,'BBB' from dual
  8          union select 4,'CCC' from dual
  9          union select 5,'CCC' from dual
 10          union select 6,'AAA' from dual
 11          union select 7,'AAA' from dual));

SortKey  Val  disticntCount
-------  ---  -------------
      1  AAA              1
      2  AAA              1
      3  BBB              2
      4  CCC              3
      5  CCC              3
      6  AAA              3
      7  AAA              3
Vlada
Thx. This is good idea but I have version 9.2 :-(
Vlada
I need compute count over Window ...
Emulation is good idea but performance of emulation is unusable - i work with 2M+ records ...
Alessandro Rossi

Interval is defined by start date, end date, lenght
and step. For example
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute

Be more clear. An interval is defined by any two of the following: start date, end date or length. Please explain what you mean with step.

  1. Analytic function count not supported distinct
    count over WINDOW

count() distinct would return 1 always.

Processing ...
with t as (
	select 1 as mycol
	from dual
	connect by rownum <= 5
)
select count( distinct mycol)
from t


Query finished, retrieving results...
         COUNT(DISTINCTMYCOL)          
-------------------------------------- 
                                     1 

1 row(s) retrieved
  1. Because I need every interval (interval for wich
    no data in my table) , i must outer join this table
    with dynamic generated intervals (using dual connect
    by level). This is very slow process (for inteval of
    1 year)

Do it with in this way

select ta.dfrom,ta.dtill,count(tb.any_key_column)
FROM (
		SELECT TRUNC (:dfrom) + (LEVEL - 1) / 1440 dfrom,
		TRUNC (:dfrom) + (LEVEL - 1) / 1440 + 59/24/60 dtill,
		level lvl
		FROM DUAL
		CONNECT BY LEVEL <= Trunc((:DTILL - :DFROM) * 1440 / :STP + 1)
	) TA
	LEFT OUTER JOIN
	d_lps_secx_c TB -- this table has 20M+ records
	ON dt = dfrom
group by ta.dfrom,ta.dtill
order by ta.dfrom

Bye Alessandro

Message was edited by:
Alessandro Rossi

that join condition was missing too many records.

select ta.dfrom,ta.dtill,count(tb.any_key_column)
FROM (
		SELECT TRUNC (:dfrom) + (LEVEL - 1) / 1440 dfrom,
		TRUNC (:dfrom) + (LEVEL - 1) / 1440 + 59/24/60 dtill,
		level lvl
		FROM DUAL
		CONNECT BY LEVEL <= Trunc((:DTILL - :DFROM) * 1440 / :STP + 1)
	) TA
	LEFT OUTER JOIN
	d_lps_secx_c TB -- this table has 20M+ records
	ON ( dt between dfrom and dtill )
group by ta.dfrom,ta.dtill
order by ta.dfrom
Rob van Wijk

1) Analytic function count not supported distinct count over WINDOW

To work around this problem in 9iR2, you can emulate the count distinct in a window by making up rows for each minute that the record should be counted for. Like this:

SQL> create table d_lps_secx_c
  2  ( dt date
  3  , no number
  4  , atr1 char(1)
  5  , atr2 char(1)
  6  , primary key (dt,no)
  7  )
  8  /

Tabel is aangemaakt.

SQL> insert into d_lps_secx_c
  2   select trunc(date '2005-12-31' + dbms_random.value(0,3),'mi')
  3        , trunc((level-1)/2)
  4        , 'A'
  5        , 'B'
  6     from dual
  7  connect by level <= 100
  8  /

100 rijen zijn aangemaakt.

SQL> var P_STARTDATE varchar2(16)
SQL> var P_ENDDATE varchar2(16)
SQL> var P_LENGTH number
SQL> var P_STEP number
SQL> exec :P_STARTDATE := '01-01-2006 00:00'

PL/SQL-procedure is geslaagd.

SQL> exec :P_ENDDATE := '01-01-2006 23:59'

PL/SQL-procedure is geslaagd.

SQL> exec :P_LENGTH := 1/24

PL/SQL-procedure is geslaagd.

SQL> exec :P_STEP := 1/24/60

PL/SQL-procedure is geslaagd.

SQL> select dt, no
  2    from d_lps_secx_c
  3   where dt between to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') and to_date(:P_ENDDATE,'dd-mm-yyyy hh24:mi') + interval '59' minute
  4   order by dt
  5  /

DT                                                      NO
------------------- --------------------------------------
01-01-2006 00:21:00                                     22
01-01-2006 01:02:00                                     32
01-01-2006 01:10:00                                     13
01-01-2006 02:06:00                                      7
01-01-2006 02:22:00                                     40
01-01-2006 03:30:00                                     29
01-01-2006 03:44:00                                     13
01-01-2006 04:26:00                                     21
01-01-2006 04:45:00                                     10
01-01-2006 05:05:00                                     49
01-01-2006 06:45:00                                     44
01-01-2006 07:06:00                                      0
01-01-2006 08:05:00                                     41
01-01-2006 08:14:00                                     25
01-01-2006 09:27:00                                     21
01-01-2006 10:46:00                                      7
01-01-2006 11:01:00                                     27
01-01-2006 12:34:00                                     20
01-01-2006 12:49:00                                     31
01-01-2006 13:17:00                                     48
01-01-2006 14:30:00                                     26
01-01-2006 14:38:00                                     10
01-01-2006 15:26:00                                     49
01-01-2006 16:07:00                                     42
01-01-2006 16:33:00                                     37
01-01-2006 16:55:00                                      4
01-01-2006 17:32:00                                     34
01-01-2006 18:06:00                                     22
01-01-2006 20:26:00                                     14
01-01-2006 20:52:00                                     45
01-01-2006 21:26:00                                     11
01-01-2006 22:37:00                                     28
01-01-2006 23:45:00                                     32
01-01-2006 23:54:00                                      4
02-01-2006 00:13:00                                     30

35 rijen zijn geselecteerd.

SQL> select all_intervals.dt_from
  2       , all_intervals.dt_till
  3       , nvl(x.ab,0) ab
  4    from ( select to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') + (level-1)/24/60 dt_from
  5                , to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') + (level-1)/24/60 + interval '59' minute dt_till
  6             from dual
  7          connect by level <= ceil((to_date(:P_ENDDATE,'dd-mm-yyyy hh24:mi') + interval '1' minute - to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi')) / :P_STEP)
  8         ) all_intervals
  9       , ( select dt dt_from
 10                , count(*) ab
 11             from ( select dt - l/24/60 dt
 12                         , no
 13                      from d_lps_secx_c
 14                         , (select level-1 l from dual connect by level <= :P_LENGTH/:P_STEP)
 15                     where dt between to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') and to_date(:P_ENDDATE,'dd-mm-yyyy hh24:mi') + interval '59' minute
 16                  )
 17            where dt between to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') and to_date(:P_ENDDATE,'dd-mm-yyyy hh24:mi')
 18            group by dt
 19         ) x
 20   where all_intervals.dt_from = x.dt_from (+)
 21  /

DT_FROM             DT_TILL                                                 AB
------------------- ------------------- --------------------------------------
01-01-2006 00:00:00 01-01-2006 00:59:00                                      1
01-01-2006 00:01:00 01-01-2006 01:00:00                                      1
01-01-2006 00:02:00 01-01-2006 01:01:00                                      1
01-01-2006 00:03:00 01-01-2006 01:02:00                                      2
01-01-2006 00:04:00 01-01-2006 01:03:00                                      2
01-01-2006 00:05:00 01-01-2006 01:04:00                                      2
01-01-2006 00:06:00 01-01-2006 01:05:00                                      2
01-01-2006 00:07:00 01-01-2006 01:06:00                                      2
01-01-2006 00:08:00 01-01-2006 01:07:00                                      2
01-01-2006 00:09:00 01-01-2006 01:08:00                                      2
01-01-2006 00:10:00 01-01-2006 01:09:00                                      2
01-01-2006 00:11:00 01-01-2006 01:10:00                                      3
01-01-2006 00:12:00 01-01-2006 01:11:00                                      3
...
<snipped>
...
01-01-2006 23:44:00 02-01-2006 00:43:00                                      3
01-01-2006 23:45:00 02-01-2006 00:44:00                                      3
01-01-2006 23:46:00 02-01-2006 00:45:00                                      2
01-01-2006 23:47:00 02-01-2006 00:46:00                                      2
01-01-2006 23:48:00 02-01-2006 00:47:00                                      2
01-01-2006 23:49:00 02-01-2006 00:48:00                                      2
01-01-2006 23:50:00 02-01-2006 00:49:00                                      2
01-01-2006 23:51:00 02-01-2006 00:50:00                                      2
01-01-2006 23:52:00 02-01-2006 00:51:00                                      2
01-01-2006 23:53:00 02-01-2006 00:52:00                                      2
01-01-2006 23:54:00 02-01-2006 00:53:00                                      2
01-01-2006 23:55:00 02-01-2006 00:54:00                                      1
01-01-2006 23:56:00 02-01-2006 00:55:00                                      1
01-01-2006 23:57:00 02-01-2006 00:56:00                                      1
01-01-2006 23:58:00 02-01-2006 00:57:00                                      1
01-01-2006 23:59:00 02-01-2006 00:58:00                                      1

1440 rijen zijn geselecteerd.

2) Because I need every interval (interval for wich no data in my table) , i must outer join this table with dynamic generated intervals (using dual connect by level). This is very slow process (for inteval of 1 year)

If it is slow, please investigate why it is slow. I wrote about it [url http://forums.oracle.com/forums/thread.jspa?threadID=501834&tstart=0]here.

Regards,
Rob.

Vlada
1)

What is unclear? I mean that example is selfexplanation ... but I can try explain it detaily:
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute

Fot this input Length of interval is 1 hour. There is 1440 inervals for witch will be computed result.

First ... 1. 1. 2006 0:00:00 1. 1. 2006 0:59:00
Second ...1. 1. 2006 0:01:00 1. 1. 2006 1:00:00
.
.
Last 1. 1. 2006 23:59:00 2. 1. 2006 0:58:00 13

Start date is incemented by step until this < EndDate

2) Your example is not ANALYTIC function ... it is AGRGATE function! You are in mistake ...
Nicolas Gasparotto

That works fine with count_distinct function :

SQL> select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value 
  2  from (select 1 as SortKey,'AAA' as Val from dual  
  3        union all
  4        select 2,'AAA' from dual  
  5        union all
  6        select 3,'BBB' from dual  
  7        union all
  8        select 4,'CCC' from dual  
  9        union all 
 10        select 5,'CCC' from dual 
 11        union all
 12        select 6,'AAA' from dual 
 13        union all
 14        select 7,'AAA' from dual)
 15  order by 1;

   SORTKEY VAL DISTINCT_VALUE
---------- --- --------------
         1 AAA              1
         2 AAA              1
         3 BBB              2
         4 CCC              3
         5 CCC              3
         6 AAA              3
         7 AAA              3

7 rows selected.

Nicolas.

Alessandro Rossi

What is unclear? I mean that example is
selfexplanation ... but I can try explain it
detaily:
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute

...

Start date is incemented by step until this <
EndDate

I supposed you were working on consecutive intevals then the step concept was out of my consideration.

  1. Your example is not ANALYTIC function ... it is
    AGRGATE function! You are in mistake ...

What you need is something that does what you asked for.If it can be done with aggreagate I suggest you to use aggregate.

I need write select statement which compute count of UNIQUE NO in date interval by WINDOW

Here the results.

Processing ...
create table d_lps_secx_c ( dt date,
	no number,
	atr1 char(1),
	atr2 char(1),
	primary key (dt,no)
)
Processing ...
insert into d_lps_secx_c
	select trunc(to_date ('2005-12-31','yyyy-mm-dd') + dbms_random.value(0,3),'mi'),
		trunc((level-1)/2),
		'A',
		'B'
	from dual
	connect by level <= 100
100 row(s) inserted
Processing ...
select to_date(:dfrom,'dd/mm/yyyy hh24:mi') as dfrom,
	to_date(:dtill,'dd/mm/yyyy hh24:mi') as dtill,
	:interval_length_sec as interval_length_sec,
	:step_secs as step_secs
from dual
Query finished, retrieving results...
        DFROM                DTILL                  INTERVAL_LENGTH_SEC                         STEP_SECS               
-------------------- -------------------- -------------------------------------- -------------------------------------- 
          1-gen-2006           2-gen-2006 3600                                   60                                     

1 row(s) retrieved

Processing ...
select dt, no
from d_lps_secx_c
where dt between to_date(:dfrom,'dd/mm/yyyy hh24:mi') and to_date(:dtill,'dd/mm/yyyy hh24:mi') + (:interval_length_sec/(24*60*60))
order by dt
Query finished, retrieving results...
         DT                            NO                   
-------------------- -------------------------------------- 
  1-gen-2006 0:06:00                                     11 
  1-gen-2006 1:04:00                                     30 
  1-gen-2006 4:07:00                                      0 
  1-gen-2006 4:22:00                                     45 
  1-gen-2006 5:07:00                                      0 
  1-gen-2006 6:32:00                                     12 
  1-gen-2006 7:01:00                                     16 
  1-gen-2006 7:21:00                                      4 
  1-gen-2006 7:26:00                                     15 
  1-gen-2006 7:35:00                                     33 
  1-gen-2006 8:23:00                                     41 
  1-gen-2006 8:26:00                                     17 
  1-gen-2006 8:50:00                                      8 
  1-gen-2006 9:11:00                                      3 
  1-gen-2006 9:47:00                                     20 
  1-gen-2006 9:55:00                                     35 
 1-gen-2006 10:11:00                                      9 
 1-gen-2006 10:32:00                                     34 
 1-gen-2006 11:09:00                                     15 
 1-gen-2006 11:23:00                                      1 
 1-gen-2006 12:03:00                                     30 
 1-gen-2006 12:18:00                                     16 
 1-gen-2006 13:27:00                                     26 
 1-gen-2006 14:02:00                                     42 
 1-gen-2006 15:42:00                                     23 
 1-gen-2006 16:50:00                                     42 
 1-gen-2006 18:14:00                                     18 
 1-gen-2006 18:22:00                                     48 
 1-gen-2006 18:33:00                                     46 
 1-gen-2006 19:15:00                                     48 
 1-gen-2006 20:59:00                                     36 
 1-gen-2006 21:36:00                                     31 
 1-gen-2006 22:10:00                                      8 
 1-gen-2006 22:12:00                                     19 
 1-gen-2006 23:32:00                                      2 
  2-gen-2006 0:43:00                                     10 

36 row(s) retrieved

Processing ...
select ta.dfrom,ta.dtill,count(tb.no)
FROM (
		SELECT to_date(:dfrom,'dd/mm/yyyy hh24:mi')+
				((LEVEL-1)*:step_secs)/(24*60*60) dfrom,
			to_date(:dfrom,'dd/mm/yyyy hh24:mi')+
				((LEVEL-1)*:step_secs)/(24*60*60)+
				(:interval_length_sec/(24*60*60)) dtill,
			level lvl
		FROM DUAL
		CONNECT BY to_date(:dfrom,'dd/mm/yyyy hh24:mi')+
			((LEVEL-1)*:step_secs)/(24*60*60)+
			(:interval_length_sec/(24*60*60)) <= 
				to_date(:dtill,'dd/mm/yyyy hh24:mi')	
	) TA
	LEFT OUTER JOIN
	d_lps_secx_c TB -- this table has 20M+ records
	ON ( dt between dfrom and dtill )
group by ta.dfrom,ta.dtill
order by ta.dfrom
Query finished, retrieving results...
        DFROM                DTILL                     COUNT(TB.NO)              
-------------------- -------------------- -------------------------------------- 
          1-gen-2006   1-gen-2006 1:00:00                                      1 
  1-gen-2006 0:01:00   1-gen-2006 1:01:00                                      1 
  1-gen-2006 0:02:00   1-gen-2006 1:02:00                                      1 
  1-gen-2006 0:03:00   1-gen-2006 1:03:00                                      1 
  1-gen-2006 0:04:00   1-gen-2006 1:04:00                                      2 
  1-gen-2006 0:05:00   1-gen-2006 1:05:00                                      2 
  1-gen-2006 0:06:00   1-gen-2006 1:06:00                                      2 
  1-gen-2006 0:07:00   1-gen-2006 1:07:00                                      1 
  1-gen-2006 0:08:00   1-gen-2006 1:08:00                                      1 
  1-gen-2006 0:09:00   1-gen-2006 1:09:00                                      1 
  1-gen-2006 0:10:00   1-gen-2006 1:10:00                                      1 
  1-gen-2006 0:11:00   1-gen-2006 1:11:00                                      1 
  1-gen-2006 0:12:00   1-gen-2006 1:12:00                                      1 
  1-gen-2006 0:13:00   1-gen-2006 1:13:00                                      1 
  1-gen-2006 0:14:00   1-gen-2006 1:14:00                                      1 
  1-gen-2006 0:15:00   1-gen-2006 1:15:00                                      1 
  1-gen-2006 0:16:00   1-gen-2006 1:16:00                                      1 
  1-gen-2006 0:17:00   1-gen-2006 1:17:00                                      1 
  1-gen-2006 0:18:00   1-gen-2006 1:18:00                                      1 
  1-gen-2006 0:19:00   1-gen-2006 1:19:00                                      1 
  1-gen-2006 0:20:00   1-gen-2006 1:20:00                                      1 
  1-gen-2006 0:21:00   1-gen-2006 1:21:00                                      1 
  1-gen-2006 0:22:00   1-gen-2006 1:22:00                                      1 
  1-gen-2006 0:23:00   1-gen-2006 1:23:00                                      1 
  1-gen-2006 0:24:00   1-gen-2006 1:24:00                                      1 
  1-gen-2006 0:25:00   1-gen-2006 1:25:00                                      1 
  1-gen-2006 0:26:00   1-gen-2006 1:26:00                                      1 
  1-gen-2006 0:27:00   1-gen-2006 1:27:00                                      1 
  1-gen-2006 0:28:00   1-gen-2006 1:28:00                                      1 
  1-gen-2006 0:29:00   1-gen-2006 1:29:00                                      1 
  1-gen-2006 0:30:00   1-gen-2006 1:30:00                                      1 
  1-gen-2006 0:31:00   1-gen-2006 1:31:00                                      1 
  1-gen-2006 0:32:00   1-gen-2006 1:32:00                                      1 
  1-gen-2006 0:33:00   1-gen-2006 1:33:00                                      1 
  1-gen-2006 0:34:00   1-gen-2006 1:34:00                                      1 
  1-gen-2006 0:35:00   1-gen-2006 1:35:00                                      1 
  1-gen-2006 0:36:00   1-gen-2006 1:36:00                                      1 
  1-gen-2006 0:37:00   1-gen-2006 1:37:00                                      1 
  1-gen-2006 0:38:00   1-gen-2006 1:38:00                                      1 
  1-gen-2006 0:39:00   1-gen-2006 1:39:00                                      1 
  1-gen-2006 0:40:00   1-gen-2006 1:40:00                                      1 
  1-gen-2006 0:41:00   1-gen-2006 1:41:00                                      1 
  1-gen-2006 0:42:00   1-gen-2006 1:42:00                                      1 
  1-gen-2006 0:43:00   1-gen-2006 1:43:00                                      1 
  1-gen-2006 0:44:00   1-gen-2006 1:44:00                                      1 
  1-gen-2006 0:45:00   1-gen-2006 1:45:00                                      1 
  1-gen-2006 0:46:00   1-gen-2006 1:46:00                                      1 
  1-gen-2006 0:47:00   1-gen-2006 1:47:00                                      1 
  1-gen-2006 0:48:00   1-gen-2006 1:48:00                                      1 
  1-gen-2006 0:49:00   1-gen-2006 1:49:00                                      1 
  1-gen-2006 0:50:00   1-gen-2006 1:50:00                                      1 
  1-gen-2006 0:51:00   1-gen-2006 1:51:00                                      1 
  1-gen-2006 0:52:00   1-gen-2006 1:52:00                                      1 
  1-gen-2006 0:53:00   1-gen-2006 1:53:00                                      1 
  1-gen-2006 0:54:00   1-gen-2006 1:54:00                                      1 
  1-gen-2006 0:55:00   1-gen-2006 1:55:00                                      1 
  1-gen-2006 0:56:00   1-gen-2006 1:56:00                                      1 
  1-gen-2006 0:57:00   1-gen-2006 1:57:00                                      1 
  1-gen-2006 0:58:00   1-gen-2006 1:58:00                                      1 
  1-gen-2006 0:59:00   1-gen-2006 1:59:00                                      1 
  1-gen-2006 1:00:00   1-gen-2006 2:00:00                                      1 
  1-gen-2006 1:01:00   1-gen-2006 2:01:00                                      1 
  1-gen-2006 1:02:00   1-gen-2006 2:02:00                                      1 
  1-gen-2006 1:03:00   1-gen-2006 2:03:00                                      1 
  1-gen-2006 1:04:00   1-gen-2006 2:04:00                                      1 
  1-gen-2006 1:05:00   1-gen-2006 2:05:00                                      0 
  1-gen-2006 1:06:00   1-gen-2006 2:06:00                                      0 
  1-gen-2006 1:07:00   1-gen-2006 2:07:00                                      0 
  1-gen-2006 1:08:00   1-gen-2006 2:08:00                                      0 
  1-gen-2006 1:09:00   1-gen-2006 2:09:00                                      0 
  1-gen-2006 1:10:00   1-gen-2006 2:10:00                                      0 
  1-gen-2006 1:11:00   1-gen-2006 2:11:00                                      0 
  1-gen-2006 1:12:00   1-gen-2006 2:12:00                                      0 
  1-gen-2006 1:13:00   1-gen-2006 2:13:00                                      0 
  1-gen-2006 1:14:00   1-gen-2006 2:14:00                                      0 
  1-gen-2006 1:15:00   1-gen-2006 2:15:00                                      0 
  1-gen-2006 1:16:00   1-gen-2006 2:16:00                                      0 
  1-gen-2006 1:17:00   1-gen-2006 2:17:00                                      0 
  1-gen-2006 1:18:00   1-gen-2006 2:18:00                                      0 
  1-gen-2006 1:19:00   1-gen-2006 2:19:00                                      0 
  1-gen-2006 1:20:00   1-gen-2006 2:20:00                                      0 
  1-gen-2006 1:21:00   1-gen-2006 2:21:00                                      0 
  1-gen-2006 1:22:00   1-gen-2006 2:22:00                                      0 
  1-gen-2006 1:23:00   1-gen-2006 2:23:00                                      0 
  1-gen-2006 1:24:00   1-gen-2006 2:24:00                                      0 
  1-gen-2006 1:25:00   1-gen-2006 2:25:00                                      0 
  1-gen-2006 1:26:00   1-gen-2006 2:26:00                                      0 
  1-gen-2006 1:27:00   1-gen-2006 2:27:00                                      0 
  1-gen-2006 1:28:00   1-gen-2006 2:28:00                                      0 
  1-gen-2006 1:29:00   1-gen-2006 2:29:00                                      0 
  1-gen-2006 1:30:00   1-gen-2006 2:30:00                                      0 
  1-gen-2006 1:31:00   1-gen-2006 2:31:00                                      0 
  1-gen-2006 1:32:00   1-gen-2006 2:32:00                                      0 
  1-gen-2006 1:33:00   1-gen-2006 2:33:00                                      0 
  1-gen-2006 1:34:00   1-gen-2006 2:34:00                                      0 
  1-gen-2006 1:35:00   1-gen-2006 2:35:00                                      0 
  1-gen-2006 1:36:00   1-gen-2006 2:36:00                                      0 
  1-gen-2006 1:37:00   1-gen-2006 2:37:00                                      0 
  1-gen-2006 1:38:00   1-gen-2006 2:38:00                                      0 
  1-gen-2006 1:39:00   1-gen-2006 2:39:00                                      0 
  1-gen-2006 1:40:00   1-gen-2006 2:40:00                                      0 
  1-gen-2006 1:41:00   1-gen-2006 2:41:00                                      0 
  1-gen-2006 1:42:00   1-gen-2006 2:42:00                                      0 
  1-gen-2006 1:43:00   1-gen-2006 2:43:00                                      0 
  1-gen-2006 1:44:00   1-gen-2006 2:44:00                                      0 
  1-gen-2006 1:45:00   1-gen-2006 2:45:00                                      0 
  1-gen-2006 1:46:00   1-gen-2006 2:46:00                                      0 
  1-gen-2006 1:47:00   1-gen-2006 2:47:00                                      0 
  1-gen-2006 1:48:00   1-gen-2006 2:48:00                                      0 
  1-gen-2006 1:49:00   1-gen-2006 2:49:00                                      0 
  1-gen-2006 1:50:00   1-gen-2006 2:50:00                                      0 
  1-gen-2006 1:51:00   1-gen-2006 2:51:00                                      0 
  1-gen-2006 1:52:00   1-gen-2006 2:52:00                                      0 
  1-gen-2006 1:53:00   1-gen-2006 2:53:00                                      0 
  1-gen-2006 1:54:00   1-gen-2006 2:54:00                                      0 
  1-gen-2006 1:55:00   1-gen-2006 2:55:00                                      0 
  1-gen-2006 1:56:00   1-gen-2006 2:56:00                                      0 
  1-gen-2006 1:57:00   1-gen-2006 2:57:00                                      0 
  1-gen-2006 1:58:00   1-gen-2006 2:58:00                                      0 
  1-gen-2006 1:59:00   1-gen-2006 2:59:00                                      0 
  1-gen-2006 2:00:00   1-gen-2006 3:00:00                                      0 
  1-gen-2006 2:01:00   1-gen-2006 3:01:00                                      0 
  1-gen-2006 2:02:00   1-gen-2006 3:02:00                                      0 
  1-gen-2006 2:03:00   1-gen-2006 3:03:00                                      0 
  1-gen-2006 2:04:00   1-gen-2006 3:04:00                                      0 
  1-gen-2006 2:05:00   1-gen-2006 3:05:00                                      0 
  1-gen-2006 2:06:00   1-gen-2006 3:06:00                                      0 
  1-gen-2006 2:07:00   1-gen-2006 3:07:00                                      0 
  1-gen-2006 2:08:00   1-gen-2006 3:08:00                                      0 
  1-gen-2006 2:09:00   1-gen-2006 3:09:00                                      0 
  1-gen-2006 2:10:00   1-gen-2006 3:10:00                                      0 
  1-gen-2006 2:11:00   1-gen-2006 3:11:00                                      0 
  1-gen-2006 2:12:00   1-gen-2006 3:12:00                                      0 
  1-gen-2006 2:13:00   1-gen-2006 3:13:00                                      0 
  1-gen-2006 2:14:00   1-gen-2006 3:14:00                                      0 
  1-gen-2006 2:15:00   1-gen-2006 3:15:00                                      0 
  1-gen-2006 2:16:00   1-gen-2006 3:16:00                                      0 
  1-gen-2006 2:17:00   1-gen-2006 3:17:00                                      0 
  1-gen-2006 2:18:00   1-gen-2006 3:18:00                                      0 
  1-gen-2006 2:19:00   1-gen-2006 3:19:00                                      0 
  1-gen-2006 2:20:00   1-gen-2006 3:20:00                                      0 
  1-gen-2006 2:21:00   1-gen-2006 3:21:00                                      0 
  1-gen-2006 2:22:00   1-gen-2006 3:22:00                                      0 
  1-gen-2006 2:23:00   1-gen-2006 3:23:00                                      0 
  1-gen-2006 2:24:00   1-gen-2006 3:24:00                                      0 
  1-gen-2006 2:25:00   1-gen-2006 3:25:00                                      0 
  1-gen-2006 2:26:00   1-gen-2006 3:26:00                                      0 
  1-gen-2006 2:27:00   1-gen-2006 3:27:00                                      0 
  1-gen-2006 2:28:00   1-gen-2006 3:28:00                                      0 
  1-gen-2006 2:29:00   1-gen-2006 3:29:00                                      0 
  1-gen-2006 2:30:00   1-gen-2006 3:30:00                                      0 
  1-gen-2006 2:31:00   1-gen-2006 3:31:00                                      0 
  1-gen-2006 2:32:00   1-gen-2006 3:32:00                                      0 
  1-gen-2006 2:33:00   1-gen-2006 3:33:00                                      0 
  1-gen-2006 2:34:00   1-gen-2006 3:34:00                                      0 
  1-gen-2006 2:35:00   1-gen-2006 3:35:00                                      0 
  1-gen-2006 2:36:00   1-gen-2006 3:36:00                                      0 
  1-gen-2006 2:37:00   1-gen-2006 3:37:00                                      0 
  1-gen-2006 2:38:00   1-gen-2006 3:38:00                                      0 
  1-gen-2006 2:39:00   1-gen-2006 3:39:00                                      0 
  1-gen-2006 2:40:00   1-gen-2006 3:40:00                                      0 
  1-gen-2006 2:41:00   1-gen-2006 3:41:00                                      0 
  1-gen-2006 2:42:00   1-gen-2006 3:42:00                                      0 
  1-gen-2006 2:43:00   1-gen-2006 3:43:00                                      0 
  1-gen-2006 2:44:00   1-gen-2006 3:44:00                                      0 
  1-gen-2006 2:45:00   1-gen-2006 3:45:00                                      0 
  1-gen-2006 2:46:00   1-gen-2006 3:46:00                                      0 
  1-gen-2006 2:47:00   1-gen-2006 3:47:00                                      0 
  1-gen-2006 2:48:00   1-gen-2006 3:48:00                                      0 
  1-gen-2006 2:49:00   1-gen-2006 3:49:00                                      0 
  1-gen-2006 2:50:00   1-gen-2006 3:50:00                                      0 
  1-gen-2006 2:51:00   1-gen-2006 3:51:00                                      0 
  1-gen-2006 2:52:00   1-gen-2006 3:52:00                                      0 
  1-gen-2006 2:53:00   1-gen-2006 3:53:00                                      0 
  1-gen-2006 2:54:00   1-gen-2006 3:54:00                                      0 
  1-gen-2006 2:55:00   1-gen-2006 3:55:00                                      0 
  1-gen-2006 2:56:00   1-gen-2006 3:56:00                                      0 
  1-gen-2006 2:57:00   1-gen-2006 3:57:00                                      0 
  1-gen-2006 2:58:00   1-gen-2006 3:58:00                                      0 
  1-gen-2006 2:59:00   1-gen-2006 3:59:00                                      0 
  1-gen-2006 3:00:00   1-gen-2006 4:00:00                                      0 
  1-gen-2006 3:01:00   1-gen-2006 4:01:00                                      0 
  1-gen-2006 3:02:00   1-gen-2006 4:02:00                                      0 
  1-gen-2006 3:03:00   1-gen-2006 4:03:00                                      0 
  1-gen-2006 3:04:00   1-gen-2006 4:04:00                                      0 
  1-gen-2006 3:05:00   1-gen-2006 4:05:00                                      0 
  1-gen-2006 3:06:00   1-gen-2006 4:06:00                                      0 
  1-gen-2006 3:07:00   1-gen-2006 4:07:00                                      1 
  1-gen-2006 3:08:00   1-gen-2006 4:08:00                                      1 
  1-gen-2006 3:09:00   1-gen-2006 4:09:00                                      1 
  1-gen-2006 3:10:00   1-gen-2006 4:10:00                                      1 
  1-gen-2006 3:11:00   1-gen-2006 4:11:00                                      1 
  1-gen-2006 3:12:00   1-gen-2006 4:12:00                                      1 
  1-gen-2006 3:13:00   1-gen-2006 4:13:00                                      1 
  1-gen-2006 3:14:00   1-gen-2006 4:14:00                                      1 
  1-gen-2006 3:15:00   1-gen-2006 4:15:00                                      1 
  1-gen-2006 3:16:00   1-gen-2006 4:16:00                                      1 
  1-gen-2006 3:17:00   1-gen-2006 4:17:00                                      1 
  1-gen-2006 3:18:00   1-gen-2006 4:18:00                                      1 
  1-gen-2006 3:19:00   1-gen-2006 4:19:00                                      1 
  1-gen-2006 3:20:00   1-gen-2006 4:20:00                                      1 
  1-gen-2006 3:21:00   1-gen-2006 4:21:00                                      1 
  1-gen-2006 3:22:00   1-gen-2006 4:22:00                                      2 
  1-gen-2006 3:23:00   1-gen-2006 4:23:00                                      2 
  1-gen-2006 3:24:00   1-gen-2006 4:24:00                                      2 
  1-gen-2006 3:25:00   1-gen-2006 4:25:00                                      2 
  1-gen-2006 3:26:00   1-gen-2006 4:26:00                                      2 
  1-gen-2006 3:27:00   1-gen-2006 4:27:00                                      2 
  1-gen-2006 3:28:00   1-gen-2006 4:28:00                                      2 
  1-gen-2006 3:29:00   1-gen-2006 4:29:00                                      2 
  1-gen-2006 3:30:00   1-gen-2006 4:30:00                                      2 
  1-gen-2006 3:31:00   1-gen-2006 4:31:00                                      2 
  1-gen-2006 3:32:00   1-gen-2006 4:32:00                                      2 
  1-gen-2006 3:33:00   1-gen-2006 4:33:00                                      2 
  1-gen-2006 3:34:00   1-gen-2006 4:34:00                                      2 
  1-gen-2006 3:35:00   1-gen-2006 4:35:00                                      2 
  1-gen-2006 3:36:00   1-gen-2006 4:36:00                                      2 
  1-gen-2006 3:37:00   1-gen-2006 4:37:00                                      2 
  1-gen-2006 3:38:00   1-gen-2006 4:38:00                                      2 
  1-gen-2006 3:39:00   1-gen-2006 4:39:00                                      2 
  1-gen-2006 3:40:00   1-gen-2006 4:40:00                                      2 
  1-gen-2006 3:41:00   1-gen-2006 4:41:00                                      2 
  1-gen-2006 3:42:00   1-gen-2006 4:42:00                                      2 
  1-gen-2006 3:43:00   1-gen-2006 4:43:00                                      2 
  1-gen-2006 3:44:00   1-gen-2006 4:44:00                                      2 
  1-gen-2006 3:45:00   1-gen-2006 4:45:00                                      2 
  1-gen-2006 3:46:00   1-gen-2006 4:46:00                                      2 
  1-gen-2006 3:47:00   1-gen-2006 4:47:00                                      2 
  1-gen-2006 3:48:00   1-gen-2006 4:48:00                                      2 
  1-gen-2006 3:49:00   1-gen-2006 4:49:00                                      2 
  1-gen-2006 3:50:00   1-gen-2006 4:50:00                                      2 
  1-gen-2006 3:51:00   1-gen-2006 4:51:00                                      2 
  1-gen-2006 3:52:00   1-gen-2006 4:52:00                                      2 
  1-gen-2006 3:53:00   1-gen-2006 4:53:00                                      2 
  1-gen-2006 3:54:00   1-gen-2006 4:54:00                                      2 
  1-gen-2006 3:55:00   1-gen-2006 4:55:00                                      2 
  1-gen-2006 3:56:00   1-gen-2006 4:56:00                                      2 
  1-gen-2006 3:57:00   1-gen-2006 4:57:00                                      2 
  1-gen-2006 3:58:00   1-gen-2006 4:58:00                                      2 
  1-gen-2006 3:59:00   1-gen-2006 4:59:00                                      2 
  1-gen-2006 4:00:00   1-gen-2006 5:00:00                                      2 
  1-gen-2006 4:01:00   1-gen-2006 5:01:00                                      2 
  1-gen-2006 4:02:00   1-gen-2006 5:02:00                                      2 
  1-gen-2006 4:03:00   1-gen-2006 5:03:00                                      2 
  1-gen-2006 4:04:00   1-gen-2006 5:04:00                                      2 
  1-gen-2006 4:05:00   1-gen-2006 5:05:00                                      2 
  1-gen-2006 4:06:00   1-gen-2006 5:06:00                                      2 
  1-gen-2006 4:07:00   1-gen-2006 5:07:00                                      3 
  1-gen-2006 4:08:00   1-gen-2006 5:08:00                                      2 
  1-gen-2006 4:09:00   1-gen-2006 5:09:00                                      2 

250 row(s) retrieved

Processing ...
drop table d_lps_secx_c

The rows to display were too many so the output has been limited to the first 250s.

Bye Alessandro

Rob van Wijk
> ON ( dt between dfrom and dtill )

Excellent!
It is indeed that easy and there is no need to unfold like I did.

Regards,
Rob.
Vlada
Thanx. This is good idea .. but it's too slow :(
Test it for 20 000 000 rows in d_lps_secx_c per year and
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute

It's take aproximatelly 50 seconds per one day => more than 5 hours ...
Analytics functions is more fastert then grouping ...

Thanks for your time
Rob van Wijk
> It's take aproximatelly 50 seconds per one day =>
more than 5 hours ...

So you are missing an index on dt?
Did you investigate why it was slow? [url http://forums.oracle.com/forums/thread.jspa?threadID=501834&tstart=0]This thread may help.

> Analytics functions is more fastert then grouping

This is nonsense generally.

Regards,
Rob.
Laurent Schneider
Is this an Oracle function?
select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value
                   *
ERROR at line 1:
ORA-00904: "COUNT_DISTINCT": invalid identifier
well, you could write your own count_distinct user defined aggregate
Nicolas Gasparotto

well, you could write your own count_distinct user defined aggregate

Bingo ;-)

SQL> create or replace type AggregateCD as object
  2  (  nb                   number,
  3     ListOfDistinctValue  clob,
  4     static               function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number,
  5     member               function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number,
  6     member               function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number,
  7     member               function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number
  8  );
  9  /

Type created.

SQL> 
SQL> create or replace type body AggregateCD is   
  2     static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number is   
  3     begin  
  4         sctx := AggregateCD(0,null);  
  5         return ODCIConst.Success;  
  6     end;  
  7     
  8     member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number is  
  9         ListOfValue CLOB:=self.ListOfDistinctValue ;
 10      begin 
 11         while instr(ListOfValue,';') > 0 loop
 12               if substr(ListOfValue,1,instr(ListOfValue,';')-1) = value then
 13                  return ODCIConst.Success ;
 14               end if;
 15               ListOfValue:=substr(ListOfValue,instr(ListOfValue,';')+1);
 16         end loop;
 17         self.ListOfDistinctValue:=self.ListOfDistinctValue||value||' ;';
 18         self.nb:=self.nb+1;
 19         return ODCIConst.Success; 
 20     end; 
 21     
 22     member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number is 
 23     begin 
 24         returnValue := self.nb; 
 25         return ODCIConst.Success; 
 26     end; 
 27     
 28     member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number is 
 29     begin  
 30         self.nb := ctx2.nb; 
 31         return ODCIConst.Success; 
 32     end; 
 33  end; 
 34  /

Type body created.

SQL> show err
No errors.
SQL> 
SQL> CREATE OR REPLACE FUNCTION Count_distinct (input VARCHAR2) RETURN number   
  2  PARALLEL_ENABLE AGGREGATE USING AggregateCD;  
  3  /

Function created.

SQL> 
SQL> select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value 
  2  from (select 1 as SortKey,'AAA' as Val from dual  
  3        union all
  4        select 2,'AAA' from dual  
  5        union all
  6        select 3,'BBB' from dual  
  7        union all
  8        select 4,'CCC' from dual  
  9        union all 
 10        select 5,'CCC' from dual 
 11        union all
 12        select 6,'AAA' from dual 
 13        union all
 14        select 7,'AAA' from dual)
 15  order by 1;

   SORTKEY VAL DISTINCT_VALUE
---------- --- --------------
         1 AAA              1
         2 AAA              1
         3 BBB              2
         4 CCC              3
         5 CCC              3
         6 AAA              3
         7 AAA              3

7 rows selected.

Nicolas.

Message was edited by:
N. Gasparotto

Laurent Schneider
Nicolas,
thanks for that piece of code. However, I would not do it this way, because it will work only for varchar2, what's more, it will bug as soon as a column contains semi-column (;)

unfortunately, I tried it with no success
drop function count_distinct;
drop type AggregateCD;

create or replace type AggregateCD as object
(  nb                   number,
     ListOfDistinctValue  clob,
     static               function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number,
     member               function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number,
     member               function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number,
     member               function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number
);
/

create or replace type body AggregateCD is
     static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number is
     begin
         sctx := AggregateCD(0,null);
         return ODCIConst.Success;
     end;

     member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number is
         ListOfValue CLOB:=self.ListOfDistinctValue ;
     begin
         while instr(ListOfValue,';') > 0 loop
               if substr(ListOfValue,1,instr(ListOfValue,';')-1) = value then
                  return ODCIConst.Success ;
               end if;
               ListOfValue:=substr(ListOfValue,instr(ListOfValue,';')+1);
         end loop;
         self.ListOfDistinctValue:=self.ListOfDistinctValue||value||' ;';
         self.nb:=self.nb+1;
         return ODCIConst.Success;
     end;

     member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number is
     begin
         returnValue := self.nb;
         return ODCIConst.Success;
     end;

     member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number is
     begin
         self.nb := ctx2.nb;
         return ODCIConst.Success;
     end;
end;
/

show err

CREATE OR REPLACE FUNCTION Count_distinct (input VARCHAR2) RETURN number
/*PARALLEL_ENABLE*/ AGGREGATE USING AggregateCD;
/

show err

select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value
from (select 1 as SortKey,'AAA' as Val from dual
union all
select 2,'AAA' from dual
union all
select 3,'BBB' from dual
union all
select 4,'CCC' from dual
union all
select 5,'CCC' from dual
union all
select 6,'AAA' from dual
union all
select 7,'AAA' from dual)
order by 1;
   SORTKEY VAL DISTINCT_VALUE
---------- --- --------------
         1 AAA              1
         2 AAA              2
         3 BBB              3
         4 CCC              4
         5 CCC              5
         6 AAA              6
         7 AAA              7
Nicolas Gasparotto
Laurent,
it will bug as soon as a column contains semi-column (;)
Yes, I would have to rewrite with a PL/SQL table instead of concatenation into clob with any separator.

Anyway, in the current case, I don't understand why that doesn't work on your side, I tried succesfully it on 9.2.0.8 and 10.2.0.2.

Nicolas.
Laurent Schneider
I will have a look (I tried with 11.1.0.6)

by the way, I tried a different approach 
with t as
(select 1 as SortKey,'AAA' as Val from dual
union all
select 2,'AAA' from dual
union all
select 3,'BBB' from dual
union all
select 4,'CCC' from dual
union all
select 5,'CCC' from dual
union all
select 6,'AAA' from dual
union all
select 7,'AAA' from dual)
select * from t model dimension by (sortkey) measures (0 c, val) rules (c[sortkey]=count(distinct val)[sortkey<=cv(sortkey)])
/
   SORTKEY          C VAL
---------- ---------- ---
         1          1 AAA
         2          1 AAA
         3          2 BBB
         4          3 CCC
         5          3 CCC
         6          3 AAA
         7          3 AAA
Laurent Schneider
I will have a look (I tried with 11.1.0.6)
same in 9.2.0.8, 10.1.0.5, 10.2.0.3

maybe copy-paste error
Nicolas Gasparotto

Nice one the model !

Since the OP is on 9i, that cannot be an option.

What about :

SQL> drop function count_distinct;

Function dropped.

SQL> drop type AggregateCD;

Type dropped.

SQL> 
SQL> create or replace type TypeListOfValue as table of varchar2(4000);
  2  /

Type created.

SQL>  
SQL> create or replace type AggregateCD as object
  2  (  nb                   number,
  3     ListOfDistinctValue  TypeListOfValue,
  4     static               function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number,
  5     member               function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number,
  6     member               function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number,
  7     member               function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number
  8  );
  9  / 

Type created.

SQL>  
SQL> create or replace type body AggregateCD is
  2       static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number is
  3       begin
  4           sctx := AggregateCD(0,TypeListOfValue(null));
  5           return ODCIConst.Success;
  6       end;
  7   
  8       member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number is
  9       begin
 10           for i in 1..self.ListOfDistinctValue.count loop
 11               if self.ListOfDistinctValue(i) = value then
 12                  return ODCIConst.Success ;
 13               end if;
 14           end loop;
 15           self.nb:=self.nb+1;
 16           self.ListOfDistinctValue.extend;
 17           self.ListOfDistinctValue(nb):=value;
 18           return ODCIConst.Success;
 19       end;
 20   
 21       member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number is
 22       begin
 23           returnValue := self.nb;
 24           return ODCIConst.Success;
 25       end;
 26   
 27       member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number is
 28       begin
 29           self.nb := ctx2.nb;
 30           return ODCIConst.Success;
 31       end;
 32  end;
 33  / 

Type body created.

SQL>  
SQL> show err
No errors.
SQL>  
SQL> CREATE OR REPLACE FUNCTION Count_distinct (input VARCHAR2) RETURN number
  2  /*PARALLEL_ENABLE*/ AGGREGATE USING AggregateCD;
  3  / 

Function created.

SQL>  
SQL> show err
No errors.
SQL>  
SQL> select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value
  2  from (select 1 as SortKey,'AAA' as Val from dual
  3  union all
  4  select 2,'AAA' from dual
  5  union all
  6  select 3,'BBB' from dual
  7  union all
  8  select 4,'CCC' from dual
  9  union all
 10  select 5,'CCC' from dual
 11  union all
 12  select 6,'AAA' from dual
 13  union all
 14  select 7,'AAA' from dual)
 15  order by 1;

   SORTKEY VAL DISTINCT_VALUE
---------- --- --------------
         1 AAA              1
         2 AAA              1
         3 BBB              2
         4 CCC              3
         5 CCC              3
         6 AAA              3
         7 AAA              3

7 rows selected.

SQL> select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value
  2  from (select 1 as SortKey,1 as Val from dual
  3  union all
  4  select 2,1 from dual
  5  union all
  6  select 3,2 from dual
  7  union all
  8  select 4,3 from dual
  9  union all
 10  select 5,3 from dual
 11  union all
 12  select 6,1 from dual
 13  union all
 14  select 7,1 from dual)
 15  order by 1;

   SORTKEY        VAL DISTINCT_VALUE
---------- ---------- --------------
         1          1              1
         2          1              1
         3          2              2
         4          3              3
         5          3              3
         6          1              3
         7          1              3

7 rows selected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production[pre]

Nicolas.
Vlada
And performance of SQL when I use own function? I working with 20M+ records itterative in 1440 cycles per one day. I think that this will be to slow ...
Laurent Schneider
nice one you too!
Laurent Schneider
I could even imagine a function that would use ora_hash instead of a plsql table

it would save a lot of memory

Message was edited by:
Laurent Schneider

but it does not seem to work :-((

Message was edited by:
Laurent Schneider
I deleted my code, I have to look for an efficient algorithm to count the distinct rows
Nicolas Gasparotto
And performance of SQL when I use own function? I
working with 20M+ records itterative in 1440 cycles
per one day. I think that this will be to slow ...
Actually, I don't understand what's your choice.
Then, if this is a daily process for one day window, who care it takes 50 seconds ?
And if the window is the year, who care it takes 5 hours ? You don't have to run it every day, have you ?

Nicolas.
Vlada
There is no only count (count I need wehn using analytics functions), there are a few sum on some conditions (see sql bellow)...

For one interval of length 1 hour is selected about 3 000 records from 20M records using non unique index on DT. With step 1 minute - there is 1440 repetations of this select If one day get 50 seconds - this mean that one select take 35ms.
And for one year there is 1440 * 365 repetations => 1440 * 365 * 50 = 304 minutes (so this is estimation because time incrementation is not linear).

Yes, there is index on DT. I test btree and bitmap index (because in some case there can be some additional conditions).
Execution plan is:
PLAN COST BYTES CARDINALITY OPTIMIZER

SORT ORDER BY 9968 1107 41
SORT GROUP BY 9968 1107 41
NESTED LOOPS OUTER 9965 1107 41
VIEW 2 492 41
CONNECT BY WITHOUT FILTERING
TABLE ACCESS FULL DUAL 2 41
VIEW 15 1
FILTER
TABLE ACCESS BY INDEX ROWID D_LPS_SECX_C 243 10 1 ANALYZED
INDEX RANGE SCAN IX_DLPSSECXCDTFLTNO UNIQUE 242 1 ANALYZED

Autotrace:
Description Value

recursive calls 0
db block gets 0
consistent gets 1072322
physical reads 0
redo size 0
bytes sent via SQL*Net to client 45436
bytes received via SQL*Net from client 1337
SQL*Net roundtrips to/from client 4
sorts (memory) 3
sorts (disk) 0


SQL STATEMENT (without analytics functions):
SELECT dt_from, dt_till
, sum(CASE WHEN dt = dt_from AND hosP IS NOT NULL THEN 1 ELSE 0 END) a
, sum(CASE WHEN hosP IS NULL THEN 1 ELSE 0 END) b
, sum(CASE WHEN dt = dt_till AND hosN IS NOT NULL THEN 1 ELSE 0 END) c
, sum(CASE WHEN hosN IS NULL THEN 1 ELSE 0 END) d
, sum(CASE WHEN hosP IS NULL
OR (dt = dt_from AND hosP IS NOT NULL) THEN Round((dt_till - dt_from) * 1440 + 1) ELSE 0 END
- CASE WHEN hosP IS NULL THEN Round((dt - dt_from) * 1440) ELSE 0 END
- CASE WHEN hosN IS NULL THEN Round((dt_till - dt) * 1440) ELSE 0 END) tab
FROM (
SELECT To_Date('01.01.2006 00:00', 'dd.mm.yyyy hh24:mi') + (LEVEL * 1 - 1) / 1440 dt_from
, To_Date('01.01.2006 00:00', 'dd.mm.yyyy hh24:mi') + (LEVEL * 1 - 1 + 60 - 1) / 1440 dt_till
FROM dual
CONNECT BY LEVEL <= 1440
)
left outer join
d_lps_secx_c B
ON dt BETWEEN dt_from AND dt_till
AND (dt = dt_from OR dt = dt_till
OR hosP IS NULL
OR hosN IS NULL)
AND hos IS NOT NULL
GROUP BY dt_from, dt_till
ORDER BY 1, 2;
Vlada
This is base for user statistics query. User can set interval (start, end, length and step) and some aditional attributes (type, origin, destination, sectors) - up to 10 attributes which values are coded into varchar or numeric values.
This mean that result cannot be computed and stored in table - but must be computed on the user request (set of possible combination of parameters is too large).
Yes - if user select interval one year - long time is acceptable - but 20 - 50 seconds per day when user operate usually on month nterval (start,end) is horrible (10-50 minutes).
System is in analyzic state - we test if possible implement request on this statistics and findnig way how implement base queries. For concrete query i am able optimize it to usable form (using hints, some query rewrites and so) but for general not (too long runnig time)
1 - 25
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 20 2012
Added on May 22 2012
13 comments
33,705 views