explain plan for select /*+ INDEX(ORDERS id_ordkey_orders */
	n_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue
from
	customer,
	orders,
	lineitem,
	supplier,
	nation,
	region
where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and l_suppkey = s_suppkey
	and c_nationkey = s_nationkey
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'AFRICA'
	and o_orderdate >= date '1994-01-01'
	and o_orderdate < date '1994-01-01' + interval '1' year
group by
	n_name
order by
	revenue desc;


explain plan for select /*+ INDEX( LINEITEM id_partkey_lineitem_ */
	o_year,
	sum(case
		when nation = 'JORDAN' then volume
		else 0
	end) / sum(volume) as mkt_share
from
	(
		select /*+ INDEX( LINEITEM id_partkey_lineitem_ */
			extract(year from o_orderdate) as o_year,
			l_extendedprice * (1 - l_discount) as volume,
			n2.n_name as nation
		from
			part,
			supplier,
			lineitem,
			orders,
			customer,
			nation n1,
			nation n2,
			region
		where
			p_partkey = l_partkey
			and s_suppkey = l_suppkey
			and l_orderkey = o_orderkey
			and o_custkey = c_custkey
			and c_nationkey = n1.n_nationkey
			and n1.n_regionkey = r_regionkey
			and r_name = 'MIDDLE EAST'
			and s_nationkey = n2.n_nationkey
			and o_orderdate between date '1995-01-01' and date '1996-12-31'
			and p_type = 'MEDIUM BRUSHED TIN'
	) all_nations
group by
	o_year
order by
	o_year;


EXPLAIN PLAN FOR select /*+ INDEX (CUSTOMER id_custkey_cust) */
	c_count,
	count(*) as custdist
from
	(
		select /*+ INDEX (CUSTOMER id_custkey_cust) */
			c_custkey,
			count(o_orderkey) as c_count
		from
			customer left outer join orders on
				c_custkey = o_custkey
				and o_comment not like '%pending%packages%'
		group by
			c_custkey
	)  c_orders 
group by
	c_count
order by
	custdist desc,
	c_count desc;

explain plan for select   /*+ INDEX(LINEITEM id_ordkey_lineitem) */ 
	s_name,
	numwait
from
(
select /*+ INDEX(LINEITEM id_ordkey_lineitem) */
	s_name,
	count(*) as numwait
from
	supplier,
	lineitem l1,
	orders,
	nation
where
	s_suppkey = l1.l_suppkey
	and o_orderkey = l1.l_orderkey
	and o_orderstatus = 'F'
	and l1.l_receiptdate > l1.l_commitdate
	and exists (
		select /*+ INDEX(LINEITEM id_ordkey_lineitem) */
			*
		from
			lineitem l2
		where
			l2.l_orderkey = l1.l_orderkey
			and l2.l_suppkey <> l1.l_suppkey
	)
	and not exists (
		select /*+ INDEX(LINEITEM id_ordkey_lineitem) */
			*
		from
			lineitem l3
		where
			l3.l_orderkey = l1.l_orderkey
			and l3.l_suppkey <> l1.l_suppkey
			and l3.l_receiptdate > l3.l_commitdate
	)
	and s_nationkey = n_nationkey
	and n_name = 'EGYPT'
group by
	s_name
order by
	numwait desc,
	s_name
)
where rownum <= 100 ;