2. Create an index:

Show the execution time of the query before create index

select current_timestamp from dual;

select cid from orders;

select current_timestamp from dual;

Create an index on orders (cid)

Show the execution time of the query after create index

select current_timestamp from dual;

select cid from orders;

select current_timestamp from dual;

3. In the following query:

Select * from orders where o_date < date '2004-1-1' and price = 1221

Index 1: on orders(o_date)

Index 2: on orders(price)

Index 3: on orders(o_date, price)

Index 4: on orders(price, o_date)

Which index is the most useful one:

Show the execution time of the query before create index

select current_timestamp from dual;

select * from orders where o_date < date '2004-1-1' and price = 1221;

select current_timestamp from dual;

Create the index:

Show the execution time of the query after create index:

select current_timestamp from dual;

select * from orders where o_date < date '2004-1-1' and price = 1221;

select current_timestamp from dual;

4: please specify what indexes you want to create to speed up the following SQL queries:

Select p_name from product P, orders O where P.p_id = o.P_id;

Select sum(price) from orders group by p_id;

Select * from customer where cname like '%n';

Select * from orders where o_date <= date 2005-1-31 and o_date >= date 2005-3-1 and price > 10;

--drop table loan_event; --drop table order_line; --drop table orders; --drop table product; --drop table customer; drop table loan_detail; drop table loan_event; drop table customer; create table customer( cid integer, cname varchar(50), primary key (cid)); create table product( pid integer, pname varchar(50), primary key (pid)); create table orders( oid integer, cid integer, pid integer, o_date date, price integer, primary key (oid), foreign key (cid) references customer(cid), foreign key (pid) references product(pid) ); create table order_line( oid integer, pid integer, quantity integer, primary key (oid, pid), foreign key (oid) references orders(oid), foreign key (pid) references product(pid)); insert into customer values (1, 'John'); insert into customer values (2, 'Alice'); insert into customer values (3, 'Lucien'); insert into customer values (4, 'Randolph'); insert into customer values (5, 'Stewart'); insert into customer values (6, 'Vern'); insert into customer values (7, 'Wilmer'); insert into customer values (8, 'Zack'); insert into customer values (9, 'Adrian'); insert into customer values (10, 'Alvah'); insert into customer values (11, 'Bertram'); insert into customer values (12, 'Clay'); insert into customer values (13, 'Ephraim'); insert into customer values (14, 'Fritz'); insert into customer values (15, 'Giles'); insert into product values (1, 'TV'); insert into product values (2, 'VCR'); insert into product values (3, 'DVD Player'); insert into product values (4, 'PC'); insert into product values (5, 'LAPTOP'); insert into product values (6, 'CAMERA'); insert into orders values(1,1,1,date '2010-12-26',2993); insert into orders values(2,1,2,date '2014-2-13',1748); insert into orders values(3,1,3,date '2010-12-26',2993); insert into orders values(4,1,4,date '2014-2-13',1748); insert into orders values(5,1,5,date '2007-8-17',1347); insert into orders values(6,1,6,date '2009-10-24',384); insert into orders values(7,1,5,date '2014-9-4',487); insert into orders values(8,2,1,date '2008-6-26',1693); insert into orders values(9,2,2,date '2009-5-24',1201); insert into orders values(10,2,3,date '2004-6-8',114); insert into orders values(11,2,4,date '2005-2-19',3315); insert into orders values(12,2,5,date '2002-5-19',1824); insert into orders values(13,3,1,date '2003-2-21',1580); insert into orders values(14,3,2,date '2001-11-16',2101); insert into orders values(15,3,3,date '2013-4-5',1710); insert into orders values(16,3,4,date '2003-3-24',2169); insert into orders values(17,3,5,date '2002-3-11',1545); insert into orders values(18,4,1,date '2002-12-24',3424); insert into orders values(19,4,2,date '2006-8-7',3075); insert into orders values(20,4,3,date '2010-2-6',3519); insert into orders values(21,4,4,date '2010-6-2',820); insert into orders values(22,4,5,date '2010-6-15',721); insert into orders values(23,5,1,date '2007-6-13',163); insert into orders values(24,5,2,date '2006-1-13',1813); insert into orders values(25,5,3,date '2008-3-17',3655); insert into orders values(26,5,4,date '2005-3-26',1688); insert into orders values(27,5,5,date '2005-6-25',971); insert into orders values(28,6,1,date '2002-3-3',2311); insert into orders values(29,6,2,date '2003-8-24',1354); insert into orders values(30,6,3,date '2007-5-2',3455); insert into orders values(31,6,4,date '2005-8-15',3355); insert into orders values(32,6,5,date '2016-10-7',2422); insert into orders values(33,7,1,date '2011-6-8',951); insert into orders values(34,7,2,date '2001-4-5',3329); insert into orders values(35,7,3,date '2003-11-4',551); insert into orders values(36,7,4,date '2001-10-15',3266); insert into orders values(37,7,5,date '2006-3-6',1446); insert into orders values(38,8,1,date '2002-9-1',1790); insert into orders values(39,8,2,date '2009-9-5',3351); insert into orders values(40,8,3,date '2008-3-16',2866); insert into orders values(41,8,4,date '2009-8-13',2727); insert into orders values(42,8,5,date '2013-11-25',1312); insert into orders values(43,9,1,date '2006-4-11',2125); insert into orders values(44,9,2,date '2004-1-3',173); insert into orders values(45,9,3,date '2016-7-22',1164); insert into orders values(46,9,4,date '2001-10-1',1949); insert into orders values(47,9,5,date '2014-4-7',1996); insert into orders values(48,10,1,date '2016-7-26',1587); insert into orders values(49,10,2,date '2001-11-11',3430); insert into orders values(50,10,3,date '2003-9-6',1926); insert into orders values(51,10,4,date '2006-1-16',1480); insert into orders values(52,10,5,date '2004-1-12',2505); insert into orders values(53,11,1,date '2002-8-7',2294); insert into orders values(54,11,2,date '2001-7-2',2711); insert into orders values(55,11,3,date '2014-6-2',66); insert into orders values(56,11,4,date '2006-4-1',3424); insert into orders values(57,11,5,date '2013-12-17',1451); insert into orders values(58,12,1,date '2014-4-4',2603); insert into orders values(59,12,2,date '2009-10-13',2544); insert into orders values(60,12,3,date '2012-2-8',2830); insert into orders values(61,12,4,date '2012-2-5',2698); insert into orders values(62,12,5,date '2005-7-15',1003); insert into orders values(63,13,1,date '2010-7-12',3060); insert into orders values(64,13,2,date '2014-11-8',1844); insert into orders values(65,13,3,date '2007-12-26',3310); insert into orders values(66,13,4,date '2010-11-16',1822); insert into orders values(67,13,5,date '2015-4-10',3133); insert into orders values(68,14,1,date '2012-1-19',3290); insert into orders values(69,14,2,date '2002-8-3',1221); insert into orders values(70,14,3,date '2008-10-1',1246); insert into orders values(71,14,4,date '2003-3-3',3559); insert into orders values(72,14,5,date '2013-9-7',1269); insert into orders values(73,15,1,date '2016-10-17',2206); insert into orders values(74,15,2,date '2015-7-11',1446); insert into orders values(75,15,3,date '2016-6-21',246); insert into orders values(76,15,4,date '2013-12-5',2403); insert into orders values(77,15,5,date '2010-11-26',214); insert into order_line values (1, 1,1); insert into order_line values (1, 2, 1); insert into order_line values (2, 1, 1); create table sales(id integer, product varchar(20), month integer, revenue number(20,2) , primary key (id)); insert into sales values(1, 'car', 1, 1000); insert into sales values(2, 'car', 2, 1200); insert into sales values(3, 'car', 3, 1400); insert into sales values(4, 'suv', 1, 1500); insert into sales values(5, 'suv', 2, 1200); insert into sales values(6, 'suv', 3, 1000);