System and method for correlated fragmentations in databases
View Patent ↗Apparatus and methods to identify a home fragment for a child row of a child table of a database, the home fragment for a row being the fragment in which the row is stored. A child key value of the child row is obtained and used to identify a fragment of a parent table that is correlated with the child key value of the child row. The child key value being the value of a child key attribute of the child row and represents a reference to a parent row in a parent table. The result is the home fragment for the child row that corresponds to identified fragment of a parent table that is correlated with the child key value of the child row.
1. A system for eliminating fragments in the evaluation of a query that references both a parent and a child table in a relational database, comprising a processor configured for:
establishing a correlated fragmentation scheme using key values to define a M:N relationship between M fragments of a fragmented parent table and N fragments of the child table, the key being a child key for the child table and a parent key for the parent table, a child key value representing a reference to a parent row in the parent table;
obtaining a set of child key values for the child rows satisfying the query predicate;
using the set of child key values to identify a set of parent table fragments, the set of parent table fragments being all the fragments of the parent table having a row that is referenced by any of the set of child key values;
using any predicate or other qualifier on the parent table to further reduce the set of parent table fragment identifiers through the process fragment elimination; and
using the identified set of parent table fragments and the correlated fragmentation scheme to identify a set of child table fragments to be scanned; and
using the identified set of parent table fragments as the basis for a scan of the parent table.
2. Computer readable storage medium to execute code to eliminate fragments in the evaluation of a query that references both a parent and a child table in a relational database, comprising instructions to:
establish a correlated fragmentation scheme using key values to define a M:N relationship between M fragments of a fragmented parent table and N fragments of the child table, the key being a child key for the child table and a parent key for the parent table, a child key value representing a reference to a parent row in the parent table;
obtain a set of child key values for the child rows satisfying the query predicate; use the set of child key values to identify a set of parent table fragments, the set of parent table fragments being all the fragments of the parent table having a row that is referenced by any of the set of child key values;
use any predicate or other qualifier on the parent table to further reduce the set of parent table fragment identifiers through the process fragment elimination;
use the identified set of parent table fragments and the correlated fragmentation scheme to identify a set of child table fragments to be scanned; and
use the identified set of parent table fragments as the basis for a scan of the parent.
3. A computer implemented method for eliminating fragments in the evaluation of a query that references both a parent and a child table in a relational database, comprising:
establishing a correlated fragmentation scheme using key values to define a M:N relationship between M fragments of a fragmented parent table and N fragments of the child table, the key being a child key for the child table and a parent key for the parent table, a child key value representing a reference to a parent row in the parent table;
obtaining a set of child key values for the child rows satisfying the query predicate;
using the set of child key values to identify a set of parent table fragments, the set of parent table fragments being all the fragments of the parent table having a row that is referenced by any of the set of child key values;
using any predicate or other qualifier on the parent table to further reduce the set of parent table fragment identifiers through the process fragment elimination; and
using the identified set of parent table fragments and the correlated fragmentation scheme to identify a set of child table fragments to be scanned; and
using the identified set of parent table fragments as the basis for a scan of the parent table.