optimize the queries below by indexing and clustering to lower the amounts of consistent gets. The tables
Question:
optimize the queries below by indexing and clustering to lower the amounts of consistent gets. The tables related to the queries are below. I want to be able to create clusters of the tables trucks albums and performances with the close the key as the titles of the songs. I wasn't sure if the classic you had to be a primary key however I tried to add the cluster title_(title); onto those tables after creating the cluster title_ and it's index. However this threw an error, because I don't think the implementation is correct as these tables have foreign keys which are affecting the cluster creation. An attempt was also made to create new tables which pulled from the desired tracks songs and performances tables the relevant columns to be used in the cluster (second picture). There was an error with this implementation that I cannot figure out as well. Could you please guide me towards proper cluster creation that combines these tables and makes the queries more efficient?
CREATE TABLE ALBUMS(
PAIR CHAR(15),
performer VARCHAR2(50) NOT NULL,
format CHAR(1) NOT NULL, -- (T)streaming (C)CD (M)Audio File (V)Vynil (S)Single
title VARCHAR2(50) NOT NULL,
rel_date DATE NOT NULL,
publisher VARCHAR2(25) NOT NULL,
manager NUMBER(9) NOT NULL,
CONSTRAINT PK_ALBUMS PRIMARY KEY(PAIR),
CONSTRAINT UK_ALBUMS UNIQUE (performer,format,title,rel_date),
CONSTRAINT FK_ALBUMS1 FOREIGN KEY(performer) REFERENCES PERFORMERS,
CONSTRAINT FK_ALBUMS2 FOREIGN KEY(manager) REFERENCES MANAGERS,
CONSTRAINT FK_ALBUMS3 FOREIGN KEY(publisher) REFERENCES PUBLISHERS,
CONSTRAINT CK_format CHECK (format in ('T','C','M','V','S'))
);
CREATE TABLE SONGS (
title VARCHAR2(50),
writer CHAR(14),
cowriter CHAR(14),
CONSTRAINT PK_SONGS PRIMARY KEY(title, writer),
CONSTRAINT FK_SONGS1 FOREIGN KEY(writer) REFERENCES MUSICIANS,
CONSTRAINT FK_SONGS2 FOREIGN KEY(cowriter) REFERENCES MUSICIANS ON DELETE SET NULL,
CONSTRAINT CK_SONGS CHECK (writer!=cowriter)
)CLUSTER TITLE_(title);
CREATE TABLE TRACKS (
PAIR CHAR(15),
sequ NUMBER(3) NOT NULL,
title VARCHAR2(50) NOT NULL,
writer CHAR(14) NOT NULL,
duration NUMBER(4) NOT NULL, -- in seconds
rec_date DATE NOT NULL,
studio VARCHAR2(50),
engineer VARCHAR2(50) NOT NULL,
CONSTRAINT PK_TRACKS PRIMARY KEY(PAIR, sequ),
CONSTRAINT FK_TRACKS1 FOREIGN KEY (PAIR) REFERENCES ALBUMS ON DELETE CASCADE,
CONSTRAINT FK_TRACKS2 FOREIGN KEY (title, writer) REFERENCES SONGS,
CONSTRAINT FK_TRACKS3 FOREIGN KEY (studio) REFERENCES STUDIOS ON DELETE SET NULL,
CONSTRAINT CK_duracion CHECK (duration<=5400)
)CLUSTER TITLE_(title);
-- -----------------------------------------
-- concerts part
-- -----------------------------------------
CREATE TABLE TOURS (
performer VARCHAR2(50),
name VARCHAR2(100),
manager NUMBER(9) NOT NULL,
CONSTRAINT PK_TOURS PRIMARY KEY (performer,name),
CONSTRAINT UK_TOURS UNIQUE (performer,name,manager),
CONSTRAINT FK_TOURS FOREIGN KEY(performer) REFERENCES PERFORMERS
);
CREATE TABLE CONCERTS (
performer VARCHAR2(50),
when DATE,
tour VARCHAR2(100),
municipality VARCHAR2(100) NOT NULL,
address VARCHAR2(100),
country VARCHAR2(100),
attendance NUMBER(7) DEFAULT (0) NOT NULL,
duration NUMBER(4),
manager NUMBER(9) NOT NULL,
CONSTRAINT PK_CONCERTS PRIMARY KEY (performer,when),
CONSTRAINT FK_CONCERTS1 FOREIGN KEY(performer) REFERENCES PERFORMERS,
CONSTRAINT FK_CONCERTS2 FOREIGN KEY(manager) REFERENCES MANAGERS,
CONSTRAINT FK_CONCERTS3 FOREIGN KEY(performer, tour, manager) REFERENCES TOURS(performer,name,manager)
);
CREATE TABLE PERFORMANCES (
performer VARCHAR2(50),
when DATE,
sequ NUMBER(3),
songtitle VARCHAR2(100) NOT NULL,
songwriter CHAR(14) NOT NULL,
duration NUMBER(4),
CONSTRAINT PK_PERFORMANCES PRIMARY KEY (performer,when,sequ),
CONSTRAINT FK_PERFORMANCES1 FOREIGN KEY (performer,when) REFERENCES CONCERTS ON DELETE CASCADE,
CONSTRAINT FK_PERFORMANCES2 FOREIGN KEY (songtitle,songwriter) REFERENCES SONGS
)CLUSTER TITLE_(songtitle);
-- -----------------------------------------
-- clients part
-- -----------------------------------------
CREATE TABLE CLIENTS (
e_mail VARCHAR2(100),
name VARCHAR2(80),
surn1 VARCHAR2(80),
surn2 VARCHAR2(80),
birthdate DATE,
phone NUMBER(9),
address VARCHAR2(100),
DNI VARCHAR2(8),
CONSTRAINT PK_CLIENTS PRIMARY KEY (e_mail),
CONSTRAINT UK_CLIENTS UNIQUE (DNI)
);
CREATE TABLE ATTENDANCES (
client VARCHAR2(100),
performer VARCHAR2(100),
when DATE,
RFID VARCHAR2(120) NOT NULL,
purchase DATE,
CONSTRAINT PK_ATTENDANCES PRIMARY KEY (client,performer,when),
CONSTRAINT UK_ATTENDANCES UNIQUE (performer,when,RFID),
CONSTRAINT FK_ATTENDANCES1 FOREIGN KEY (performer,when) REFERENCES CONCERTS ON DELETE CASCADE,
CONSTRAINT FK_ATTENDANCES2 FOREIGN KEY (client) REFERENCES CLIENTS
);
CREATE INDEX involvement_band ON INVOLVEMENT(band) TABLESPACE TAB_8k;
CREATE INDEX involvement_musician ON INVOLVEMENT(musician) TABLESPACE TAB_8k;
CREATE INDEX album_perf ON ALBUMS(performer) TABLESPACE TAB_8k;
CREATE INDEX album_publisher ON ALBUMS(publisher) TABLESPACE TAB_8k;
CREATE INDEX album_manager ON ALBUMS(manager) TABLESPACE TAB_8k;
CREATE INDEX songs_writer ON SONGS(writer) TABLESPACE TAB_8k;
CREATE INDEX songs_cowriter ON SONGS(cowriter) TABLESPACE TAB_8k;
CREATE INDEX tracks_pair ON TRACKS(PAIR) TABLESPACE TAB_8k;
CREATE INDEX tracks_titlewriter ON TRACKS(title, writer) TABLESPACE TAB_8k;
Expert Answer:
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
