Automatically determining optimization frequencies of...

Details Automatically determining optimization frequencies of... Automatically determining optimization frequencies of...

2011-07-26

2011-07-26

Channavajjala, Srirama (Department: 2157)

Data processing: database and file management or data structures

Database and file access

Query optimization

C707S718000, C707S719000

Reexamination Certificate

active

07987178

ABSTRACT:
A method and system for automatically determining optimization frequencies of queries having one or more parameter markers. Execution plans for a query are generated and each plan is associated with one or more bind value sets. An optimization frequency is selected based on differences between pairs of execution costs where one execution cost of a pair is a cost of executing the query with a bind value set via a first execution plan and the other execution cost of the pair is a cost of optimally executing the query with the bind value set via a second execution plan. The differences are based on maximum selectivity or cardinality distances associated with the bind value sets. If none of the differences exceeds a predefined value, the query is optimized once. If at least one of the differences exceeds the predefined value, the query is reoptimized each time the query is executed.

REFERENCES:
patent: 5412804 (1995-05-01), Krishna
patent: 5544355 (1996-08-01), Chaudhuri et al.
patent: 5819255 (1998-10-01), Celis et al.
patent: 5822747 (1998-10-01), Graefe et al.
patent: 6021405 (2000-02-01), Celis et al.
patent: 6061676 (2000-05-01), Srivastava et al.
patent: 6205441 (2001-03-01), Al-omari et al.
patent: 6219660 (2001-04-01), Haderle et al.
patent: 6330552 (2001-12-01), Farrar et al.
patent: 6351742 (2002-02-01), Agarwal et al.
patent: 6353818 (2002-03-01), Carino, Jr.
patent: 6356891 (2002-03-01), Agrawal et al.
patent: 6510428 (2003-01-01), Tsuchida et al.
patent: 6529901 (2003-03-01), Chaudhuri et al.
patent: 6735594 (2004-05-01), Zimowski et al.
patent: 6757670 (2004-06-01), Inohara et al.
patent: 6865567 (2005-03-01), Oommen et al.
patent: 6928451 (2005-08-01), Mogi et al.
patent: 6957211 (2005-10-01), Tyulenev et al.
patent: 7051034 (2006-05-01), Ghosh et al.
patent: 7299226 (2007-11-01), Bruno et al.
patent: 2005/0065921 (2005-03-01), Hrle et al.
patent: 2005/0071346 (2005-03-01), Bernal et al.
patent: 2005/0097078 (2005-05-01), Lohman et al.
patent: 2005/0192951 (2005-09-01), Day et al.
patent: 2005/0223019 (2005-10-01), Das et al.
patent: 2005/0267866 (2005-12-01), Markl et al.
patent: 2006/0074875 (2006-04-01), Faunce et al.
patent: 2006/0136396 (2006-06-01), Brobst
patent: 2006/0212429 (2006-09-01), Bruno et al.
patent: 2007/0179947 (2007-08-01), Ahmed et al.
patent: 2007/0192296 (2007-08-01), Burger et al.
patent: 2008/0052266 (2008-02-01), Goldstein et al.
patent: 2008/0109424 (2008-05-01), Day et al.
patent: 2008/0195577 (2008-08-01), Fan et al.
patent: 2008/0215531 (2008-09-01), Markl et al.
patent: 2008/0222092 (2008-09-01), Hueske et al.
patent: 2008/0222093 (2008-09-01), Fan et al.
patent: 2008/0228831 (2008-09-01), Aboulnaga et al.
patent: W00055755 (2000-09-01), None
patent: W02005069163 (2005-07-01), None
Florian Waas1; et al. “Counting, Enumerating, and Sampling of Execution Plans in a Cost-Based Query Optimizer”,ACM 2000, pp. 499-509.
Yannis E. Ioannidis, “Query Optimization”, pp. 1-38.
Yousuke Watanabe et al. “Adaptive Query Optimization Method for Multiple Continuous Queries”,Proceedings of the 21st International Conference on Data Engineering (ICDE '05), pp. 1-4.
Kil Hong Joo et al. “An efficient query evaluation in a mediator based on implementation plan”,Information Sciences 176 (2006) 799-819.
Wei Hong et al. “Optimization of Parallel Query Execution Plans in XPRS”, 1991 EBB, pp. 218-225.
Faruk Polat et al. “Semantic information based-alternative plan generation for multiple query optimization”,Informatin Sciences 137 (2001) 103-133.
Cole et al., “Optimization of Dynamic Query Evaluation Plans”; Dec. 1993; pp. 1-23.
Ganguly, Sumit; “Design and Analysis of Parametric Query Optimization Algorithms”; Proceedings of the 24th VLDB Conference New York, USA, 1998; pp. 228-238.
Markl, Volker et al.; “Robust Query Processing through Progressive Optimization”; SIGMOD 2004, Jun. 13-18, 2004, Paris, Framce; 12 pages.
Navin Kabra et al.; “Efficient Mid-Query Re-Optimization of Sub-Optimal Query Execution Plans”; 1998 ACM; pp. 106-117.
Tanu Malik et al; A Black-Box Approach to Query Cardinality Estimation; 3rd Biennial Conference on Innovative Data Systems Research (CIDR) Jan. 7-10, 2007, Asilomar, California, USA; pp. 56-67.
Walid G. Aref et al; Optimization Strategies for Spatial Query Processing*; Proceedings of the 17th International conference on Very Large Data Bases; Barcelona, Sep. 1991; pp. 81-90.
Zaiqing Nie & Subbarao Kambhampati; Joint Optimization of Cost and Coverage of Query Plans in Data Integration; appeared in Proc. CIKM 2001; pp. 223-230.
Notice of Allowance for U.S. Appl. No. 11/673,142, filing Date Feb. 9, 2007; Confirmation No. 2106.
Gunning Technology Solutions, LLC; We help companies succeed with DB2, ORACLE, SQL Server, and MySQL; http://www.gunningts.com/db2zone.htm; 16 pages.
Hulgeri, et al.; AniPQO: Almost Non-intrusive Parametric Query Optimization for Nonlinear Cost Functions; Proceedings of the 29th VLDB Conference, Berlin, Germany, 2003; 12 pages.
Dietterich, et al.; Solving Multiclass Learning Problems via Error-Correcting Output Codes; Journal of Artificial Intelligence Research 2 (1995); Submitted Aug. 1994; published Jan. 1995; pp. 263-286.
Ioannidis, et al.; Parametric Query Optimization; In Proceedings of the 18th International Conference on Very Large Data Bases, Vancouver, Aug. 1992; 24 pages.

Affiliated with

Also associated with

Rating

Automatically determining optimization frequencies of... does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Automatically determining optimization frequencies of..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Automatically determining optimization frequencies of... will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2622585