1

I am using Oracle 11g database and I have 45 million records. I want to partition the table, but if it's possible for partitioning to support the like operator then it will be much helpful for me.

In my table one column is URI, and for that column I want to use the like operator during partition so that it can help during insertion, because it contains different type of uris such as model://a/b or list://a/b.

I want something like:

CREATE TABLE table_name (
    URI varchar2(20);
)
PARTITION BY LIST ( URI )
      (PARTITION part_mod VALUES (like 'model://%'),
       PARTITION part_list VALUES (like 'list://%'),
       PARTITION part_def VALUES (default)
       );

I tried using Virtual Column-Based Partitioning but didn't get any success. Partitions were created, but during insertion no data is going into corresponding partitions; everything is going into part_def.

So is it possible to use the like operator to partition a table in oracle database?

2
  • Which Oracle version are you using?
    – miracle173
    Nov 21, 2014 at 13:56
  • @miracle i have already mentioned in the post i.e. Oracle 11g database.
    – Krishna
    Nov 24, 2014 at 5:56

1 Answer 1

1

Virtual column partitioning:

create table table_name (
    uri varchar2(20),
    part as (
      case 
        when uri like 'model://%' then 'mod'
        when uri like 'list://%' then 'list'
        else 'def'
      end
    )
)
partition by list (part) (
  partition part_mod values ('mod'),
  partition part_list values ('list'),
  partition part_def values ('def')
);

Seems to work:

insert into table_name (uri) values ('model://abc');
insert into table_name (uri) values ('list://abc');
insert into table_name (uri) values ('test://abc');

select * from table_name partition (part_mod);
select * from table_name partition (part_list);
select * from table_name partition (part_def);

Inserted rows are returned from correct partitions.

1
  • thanks i can use as a last option but if it is possible without adding any extra column in the table then it will be much easy for me to implement.
    – Krishna
    Nov 24, 2014 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.