当前位置: 动力学知识库 > 问答 > 编程问答 >

database - How to generate permutations in Oracle?

问题描述:

In Oracle, I have a table of object types.

I would like to generate all the permutations on ITEM_PURPOSE_CODE.

The table looks something like this:

ITEM_PURPOSE_CODE ITEM_CATEGORY_ID ITEM_ID

==========================================

1 101 50

2 202 94

2 202 95

What I would like then, is to generate a bunch of table types representing the permutations, for example:

ITEM_PURPOSE_CODE ITEM_CATEGORY_ID ITEM_ID

==========================================

1 101 50

2 202 94

and

ITEM_PURPOSE_CODE ITEM_CATEGORY_ID ITEM_ID

==========================================

1 101 50

2 202 95

Obviously this is a very simple case. There could be any number of item purpose codes (1 to n) and these codes could be repeated any number of times for differing item category IDs/item IDs.

Thanks for any advice.

网友答案:

Please find the solution to generating combinations here. It was a nice variant on a previous problem we've had in our software for real estate development.

Create and fill datamodel

First set up:

create table contents
( item_purpose_code number
, item_category_id  number
, item_id           number
)
/
begin
  insert into contents values (1, 101, 50);
  insert into contents values (2, 202, 94);
  insert into contents values (2, 202, 95);
  commit;
end;
/

Assisting views

First I create some views. But ofcourse you can also inline them or use with.

--
-- Add to each row the consecutive number of the driver columns
-- (here only item_purpose_code) and for each different value
-- for the driver columns a consecutive number that restarts
-- when a new driver column value starts.
--
create or replace force view sequencedrows
as
select item_purpose_code
,      item_category_id
,      item_id
,      dense_rank() 
       over 
       ( order 
         by        item_purpose_code
       ) driver_seq 
,      row_number() 
       over 
       ( partition 
         by        item_purpose_code 
         order 
         by        item_category_id
         ,         item_id 
       ) 
       values_per_driver_seq
from   contents
/
--
-- Generate list of combinations.
-- 
create or replace force view combinations
as
select  sys_connect_by_path (driver_seq || '-' || values_per_driver_seq, '#') || '#' combination
from    sequencedrows
where   level = ( select max(driver_seq) from sequencedrows )
start 
with    driver_seq = 1
connect 
by  
nocycle driver_seq  = prior driver_seq + 1
/

With these, it becomes really simple since the combination is already contained in the field combination and the rows have been numbered:

select c.combination
,      s.item_purpose_code
,      s.item_category_id
,      s.item_id
from   combinations c
join   sequencedrows s
on     c.combination like '%#' || to_char(s.driver_seq) || '-' || to_char(s.values_per_driver_seq) || '#%'
order
by     c.combination
,      s.driver_seq
,      s.values_per_driver_seq
/

The results are:

#1-1#2-1#  1  101  50
#1-1#2-1#  2  202  94

#1-1#2-2#  1  101  50
#1-1#2-2#  2  202  95

Performance

Depending on the data volume and indexes, the performance can be insufficient for interactive use. In our real estate development package we've however found that even with 50K rows generated performance is acceptable since Oracle 11g. Oracle 10g did a less optimal job on optimization.

When performance is unacceptable at your site, please list some key statistics or add a reproduction scenario.

分享给朋友:
您可能感兴趣的文章:
随机阅读: