3

I'm doing an exercise, where we have to improve several queries:

  • (30%) Give me FH and FC per month, filtered by the aircraft model (e.g., "777").
  • (30%) Give me ADOSS, ADOSU per year, filtered by the aircraft from the fleet (e.g., "XY-WTR").
  • (20%) Give me the RRh, RRc, PRRh, PRRc, MRRh and MRRc per month, filtered by the aircraft model (e.g., "777").
  • (20%) Give me the MRRh and MRRc per aircraft model, filtered by the airport of the reporting person (e.g., "KRS").

The percentage is the frequency of execution of each query.

The script for generating the tables and the sample data is the following:

CREATE TABLE Months (
ID CHAR(7),
y NUMBER(4) NOT NULL
) PCTFREE 0 ENABLE ROW MOVEMENT;

CREATE TABLE TemporalDimension (
ID DATE,
monthID CHAR(7) NOT NULL
) PCTFREE 0 ENABLE ROW MOVEMENT;

CREATE TABLE AircraftDimension (
ID CHAR(6),
model VARCHAR2(100) NOT NULL,
manufacturer VARCHAR2(100) NOT NULL
) PCTFREE 0 ENABLE ROW MOVEMENT;

CREATE TABLE AircraftUtilization (
aircraftID CHAR(6),
timeID DATE,
scheduledOutOfService NUMBER(2),
unScheduledOutOfService NUMBER(2),
flightHours NUMBER(2),
flightCycles NUMBER(2),
delays NUMBER(2),
delayedMinutes NUMBER(3),
cancellations NUMBER(2)
) PCTFREE 0 ENABLE ROW MOVEMENT;

CREATE TABLE PeopleDimension (
ID CHAR(6),
airport CHAR(3),
role CHAR(1) CHECK (role IN ('P','M')) NOT NULL
) PCTFREE 0 ENABLE ROW MOVEMENT;

CREATE TABLE LogBookReporting (
aircraftID CHAR(6),
monthID CHAR(7),
personID CHAR(6),
counter NUMBER(2) NOT NULL
) PCTFREE 0 ENABLE ROW MOVEMENT;


-- generate data

DECLARE
  i INTEGER;
  j INTEGER;
  k INTEGER;
  nextDate DATE;
  nextMonth varchar2(6);
  manufacturer varchar2(6);
  model varchar2(25);
  maxPeople CONSTANT INTEGER := 100;
  maxTemporal CONSTANT INTEGER := 5000;
  l_start_date DATE := DATE'2004-01-01';

  type aircraftarray is varray(20) of varchar2(6);
  aircrafts CONSTANT aircraftarray := aircraftarray('XY-WTR', 'XY-VWK', 'XY-OKG','XY-HNS','XY-QLY','XY-XTT','XY-QXN','XY-JJQ','XY-ZSE','XY-XVI','XY-HXO','XY-IOL','XY-WBH','XY-EYQ','XY-OSF','XY-KKF','XY-ZHR','XY-HCI','XY-XPV','XY-VYU');

  type modelBoeingarray is varray(4) of varchar2(25);
  modelsBoeing CONSTANT modelBoeingarray := modelBoeingarray('767','777','747','737');

  type modelAirbusarray is varray(6) of varchar2(25);
  modelsAirbus CONSTANT modelAirbusarray := modelAirbusarray('A340','A330','A321','A330neo','A320neo family','A350 XWB');

  type manufacturerarray is varray(2) of varchar2(6);
  manufacturers CONSTANT manufacturerarray := manufacturerarray('Boeing','Airbus');

  type airportarray is varray(100) of varchar2(3);
  airports CONSTANT airportarray := airportarray('AGP','SKP','TSE','ESB','VKO','IEV','TIV','FRA','DUB','PDL','GYD','KIV','TRD','CLJ','TLL','OLB','BHX','KRS','DME','DRS','VNO','TFS','NUE','GDN','OVD','BGY','ORY','FNC','MSQ','LPL','TLS','TPS','RVN','PUY','AJA','LWO','CIA','GVA','LTN','HUY','BIQ','DBV','REU','BOO','ODS','RHO','CIY','BJV','BSL','GLA','EIN','LUG','BRQ','BRN','CTA','AAR','TSF','KUN','ZTH','WAW','BIO','LYS','PMO','CFU','OSL','BES','AER','ZAZ','FUE','LNZ','FMO','LIL','SVG','PMI','GOA','INI','LJU','IST','SVX','KBP','XRY','TZX','SEN','OVB','FCO','OST','VLC','SCQ','PFO','HAJ','ALC','PSR','ORK','AAL','EXT','BIA','MAN','BRS','SXF','FAO');

  type rolearray is varray(2) of varchar2(1);
  roles CONSTANT rolearray := rolearray('P','M');

