System and method of insurance database optimization using social networking

• US 10,331,664 B2
• Filed: 09/23/2011
• Issued: 06/25/2019
• Est. Priority Date: 09/23/2011
• Status: Active Grant

• Current Assignee: Hartford Fire Insurance Company (The Hartford Financial Services Group, Inc.)
• Original Assignee: Hartford Fire Insurance Company (The Hartford Financial Services Group, Inc.)
• Inventors: Meade, Kevin
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): Le, Jessica N
• Application Number:
  US13/242,610
• Publication Number:
  US 20130080416A1
• Time in Patent Office: 2,832 Days
• Field of Search: 707713
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

A system and method of collecting statistics on a database for performing at least one insurance related structured query language (SQL) query are disclosed. The system and method includes providing at least one insurance related SQL query, determining the relationships of tables subject to the provided at least one insurance related SQL query, transforming the determined relationships of tables into a display representing communities of tables, and collecting statistics on at least one community of tables based on the at least one insurance related SQL query. The tables of the system and method represent insurance data.

Claims

1. A system for collecting statistics on a database for performing at least one structured query language (SQL) query, the system comprising:

a memory for storing data in a database organized in a plurality of tables; and

a processor coupled to the memory, the processor, accessing the memory, to;

process the at least one SQL query related to insurance data;

determine one or more interrelationships of the plurality of tables in the database by parsing the SQL query and identifying common dependencies of the tables after parsing the SQL query, wherein the determining interrelationships and identifying common dependencies at least includes identifying perfect communities and imperfect communities and for imperfect communities identifying one or more of the plurality of tables that reference another community;

transform the determined interrelationships of the plurality of tables into a group representing communities of tables based on the common dependencies by quantifying a risk of the imperfect communities and adding in additional tables with the plurality of tables;

perform the at least one SQL query on the transformed group of tables.

2. The system of claim 1 wherein the plurality of tables are interrelated.

3. The system of claim 1 further comprising a display on which a representation of the grouping of communities of tables is rendered.

4. The system of claim 3 wherein the display presents a depiction of the interrelation using communities of tables.

5. The system of claim 1 further comprising an optimizer for collecting statistics on the plurality of tables and the collected statistics are used to optimize the insurance related query.

6. The system of claim 1 wherein the communities of tables represent at least one perfect community of tables.

7. The system of claim 1 wherein the communities of tables represent at least one imperfect community of tables.

8. The system of claim 1 wherein a risk associated with the at least one table referencing another community of tables is managed using hints.

9. The system of claim 1 wherein a risk includes cascading.

10. The system of claim 1 wherein the insurance related query is a query for a client request rate fact quote.

11. A method of collecting statistics on a database for performing at least one structured query language (SQL) query, the method comprising:

processing at least one SQL query related to insurance data;

determining one or more interrelationships of tables in a database stored in a memory device by parsing the query and determining common dependencies of the tables identified after the parsed query, the one or more interrelationships of tables responsive to the at least one SQL query, wherein the determining interrelationships and identifying common dependencies at least includes identifying perfect communities and imperfect communities and for imperfect communities identifying one or more of the tables that reference another community;

transforming the determined interrelationships of tables into a group representing communities of tables with the one or more interrelationships of tables based on the common dependencies by quantifying a risk of the imperfect communities and adding in additional tables with the tables; and

perform the at least one SQL query on the transformed group of tables,

wherein the one or more interrelationships of the plurality of tables in the database represent insurance data.

12. The method of claim 11 further comprising collecting statistics on the at least one community of tables based on the at least one insurance related SQL query using the collected statistics to optimize the insurance related query.

13. The method of claim 11 wherein the at least one insurance related SQL query comprises client request rate fact quotes.

14. The method of claim 11 wherein the communities of tables represent at least one perfect community of tables.

15. The method of claim 11 wherein the communities of tables represent at least one imperfect community of tables.

16. The method of claim 15 further comprising denoting for each of the at least one imperfect community of tables the at least one table referencing another community of tables.

17. The method of claim 16 further comprising managing a risk associated with the denoted at least one table.

18. The method of claim 17 wherein the risk includes cascading.

19. The method of claim 17 wherein the risk is managed using hints.

20. The method of claim 11 further comprising displaying the grouping of tables to graphically depict the relationships of tables.

21. A non-transitory computer readable medium including hardware design code stored thereon which when executed by a processor cause the system to perform a method for collecting statistics on a database for performing at least one insurance related SQL query, the method comprising:

processing at least one insurance related SQL query;

determining one or more interrelationships of tables of a database stored in a memory device by parsing the query and determining the dependencies of the tables identified after the parsing query, wherein the one or more relationships of tables responsive to the at least one insurance related SQL query, wherein the determining interrelationships and identifying common dependencies at least includes identifying perfect communities and imperfect communities and for imperfect communities identifying one or more of the plurality of tables that reference another community;

transforming the determined interrelationships of tables into a group representing communities of tables with the interrelationships of tables based on common dependencies by quantifying a risk of the imperfect communities and adding in additional tables with the plurality of tables; and

perform the at least one SQL query on the transformed group of tables,

wherein the one or more interrelationships of tables represent data associated with at least one aspect of insurance data.