I have a table with 35m rows.
eg:
create table table1 as (
unique_id number,
some_data varchar2(10),
test_column varchar2(10)
);
insert into table1 values (1, 'test1','abc');
insert into table1 values (2, 'test2','abcd');
insert into table1 values (3, 'test3','abcde');
insert into table1 values (4, 'test4','jsgkjh');
insert into table1 values (5, 'test5','abc');
I need to create a view of the count of the distinct rows where the test_column exists as a start of another row.
eg
create view view1
with t1 as (select distinct some_data, test_column from table1)
select test_column, count(*)+1 as cnt, 'Partial' as con_type
from table1 t2
inner join t1
on t1.unique_id = t2.unique_id
and t1.some_data = t2.some_data
where (instr(t1.test_column,t2.test_column) = 1
or instr(t2.test_column,t1.test_column) = 1
and t1.test_column != t2.test_column
group by some_data;
select * from view1;
test_column, cnt
abc 3
abcd 2
abced 1
jsgkjh 1
............trouble is, it takes ages! i was wondering if someone knew if a regular expression would be faster (or anything!!).
Many thanks for any help