Performance data for query optimization of database partitions

Details Performance data for query optimization of database partitions Performance data for query optimization of database partitions

2005-05-26

2010-06-08

Cottingham, John R. (Department: 2167)

Data processing: database and file management or data structures

Database and file access

Query optimization

Reexamination Certificate

active

07734615

ABSTRACT:
A method, apparatus, system, and signal-bearing medium that, in an embodiment, determine performance data for query optimization of a query directed to a database table having multiple partitions. The performance data for the query optimization is presented in a user interface, and an option is provided in the user interface to create a non-spanning index for any of the partitions mentioned in the performance data. In various embodiments, determining the performance data may include: determining the partitions that were unexpanded by the query optimization, determining the partitions that had a runtime estimate that was different from an actual query runtime by a threshold, determining whether any of the partitions had expansion forced by a client request, determining whether the query performed partition elimination, determining whether any of the partitions has an associated index that is unused by the query and that also has a key that is not present in all of the partitions, and determining a constraint for partition elimination.

REFERENCES:
patent: 5404510 (1995-04-01), Smith et al.
patent: 5590319 (1996-12-01), Cohen et al.
patent: 5668987 (1997-09-01), Schneider
patent: 5819255 (1998-10-01), Celis et al.
patent: 5822747 (1998-10-01), Graefe et al.
patent: 5924094 (1999-07-01), Sutter
patent: 5974457 (1999-10-01), Waclawsky et al.
patent: 6021405 (2000-02-01), Celis et al.
patent: 6026391 (2000-02-01), Osborn et al.
patent: 6032143 (2000-02-01), Leung et al.
patent: 6092062 (2000-07-01), Lohman et al.
patent: 6112198 (2000-08-01), Lohman et al.
patent: 6223171 (2001-04-01), Chaudhuri et al.
patent: 6275818 (2001-08-01), Subramanian et al.
patent: 6339769 (2002-01-01), Cochrane et al.
patent: 6470335 (2002-10-01), Marusak
patent: 6564212 (2003-05-01), Koskas
patent: 6567802 (2003-05-01), Popa et al.
patent: 6581055 (2003-06-01), Ziauddin et al.
patent: 6609123 (2003-08-01), Cazemier et al.
patent: 6618719 (2003-09-01), Andrei
patent: 6643640 (2003-11-01), Getchius et al.
patent: 6691101 (2004-02-01), MacNicol et al.
patent: 6763359 (2004-07-01), Lohman et al.
patent: 6785673 (2004-08-01), Fernandez et al.
patent: 6816874 (2004-11-01), Cotner et al.
patent: 6931401 (2005-08-01), Gibson et al.
patent: 6957225 (2005-10-01), Zait et al.
patent: 7020661 (2006-03-01), Cruanes et al.
patent: 7076508 (2006-07-01), Bourbonnais et al.
patent: 7130838 (2006-10-01), Barsness et al.
patent: 7139749 (2006-11-01), Bossman et al.
patent: 7149716 (2006-12-01), Gatto
patent: 7171399 (2007-01-01), Kapoor et al.
patent: 7177855 (2007-02-01), Witkowski et al.
patent: 7181450 (2007-02-01), Malloy et al.
patent: 7395537 (2008-07-01), Brown et al.
patent: 2002/0035559 (2002-03-01), Crowe et al.
patent: 2002/0049687 (2002-04-01), Helsper et al.
patent: 2002/0103793 (2002-08-01), Koller et al.
patent: 2003/0084030 (2003-05-01), Day et al.
patent: 2004/0122845 (2004-06-01), Lohman et al.
patent: 2004/0249810 (2004-12-01), Das et al.
patent: 2005/0038784 (2005-02-01), Zait et al.
patent: 2005/0160102 (2005-07-01), Abdo et al.
patent: 2005/0192937 (2005-09-01), Barsness et al.
patent: 2005/0210010 (2005-09-01), Larson et al.
patent: 2006/0080285 (2006-04-01), Chowdhuri
patent: 2006/0101001 (2006-05-01), Lindsay et al.
patent: 2006/0155679 (2006-07-01), Kothuri et al.
patent: 2006/0173852 (2006-08-01), Bestgen et al.
patent: 2006/0271504 (2006-11-01), Anderson et al.
patent: 2007/0016432 (2007-01-01), Piggott et al.
patent: 2007/0016558 (2007-01-01), Bestgen et al.
patent: 2007/0027860 (2007-02-01), Bestgen et al.
patent: 2007/0061487 (2007-03-01), Moore et al.
patent: 2007/0124276 (2007-05-01), Weissman et al.
patent: 2007/0226176 (2007-09-01), Bestgen et al.
patent: 2008/0033914 (2008-02-01), Cherniack et al.
“Using the Design Advisor to migrate from a single-partition to a multiple-partition database,” http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/t0011.
Patent U.S. Appl. No. 11/047,533 entitled “Apparatus and Method for Highlighting Discrepancies Between Query Performance Estimates and Actual Query Performance,” filed Jan. 31, 2005 by Robert Bestgen et al.
D. Chatziantoniou et al., “Groupwise Processing of Relational Queries”, Proceedings of the 23rd VLDB Conference (1997).
A. Shatdal et al., “Adaptive Parallel Aggregation Algorithms”, Proceedings of the 1995 ACM-SIGMOD Conference (May 1995).
R. Niemiec, “Oracle9i Introduces List Partitioning”, (Oracle Magazine Jul./Aug. 2002).
“Getting to Know Oracle 8i”, Chapter 2: Oracle8i New Features (Oracle Corp. 1999).
“Event 10128: debug partition elimination”, published at www.oracleadvice.com/Tips/partprune.htm (publication date unknown).
Stocker, et al “Integrating Semi-Join-reducers into State-of-the-Art Query Processors”, IEEE Computer Society, 2001.

Affiliated with

Also associated with

Rating

Performance data for query optimization of database partitions does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Performance data for query optimization of database partitions, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Performance data for query optimization of database partitions will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-4163379