Data Type of Partition Key Column Cannot Be Inferred Through Foreign Key Reference

I remember coming across this issue a few years ago and as I just hit it again I thought I’d “pass the note round the class”.

When you create a table that has foreign keys then a nicely robust way of specifying the data type for the foreign key columns is by allowing Oracle to infer it. Try the following script …

drop table fct_sale
/
drop table dim_date_of_sale
/
create table dim_date_of_sale
(
date_of_sale date primary key
)
/
create table fct_sale
(
date_of_sale not null references dim_date_of_sale,
product_cd number not null,
payment_method number not null,
transaction_id number not null,
sale_amt number not null
)
pctfree 0 nologging
/
desc fct_sale

However if you try this method when specifying that the fct_sale table should be partitioned on date_of_sale then Oracle will raise an error:

drop table fct_sale
/
drop table dim_date_of_sale
/
create table dim_date_of_sale
(
date_of_sale date primary key
)
/
create table fct_sale
(
date_of_sale not null references dim_date_of_sale,
product_cd number not null,
payment_method number not null,
transaction_id number not null,
sale_amt number not null
)
pctfree 0 nologging
partition by range
(
date_of_sale
)
(
partition P200501 values less than (date '2005-01-01')
)
/

This will give you “SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE”.

Now if you are partitioning on a numeric, does this problem go away?

No. Try the following:

drop table fct_sale
/
drop table dim_location
/
create table dim_location
(
location_cd number primary key
)
/
create table fct_sale
(
date_of_sale date not null,
location_cd not null references dim_location,
payment_method number not null,
transaction_id number not null,
sale_amt number not null
)
pctfree 0 nologging
partition by range
(
location_cd
)
(
partition P5 values less than (6)
)
/

This time we get the message “SQL Error: ORA-00932: inconsistent datatypes: expected – got NUMBER”. Oracle evidently is not sure what it is expecting, but it knows that it doesn’t like what it got — or what it thinks it didn’t get.

The fix is simply to make sure that despite the specification of the foreign key (which you ought to name, by the way) you specify the column data type also.

drop table fct_sale
/
drop table dim_date_of_sale
/
create table dim_date_of_sale
(
date_of_sale date primary key
)
/
create table fct_sale
(
date_of_sale date not null references dim_date_of_sale,
product_cd number not null,
payment_method number not null,
transaction_id number not null,
sale_amt number not null
)
pctfree 0 nologging
partition by range
(
date_of_sale
)
(
partition P200501 values less than (date '2005-01-01')
)
/

About these ads

3 thoughts on “Data Type of Partition Key Column Cannot Be Inferred Through Foreign Key Reference

  1. Hi David,

    I saw you have researched a lot on Materialize Views.
    Just a question I thought you might know the answer:

    I have a query (Q) that takes some 10 to 15 minutes to execute.
    What I discovered was that this query has very different performance in those 2 statements:

    1. create table T as Q;

    2. create materialized view MV refresh force on demand as Q;

    Case 1 takes ca 10-15 minutes to execute, while case 2 takes between 1 and 2 hours to execute, even though the query Q is identical in the two statements.

    I had expected those stmts to take approx the same time, and first though it must have been caused by some other loads affecting the disk-systems, but I then ran the same statements several times over, alternating between the 2 cases and could only confirm I get similar results every time.

    Case 2 takes ca 5 times longer to execute than case 1.

    You have any clue on the details within the Oracle-engine that may cause this difference?

    BR, John

  2. Tracing the materialized view session is the way to go here. Find out what recursive SQL is being executed and which of them is responsible for that time. Don’t forget that you can create and populate a table then create a materialized view on top of it. There are some restrictions when you have done this but probably none of them will be significant to you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s