BEGIN
DBMS_RANDOM.seed(0);

-- Insertions in AircraftDimentsion
FOR i IN 1..(aircrafts.count) LOOP
  manufacturer := manufacturers(dbms_random.value(1,2));
  IF (manufacturer = 'Airbus') THEN
  INSERT INTO AIRCRAFTDIMENSION(ID, MODEL, MANUFACTURER) VALUES (aircrafts(i), modelsAirbus(dbms_random.value(1,6)), manufacturer);
  ELSIF (manufacturer = 'Boeing') THEN
  INSERT INTO AIRCRAFTDIMENSION(ID, MODEL, MANUFACTURER) VALUES (aircrafts(i), modelsBoeing(dbms_random.value(1,4)), manufacturer);
  END IF;
 END LOOP;

 -- Insertions in PeopleDimension
FOR i IN 1..(maxPeople) LOOP
  INSERT INTO PEOPLEDIMENSION(ID, AIRPORT, "ROLE") VALUES (i, airports(dbms_random.value(1,100)), roles(dbms_random.value(1,2)));
  END LOOP;


 -- Insertions in TemporalDimensions and Months
 nextMonth :=  CONCAT(LPAD(EXTRACT(MONTH FROM l_start_date),2,'0'), LPAD(EXTRACT(YEAR FROM l_start_date),4,'0'));
 INSERT INTO MONTHS(ID,Y) VALUES (nextMonth, SUBSTR(nextMonth,LENGTH(nextMonth)-3,4));
 FOR i IN 1..maxTemporal LOOP
  nextDate := l_start_date + i;
  IF CONCAT(LPAD(EXTRACT(MONTH FROM nextDate),2,'0'), LPAD(EXTRACT(YEAR FROM nextDate),4,'0')) <> nextMonth THEN
      nextMonth :=  CONCAT(LPAD(EXTRACT(MONTH FROM nextDate),2,'0'), LPAD(EXTRACT(YEAR FROM nextDate),4,'0'));
      INSERT INTO MONTHS(ID,Y) VALUES (nextMonth, SUBSTR(nextMonth,LENGTH(nextMonth)-3,4));
  END IF;
  INSERT INTO TEMPORALDIMENSION(ID, MONTHID) VALUES (nextDate, nextMonth);
 END LOOP;

-- Insertions in LogBookReporting
 nextMonth :=  CONCAT(LPAD(EXTRACT(MONTH FROM l_start_date),2,'0'), LPAD(EXTRACT(YEAR FROM l_start_date),4,'0'));
 FOR i IN 1..aircrafts.count LOOP
   FOR k IN 1..maxPeople LOOP
     FOR j IN 1..maxTemporal LOOP
         nextDate := l_start_date + j;
         IF CONCAT(LPAD(EXTRACT(MONTH FROM nextDate),2,'0'), LPAD(EXTRACT(YEAR FROM nextDate),4,'0')) <> nextMonth THEN
            nextMonth :=  CONCAT(LPAD(EXTRACT(MONTH FROM nextDate),2,'0'), LPAD(EXTRACT(YEAR FROM nextDate),4,'0'));
         ELSE CONTINUE;
         END IF;
          INSERT INTO LOGBOOKREPORTING(AIRCRAFTID, MONTHID, PERSONID, COUNTER) VALUES (
            aircrafts(i),
            nextMonth,
            k,
            CAST(dbms_random.value(1,50) AS INT) );
         END LOOP;
     END LOOP;
 END LOOP;

-- Insertions in AircraftUtilization
FOR i IN 1..aircrafts.count LOOP
     FOR j IN 1..maxTemporal LOOP
     nextDate := l_start_date + j;
      INSERT INTO AIRCRAFTUTILIZATION(AIRCRAFTID, TIMEID, FLIGHTHOURS, FLIGHTCYCLES, DELAYS, DELAYEDMINUTES, CANCELLATIONS, SCHEDULEDOUTOFSERVICE, UNSCHEDULEDOUTOFSERVICE) VALUES (
        aircrafts(i),
        nextDate,
        CAST(dbms_random.value(1,24) AS INT),
        CAST(dbms_random.value(1,5) AS INT),
        CAST(dbms_random.value(1,5) AS INT),
        CAST(dbms_random.value(1,3) AS INT),
        CAST(dbms_random.value(0,2) AS INT),
        CAST(dbms_random.value(1,5) AS INT),
        CAST(dbms_random.value(1,5) AS INT));
     END LOOP;
