Facilitating Identification of Star Schemas in Database Environments

• US 20080249985A1
• Filed: 04/05/2007
• Published: 10/09/2008
• Est. Priority Date: 04/05/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Mali, Suresh, Mittal, Ashish, Steadman, James, Fragapane, Paolo, Jayashankar, Mahesh, Durran, Mike, Kadambalil Baby, Geemon
• Granted Patent: US 9,158,818 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

Facilitating identification of star schemas in database environments. In an embodiment, queries are examined to determine the joins required. The two tables of each join are added as a relationship in the star schema.

Claims

1. A computer readable medium carrying one or more sequences of instructions for causing a system to facilitate identification of a star schema for a database, wherein a schema of said database defines a plurality of tables in said database, wherein execution of said one or more sequences of instructions by one or more processors contained in said system causes said system to perform the actions of:

receiving a first query requiring a first join of a first table and a second table, wherein said first table and said second table are contained in said plurality of tables; and

including a first relationship associating said first table and said second table in said star schema.

2. The computer readable medium of claim 1, wherein said first query requires a second join of said second table and a third table, further comprising more instructions for:

including a second relationship associating said second table and said third table in said star schema; and

identifying said second table as a fact table and said first table and said third table as dimension tables in said star schema.

3. The computer readable medium of claim 2, further comprising more instructions for:

identifying a fourth table contained in said plurality of tables, but not yet included in said star schema;

determining a fifth table in said star schema having a relationship with said fourth table in said normalized schema; and

adding a fourth relationship associating said forth table and said fifth table in said star schema.

4. The computer readable medium of claim 3, further comprising more instructions for merging a first mini-star with a second mini-star, wherein said first mini-star contains said first relationship, said second relationship and said fourth relationship, wherein said merging comprises:

finding a common dimension table in both of said first mini-star and said second mini-star; and

linking said first mini-star with said second mini-star using said common dimension table as a single node.

5. A method of faciliating identification of a star schema for a database, wherein a schema of said database indicates a plurality of relationships existing among a plurality of tables in said database, wherein said method comprises:

receiving a plurality of queries, wherein each of said plurality of queries specifies a corresponding one of a plurality of subset of relationships, said subset of relationships being contained in said plurality of relationships; and

generating said star schema as including said plurality of subset of relationships.

6. The method of claim 5, wherein said generating further comprises:

identifying a plurality of mini-star schemas corresponding to said plurality of queries, wherein each of said plurality of mini-star schemas includes said subset of relationships corresponding to one of said plurality of queries; and

merging said plurality of mini-star schemas to generate said star schema.

7. The method of claim 6, wherein said identifying further comprises:

determining a first table not existing in said plurality of mini-star schemas and a second table existing in said plurality of mini-star schemas, wherein said first table and said table are contained in said plurality of tables; and

adding a new relationship to one of said plurality of mini-star schemas, said new relationship existing between said first table and said second table, wherein said new relationships is contained in said plurality of relationships.

8. The method of claim 6, wherein merging a first mini-star schema with a second mini-star schema is performed only if there is a common table existing in said first mini-star schema and said second mini-star schema, wherein said first mini-star schema and said second mini-star schema are contained in said plurality of mini-star schemas.

9. The method of claim 5, wherein each of said subset of relationships is specified by a corresponding join in the corresponding query.

10. An apparatus for facilitating identification of a star schema for a database, wherein a schema of said database indicates a plurality of relationships existing among a plurality of tables in said database, wherein said apparatus comprises:

means for receiving a plurality of queries, wherein each of said plurality of queries specifies a corresponding one of a plurality of subset of relationships, said subset of relationships being contained in said plurality of relationships; and

means for generating said star schema as including said plurality of subset of relationships.

11. The apparatus of claim 10, wherein said means for generating further comprises:

means for identifying a plurality of mini-star schemas corresponding to said plurality of queries, wherein each of said plurality of mini-star schemas includes said subset of relationships corresponding to one of said plurality of queries; and

means for merging said plurality of mini-star schemas to generate said star schema.

12. The apparatus of claim 11, wherein said means for identifying further comprises:

means for determining a first table not existing in said plurality of mini-star schemas and a second table existing in said plurality of mini-star schemas, wherein said first table and said table are contained in said plurality of tables; and

means for adding a new relationship to one of said plurality of mini-star schemas, said new relationship existing between said first table and said second table, wherein said new relationships is contained in said plurality of relationships.

13. The apparatus of claim 11, wherein means for merging a first mini-star schema with a second mini-star schema is performed only if there is a common table existing in said first mini-star schema and said second mini-star schema, wherein said first mini-star schema and said second mini-star schema are contained in said plurality of mini-star schemas.

14. The apparatus of claim 10, wherein each of said subset of relationships is specified by a corresponding join in the corresponding query.