Edit

Oracle DB SQL Quiz!

Postach.io OracleDB published

I’ve been taking lessons on oracle database and PL/SQL, trying to get up to speed with the huge number of features. In one of my classes we’ve been going over indexes and have open discussion forums with quizes. Here’s a rundown on one such quiz

Question 1:

Suppose you have created the following tables:

Create table Customer (c_id integer,
c_name varchar(40),
c_phone char(20),
c_address varchar(200),
primary key (c_id));
Create table Product(
p_id integer,
p_name varchar(40),
description varchar(200),
primary key (p_id));
Create table Orders(
o_id integer,
c_id integer,
p_id integer,
o_date date,
price real,
primary key (o_id),
foreign key (c_id) references customer(c_id),
foreign key (p_id) references product(p_id));

What indexes have been created automatically by Oracle once you created these tables?

ans 1:

Although we know Oracle indexes primary keys let’s check with the all_ind_columns table. In the oracle database this table contains ALL the indexes!

select table_name, column_name
from all_ind_columns
where table_name in ('ORDERS', 'PRODUCT', 'CUSTOMER');

Which gives us the names of tables and the column name of their index(s).

TABLE_NAME COLUMN_NAME
PRODUCT P_ID
CUSTOMER C_ID
ORDERS O_ID

It looks like we were correct and the primary keys were indexed!

Question 2:

Given the tables created in question 1, please specify which of the following indexes is the most useful for answering the following query:

select * from orders where o_date < date2004-1-1and price = 100
  • 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)

ans 2:

Let’s use some logic…
Now that we know indexes are most commonly B+- trees we can apply logic to figure out the most appropriate index. To make the biggest difference in query performance we want to narrow our search down to the smallest set of data. Because there are probably lots of orders on the same date but relatively few with the same total price we should index price first. It is questionable whether we should index the date field at all but having our index secondarily ordered by date should allow the database to further narrow down the search space quickly.

To recap, we want to narrow down the search space quickly so pick

Index 4: on orders(price, o_date)

Question 3:

Given tables created in Question 1, please specify what indexes you want to create to speed up the following SQL queries:

1) select * from customer where c_name like ‘John%’;

2)

select * from orders
where o_date <= date2005-1-31
and o_date >= date2005-1-1
and price > 10;

3) select p_name from product P, orders O where P.p_id = o.P_id;

4) select sum(price) from orders group by p_id;

ans 3:

  1. index on customer(c_name)
  2. orders(price, o_date)
  3. product(p_id), orders(p_id)
  4. orders(p_id)
