data architecture question about indexes for foreign keys [message #273007] |
Mon, 08 October 2007 11:00 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #273304 is a reply to message #273284] |
Tue, 09 October 2007 22:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #273539 is a reply to message #273007] |
Wed, 10 October 2007 15:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #274044 is a reply to message #273539] |
Sat, 13 October 2007 00:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #274348 is a reply to message #274309] |
Mon, 15 October 2007 13:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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)
|
|
|