END LOOP;

UPDATE PEOPLEDIMENSION SET airport=null WHERE ROLE='P';

END;

COMMIT;


ALTER TABLE AIRCRAFTDIMENSION SHRINK SPACE;
ALTER TABLE PEOPLEDIMENSION SHRINK SPACE;
ALTER TABLE TEMPORALDIMENSION SHRINK SPACE;
ALTER TABLE MONTHS SHRINK SPACE;
ALTER TABLE AIRCRAFTUTILIZATION SHRINK SPACE;
ALTER TABLE LOGBOOKREPORTING SHRINK SPACE;

-- update statistics

DECLARE
esquema VARCHAR2(100);
CURSOR c IS SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME NOT LIKE 'SHADOW_%';
BEGIN
SELECT '"'||sys_context('USERENV', 'CURRENT_SCHEMA')||'"' INTO esquema FROM dual;
FOR taula IN c LOOP
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => esquema,
    tabname => taula.table_name,
    estimate_percent => NULL,
    method_opt =>'FOR ALL COLUMNS SIZE REPEAT',
    granularity => 'GLOBAL',
    cascade => TRUE
    );
  END LOOP;
END;

We also have some views:

CREATE VIEW LogBookReporting_A_M_P AS
SELECT l.AircraftID AS aircraft, l.monthID AS MONTH, l.personID as person
    , SUM(CASE WHEN p.role='P' THEN counter ELSE 0 END) AS PIREP
    , SUM(CASE WHEN p.role='M' THEN counter ELSE 0 END) AS MAREP
FROM LogBookReporting l, peopleDimension p
WHERE l.personID=p.ID
GROUP BY l.aircraftID, l.monthID, l.personID;

CREATE VIEW AircraftUtilization_A_M AS
SELECT a.AircraftID AS aircraft, t.monthID AS month
    , SUM(a.flightHours) AS FH
    , SUM(a.flightCycles) AS FC
    , COUNT(DISTINCT t.ID)-SUM(a.scheduledOutOfService)-SUM(a.unscheduledOutOfService) AS ADIS
    , SUM(a.scheduledOutOfService) AS ADOSS
    , SUM(a.unscheduledOutOfService) AS ADOSU
    , SUM(delays) AS DY
    , SUM(delayedMinutes) AS delayedMinutes
    , SUM(cancellations) AS CN
FROM AircraftUtilization a, TemporalDimension t
WHERE a.timeID=t.ID
GROUP BY a.AircraftID, t.monthID;

And my answer is:

CREATE INDEX aircraftModelIndex
ON AIRCRAFTDIMENSION(MODEL) PCTFREE 33;

CREATE BITMAP INDEX aircraftIdIndex
ON AIRCRAFTUTILIZATION(AIRCRAFTID) PCTFREE 0;

CREATE INDEX airportIndex
on PEOPLEDIMENSION(airport) PCTFREE 33;

Note that the PCTFREE are required. 0 on bitmap indexes and 33 in regular ones.

Also "Bitmaps should have more than 100 repetitions per value", and the total size of the blocks should be less than 1900 disk blocks overall (including data and indexes).

However, this answer is not fast enough for the tests.

My thoughts were that we have to create indexes for the columns inside the where clauses.

For the first and third query, aircraft model has only 8 different models in aircraftdimension, with at most 4 observations for each, so I have to use a regular index.

For the second query the aircraft from the fdleet is aircraftid from aircraftutilization, which has 20 different values with 5000 records for each.

Finally, for the fourth query, the airport has 42 different values with 51 nulls and at most 3.

I want some guidance on how to improve these indexes.

2
  • 2
    Well, in real life, if you have 5,000 * 20 = 100,000 rows in a table, no need to index anything. It seems like whoever wrote that exam/homework question might have written it 20 years ago, based on PCTFREE "requirements" and some of the other "requirements." The bit about 1,900 disk blocks is something nobody even thinks about. Now, yes, some of the theoretical ideas would be useful in real life, but usually you don't need to get to this level of detail in real life. Dec 30, 2022 at 19:15
  • 1
    @MarkStewart - I've generally found that lecturers in College are as lazy and willing to copy and paste as their students - and they don't have to worry about plagiarising. This question was probably inherited from some lecturer who actually did some work many moons ago!
    – Vérace
    Dec 31, 2022 at 10:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.