%23%20Oracle%20DB%20SQL%20Quiz%21%20%0A@%28Postach.io%29%5BOracleDB%2C%20published%5D%0A%0AI%27ve%20been%20taking%20lessons%20on%20oracle%20database%20and%20%60PL/SQL%60%2C%20trying%20to%20get%20up%20to%20speed%20with%20the%20huge%20number%20of%20features.%20In%20one%20of%20my%20classes%20we%27ve%20been%20going%20over%20indexes%20and%20have%20open%20discussion%20forums%20with%20quizes.%20Here%27s%20a%20rundown%20on%20one%20such%20quiz%0A%0A%23%23%20Question%201%3A%20%0ASuppose%20you%20have%20created%20the%20following%20tables%3A%0A%60%60%60sql%0ACreate%20table%20Customer%20%28c_id%20integer%2C%20%0A%20%20%20%20c_name%20varchar%2840%29%2C%20%20%0A%20%20%20%20c_phone%20char%2820%29%2C%20%0A%20%20%20%20c_address%20varchar%28200%29%2C%20%0A%20%20%20%20primary%20key%20%28c_id%29%29%3B%0A%0ACreate%20table%20Product%28%0A%20%20%20%20p_id%20integer%2C%20%0A%20%20%20%20p_name%20varchar%2840%29%2C%20%0A%20%20%20%20description%20varchar%28200%29%2C%20%0A%20%20%20%20primary%20key%20%28p_id%29%29%3B%0A%20%20%20%20%0ACreate%20table%20Orders%28%0A%20%20%20%20o_id%20integer%2C%20%0A%20%20%20%20c_id%20integer%2C%20%0A%20%20%20%20p_id%20integer%2C%20%0A%20%20%20%20o_date%20date%2C%20%0A%20%20%20%20price%20real%2C%20%0A%20%20%20%20primary%20key%20%28o_id%29%2C%20%0A%20%20%20%20foreign%20key%20%28c_id%29%20references%20customer%28c_id%29%2C%20%0A%20%20%20%20foreign%20key%20%28p_id%29%20references%20product%28p_id%29%29%3B%0A%60%60%60%0A**What%20indexes%20have%20been%20created%20automatically%20by%20Oracle%20once%20you%20created%20these%20tables%3F**%0A%0A%23%23%23%20ans%201%3A%20%0AAlthough%20we%20know%20Oracle%20indexes%20primary%20keys%20let%27s%20check%20with%20the%20%60all_ind_columns%60%20table.%20In%20the%20oracle%20database%20this%20table%20contains%20ALL%20the%20indexes%21%20%0A%60%60%60sql%0Aselect%20table_name%2C%20column_name%0Afrom%20all_ind_columns%0Awhere%20table_name%20in%20%28%27ORDERS%27%2C%20%27PRODUCT%27%2C%20%27CUSTOMER%27%29%3B%0A%60%60%60%0AWhich%20gives%20us%20the%20names%20of%20tables%20and%20the%20column%20name%20of%20their%20index%28s%29.%20%0A%7C%20TABLE_NAME%20%7C%09COLUMN_NAME%20%7C%0A%7C%20-%20%7C%20-%20%7C%0A%7C%20PRODUCT%20%7C%09P_ID%20%7C%0A%7C%20CUSTOMER%20%7C%09C_ID%20%7C%0A%7C%20ORDERS%20%7C%09O_ID%20%7C%0AIt%20looks%20like%20we%20were%20correct%20and%20the%20primary%20keys%20were%20indexed%21%0A%0A%23%23%20Question%202%3A%20%0A**Given%20the%20tables%20created%20in%20question%201%2C%20please%20specify%20which%20of%20the%20following%20indexes%20is%20the%20most%20useful%20for%20answering%20the%20following%20query%3A**%0A%0A%60%60%60sql%0Aselect%20*%20from%20orders%20where%20o_date%20%3C%20date%20%u20182004-1-1%u2019%20and%20price%20%3D%20100%0A%60%60%60%0A%0A-%20Index%201%3A%20on%20%60orders%28o_date%29%20%60%0A-%20Index%202%3A%20on%20%60orders%28price%29%20%60%0A-%20Index%203%3A%20on%20%60orders%28o_date%2C%20price%29%20%60%0A-%20Index%204%3A%20on%20%60orders%28price%2C%20o_date%29%60%0A%0A%23%23%23%20ans%202%3A%20%0ALet%27s%20use%20some%20logic...%20%20%0ANow%20that%20we%20know%20indexes%20are%20most%20commonly%20B+-%20trees%20we%20can%20apply%20logic%20to%20figure%20out%20the%20most%20appropriate%20index.%20To%20make%20the%20biggest%20difference%20in%20query%20performance%20we%20want%20to%20narrow%20our%20search%20down%20to%20the%20smallest%20set%20of%20data.%20Because%20there%20are%20probably%20lots%20of%20orders%20on%20the%20same%20date%20but%20relatively%20few%20with%20the%20same%20total%20price%20we%20should%20index%20price%20first.%20It%20is%20questionable%20whether%20we%20should%20index%20the%20date%20field%20at%20all%20but%20having%20our%20index%20secondarily%20ordered%20by%20date%20should%20allow%20the%20database%20to%20further%20narrow%20down%20the%20search%20space%20quickly.%0A%0ATo%20recap%2C%20we%20want%20to%20narrow%20down%20the%20search%20space%20quickly%20so%20pick%0A%0AIndex%204%3A%20on%20%60orders%28price%2C%20o_date%29%60%0A%0A%0A%23%23%20Question%203%3A%20%0A**Given%20tables%20created%20in%20Question%201%2C%20please%20specify%20what%20indexes%20you%20want%20to%20create%20to%20speed%20up%20the%20following%20SQL%20queries%3A**%0A%0A1%29%20%60select%20*%20from%20customer%20where%20c_name%20like%20%u2018John%25%u2019%3B%60%0A%0A2%29%20%0A%60%60%60sql%0Aselect%20*%20from%20orders%20%0Awhere%20o_date%20%3C%3D%20date%20%u20182005-1-31%u2019%20%0Aand%20o_date%20%3E%3D%20date%20%u20182005-1-1%u2019%20%0Aand%20price%20%3E%2010%3B%0A%60%60%60%0A%0A3%29%20%60select%20p_name%20from%20product%20P%2C%20orders%20O%20where%20P.p_id%20%3D%20o.P_id%3B%60%0A%0A4%29%20%60select%20sum%28price%29%20from%20orders%20group%20by%20p_id%3B%60%0A%0A%23%23%23%20ans%203%3A%20%0A%0A1.%20index%20on%20%60customer%28c_name%29%60%0A2.%20%60orders%28price%2C%20o_date%29%60%0A3.%20%60product%28p_id%29%60%2C%20%60orders%28p_id%29%60%0A4.%20%60orders%28p_id%29%60%0A%0A%0A%0A