查询分片表的规则

测试表和数据：

drop table if exists employee ;
drop table if exists employee3 ;
CREATE TABLE employee (emp_id INTEGER, name CHAR(32), dept_id CHAR(2), mgr_id INTEGER, ssn CHAR(12))
  partition BY expression 
  partition p0 (emp_id < 100) in dbs1,
  PARTITION p1 (emp_id < 200) IN dbs1,
  PARTITION p2 (emp_id < 400) IN dbs1,
  remainder in dbs1 
;
CREATE INDEX employee_id_idx ON employee(emp_id) 
  partition BY expression 
  partition p0 (emp_id < 100) in dbs1,
  PARTITION p1 (emp_id < 200) IN dbs1,
  PARTITION p2 (emp_id < 400) IN dbs1,
  remainder in dbs1 
;
INSERT INTO employee VALUES (401, "Susan", "DV", 101, "123-45-6789");
INSERT INTO employee VALUES (601, "David", "QA", 104, "987-65-4321");

查看分片规则：

select f.fragtype as t, f.strategy as s ,f.evalpos::char(2) as evalpos ,f.partition[1,10] ,dbms_lob_substr(f.exprtext,30)::char(30) as exprtext
from sysfragments f, systables t
where t.tabname = 'employee' and t.tabid = f.tabid
order by 1,evalpos

 

间隔分片的查询规则：

select f.fragtype as t, f.strategy as s ,f.evalpos::char(2) as evalpos ,f.partition[1,10] ,dbms_lob_substr(f.exprtext,30)::char(30) as exprtext
from sysfragments f, systables t
where t.tabname = 'employee' and t.tabid = f.tabid
order by 1,evalpos