This discussion is archived
7 Replies Latest reply: Apr 4, 2013 1:43 PM by rp0428 RSS

Unable to partition table on referenced columns?

siromega Newbie
Currently Being Moderated
Is there any way to partition a table on a FK that is referencing another table in the table definition?

Create table test (
ID number(12) primary key,
FK referencing other_test,
c1 varchar2(50)
)
Partition by List(FK) (
Partition P1 values (1),
Partition P2 values (2),
Partition P3 values (3)
);
  • 1. Re: Unable to partition table on referenced columns?
    rp0428 Guru
    Currently Being Moderated
    >
    Is there any way to partition a table on a FK that is referencing another table in the table definition?
    >
    What happened when you tried it?

    Why does your subject say 'Unable to partition...' when you didn't try it to even know if it works or not?

    Don't be afraid of breaking Oracle by actually trying things.

    This works for me.
    CREATE TABLE EMP_PART
    (
      EMPNO     NUMBER(4) PRIMARY KEY,
      ENAME     VARCHAR2(10 BYTE),
      JOB       VARCHAR2(9 BYTE),
      MGR       NUMBER(4),
      HIREDATE  DATE,
      SAL       NUMBER(7,2),
      COMM      NUMBER(7,2),
      DEPTNO    NUMBER(2)
    )
    PARTITION BY LIST(DEPTNO)
    (
    PARTITION P10 VALUES (10),
    PARTITION P20 VALUES (20),
    PARTITION P30 VALUES (30),
    PARTITION P40 VALUES (40)
    )
    
    ALTER TABLE EMP_PART ADD (
      CONSTRAINT FK_DEPTNO_PART 
     FOREIGN KEY (DEPTNO) 
     REFERENCES DEPT (DEPTNO));
    You don't partition a table on a 'FK'; you partition it on one or more columns. Why did you think it matters if the column is part of a foreign key?
  • 2. Re: Unable to partition table on referenced columns?
    siromega Newbie
    Currently Being Moderated
    This is the error...

    Create table test (
    ID number(12) primary key,
    FK referencing other_test,
    c1 varchar2(50)
    )
    Partition by List(FK) (
    Partition P1 values (1),
    Partition P2 values (2),
    Partition P3 values (3)
    );

    Error at Command Line:7 Column:22
    Error report:
    SQL Error: ORA-00932: inconsistent datatypes: expected - got NUMBER
    00932. 00000 - "inconsistent datatypes: expected %s got %s"
    *Cause:   
    *Action:


    However, this works

    Create table test (
    ID number(12) primary key,
    FK Number(12),
    c1 varchar2(50)
    )
    Partition by List(FK) (
    Partition P1 values (1),
    Partition P2 values (2),
    Partition P3 values (3)
    );

    So basically, the "referencing" column definition breaks the ability to partition on that column.
  • 3. Re: Unable to partition table on referenced columns?
    rp0428 Guru
    Currently Being Moderated
    You still haven't posted any DDL that can be used to reproduce your problem. I posted a working example that anyone can try in the default SCOTT schema and tables.

    This also works for me
    Create table test (
    ID number(12) primary key,
    FK number(2) referencing dept,
    c1 varchar2(50)
    )
    Partition by List(FK) ( 
    Partition P1 values (1),
    Partition P2 values (2),
    Partition P3 values (3)
    );
    Next time post DDL that can be used to reproduce your problem and DO NOT use reserved words or terms (e.g. FK) as object or column names.
  • 4. Re: Unable to partition table on referenced columns?
    siromega Newbie
    Currently Being Moderated
    Is this better? Geez, I think you would get the point from the DDL I posted above.

    Create table test (
    ID number(12) primary key,
    FK1 referencing other_test,
    c1 varchar2(50)
    )
    Partition by List(FK1) (
    Partition P1 values (1),
    Partition P2 values (2),
    Partition P3 values (3)
    );

    Error at Command Line:7 Column:22
    Error report:
    SQL Error: ORA-00932: inconsistent datatypes: expected - got NUMBER
    00932. 00000 - "inconsistent datatypes: expected %s got %s"
    *Cause:   
    *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 5. Re: Unable to partition table on referenced columns?
    rp0428 Guru
    Currently Being Moderated
    >
    Geez, I think you would get the point from the DDL I posted above.
    >
    I posted your answer almost 3 hours ago.

    Geez, I think you would get the point from the DDL I posted above.
  • 6. Re: Unable to partition table on referenced columns?
    siromega Newbie
    Currently Being Moderated
    The issue is that I don't want to have the column defined in a bunch of places. The "referencing" keyword takes care of that by just copying the definition from the referenced table. But that breaks when you use that column to partition on. So is there a way around that, is it a bug or defect, or am I stuck?
  • 7. Re: Unable to partition table on referenced columns?
    rp0428 Guru
    Currently Being Moderated
    >
    So is there a way around that, is it a bug or defect, or am I stuck?
    >
    The only one of those three possibilities I am certain of is the third! ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points