So I'm a bit upgraded this select in two ways -
(1) one have to look anly for those rows that has the given number of parts
(2) I think, that to make the result in reasonable time you should'nt
look into all combinations, let's take one specific, for example
ordered by part name
You can get (1) firstly grouping foo rows and counting them (all even
better in real life having already precalculated column not to make
full scan of this table each time)
(2) condition can be achieved using a little bit of analytics
So here it is:
select * FROM (
select ltrim(sys_connect_by_path(part, ','), ',') combination, item
from (
- this subselect is to get ONE combination (or group of combinations)
- for each item i.e. ,A;,A,B;,A,B,C for item 3
select item,
part,
lead(part) over (partition by item order by part) np,
row_number() over(partition by item order by part) rn
from foo
where item in (
- this is to filter out initially unnecessary items, can be replaced
- to something like where item_count = count_of_parts
select item from foo
group by item
having count(*) = length('&ch') - length(replace('&ch', ',', '')) + 1
)
)
start with rn = 1
connect by item = prior item
and rn = prior (rn + 1)
)
where combination='&ch'
/
for generated data as follows:
begin
for j in 4..50 loop
for i in 1..j loop
insert into foo values (j, chr(i+64));
end loop;
end loop;
end;
/
and with following index
create index item_idx on foo (item);
it worked nicely.
As a result one always have to search for alphabetically ordered parts
i.e. A,B,C and not for example B,A,C.
Gints
On 1/13/06, Stephane Faroult <sfaroult_at_roughsea.com> wrote:
> I am not too proud of it, but it works:
>
> SQL> set verify off
> SQL> select * from foo;
>
> ITEM PA
> ---------- --
> 1 A
> 2 A
> 2 B
> 3 A
> 3 B
> 3 C
>
> 6 rows selected.
>
> SQL> @fooquery
> Enter value for combination: A
>
> ITEM
> ----------
> 1
>
> SQL> @fooquery
> Enter value for combination: A,B
>
> ITEM
> ----------
> 2
>
> SQL> @fooquery
> Enter value for combination: A,B,C
>
> ITEM
> ----------
> 3
>
> SQL> !cat fooquery.sql
> select a.item
> from (select item,
> ltrim(sys_connect_by_path(part, ','), ',') combination,
> level parts
> from foo
> connect by nocycle item = prior item
> and part != prior part) a,
> (select item, count(*) cnt
> from foo
> group by item) b
> where a.parts = b.cnt
> and a.item = b.item
> and a.combination = '&combination'
> /
>
> Note that it generates all combination. Entertaining, but I wouldn't try
> it on large numbers of rows.
>
> HTH
>
> S Faroult
>
>
> On Fri, 2006-01-13 at 14:30 -0500, Sandeep Dubey wrote:
> > Hi,
> >
> > I am having tough time writing a select query. I won't blame it on
> > Friday afternoon, it is really difficult to me.
> >
> > Here is an example:
> >
> > create table foo ( item number, part varchar2(2));
> >
> > insert into foo values(1,'A');
> > insert into foo values(2,'A');
> > insert into foo values(2,'B');
> > insert into foo values(3,'A');
> > insert into foo values(3,'B');
> > insert into foo values(3,'C');
> >
> > I want to query for item that matches exact parts given in the query.
> > i.e.if I query for part A, I should get 1 ( no 2 and 3)
> >
> > If I pass A and B , I should get only 2 (no 3)
> > If I query for A, B and C, I should get only 3.
> >
> > Query can have n number of parts, It should return only that item that
> > has exact match to parts, no less no more.
> >
> > Thanks
> >
> >
> > Sandeep
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jan 15 2006 - 16:13:05 CST
