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;

Restricted Mode is intended for safe code browsing. Trust this window to enable all features. Manage Learn More script_statistics_2023_def (1).sql createBD_2023.sql Users > nadiasharp > Downloads > script_statistics_2023_def (1).sql DO COMPET 69 70 71 72 WITH authors as (select title, writer, writer musician from songs 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 5 105 106 107 108 109 110 111 112 113 114 QUERY 1 FOR fila in ( == | | UNION select title, writer, cowriter musician from songs), authorship as (select distinct band performer, title, writer, 1 flag FROM involvement join authors using (musician)), recordings as recs_match as insert2023.sql (select performer, tracks.title, writer from albums join tracks using (pair)), (select performer, round(sum (flag)*100/count('c'),2) pct_recs from recordings left join authorship using (performer, title, writer) group by performer), Restricted Mode pers_match as (select performer, round(sum (flag)*100/count('c'), 2) pct_pers from (select performer, songtitle title, songwriter writer group by performer) SELECT performer, pct_recs, pct_pers from recs_match full join pers_match using (performer) ) LOOP null; END LOOP; from performances) P left join authorship using (performer, title, writer) createBD_2023_modified.sql QUERY 2 FOR fila in ( WITH recordings as (select performer, tracks.title, writer, min (rec_date) rec, 1 token from albums join tracks using (pair) group by performer, tracks.title, writer), playbacks as (select P.performer, sum (token)*100/count('x') percentage, avg(nv12 (rec, when-rec, rec)) as age FROM performances P left join recordings R on (P. performer=R.performer AND R.title=P.songtitle AND R.writer=P.songwriter AND P.when>R. rec) GROUP BY P. performer ORDER BY percentage desc) SELECT performer, percentage, floor(age/365.2422) years, floor (mod (age, 365.2422)/30.43685) months, floor (mod (age, 365.2422)-(floor (mod (age, 365.2422)/30.43685)*30.43685)) days FROM playbacks WHERE rownum