User-defined function

Abstract: Current search engines can hardly cope adequately with fuzzy predicates defined by complex preferences. The biggest problem of search engines implemented with standard SQL is that SQL does not directly understand the notion of preferences. Preference SQL extends SQL by a preference model based on strict partial orders (presented in more detail in the companion paper [Kie02]), where preference queries behave like soft selection constraints. Several built-in base preference types and the powerful Pareto operator, combined with the adherence to declarative SQL programming style, guarantees great programming productivity. The Preference SQL optimizer does an efficient re-writing into standard SQL, including a high-level implementation of the skyline perator for Pareto-optimal sets. This pre-processor approach enables a seamless application integration, making Preference SQL available on all major SQL platforms. Several commercial B2C portals are powered by Preference SQL. Its benefits comprise cooperative query answering and smart customer advice, leading to higher e-customer satisfaction and shorter development times of personalized search engines. We report practical experiences ranging from m-commerce and comparison shopping to a large-scale performance test for a job portal.

Abstract: SQL tuning is a very critical aspect of database performance tuning. It is an inherently complex activity requiring a high level of expertise in several domains: query optimization, to improve the execution plan selected by the query optimizer; access design, to identify missing access structures; and SQL design, to restructure and simplify the text of a badly written SQL statement. Furthermore, SQL tuning is a time consuming task due to the large volume and evolving nature of the SQL workload and its underlying data. In this paper we present the new Automatic SQL Tuning feature of Oracle 10g. This technology is implemented as a core enhancement of the Oracle query optimizer and offers a comprehensive solution to the SQL tuning challenges mentioned above. Automatic SQL Tuning introduces the concept of SQL profiling to transparently improve execution plans. It also generates SQL tuning recommendations by performing cost-based access path and SQL structure "what-if" analyses. This feature is exposed to the user through both graphical and command line interfaces. The Automatic SQL Tuning is an integral part of the Oracle's framework for self-managing databases. The superiority of this new technology is demonstrated by comparing the results of Automatic SQL Tuning to manual tuning using a real customer workload.

Abstract: Most object oriented applications that involve persistent data interact with a relational database. The most common interaction mechanism is a call level interface (CLI) such as ODBC or JDBC. While there are many advantages to using a CLI -- expressive power and performance being two of the most key -- there are also drawbacks. Applications communicate through a CLI by constructing strings that contain SQL statements. These SQL statements are only checked for correctness at runtime, tend to be fragile and are vulnerable to SQL injection attacks. To solve these and other problems, we present the SQL DOM: a set of classes that are strongly-typed to a database schema. Instead of string manipulation, these classes are used to generate SQL statements. We show how to extract the SQL DOM automatically from an existing database schema, demonstrate its applicability to solve the mentioned problems, and evaluate its performance.

Abstract: A method for programming a computer to execute a procedure is based on a graphical interface which utilizes data flow diagrams to represent the procedure. The method stores a plurality of executable functions, scheduling functions, and data types. A data flow diagram is assembled in response to the user input utilizing icons which correspond to the respective executable functions, scheduling functions, and data types which are interconnected by arcs on the screen. A panel, representative of an instrument fron panel having input and output formats is likewise assembled for the data flow diagram. An executable program is generated in response to the data flow diagram and the panel utilizing the executable functions, scheduling functions, and data types stored in the memory. Furthermore, the executable functions may include user defined functions that have been generated using the method for programming. In this manner, a hierarchy of procedures is implemented, each represented by a data flow diagram.