How to partition an Oracle table with 65 million rows

indexoraclepartitioningperformance

The table that needs partitioning contains a list of Components and their Values.

So one table is tblComponents. Another is tblFields. Then this table, tblValues, contains the values of the Components/Fields, so it has a Component foreign key as well as a Field foreign key. Right now this tblValues table contains 68,000,000 values. Components table contains ~80,000 rows. Each component has around 500-800 fields of inventory.

I was thinking about creating a partition by hash on the field id. As the user will query, they will pick something like.. ."Show me the components where field id 5 = '03530'" Something along those lines.

Sample queries are below that are common:

select * from tblComponents comp inner join tblValues v on comp.comp_id = v.comp_id
where v.field_id = 55 and dbms_lob.substr(v.value, 2000, 1) like '%brick%'

Also, the values are currently stored as CLOB's and can have numeric values or string values.

Any tips regarding how you would partition or index this would be great, especially for optimizing querying.

Best Answer

Partitioning is a licensable option for the Enterprise Edition. This makes it expensive. Unless you already have purchased it this is not the way to go. And even it you have purchased it, it might not be the right approach.

Why not start with more straightforward tuning? Obviously you have neglected to provide even cursory information regard data distribution (how many distinct values of FIELD_ID?) or database version, let alone something as useful as an explain plan, so the following are guesses.

"Show me the components where field id 5 = '03530'"

That sort of query would most likely benefit from a compound index on:

tblValues(field_id, comp_id)

dbms_lob.substr(v.value, 2000, 1) like '%brick%'

Is the sort of thing which is bound to suck. You show those sorts of queries are common. In which case you need to use Oracle Text. That is designed for indexing and searching large free text fields. Find out more.

In your case I think you need a CTXCAT index; in Oracle 11g we can build Composite Domain Indexes which mix CTXCAT indexes with regular columns. You might get some good results from creating a CTXCAT index on value ad creating sub-indexes on (field_id) and perhaps (field_id, comp_id). Find out more.

Related Solutions

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

Situation 1:

Your tables have one relationship and not two. (example: a Device belongs to a DeviceType)

So, keep only one relationship, the one with the composite keys (that include the Primary Key). The other relationship is redundant when the composite one is defined.

I would also suggest you have same names for related columns:

DeviceCategory Table
CategoryCode | Name        | Description
----------------------------------------
WKS          | Workstation | Description of what classifies an item as a workstation...
LPT          | Laptop      | Description of what classifies an item as a laptop...

DeviceType Table
DeviceTypeID | CategoryCode | Manufacturer | Model  | IsTrackedInOtherSystemDefault
-----------------------------------------------------------------------------------
1            | WKS          | Dell         | GX1000 | true
2            | LPT          | HP           | dv4000 | false
3            | WKS          | HP           | xx9000 | false

Device Table
DeviceID | SerialNumber | DeviceTypeID | IsTrackedInOtherSystem | CategoryCode 
------------------------------------------------------------------------------
1        | I81U812      | 1            | true                   | WKS
2        | N0S4A2       | 1            | false                  | WKS
3        | 3BL1NDMIC3   | 2            | false                  | LPT

So, the design would be:

DeviceCategory
--------------
CategoryCode   PK
Name           U1
Description

DeviceType 
----------
DeviceTypeID   PK   U1
CategoryCode   FK   U1
Manufacturer   U2
Model          U2
IsTrackedInOtherSystemDefault

Device 
------
DeviceID       PK   U1
SerialNumber   U2
DeviceTypeID   FK1
IsTrackedInOtherSystem
CategoryCode   FK1  U1

and for the Computer:

Computer
--------
DeviceID       PK   FK1
Hostname       U1
IPAddress      U2
CategoryCode   FK1  CHK

The "additional" UNIQUE keys (the two composite U1 ones) will be needed in most DBMS to enforce the foreign key constraints. I guess this answers your question 2, relationships needs indices to be enforced, so (you have to) use them. They will be used by the DBMS not only to enforce integrity but in your queries/statements, when you will be joining the tables.

The only one that is not needed is the U3 you had in the Computer table.

About question 3 (the over-engineering part): No, I don't think so but that's just my opinion. And you haven't told us if this is a homework/exercise or a real project, whether you will be holding only your family's or a multi-million company's inventory, etc.

Situation 2

I think what you have is fine and there is no need (and not a good idea) to have referential integrity constraints on these columns. This is a default value that is copied in the second table via a stored procedure (I guess during Inserts on the second table?) or altered by a user. If you add an FK, won't that deny users the ability to override the default?

The names of the two columns are self-explanatory enough for a DBA to understand the functionality.

Sql-server – Large time difference between two almost identical simple queries…why

I am willing to bet the issue is that the first does a scan of the table, while the second can look things up from an index. This happens all the time. The reason, as Phil has said, is that there are an infinite number of possibilities.

If you want to make the first approach work better with more flexibility you need to use a recursive query structure. Something like:

WITH RECURSIVE sparse_scan AS (
    SELECT m.Metadata, COUNT(*) As Count
      FROM TWG10MinData twg, Metadata m
     WHERE twg.metadata = m.Metadata and m.Metadata = 1100
  GROUP BY m.Metadata
 UNION ALL
    SELECT m.Metadata, COUNT(*) As Count
      FROM TWG10MinData twg, Metadata m
      JOIN sparse_scan s ON (m.Metadata = s.Metadata + 1000)
     WHERE twg.metadata = m.Metadata 
  GROUP BY m.Metadata
)
SELECT * FROM sparse_scan order by m.Metadata;

This works differently, running the query for 1000, 2100, 3100, 4100, etc, until a row is not returned. You can be a little fancier to skip cases where a row does not exist but the next one does, but the basic approach is something I use frequently, albeit on PostgreSQL.

Best Answer

Related Solutions

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

Sql-server – Large time difference between two almost identical simple queries…why

Related Question