Home » SQL & PL/SQL » SQL & PL/SQL » data architecture question about indexes for foreign keys (Referential integrity for foreign keys)
icon5.gif  data architecture question about indexes for foreign keys [message #273007] Mon, 08 October 2007 11:00 Go to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
The OBJ_TIME_REF table below will have 100M rows.

This implies that the secondary index shown below will also have 100M rows.

CREATE TABLE DBO.OBJ_TIME_REF
( 
    OBJ_TIME_REF_ID NUMBER( 10 )   NOT NULL,
    OBJ_TIME_REF    TIMESTAMP( 6 ) NOT NULL,
    OBJ_ALIAS_ID    VARCHAR2( 20 ) NOT NULL,
    SM_GRP_ID       NUMBER( 2 )    NOT NULL,
    FILE_TYPE_CD    VARCHAR2( 25 ) NOT NULL,
    CONSTRAINT REF_FILE_TYPE_CD_5484
    FOREIGN KEY ( FILE_TYPE_CD )
    REFERENCES DBO.FILE_TYPE_CD ( FILE_TYPE_CD )
    ENABLE,
    CONSTRAINT REF_SM_GRP_5064
    FOREIGN KEY ( SM_GRP_ID )
    REFERENCES DBO.SM_GRP ( SM_GRP_ID )
    ENABLE
 )

CREATE INDEX DBO.OBJ_TIME_REF_FK1_IDX
    ON DBO.OBJ_TIME_REF( FILE_TYPE_CD )


I understand that, for referential integrity, if an attempt to delete a row from the FILE_TYPE_CD table that has a corresponding row in the OBJ_TIME_REF table with the same FILE_TYPE_CD value, then the delete will be disallowed.

There are only 10 differnt FILE_TYPE_CD values. Hence, it seems to me that to have 100M rows in the OBJ_TIME_REF_FK1_IDX index is a tremendous amount of overhead.

Is there a mechanism to implement referential integrity for foreign keys so that the OBJ_TIME_REF_FK1_IDX index would only have 10 rows? ... or 1 row for each unique FILE_TYPE_CD value in the OBJ_TIME_REF table?

Thanks,
Tom


Re: data architecture question about indexes for foreign keys [message #273010 is a reply to message #273007] Mon, 08 October 2007 11:30 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
A bitmap index uses very little space compared to a regular index - especially in your case of low selectivity. Bitmap indexes however have a higher maintenance overhead so are less suitable for regular insert/update/delete DML. If your list of values is small and stable, you could consider eliminating your code table in favour of code enforced permitted values. Of course that has disadvantages of it's own...
Re: data architecture question about indexes for foreign keys [message #273114 is a reply to message #273007] Tue, 09 October 2007 02:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you ever going to delete/update the values of FILE_TYPE_CD in the FILE_TYPE_CD table?

If you do not have the index on OBJ_TIME_REF.FILE_TYPE_CD then every time you try to update/delete FILE_TYPE_CD in the parent table it will force a full table scan of OBJ_TIME_REF.

If you are not likely to update/delete these values, and you don't search OBJ_TIME_REF by the FILE_TYPE_CD column then there is no reason why you shouldn't drop the index. The FK constraint doesn't require the index to exist, it just makes it work faster.
Re: data architecture question about indexes for foreign keys [message #273284 is a reply to message #273007] Tue, 09 October 2007 18:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
as an additional alternative, Oracle now supports compression.

You could try compressing the index if you are worried about space. If there are truly only about 10 different codes, then you may be looking at serious savings.

Just read up on compression in Oracle, and do some googling, before you us it. Like every Oracle advanced feature, it has its own quirks. You must go into it with both eyes open.

And you should specifically look for information on the performance costs of compressions. You would in particular be wanting to know if compressed indexes suffer from similar issues as bitmap indexes.

Good luck, Kevin
Re: data architecture question about indexes for foreign keys [message #273304 is a reply to message #273284] Tue, 09 October 2007 22:44 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Kevin Meade wrote on Wed, 10 October 2007 09:40

as an additional alternative, Oracle now supports compression.

You could try compressing the index if you are worried about space. ...

Are we talking about Key Compression here? I'm pretty sure this only works on multi-column (concatenated) indexes. When the leading column(s) do not have many different values, Oracle can store just one copy of them in each index block. More here.

The new compression feature that I am aware of is Table Compression. It works on similar principals (ie. repeated values), but as of 10gR2 it only works for direct-path loaded data. 11g has extendended it to conventionally loaded data, but (I believe) it still only applies to tables - not indexes.

Ross Leishman
Re: data architecture question about indexes for foreign keys [message #273507 is a reply to message #273304] Wed, 10 October 2007 13:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
You are correct, I was not payting attention, thanks for the clarification on that single column index.

sorry guys. Kevin
Re: data architecture question about indexes for foreign keys [message #273539 is a reply to message #273007] Wed, 10 October 2007 15:14 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Thanks for everyones input.

I still want the index for the referential integrity on the foreign key.

I guess I was hoping for new type of index that would only store unique values, in this case 10 unique values; i.e. there will probably never be a need for a query that displays all OBJ_TIME_REF records for a given FILE_TYPE_CD, and when a row is deleted from the FILE_TYPE_CD table I still want the error to occur if there are 1 or more OBJ_TIME_REF records with the deleted FILE_TYPE_CD.

Maybe this type of index could be suggested to the Oracle Corp. as potential enhancement.
Re: data architecture question about indexes for foreign keys [message #273624 is a reply to message #273539] Thu, 11 October 2007 02:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As long as you have a Foreign Key constraint, you have referential integrity. The index just makes it happen faster in the event of deletes or updates of the parent table.

You will not lose RI by dropping the index.
Re: data architecture question about indexes for foreign keys [message #274044 is a reply to message #273539] Sat, 13 October 2007 00:13 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
tomstone_98 wrote on Thu, 11 October 2007 06:14


Maybe this type of index could be suggested to the Oracle Corp. as potential enhancement.


And how would Oracle use such an index. By definition, an index is a structure that helps you find specific individual rows in a table without having to scan the table completely.

What you are talking about is not an index but a Denormalised Table. You could do it yourself by creating a table containing (KEYVAL, KEYCOUNT). Add triggers to the base table to create/delete/increment/decrement the KEYCOUNT as rows are inserted/deleted or their key is updated. Then when you want to know the COUNT or DISTINCT keys, simply query the denormalised version.

Ross Leishman
Re: data architecture question about indexes for foreign keys [message #274309 is a reply to message #274044] Mon, 15 October 2007 08:57 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member

Quote:

What you are talking about is not an index but a Denormalised Table. You could do it yourself ...


Yes ... and what I am suggesting is that Oracle gives us an option when creating foreign key relationships such that Oracle creates and maintains this table, and uses it for RI.

If any of the moderators deems this idea to be worth mentioning to Oracle, then I think that would be great.

Re: data architecture question about indexes for foreign keys [message #274319 is a reply to message #274309] Mon, 15 October 2007 09:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This index would only be any conceivable use if you never needed to get the child values for a given parent.

If you ever need to get the child values, and the child table is large, then you'll want an index on the FK column anyway.

Maintaining this new index would be slow.
Every time you deleted a child record, or updated a FK value in a child record, you'd have to scan the entire table to see if there were any other records with the same FK value as the deleted record, to let you know whether you had to remove that value from this new index type, and the quickest way of doing that would be .... with a standard FK index.

I honestly can't see any real merit in this idea.

Re: data architecture question about indexes for foreign keys [message #274321 is a reply to message #274319] Mon, 15 October 2007 10:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
not fully following all the repies, but are we perchance talking about a bitmap-join index. Oracle does these. And yes, they are costly to update. But has a look at them and let us know what you think.

Kevin
Re: data architecture question about indexes for foreign keys [message #274348 is a reply to message #274309] Mon, 15 October 2007 13:13 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
tomstone_98 wrote on Mon, 15 October 2007 15:57

If any of the moderators deems this idea to be worth mentioning to Oracle, then I think that would be great.

Apart from the fact whether or not this idea is good, the moderators of this forum are not affiliated with Oracle Corp. *). We are just users like any of the others in the forum; we just happen to have posted some more.

*) ok, not through this forum anyway)
Previous Topic: Declaration of an associative array
Next Topic: Utl__File
Goto Forum:
  


Current Time: Tue Dec 03 20:40:32 CST 2024