And lsuppkey maxsuppkey l3 on l3lorderkey t2lorderkey

This preview shows page 140 - 143 out of 272 pages.

AND (l_suppkey <> max_suppkey))) l3 ON l3.l_orderkey = t2.l_orderkey) b WHERE (count_suppkey is null) OR ((count_suppkey=1) AND (l_suppkey = max_suppkey))) c ORDER BY numwait DESC, s_name SELECT s_name, count(1) AS numwait FROM (SELECT s_name FROM (SELECT s_name, t2.l_orderkey, l_suppkey, count_suppkey, max_suppkey FROM (SELECT l_orderkey, count(distinct l_suppkey) as count_suppkey, max(l_suppkey) as max_suppkey FROM lineitem WHERE l_receiptdate > l_commitdate GROUP BY l_orderkey) t2 RIGHT OUTER JOIN (SELECT s_name, l_orderkey, l_suppkey FROM (SELECT s_name, t1.l_orderkey, l_suppkey, count_suppkey, max_suppkey FROM (SELECT l_orderkey, count(distinct l_suppkey) as count_suppkey, max(l_suppkey) as max_suppkey FROM lineitem GROUP BY l_orderkey) t1 Original Query Re-written query Power of standard SQL Some of the most popular query workloads were defined by the Transaction Processing Council (TPC). These workloads have been used for years for performance benchmarking of RDBMSs. Big SQL can run all 22 TPC-H queries and all 99 TPC-DS queries without modification. Most other SQL-on- Hadoop implementations can’t do that. Certain queries had to be rewritten because support for certain standard SQL expressions is missing. This example is of one query that needed to be rewritten for a different SQL-on-Hadoop implementation. When looking at various vendors' performance numbers, it’s important to understand the effort required to achieve those numbers, including the effort to rewrite standard SQL queries so that they could be run against the target platform.
Unit 3 File formats and querying Big SQL tables © Copyright IBM Corp. 2018 3-27 Course materials may not be reproduced in whole or in part without the prior written permission of IBM. © Copyright IBM Corporation 2018 File formats and querying Big SQL tables SQL capability highlights (1 of 2) Query operations Projections, restrictions UNION, INTERSECT, EXCEPT Wide range of built-in functions (e.g. OLAP) Full support for subqueries In SELECT, FROM, WHERE and HAVING clauses Correlated and uncorrelated Equality, non-equality subqueries EXISTS, NOT EXISTS, IN, ANY, SOME, etc. SQL capability highlights The basic relational operators associated with SQL are all supported by Big SQL, as shown here on this and the following slide. Many more sophisticated query operations are supported, too, including various types of joins, correlated and uncorrelated subqueries, PL/SQL, OLAP functions, and more.
Unit 3 File formats and querying Big SQL tables © Copyright IBM Corp. 2018 3-28 Course materials may not be reproduced in whole or in part without the prior written permission of IBM. © Copyright IBM Corporation 2018 File formats and querying Big SQL tables SQL capability highlights (2 of 2) All standard join operations Standard and ANSI join syntax Inner, outer, and full outer joins Equality, non-equality, cross join support Multi-value join Stored procedures, UDFs Db2 compatible PL/SQL support Cursors, flow of control (if/then/else, error handling, …), etc.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture