value of serializability
DESCRIPTION
Value of Serializability. Settings: accounts ( number , branchnum, balance); create clustered index c on accounts(number); 100000 rows Cold buffer Isolation level (SERIALIZABLE or READ COMMITTED) SQL Server 7, DB2 v7.1 and Oracle 8i on Windows 2000 - PowerPoint PPT PresentationTRANSCRIPT
Value of Serializability
Settings:accounts( number, branchnum, balance);
create clustered index c on accounts(number);
– 100000 rows
– Cold buffer
– Isolation level (SERIALIZABLE or READ COMMITTED)
– SQL Server 7, DB2 v7.1 and Oracle 8i on Windows 2000
– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.
Value of Serializability
Concurrent Transactions:– T1: summation query [1 thread]
select sum(balance) from accounts;
– T2: swap balance between two account numbers (in order of scan to avoid deadlocks) [N threads]
valX:=select balance from accounts where number=X;valY:=select balance from accounts where number=Y;update accounts set balance=valX where number=Y?;update accounts set balance=valY where number=X?;
Locking Overhead
Settings:accounts( number, branchnum, balance);
create clustered index c on accounts(number);
– 100000 rows
– Cold buffer
– SQL Server 7, DB2 v7.1 and Oracle 8i on Windows 2000
– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.
Locking Overhead
No Concurrent Transactions:– Update [10 000 updates]update accounts set balance = Val;
– Insert [10 000 transactions]insert into accounts values(664366,72255,2296.12);
Counter Facility
Settings:
– default isolation level: READ COMMITTED; Empty tables
– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.
accounts( number, branchnum, balance);create clustered index c on accounts(number);
counter ( nextkey );insert into counter values (1);
Counter Facility
No Concurrent Transactions:– System [100 000 inserts, N threads]
• SQL Server 7 (uses Identity column)insert into accounts values (94496,2789);
• Oracle 8iinsert into accounts values (seq.nextval,94496,2789);
– Ad-hoc [100 000 inserts, N threads]begin transaction NextKey:=select nextkey from counter; update counter set nextkey = NextKey+1;commit transactionbegin transaction insert into accounts values(NextKey,?,?);commit transaction
Log IO
Settings:lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER , L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT );
– READ COMMITTED isolation level
– Empty table
– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.
Log IO
No Concurrent Transactions:
Insertions [300 000 inserts, 10 threads], e.g., but with values changed:
insert into lineitem values (1,7760,401,1,17,28351.92,0.04,0.02,'N','O','1996-03-13','1996-02-12','1996-03-22','DELIVER IN PERSON','TRUCK','blithely regular ideas caj');
Buffer Size
Settings:employees(ssnum, name, lat, long, hundreds1,
hundreds2);
create clustered index c on employees(lat);– 10 distinct values of lat and long, 100 distinct values of hundreds1
and hundreds2
– 20000000 rows (630 Mb);
– Warm Buffer
– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000 RPM), Windows 2000.
Buffer Size
Queries:– Scan Queryselect sum(long) from employees;
– Multipoint queryselect * from employees where lat = ?;
Scan Performance
Settings:lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER , L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT );
– 600 000 rows – Cold Buffer– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.
Scan Performance
Queries:select avg(l_discount) from lineitem;
RAID Levels
Settings:accounts( number, branchnum, balance);create clustered index c on accounts(number);
– 100000 rows
– Cold Buffer
– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.
RAID Levels
No Concurrent Transactions:– Read Intensive: select avg(balance) from accounts;
– Write Intensive:insert into accounts values (690466,6840,2272.76);
Controller Cache
Settings:employees(ssnum, name, lat, long, hundreds1,hundreds2);
create clustered index c on employees(hundreds2);
– Employees table partitioned over two disks; Log on a separate disk.
– 200 000 rows per table– Database buffer size limited to 400 Mb.– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.
Controller Cache
No Concurrent Transactions:update employees set lat = long, long = lat where hundreds2 = ?;
– cache friendly: update of 20,000 rows (~90Mb)
– cache unfriendly: update of 200,000 rows (~900Mb)
Index Tuning
Settings:employees(ssnum, name, lat, long, hundreds1,
hundreds2);
clustered index c on employees(hundreds2) with fillfactor = 100;
nonclustered index nc on employees (hundreds2);
index nc3 on employees (ssnum, name, hundreds2);
index nc4 on employees (hundreds2, ssnum, name);– 1000000 rows ; Cold buffer
– Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.
Index Tuning
Queries:– Update:
update employees set name = ‘XXX’ where ssnum = ?;– Insert:
insert into employees values (1003505,'polo94064',97.48,84.03,4700.55,3987.2);
– Multipoint query: select * from employees where hundreds = ?;
– Covered query: select ssnum, name, lat from employees;
– Range Query: select * from employees where long between ? and ?;
– Point Query: select * from employees where ssnum = ?
Denormalizing
Settings:lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT );
region( R_REGIONKEY, R_NAME, R_COMMENT );
nation( N_NATIONKEY, N_NAME, N_REGIONKEY, N_COMMENT,);
supplier( S_SUPPKEY, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL, S_COMMENT);
– 600000 rows in lineitem, 25 nations, 5 regions, 500 suppliers
Denormalizing
lineitemdenormalized ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT, L_REGIONNAMEL_REGIONNAME);
– 600000 rows in lineitemdenormalized
– Cold Buffer
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives (10000RPM), Windows 2000.
Queries on Normalized vs. Denormalized Schemas
Queries:select L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER, L_QUANTITY,
L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT, R_NAME
from LINEITEM, REGION, SUPPLIER, NATIONwhereL_SUPPKEY = S_SUPPKEYand S_NATIONKEY = N_NATIONKEYand N_REGIONKEY = R_REGIONKEYand R_NAME = 'EUROPE';
select L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT, L_REGIONNAME
from LINEITEMDENORMALIZEDwhere L_REGIONNAME = 'EUROPE';
Queries
Settings: employee(ssnum, name, dept, salary, numfriends);student(ssnum, name, course, grade);techdept(dept, manager, location);
clustered index i1 on employee (ssnum);nonclustered index i2 on employee (name);nonclustered index i3 on employee (dept);
clustered index i4 on student (ssnum);nonclustered index i5 on student (name);
clustered index i6 on techdept (dept);
– 100000 rows in employee, 100000 students, 10 departments; Cold buffer– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives
(10000RPM), Windows 2000.
Queries - Views
View Techlocation:create view techlocation as select ssnum, techdept.dept, location from employee, techdept where employee.dept = techdept.dept;
Queries:– Original:
select dept from techlocation where ssnum = ?;
– Rewritten:Select dept from employee where ssnum = ?;
Queries – Correlated SubqueriesQueries:
– Original:select ssnum from employee e1 where salary = (select max(salary) from employee e2 where e2.dept = e1.dept);
– Rewritten:select max(salary) as bigsalary, deptinto TEMP from employee group by dept;
select ssnumfrom employee, TEMPwhere salary = bigsalaryand employee.dept = temp.dept;
Aggregate Maintenance
Settings:orders( ordernum, itemnum, quantity, purchaser, vendor );create clustered index i_order on orders(itemnum);
store( vendor, name );
item(itemnum, price);create clustered index i_item on item(itemnum);
vendorOutstanding( vendor, amount);
storeOutstanding( store, amount);
– 1000000 orders, 10000 stores, 1000 items; Cold buffer– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives
(10000RPM), Windows 2000.
Aggregate Maintenance
Triggers for Aggregate Maintenancecreate trigger updateVendorOutstanding on orders for insert asupdate vendorOutstandingset amount =
(select vendorOutstanding.amount+sum(inserted.quantity*item.price)from inserted,itemwhere inserted.itemnum = item.itemnum)
where vendor = (select vendor from inserted) ;
create trigger updateStoreOutstanding on orders for insert asupdate storeOutstandingset amount =
(select storeOutstanding.amount+sum(inserted.quantity*item.price) from inserted,item where inserted.itemnum = item.itemnum)
where store = (select store.name from inserted, store where inserted.vendor = store.vendor) ;
Aggregate Maintenance
Concurrent Transactions:– Insertions
insert into orders values (1000350,7825,562,'xxxxxx6944','vendor4');
– Queriesselect orders.vendor, sum(orders.quantity*item.price)from orders,itemwhere orders.itemnum = item.itemnumgroup by orders.vendor;
select * from vendorOutstanding;
select store.name, sum(orders.quantity*item.price)from orders,item, storewhere orders.itemnum = item.itemnum
and orders.vendor = store.vendorgroup by store.name;
select * from storeOutstanding;
Superlinearity
Settings:sales( id, itemid, customerid, storeid, amount, quantity);
item (itemid);
customer (customerid);
store (storeid);
successfulsales(id, itemid, customerid, storeid, amount, quantity);
unsuccessfulsales(id, itemid, customerid, storeid, amount, quantity);
tempsales( id, itemid, customerid, storeid, amount,quantity);
Superlinearity
Settings (non-clustering, dense indexes):index s1 on sales(itemid);index s2 on sales(customerid);index s3 on sales(storeid);
index succ on successfulsales(id);index succ1 on successfulsales(itemid);index succ2 on successfulsales(customerid);index succ3 on successfulsales(storeid);
– 1000000 sales, 400000 customers, 40000 items, 1000 stores– Cold buffer– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives
(10000RPM), Windows 2000.
Superlinearity
Queries:– Insert/delete
insert into successfulsalesselect sales.id, sales.itemid, sales.customerid,
sales.storeid, sales.amount, sales.quantityfrom sales, item, customer, storewhere sales.itemid = item.itemidand sales.customerid = customer.customeridand sales.storeid = store.storeid;
insert into unsuccessfulsales select * from sales;godelete from unsuccessfulsaleswhere id in (select id from successfulsales)
Superlinearity
Queries:– Small batches
DECLARE @Nlow INT;DECLARE @Nhigh INT;DECLARE @INCR INT;set @INCR = 100000set @NLow = 0set @Nhigh = @INCRWHILE (@NLow <= 500000)BEGIN
insert into tempsales select * from sales where id between @NLow and @Nhighset @Nlow = @Nlow + @INCRset @Nhigh = @Nhigh + @INCRdelete from tempsales
where id in (select id from successfulsales);insert into unsuccessfulsales select * from tempsales;delete from tempsales;
END
Superlinearity
Queries:– outerjoin
insert into successfulsalesselect sales.id, item.itemid, customer.customerid, store.storeid, sales.amount,
sales.quantityfrom ((sales left outer join item on sales.itemid = item.itemid)left outer join customer on sales.customerid = customer.customerid)left outer join store on sales.storeid = store.storeid;
insert into unsuccessfulsalesselect *from successfulsaleswhere itemid is nullor customerid is nullor storeid is null;godelete from successfulsaleswhere itemid is nullor customerid is nullor storeid is null
Looping can hurt
Settings:lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT );
– 600 000 rows; warm buffer.
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives (10000RPM), Windows 2000.
Looping can hurt• Queries:
– No loop:sqlStmt = “select * from lineitem where l_partkey <= 200;”odbc->prepareStmt(sqlStmt);odbc->execPrepared(sqlStmt);
– Loop:sqlStmt = “select * from lineitem where l_partkey = ?;”odbc->prepareStmt(sqlStmt);for (int i=1; i<100; i++){
odbc->bindParameter(1, SQL_INTEGER, i);odbc->execPrepared(sqlStmt);
}
Cursors are Death
Settings:employees(ssnum, name, lat, long, hundreds1,
hundreds2);
– 100000 rows ; Cold buffer
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives (10000RPM), Windows 2000.
Cursors are Death
Queries:– No cursor
select * from employees;
– Cursor
DECLARE d_cursor CURSOR FOR select * from employees;
OPEN d_cursorwhile (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT from d_cursorEND
CLOSE d_cursor
go
Retrieve Needed Columns Only
Settings:lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT );
create index i_nc_lineitem on lineitem (l_orderkey, l_partkey, l_suppkey, l_shipdate, l_commitdate);
– 600 000 rows; warm buffer.
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives (10000RPM), Windows 2000.
Retrieve Needed Columns Only
Queries:– All
Select * from lineitem;
– Covered subsetSelect l_orderkey, l_partkey, l_suppkey, l_shipdate, l_commitdate from lineitem;
Bulk Loading Data
Settings:lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT );
– Initially the table is empty; 600 000 rows to be inserted (138Mb)
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives (10000RPM), Windows 2000.
Bulk Loading Data
Oracle 8isqlldr directpath=true control=load_lineitem.ctl data=E:\Data\lineitem.tbl
load data infile "lineitem.tbl"into table LINEITEM appendfields terminated by '|' (
L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE DATE "YYYY-MM-DD", L_COMMITDATE DATE "YYYY-MM-DD", L_RECEIPTDATE DATE "YYYY-MM-DD", L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT
)
Bulk Loading Data
SQL Server 2000bulk insert lineitem
from "E:\Data\lineitem.tbl"
with
(
TABLOCK,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
Bulk Loading Data
DB2 v7.1LOAD FROM E:\data\lineitem.tbl OF DEL
MODIFIED BY coldel| chardel""
dateformat=""YYYY-MM-DD""
savecount 10000
MESSAGES db2load.msg
INSERT INTO DBTUNING.LINEITEM
STATISTICS NO
INDEXING MODE AUTOSELECT
Ecommerce
Settings:shoppingcart( shopperid, itemid, price, qty);
– 500000 rows; warm buffer
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives (10000RPM), Windows 2000.
Ecommerce
Concurrent Transactions:– Mix
insert into shoppingcart values (107999,914,870,214);update shoppingcart set Qty = 10 where shopperid = 95047 and itemid = 88636;
delete from shoppingcart where shopperid = 86123 and itemid = 8321;
select shopperid, itemid, qty, price from shoppingcart where shopperid = ?;
– Queries Onlyselect shopperid, itemid, qty, price from shoppingcart where shopperid = ?;
Bitmaps
Settings:lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY, L_LINENUMBER,
L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT );
create bitmap index b_lin_2 on lineitem(l_returnflag);
create bitmap index b_lin_3 on lineitem(l_linestatus);
create bitmap index b_lin_4 on lineitem(l_linenumber);
– 100000 rows ; cold buffer
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives (10000RPM), Windows 2000.
Bitmaps
Queries:– 1 attribute
select count(*) from lineitem where l_returnflag = 'N';
– 2 attributesselect count(*) from lineitem where l_returnflag = 'N' and l_linenumber > 3;
– 3 attributesselect count(*) from lineitem where l_returnflag =
'N' and l_linenumber > 3 and l_linestatus = 'F';
Multidimensional Indexes
Settings:create table spatial_facts( a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, a10 int, geom_a3_a7 mdsys.sdo_geometry );create index r_spatialfacts on spatial_facts(geom_a3_a7) indextype is mdsys.spatial_index;create bitmap index b2_spatialfacts on
spatial_facts(a3,a7);
– 500000 rows ; cold buffer– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, 3x18Gb drives
(10000RPM), Windows 2000.
Multidimensional Indexes
Queries:– Point Queries
select count(*) from fact where a3 = 694014 and a7 = 928878;
select count(*) from spatial_facts where SDO_RELATE(geom_a3_a7, MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(694014,928878, NULL), NULL, NULL), 'mask=equal querytype=WINDOW') = 'TRUE';
– Range Queriesselect count(*) from spatial_facts where SDO_RELATE(geom_a3_a7,
mdsys.sdo_geometry(2003,NULL,NULL, mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(10,800000,1000000,1000000)), 'mask=inside querytype=WINDOW') = 'TRUE';
select count(*) from spatial_facts where a3 > 10 and a3 < 1000000 and a7 > 800000 and a7 < 1000000;
Approximations
Settings:– TPC-H schema– Approximations
insert into approxlineitem
select top 6000 *from lineitemwhere l_linenumber = 4;
insert into approxordersselect O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE, O_ORDERDATE, O_ORDERPRIORITY, O_CLERK, O_SHIPPRIORITY, O_COMMENT
from orders, approxlineitemwhere o_orderkey = l_orderkey;
Approximationsinsert into approxsupplierselect distinct S_SUPPKEY,
S_NAME ,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT
from approxlineitem, supplierwhere s_suppkey = l_suppkey;
insert into approxpartselect distinct P_PARTKEY,
P_NAME , P_MFGR , P_BRAND , P_TYPE , P_SIZE ,P_CONTAINER , P_RETAILPRICE , P_COMMENT
from approxlineitem, partwhere p_partkey = l_partkey;
insert into approxpartsuppselect distinct PS_PARTKEY,
PS_SUPPKEY,PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT
from partsupp, approxpart, approxsupplierwhere ps_partkey = p_partkey and
ps_suppkey = s_suppkey;
insert into approxcustomerselect distinct C_CUSTKEY,
C_NAME ,C_ADDRESS,C_NATIONKEY,C_PHONE ,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT
from customer, approxorderswhere o_custkey = c_custkey;insert into approxregion select * from
region;insert into approxnation select * from
nation;
Approximations
Queries:– Query on lineitemSelect l_returnflag, l_linestatus, sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order
From lineitem
Where datediff(day, l_shipdate, '1998-12-01') <= '120'
group by l_returnflag, l_linestatus
order by l_returnflag, l_linestatus;
Approximations
Queries:– 6-way joinSelect n_name, avg(l_extendedprice * (1 - l_discount)) as
revenueFrom customer, orders, lineitem, supplier, nation, regionWhere c_custkey = o_custkey
and l_orderkey = o_orderkeyand l_suppkey = s_suppkeyand c_nationkey = s_nationkeyand s_nationkey = n_nationkeyand n_regionkey = r_regionkeyand r_name = 'AFRICA'and o_orderdate >= '1993-01-01'and datediff(year, o_orderdate,'1993-01-01') < 1
group by n_nameorder by revenue desc;