· 7 years ago · Mar 26, 2019, 01:38 PM
1/*
2-- This should be executed as SYS:
3-- DON'T USE A SSD FOR STORING THE TABLESPACE FILES !!!
4
5CREATE TABLESPACE tbs_perm_student_file
6 DATAFILE 'd:\tbs_perm_student_file.dat'
7 SIZE 50M
8 REUSE
9 AUTOEXTEND ON NEXT 50M MAXSIZE 2000M
10/
11
12CREATE TEMPORARY TABLESPACE tbs_temp_student_file
13 TEMPFILE 'd:\tbs_temp_student_file.dbf'
14 SIZE 50M
15 AUTOEXTEND ON
16/
17
18CREATE UNDO TABLESPACE tbs_undo_student_file
19 DATAFILE 'd:\tbs_undo_student_file.f'
20 SIZE 50M
21 AUTOEXTEND ON
22 RETENTION GUARANTEE
23/
24
25drop user std;
26
27create user std identified by std;
28alter user std default tablespace tbs_perm_student_file quota 1990M on tbs_perm_student_file;
29
30grant connect to std;
31grant all privileges to std;
32
33
34--UP to this point (AS SYS)
35*/
36--------------------------------------------------------------------------------
37
38
39
40
41
42
43
44
45DROP TABLE students CASCADE CONSTRAINTS
46/
47DROP TABLE courses CASCADE CONSTRAINTS
48/
49DROP TABLE grades CASCADE CONSTRAINTS
50/
51DROP TABLE instructors CASCADE CONSTRAINTS
52/
53DROP TABLE didactic CASCADE CONSTRAINTS
54/
55DROP TABLE friendships CASCADE CONSTRAINTS
56/
57DROP TABLE studenti CASCADE CONSTRAINTS
58/
59DROP TABLE cursuri CASCADE CONSTRAINTS
60/
61DROP TABLE note CASCADE CONSTRAINTS
62/
63DROP TABLE profesori CASCADE CONSTRAINTS
64/
65DROP TABLE didactic CASCADE CONSTRAINTS
66/
67DROP TABLE prieteni CASCADE CONSTRAINTS
68/
69DROP TABLE alumni CASCADE CONSTRAINTS
70/
71
72
73CREATE TABLE students (
74 id INT NOT NULL PRIMARY KEY,
75 registration_number VARCHAR2(6) NOT NULL,
76 lname VARCHAR2(15) NOT NULL,
77 fname VARCHAR2(30) NOT NULL,
78 year NUMBER(1),
79 groupno CHAR(2),
80 scholarship NUMBER(6,2),
81 dob DATE,
82 email VARCHAR2(40),
83 created_at DATE,
84 updated_at DATE
85)
86/
87
88
89CREATE TABLE courses (
90 id INT NOT NULL PRIMARY KEY,
91 course_title VARCHAR2(50) NOT NULL,
92 year NUMBER(1),
93 sem NUMBER(1),
94 credits NUMBER(2),
95 created_at DATE,
96 updated_at DATE
97)
98/
99
100
101CREATE TABLE grades (
102 id INT NOT NULL PRIMARY KEY,
103 id_student INT NOT NULL,
104 id_course INT NOT NULL,
105 value NUMBER(2),
106 grading_date DATE,
107 created_at DATE,
108 updated_at DATE,
109 CONSTRAINT fk_grades_id_student FOREIGN KEY (id_student) REFERENCES students(id),
110 CONSTRAINT fk_grades_id_course FOREIGN KEY (id_course) REFERENCES courses(id)
111)
112/
113
114CREATE TABLE instructors (
115 id INT NOT NULL PRIMARY KEY,
116 lname VARCHAR2(15) NOT NULL,
117 fname VARCHAR2(30) NOT NULL,
118 title VARCHAR2(20),
119 created_at DATE,
120 updated_at DATE
121)
122/
123
124CREATE TABLE didactic (
125 id INT NOT NULL PRIMARY KEY,
126 id_instructor INT NOT NULL,
127 id_course INT NOT NULL,
128 created_at DATE,
129 updated_at DATE,
130 CONSTRAINT fk_didactic_id_instructor FOREIGN KEY (id_instructor) REFERENCES instructors(id),
131 CONSTRAINT fk_didactic_id_course FOREIGN KEY (id_course) REFERENCES courses(id)
132)
133/
134
135
136CREATE TABLE friendships (
137 id INT PRIMARY KEY,
138 id_student1 INT NOT NULL,
139 id_student2 INT NOT NULL,
140 created_at DATE,
141 updated_at DATE,
142 CONSTRAINT fk_friendships_id_student1 FOREIGN KEY (id_student1) REFERENCES students(id),
143 CONSTRAINT fk_friendships_id_student2 FOREIGN KEY (id_student2) REFERENCES students(id),
144 CONSTRAINT no_duplicates UNIQUE (id_student1, id_student2)
145)
146/
147
148SET SERVEROUTPUT ON;
149DECLARE
150 TYPE varr IS VARRAY(1000) OF varchar2(255);
151 lista_lname varr := varr('Morrison','Bennett','Brady','Coleman','Ford','Rios','Poole','Walters','Guerrero','Flores','Lee','Miller','Francis','French','Martin','Sherman','Graham','Garner','Maxwell','Estrada','Morales','Owen','Lawson','Benson','Hammond','Greene','Lamb','Castro','Perkins','Hughes','Barnes','Mckenzie','Watts','Anderson','Gregory','Alvarez','Yates','Fowler','Wilkins','Warren','Burns','Boone','Goodwin','Porter','Wheeler','Brock','Howard','Barton','Zimmerman','Hodges','Massey','Norton','Gibson','Strickland','Bell','Robinson','Graves','Craig','Howell','Hunt','Malone','Richards','Murphy','Nash','West','Lloyd','Paul','Fuller','Holloway','Goodman','Ryan','Reeves','Cole','Parker','Cohen','Ingram','Scott','Byrd','Hart','Casey','Franklin','Morgan','Mclaughlin','Lyons','Montgomery','Stephens','Glover','Roberts','Erickson','Allison','Ramos','Holland','Hawkins','Williamson','Edwards','Mccoy','Swanson','Delgado','Ellis','Collins','Boyd','Myers','Nichols','Wood','Rice','Wolfe','Stokes','Ortiz','Haynes','Mccormick','Norman','Knight','Patton','Gomez','Chandler','Henry','Tucker','Kennedy','Day','Gray','Banks','Allen','Clark','Reed','Oliver','Price','Simon','Fox','Copeland','Harrington','Brooks','Ruiz','Taylor','Griffith','Jordan','Ballard','Clarke','Kelley','Waters','Russell','Luna','Becker','Nguyen','Norris','Munoz','Wilson','Todd','Olson','George','Rivera','Williams','White','Torres','Brewer','Mendoza','Alexander','Joseph','Mason','Webster','Higgins','Barnett','Harrison','Bailey','Underwood','Robertson','Watkins','Stone','Quinn','Hicks','Holt','Burgess','Hoffman','Adams','Stevens','Chavez','Wilkerson','Bryan','Sandoval','Greer','Soto','Walsh','Wagner','Vega','Schmidt','Figueroa','Thornton','Diaz','Hamilton','Peters','Sims','Duncan','Rhodes','Carter','Alvarado','Powell','Burton','Osborne','Blake','Palmer','Moore','Dawson','Henderson','Lowe','Peterson','Sanders','Shelton','Lopez','Mckinney','Ferguson','Pierce','Neal','Abbott','Keller','Silva','Stewart','Griffin','Lynch','Bush','Nelson','Townsend','Butler','Webb','Spencer','Mack','Frazier','Gutierrez','Moody','Carroll','Bowman','Little','Guzman','Martinez','Larson','Clayton','Perez','Colon','Daniel','Adkins','Turner','Smith','Tate','Mccarthy','Douglas','Riley','Mills','Briggs','Collier','Perry','Murray','Mullins','Vasquez','Wright','Pearson','Cooper','Lewis','Foster','Mann','Santiago','Santos','Cain','Rodgers','Lambert','Fitzgerald','Hudson','Fletcher','Jennings','Schultz','Bowen','Schwartz','Rose','Hopkins','Doyle','Carr','Saunders','Meyer','Cruz','Roy','Baker','Simpson','Valdez','Newton','Caldwell','Parks','Obrien','Johnson','Weaver','Steele','Thomas','Fisher','Walker','Johnston','Grant','Watson','Reid','Gill','Carson','Simmons','Barrett','Holmes','Wells','Mcdonald','Garza','Cook','Bridges','Cox','Leonard','Klein','Lawrence','Rowe','Quinnteles','Aguilar','Willis','Harmon','Long','Davis','Summers','Davidson','Baldwin','Harper','Patrick','Sanchez','Gonzalez','Lindsey','Miles','Wise','Roberson','Bass','Mcgee','Powers','Richardson','Nunez','Hogan','Gordon','Singleton','Harvey','Wade','Welch','Kelly','Houston','Sutton','Love','Bradley','Jimenez','Floyd','Ortega','Black','Ball','Crawford','Bowers','Hernandez','Tran','Brown','Armstrong','Gilbert','Cummings','Snyder','Hayes','Padilla','Dixon','Hampton','Mathis','Medina','Jenkins','Hill','Jacobs','King','Jefferson','Conner','Chapman','Terry','Christensen','Maldonado','Stanley','Gardner','Fields','Ward','Hunter','Ross','Cannon','Sharp','Manning','Newman','Mitchell','Morris','Morton','Hansen','Ramsey','Garcia','Moss','Vargas','Hale','Wallace','Dennis','Fernandez','Thompson','Huff','Park','Walton','Kim','Chambers');
152 lista_fname_fete varr := varr('Bonnie','Louise','Janet','Anna','Jane','Ruth','Ashley','Tina','Joyce','Stephanie','Laura','Virginia','Alice','Margaret','Lori','Sharon','Anne','Emily','Andrea','Elizabeth','Sarah','Rebecca','Ann','Brenda','Jessica','Paula','Jennifer','Diana','Cheryl','Lois','Teresa','Susan','Evelyn','Karen','Wanda','Gloria','Carol','Nicole','Phyllis','Martha','Carolyn','Denise','Heather','Theresa','Marie','Sara','Doris','Cynthia','Joan','Sandra','Kathryn','Julie','Mildred','Jacqueline','Donna','Rose','Dorothy','Debra','Rachel','Diane','Irene','Helen','Jean','Lillian','Patricia','Norma','Kelly','Janice','Frances','Annie','Christine','Michelle','Beverly','Catherine','Melissa','Judith','Lisa','Pamela','Tammy','Kathy','Deborah','Linda','Judy','Kathleen','Angela','Christina','Katherine','Marilyn','Shirley','Maria','Ruby','Mary','Kimberly','Barbara','Nancy','Betty','Amy','Julia','Amanda');
153 lista_fname_baieti varr := varr('Alonzo','Lorenzo','Tommy','Levi','Dustin','Angelo','Matthew','Johnny','Andres','Jeffrey','Samuel','Alberto','Leland','Wallace','Loren','Gustavo','Virgil','Dale','Jaime','Gerard','Carlos','Jason','Roy','Harvey','Willard','Rick','Stuart','Cody','Eduardo','Gerardo','Curtis','Aubrey','Sammy','Gene','Toby','Winston','Tony','Charlie','Wm','Joseph','Marty','Johnnie','Earl','Brad','Jonathan','Rex','Cornelius','Eddie','Cesar','Keith','Louis','Micheal','Nicholas','Dwight','Dave','Rodolfo','Warren','Raymond','Shannon','Emmett','George','Moses','Preston','Guillermo','Andrew','Ignacio','Leslie','Ian','Kirk','Amos','Bert','Ronnie','Timmy','Manuel','Tim','Gregory','Mario','Earnest','Luis','Lawrence','Eric','Miguel','Rudy','Albert','Wayne','Colin','Larry','Israel','Salvador','Jorge','Thomas','Alton','Pat','Malcolm','Randolph','Nicolas','Marshall','Francis','Tyrone','Lewis');
154 lista_materii_year_1 varr := varr('Logic in Informatics','Mathematics','Introduction to Programming','Computer Architecture and Operating Systems','Operating Systems','Object-Oriented Programming','Fundamental Algebraic of Information','Probabilities and Statistics');
155 lista_materii_year_2 varr := varr('Computer Networks','Data Basis','Formal Languages, Automata and Compilers','Graph Algorithms','Web Technologies','Advanced Programming','Software Engineering','DBMS Practice');
156 lista_materii_year_3 varr := varr('Automated Learning','Network Security','Artificial Intelligence','Python Programming','Numeric Calculus','Computer graphics','Data Mining','Petri networks and their applications');
157 lista_grade_diactice varr := varr('Colaborator','Assistant Lecturer','Lecturer','Associate Professor','Professor');
158
159 v_lname VARCHAR2(255);
160 v_fname VARCHAR2(255);
161 v_fname1 VARCHAR2(255);
162 v_fname2 VARCHAR2(255);
163 v_matr VARCHAR2(6);
164 v_matr_aux VARCHAR2(6);
165 v_temp int;
166 v_temp1 int;
167 v_temp2 int;
168 v_temp3 int;
169 v_temp_date date;
170 v_year int;
171 v_groupno varchar2(2);
172 v_scholarship int;
173 v_dob date;
174 v_email varchar2(40);
175BEGIN
176
177 DBMS_OUTPUT.PUT_LINE('Adding 1025 students...');
178 FOR v_i IN 1..1025 LOOP
179 v_lname := lista_lname(TRUNC(DBMS_RANDOM.VALUE(0,lista_lname.count))+1);
180 IF (DBMS_RANDOM.VALUE(0,100)<50) THEN
181 v_fname1 := lista_fname_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_fete.count))+1);
182 LOOP
183 v_fname2 := lista_fname_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_fete.count))+1);
184 exit when v_fname1<>v_fname2;
185 END LOOP;
186 ELSE
187 v_fname1 := lista_fname_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_baieti.count))+1);
188 LOOP
189 v_fname2 := lista_fname_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_baieti.count))+1);
190 exit when v_fname1<>v_fname2;
191 END LOOP;
192 END IF;
193
194 IF (DBMS_RANDOM.VALUE(0,100)<60) THEN
195 IF LENGTH(v_fname1 || ' ' || v_fname2) <= 20 THEN
196 v_fname := v_fname1 || ' ' || v_fname2;
197 END IF;
198 else
199 v_fname:=v_fname1;
200 END IF;
201
202 LOOP
203 v_matr := FLOOR(DBMS_RANDOM.VALUE(100,999)) || CHR(FLOOR(DBMS_RANDOM.VALUE(65,91))) || CHR(FLOOR(DBMS_RANDOM.VALUE(65,91))) || FLOOR(DBMS_RANDOM.VALUE(0,9));
204 select count(*) into v_temp from students where registration_number = v_matr;
205 exit when v_temp=0;
206 END LOOP;
207
208 LOOP
209 v_year := TRUNC(DBMS_RANDOM.VALUE(0,3))+1;
210 v_groupno := chr(TRUNC(DBMS_RANDOM.VALUE(0,2))+65) || chr(TRUNC(DBMS_RANDOM.VALUE(0,6))+49);
211 select count(*) into v_temp from students where year=v_year and groupno=v_groupno;
212 exit when v_temp < 30;
213 END LOOP;
214
215 v_scholarship := '';
216 IF (DBMS_RANDOM.VALUE(0,100)<10) THEN
217 v_scholarship := TRUNC(DBMS_RANDOM.VALUE(0,10))*100 + 500;
218 END IF;
219
220 v_dob := TO_DATE('01-01-1974','MM-DD-YYYY')+TRUNC(DBMS_RANDOM.VALUE(0,365));
221
222 v_temp:='';
223 v_email := lower(v_lname ||'.'|| v_fname1);
224 LOOP
225 select count(*) into v_temp from students where email = v_email||v_temp;
226 exit when v_temp=0;
227 v_temp := TRUNC(DBMS_RANDOM.VALUE(0,100));
228 END LOOP;
229
230 if (TRUNC(DBMS_RANDOM.VALUE(0,2))=0) then v_email := v_email ||'@gmail.com';
231 else v_email := v_email ||'@info.ro';
232 end if;
233
234 --DBMS_OUTPUT.PUT_LINE (v_i||' '||v_matr||' '||v_lname||' '||v_fname ||' '|| v_year ||' '|| v_groupno||' '|| v_scholarship||' '|| to_char(v_dob, 'DD-MM-YYYY')||' '|| v_email);
235 insert into students values(v_i, v_matr, v_lname, v_fname, v_year, v_groupno, v_scholarship, v_dob, v_email, sysdate, sysdate);
236 END LOOP;
237 DBMS_OUTPUT.PUT_LINE('done !');
238
239
240 select count(*) into v_temp from students;
241 FOR v_i IN 1..20000 LOOP
242 LOOP
243 v_temp1 := TRUNC(DBMS_RANDOM.VALUE(0,v_temp-1))+1;
244 v_temp2 := TRUNC(DBMS_RANDOM.VALUE(0,v_temp-1))+1;
245 EXIT WHEN v_temp1<>v_temp2;
246 END LOOP;
247 DECLARE
248 BEGIN
249 --DBMS_OUTPUT.PUT_LINE(v_temp1 || ' ' || v_temp2);
250 v_dob := (sysdate-TRUNC(DBMS_RANDOM.VALUE(0,1000)));
251 insert into friendships values(v_i, v_temp1, v_temp2, v_dob, v_dob);
252 exception
253 when OTHERS then null;
254 END;
255 END LOOP;
256
257
258 DBMS_OUTPUT.PUT_LINE('Adding Courses...');
259 FOR v_i IN 1..8 LOOP
260 IF (v_i<5) THEN v_temp := 1; ELSE v_temp := 2; END IF;
261 IF (v_i IN (2,3,6,7)) THEN v_temp1 := 5; END IF;
262 IF (v_i IN (1,5)) THEN v_temp1 := 4; END IF;
263 IF (v_i IN (4,8)) THEN v_temp1 := 6; END IF;
264 insert into courses values (v_i, lista_materii_year_1(v_i), 1, v_temp, v_temp1, sysdate-1200, sysdate-1200);
265 END LOOP;
266
267 FOR v_i IN 1..8 LOOP
268 IF (v_i<5) THEN v_temp := 1; ELSE v_temp := 2; END IF;
269 IF (v_i IN (2,3,6,7)) THEN v_temp1 := 5; END IF;
270 IF (v_i IN (1,5)) THEN v_temp1 := 4; END IF;
271 IF (v_i IN (4,8)) THEN v_temp1 := 6; END IF;
272 insert into courses values (v_i+8, lista_materii_year_2(v_i), 2, v_temp, v_temp1, sysdate-1200, sysdate-1200);
273 END LOOP;
274
275 FOR v_i IN 1..8 LOOP
276 IF (v_i<5) THEN v_temp := 1; ELSE v_temp := 2; END IF;
277 IF (v_i IN (2,3,6,7)) THEN v_temp1 := 5; END IF;
278 IF (v_i IN (1,5)) THEN v_temp1 := 4; END IF;
279 IF (v_i IN (4,8)) THEN v_temp1 := 6; END IF;
280 insert into courses values (v_i+16, lista_materii_year_3(v_i), 3, v_temp, v_temp1, sysdate-1200, sysdate-1200);
281 END LOOP;
282 DBMS_OUTPUT.PUT_LINE('Done !');
283
284
285 DBMS_OUTPUT.PUT_LINE('Adding grades...');
286
287 v_temp3 := 1;
288 FOR v_i IN 1..1025 LOOP
289 select year into v_temp from students where id = v_i;
290 if (v_temp=1) then
291 FOR v_temp1 IN 1..8 LOOP
292 if (v_temp1 IN (1,2,3,4)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(40+TRUNC(DBMS_RANDOM.VALUE(0,14)))-365;
293 ELSE v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(180+TRUNC(DBMS_RANDOM.VALUE(0,14)))-365;
294 END IF;
295 insert into grades values (v_temp3, v_i, v_temp1, TRUNC(DBMS_RANDOM.VALUE(0,7)) + 4, v_temp_date, v_temp_date, v_temp_date);
296 v_temp3 := v_temp3+1;
297 END LOOP;
298 end if;
299 if (v_temp=2) then
300 FOR v_temp1 IN 1..16 LOOP
301 if (v_temp1 IN (1,2,3,4)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(40+TRUNC(DBMS_RANDOM.VALUE(0,14)))-730; END IF;
302 if (v_temp1 IN (5,6,7,8)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(180+TRUNC(DBMS_RANDOM.VALUE(0,14)))-730; END IF;
303 if (v_temp1 IN (9,10,11,12)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(40+TRUNC(DBMS_RANDOM.VALUE(0,14)))-365; END IF;
304 if (v_temp1 IN (13,14,15,16)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(180+TRUNC(DBMS_RANDOM.VALUE(0,14)))-365; END IF;
305 insert into grades values (v_temp3, v_i, v_temp1, TRUNC(DBMS_RANDOM.VALUE(0,6)) + 4, v_temp_date, v_temp_date, v_temp_date);
306 v_temp3 := v_temp3+1;
307 END LOOP;
308 end if;
309
310 if (v_temp=3) then
311 FOR v_temp1 IN 1..24 LOOP
312 if (v_temp1 IN (1,2,3,4)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(40+TRUNC(DBMS_RANDOM.VALUE(0,14)))-1095; END IF;
313 if (v_temp1 IN (5,6,7,8)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(180+TRUNC(DBMS_RANDOM.VALUE(0,14)))-1095; END IF;
314 if (v_temp1 IN (9,10,11,12)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(40+TRUNC(DBMS_RANDOM.VALUE(0,14)))-730; END IF;
315 if (v_temp1 IN (13,14,15,16)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(180+TRUNC(DBMS_RANDOM.VALUE(0,14)))-730; END IF;
316 if (v_temp1 IN (17,18,19,20)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(40+TRUNC(DBMS_RANDOM.VALUE(0,14)))-365; END IF;
317 if (v_temp1 IN (21,22,23,24)) THEN v_temp_date := to_date(to_char(sysdate,'YYYY')||'-01-01','YYYY-MM-DD')+(180+TRUNC(DBMS_RANDOM.VALUE(0,14)))-365; END IF;
318
319 insert into grades values (v_temp3, v_i, v_temp1, TRUNC(DBMS_RANDOM.VALUE(0,7)) + 4, v_temp_date, v_temp_date, v_temp_date);
320 v_temp3 := v_temp3+1;
321 END LOOP;
322 end if;
323 END LOOP;
324
325 DBMS_OUTPUT.PUT_LINE('Done !');
326
327
328 DBMS_OUTPUT.PUT_LINE('Adding instructors...');
329
330 FOR v_i IN 1..30 LOOP
331 v_lname := lista_lname(TRUNC(DBMS_RANDOM.VALUE(0,lista_lname.count))+1);
332 IF (DBMS_RANDOM.VALUE(0,100)<50) THEN
333 v_fname1 := lista_fname_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_fete.count))+1);
334 LOOP
335 v_fname2 := lista_fname_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_fete.count))+1);
336 exit when v_fname1<>v_fname2;
337 END LOOP;
338 ELSE
339 v_fname1 := lista_fname_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_baieti.count))+1);
340 LOOP
341 v_fname2 := lista_fname_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_baieti.count))+1);
342 exit when v_fname1<>v_fname2;
343 END LOOP;
344 END IF;
345
346 IF (DBMS_RANDOM.VALUE(0,100)<60) THEN
347 IF LENGTH(v_fname1 || ' ' || v_fname2) <= 20 THEN
348 v_fname := v_fname1 || ' ' || v_fname2;
349 END IF;
350 else
351 v_fname:=v_fname1;
352 END IF;
353 INSERT INTO instructors values (v_i, v_lname, v_fname, lista_grade_diactice(TRUNC(DBMS_RANDOM.VALUE(0,5))+1), sysdate-1000, sysdate-1000);
354 END LOOP;
355
356 DBMS_OUTPUT.PUT_LINE('Done !');
357
358
359 DBMS_OUTPUT.PUT_LINE('Adding links between instructors and courses...');
360 v_temp3:=1;
361 FOR v_i IN 1..24 LOOP
362 INSERT INTO didactic values(v_temp3,v_i, v_i, sysdate-1000, sysdate-1000);
363 v_temp3:=v_temp3+1;
364 END LOOP;
365
366 FOR v_i IN 1..50 LOOP
367 INSERT INTO didactic values(v_temp3,(TRUNC(DBMS_RANDOM.VALUE(0,30))+1), (TRUNC(DBMS_RANDOM.VALUE(0,24))+1), sysdate-1000, sysdate-1000);
368 v_temp3:=v_temp3+1;
369 END LOOP;
370
371
372 DBMS_OUTPUT.PUT_LINE('Done !');
373
374 DBMS_OUTPUT.PUT_LINE('English version of this script was made by Mihaita Manolache');
375
376END;
377/
378
379
380select count(*)|| ' students' from students;
381select count(*)|| ' instructors' from instructors;
382select count(*)|| ' courses' from courses;
383select count(*)|| ' grades' from grades;
384select count(*)|| ' friendships' from friendships;
385
386
387
388
389
390
391
392
393
394
395
396
397select count(*) from (select * from grades g1 join students s1 on g1.ID_STUDENT=s1.ID join friendships F on F.ID_STUDENT1=s1.ID join students s2 on F.ID_STUDENT2=s2.id join grades g2 on g2.id_student=s2.id);
398/
399
400drop table fg
401/
402
403CREATE TABLE fg (
404 ID NUMBER(10) PRIMARY KEY,
405 lnameA VARCHAR2(15) NOT NULL,
406 fnameA VARCHAR2(30) NOT NULL,
407 dobA DATE,
408 valueA NUMBER(2),
409 lnameB VARCHAR2(15) NOT NULL,
410 fnameB VARCHAR2(30) NOT NULL,
411 dobB DATE,
412 valueB NUMBER(2)
413)
414/
415
416drop sequence id_seq;
417CREATE SEQUENCE id_seq START WITH 1;
418
419CREATE OR REPLACE TRIGGER fg_id_autoinc
420BEFORE INSERT ON fg
421FOR EACH ROW
422BEGIN
423 SELECT id_seq.NEXTVAL
424 INTO :new.id
425 FROM dual;
426END;
427/
428
429-- this will run in like 3 minutes:
430insert into fg (lnameA, fnameA, dobA, valueA, lnameB, fnameB, dobB, valueB) select s1.lname, s1.fname, s1.dob, g1.value, s2.lname, s2.fname, s2.dob, g2.value
431from grades g1 join students s1 on g1.ID_STUDENT=s1.ID join friendships F on F.ID_STUDENT1=s1.ID join students s2 on F.ID_STUDENT2=s2.id join grades g2 on g2.id_student=s2.id
432/
433
434--update one row (Michael Jackson will be unique)
435update fg set lnameA='Jackson', fnameA='Michael' where id=1025;
436/
437
438
439
440
441
442
443
444
445-- any time you need to see the curent indexes, remember that:
446-- don;t drop any starting with sys...
447select index_name from user_indexes;
448
449
450
451
452-- Why indexes ? because it suck without. Try this (just for not having fun):
453-- Step 1:
454select * from fg where lnameA='Jackson';
455-- how much did it take ? See execution plan !
456
457
458
459
460
461
462-------- A: a simple index:
463-- Step 1: create an index:
464create index idx_lnameA on fg(lnameA);
465-- how much did it take ?
466-- Step 2:
467select * from fg where lnameA='Jackson';
468-- how much did it take ? Why is faster ? See execution plan !
469-- Step 3: deleting index
470drop index idx_lnameA;
471
472
473
474
475
476
477
478--------- B: primary key is always indexed
479-- Step 1: make a search on PK:
480select * from fg where id=9999;
481-- how much did it take ?
482-- run this again
483-- how much did it take ? Can you explain ?
484-- investigate the execution plan
485
486
487
488
489
490
491
492---------- C: index on more columns
493-- Step 1: create the index:
494create index idx_lnA_grA_lnB_grB on fg(lnameA, valueA, lnameB, valueB);
495-- Step 2: ask for all data:
496select * from fg where lnameA='Jackson';
497-- execute it again (statistics are in place this time)
498-- observe the execution plan; can you make a querry on this index that
499-- does not use the access into the table (table access (By Ibdex ROWID)) ?
500-- Step 3: try this:
501select lnameB from fg where lnameA='Jackson';
502-- explain why is not accessing the table anymore
503-- Step 4: let's find somebody else (all infos):
504select * from fg where id=9999;
505-- remember the values for: lnameA, valueA, lnameB, valueB;
506-- Step 5: Search the same row by fields: lnameA, valueA, lnameB, valueB;
507select * from fg where lnameA='...' and valueA=... and lnameB='...' and valueB=...;
508-- observe the execution plan !
509-- Step 6: for the same row now search it by using lnameA, valueA
510select * from fg where lnameA='...' and valueA=...;
511-- observe the execution plan !
512-- Step 7: for the same row, now search it by using lnameB, valueB
513select * from fg where lnameB='...' and valueB=...;
514-- observe the execution plan !
515-- Step 8: do this (complete the points)
516select lnameA, valueA from fg where lnameB='...' and valueB=...;
517-- observe the execution plan !
518-- Step 9: do this (complete the points)
519select lnameB, valueB from fg where lnameA='...' and valueA=...;
520-- observe the execution plan !
521-- Step 10: clear it:
522drop index idx_lnA_grA_lnB_grB;
523-- you want to drop this because a simple update (like the next one will run
524-- super slow if you don't - it needs to rebalance the B+ tree after each update)
525
526
527
528
529
530
531----------- D: on groups:
532-- this updates will take around 6 minutes
533update fg set valueB=9 where valueB=8
534/
535update fg set valueB=9 where valueB=7
536/
537update fg set valueB=9 where valueB=6
538/
539update fg set valueB=9 where valueB=5
540/
541update fg set valueB=9 where valueB=4
542/
543select count(*) from fg where valueB=10;
544update fg set valueB=8 where id=9999;
545-- observe the time needed and the execution plan (full table scan)
546-- Step 2: let's create the index
547create index idx_valueB on fg(valueB);
548-- Step 3: do the select again:
549select count(*) from fg where valueB=10;
550-- execution plan: did it need the table ?
551-- Step 4: see the execution plans for:
552select * from fg where valueB=11; -- should go on index
553select * from fg where valueB=10; -- should go on index
554select * from fg where valueB=9; -- if this go on index is the big if
555select * from fg where valueB=8; -- index (for sure)
556-- Step 5:
557drop index idx_valueB;
558
559
560
561
562
563
564
565---------- E: Function based indexes:
566-- Step 1:
567create index idx_lnameA on fg(lnameA);
568-- Step 2: verify that searching for a name go on index:
569select * from fg where lnameA='Jackson';
570-- check the execution plan (should see the idx_lnameA used)
571-- Step 3: try ignoring the case:
572select * from fg where upper(lnameA)=upper('Jackson');
573-- check the execution plan (remember the cost and how is executed)
574-- Step 4: create an function based index:
575create index idx_fbi_upper_lnameA on fg(upper(lnameA));
576-- Step 5: check the select from step 3 again:
577select * from fg where upper(lnameA)=upper('Jackson');
578-- check the execution plan (compare with what you wrote down at step 3)
579-- Step 6: update Jackson to jackson (in the table) and search again - the
580-- FBI will rebalance and pinpoint the record again
581update fg set lnameA='jackson' where lnameA='Jackson';
582-- even this update has an execution plan.... check it out
583-- can you imagine what else has an execution plan except select and update ?
584-- Step 7: how does the query from Step 3 or 5 executes now ? how fast ?
585-- [same as in step 3 or as in step 5] - answer before testing
586select * from fg where upper(lnameA)=upper('Jackson');
587-- Step 8: update it back and drop the index:
588update fg set lnameA='Jackson' where lnameA='jackson'
589/
590drop index idx_lnameA
591/
592drop index idx_fbi_upper_lnameA
593/
594
595
596
597
598
599--------- F: Search on intervals:
600-- Step 1: Let's find out min and max dobA
601select min(dobA), max(dobA) from fg;
602-- Step 2: Create and index on dobA and on valueA:
603create index idx_dobA_valueA on fg(dobA, valueA);
604-- Step 3: ask guys who are born in march and got a 10:
605select * from fg where dobA between to_date('01-01-1974','DD-MM-YYYY') and to_date('31-01-1974','DD-MM-YYYY') and valueA=10;
606-- remember the cost for this operation !!!
607-- Step 4: Create and index on valueA and on dobA:
608create index idx_valueA_dobA on fg(valueA, dobA);
609-- Step 5: ask guys who are born in march and got a 10:
610select * from fg where dobA between to_date('01-01-1974','DD-MM-YYYY') and to_date('31-01-1974','DD-MM-YYYY') and valueA=10;
611-- compare with the cost from step 3; also observe that the last index is prefered
612-- WHEN SEARCHING ON INTERVALS, ALWAYS PUT THE INDEX ON EQUALITY FIRST !
613-- Step 6: ok, now drop both indexes:
614drop index idx_dobA_valueA
615/
616drop index idx_valueA_dobA
617/
618
619
620
621
622
623--------- G: Searhing using LIKE
624-- Step 1: build an index on the lnameA;
625create index idx_lnameA on fg(lnameA);
626-- Step 2: Now search for that Jackson dude:
627select * from fg where lnameA='Jackson';
628-- check out the execution plan (cost and access predicates);
629-- Step 3: Now do search for 'Jackson' (but using like)
630select * from fg where lnameA like 'Jackson';
631-- cost and access should be about the same
632-- Step 4: now let's add an '%' in the middle:
633select * from fg where lnameA like 'Jac%kson';
634-- observe that in the execution plan the cost has doubled and also
635-- the querry has now an access predicates and also some filter predicates. Why ?
636-- Move the position of that '%' around and check the execution plan each time:
637select * from fg where lnameA like 'Jackso%n'; -- this should be better/faster
638select * from fg where lnameA like 'J%ackson'; -- or a bit slower
639select * from fg where lnameA like '%Jackson'; -- this should be much much worse
640-- also observe that it only filter from table, not using indexes at all.
641-- observe both the execution plan and the time needed for the query to be executed
642-- Step 5:
643-- Drop the index:
644drop index idx_lnameA
645/
646-- Step 6:
647-- ok, let's make the '%Jackson' run faster:
648create index idx_reverse_lnameA on fg(reverse(lnameA));
649-- Step 7: and the query:
650select * from fg where reverse(lnameA) like reverse('%Jackson');
651-- Step 8: aaaand... drop it:
652drop index idx_reverse_lnameA
653/
654
655
656
657
658
659--------- H: NULL in Oracle
660--Step 1: WHAT IS NULL IN ORACLE ? : '' is null
661select '0 IS NULL ?' as "WHAT IS NULL ?" FROM DUAL WHERE 0 IS NULL
662UNION
663select '0 IS NOT NULL ?' as "WHAT IS NULL ?" FROM DUAL WHERE 0 IS NOT NULL
664UNION
665select ''''' IS NULL ?' as "WHAT IS NULL ?" FROM DUAL WHERE '' IS NULL
666UNION
667select ''''' IS NULL ?' as "WHAT IS NULL ?" FROM DUAL WHERE '' IS NOT NULL
668-- STEP 2: anso NULL is ''
669select dummy, dummy||'', dummy||NULL from dual;
670-- Step 3: set a null value in some random row:
671update fg set valueb='' where id=1000;
672-- step 4: try to index it:
673create index idx_valueb on fg(valueb);
674-- step 5: check out the execution plan for this:
675select * from fg where valueb is null;
676-- because Oracle will not index null values, you will get a table access(full) - scanning the entire table
677-- step6: add a non-null column in the index:
678drop index idx_valueb;
679create index idx_valueb on fg(valueb, lnameA);
680-- we know that lnameA is not null because the table was created this way.
681-- step 6: he realizes that lname cannot be null and knows that he added it to the index (the pair null,notnull are added):
682select * from fg where valueb is null and lnameA like '';
683-- step 7: he uses both predicates in this (valueB is in the index):
684select * from fg where valueb is null and lnameA like 'A%';
685-- step 8: can it be selected individually (from the index?)
686select * from fg where valueb is null;
687-- so, as we can see, adding a not null column on the second position of the index
688-- will allow you to search for the records having null on the first column in the index
689-- step 9: can we find the not-null values from index ?
690select * from fg where valueb is not null;
691-- even though all the not-null values are in the indx (and he knows that he can find them there)
692-- if the time needed to access by index rowid is greater then scanning the full table, he will
693-- choose to ignore the index. (ofc, if you use a SSD he might choose to use the index anyway).
694-- you can learn how to trick QO but what he does depends on more than one or two parameters
695
696-- step 10:
697-- you might think that the rows where valueB=4 will be identified
698-- based on the rowid found in the index. If the number of rows is
699-- too big than QO might prefer to full scan the table:
700select * from fg where valueb=4;
701-- step 11:
702-- however, if you only asks data that are in the index or that can
703-- be computed directly from the index, the index will be used:
704select valueb from fg where valueb=4;
705-- or:
706select count(*) from fg where valueb=4;
707--step12: as we have seen, all the nulls are now in the index. So, the following query will use it:
708select count(*) from fg where valueb is null;
709-- what if the lnamea can have nulls ?
710--Step 13: update table:
711alter table fg modify lnamea null;
712-- execute the last query again - will not work, can you explain why (maybe both are null and
713-- the row is not indexed anymore).
714drop index idx_valueb;
715
716
717
718
719
720
721
722
723--------- I: Partial indexing in Oracle
724-- the main ideea is to create a function based index that returns null where
725-- you don;t want the index to store the information
726-- Step 1:
727-- let's do an index that only indexes rows where valueB = 10.
728-- First compile the following deterministic function:
729create or replace function splittable(val IN number) return number deterministic is
730begin
731 if (val=10) then return val;
732 else return null;
733 end if;
734end;
735-- and then create the index:
736create index idx_10 on fg(splittable(valueB));
737-- this time he will apply the function to each value in the valueB field...
738-- and create an index containing the results
739-- step 2: test it:
740select count(*) from fg where splittable(valueB)=10; -- will access via index
741select count(*) from fg where splittable(valueB)<>10; -- will still go on the index - can you explain why ?
742-- Answer: because NULL is interogated with IS NULL (and not with <>), he thinks that you are interested
743-- where you actually have a value in there.
744-- Step 3:
745select count(*) from fg where splittable(valueB) is null; -- since he doesn't store nulls, he has to use the table
746drop index idx_10;
747-- step 3: is it possible to make it work on index (the last query?)
748create index idx_10 on fg(splittable(valueB),'1');
749-- now we added a value that will never be null ('1'... can be also 1 or any constant)
750-- actually this is the same with indexing an non-null field
751select count(*) from fg where splittable(valueB) is null;
752drop index idx_valueb;
753
754
755
756
757
758
759--------- J: Joins
760-- Step 1: let's first create a join that can be used as a testcase in indexing:
761select scholarship from fg join students on fg.lnamea=students.lname where fg.lnamea like 'A%';
762-- cost in my case is around 13k
763-- Step 2and now let's test by forcing a specific join alg:
764-- Nested loops:
765select /*+ USE_NL(students fg) */ scholarship from fg join students on fg.lnamea=students.lname where fg.lnamea like 'A%';
766-- cost in my case is around 15k
767-- Nested loops:
768select /*+ use_hash */ scholarship from fg join students on fg.lnamea=students.lname where fg.lnamea like 'A%';
769-- like the initial case - which was executed with hash joins: 13k
770-- Step 4:
771-- Let's now create an index:
772create index idx_join_fg on fg(lnamea)
773/
774select /*+ USE_NL(students fg) */ scholarship from fg join students on fg.lnamea=students.lname where fg.lnamea like 'A%';
775-- cost in my case is around 1k
776-- Nested loops:
777select /*+ use_hash */ scholarship from fg join students on fg.lnamea=students.lname and fg.fnamea=students.fname where fg.lnamea like 'A%';
778-- cost: 0.6k
779-- if you really want to use nested loops you and you have very large tables, you have to index the attributes
780-- from the second table (to make faster access in the innter loop)
781-- so, for nested loops this is the actual way to go:
782create index idx_join_students on students(lname);
783-- now let's try that again (the nested loops)
784select /*+ USE_NL(students fg) */ scholarship from fg join students on fg.lnamea=students.lname where fg.lnamea like 'A%';
785-- in my case it uses both indexes but he still gets a cost of 1k (probably just because the table students is so small)
786-- for nested loops you should index the fields that are searched in the inner table (most important) and the
787-- ones that are used for filtering in the first table (less important)
788-- for hash joins, since they will create an inner hash and you cannot index that, you should only index what is after the where clause...
789drop index idx_join_students
790/
791-- Step 5 - the index in where clause is used by both hash and merge joins:
792select /*+ use_hash */ scholarship from fg join students on fg.lnamea=students.lname where fg.lnamea like 'A%';
793select /*+ use_merge(fg,students) parallel(fg, 4) parallel(students, 4) */ scholarship from fg join students on fg.lnamea=students.lname where fg.lnamea like 'A%';
794-- without this, it will give you huge costs.
795drop index idx_join_fg
796/
797
798
799
800--------- K: Clustering data
801-- index filters used intentionally:
802-- Step 1: see the execution plan for:
803SELECT lnameA, fnameA, dobA FROM fg WHERE valueA = 9 and UPPER(lnameA) LIKE '%ACK%';
804-- index at least valueA (because you cannot index for lnameA or upper(lnameA) ... since the
805-- value in LIKE has % both at begin and end.
806-- Step 2:
807create index idx_valueA on fg(valueA);
808-- now the execution plan should use the index:
809SELECT lnameA, fnameA, dobA FROM fg WHERE valueA = 9 and UPPER(lnameA) LIKE '%ACK%';
810-- can we lower the cost ?
811-- yea: by adding the upper(lnamea) he won;t have to filter data from the table;
812-- he will do it directly from the index:
813-- step 3:
814drop index idx_valueA
815/
816create index idx_valuea_upplnamea on fg(valueA, UPPER(lnameA))
817/
818-- check it out again:
819SELECT lnameA, fnameA, dobA FROM fg WHERE valueA = 9 and UPPER(lnameA) LIKE '%ACK%';
820-- now the cost is much much lower just because he could filter data directly from index.
821drop index idx_valuea_upplnamea
822/
823
824-- index only scan:
825-- Step 4: create the following index:
826create index idx_only_scan on fg(lnameA, fnameA, dobA);
827-- and now here is a select that will only use the index:
828select fnameA, lnameA, dobA from fg where lnameA = 'Jackson';
829-- Step 5:
830--check out the difference in execution plans for:
831select fnameA, lnameA, dobA from fg where lnameA = 'Jackson' and dobA=sysdate;
832--and
833select fnameA, lnameA, dobA from fg where lnameA = 'Jackson' and fnamea='Michael';
834-- why?
835-- The second one can use access directly from index (because lnamea and fnamea are
836-- the first two columns indexed. The first one (the one with lname and dobA can
837-- access based on the lnamea and only filter from the index those rows having
838-- a certain dobA. However, because the information is in the index, there is no
839-- table access.
840drop index idx_only_scan;
841
842
843--------- L: IOT
844--Step 1:
845-- create a IOT (it is actually the table students but organized as an index (IOT)
846CREATE TABLE stud (
847 id INT NOT NULL PRIMARY KEY,
848 registration_number VARCHAR2(6) NOT NULL,
849 lname VARCHAR2(15) NOT NULL,
850 fname VARCHAR2(30) NOT NULL,
851 year NUMBER(1),
852 groupno CHAR(2),
853 scholarship NUMBER(6,2),
854 dob DATE,
855 email VARCHAR2(40),
856 created_at DATE,
857 updated_at DATE
858) ORGANIZATION INDEX;
859/
860-- step 2:
861-- insert now all the lines in the table students:
862insert into STUD (id, registration_number, lname, fname, year, groupno, scholarship, dob, email, created_at, updated_at) select * from students;
863/
864-- step 3:
865-- any select will be an access into a table. See the execution plan for the following queries:
866select * from stud;
867-- probably, the cost in the original table is smaller ? try the execution plan for:
868select * from students;
869-- step 5:
870-- however, this runs faster on the IOT:
871select id, registration_number from stud where id between 10 and 100;
872--compared to the original table(even though there is an index on the primary key - the id). can you say why ?
873select id, registration_number from students where id between 10 and 100;
874--AnsewerL because in the IOT there is no need for table access by index row ID.
875-- step 6:
876-- if you want to find some information that is not indexed first in the iot:
877select id from stud where scholarship>1000;
878select id from students where scholarship>1000; -- in the students is faster ?
879--Try to create a secondary index and see how it is accessed.
880drop table stud;
881
882
883
884
885
886-------- M: order by
887-- Step 1: see how this is executed:
888-- this should have a huge cost (because it has to sort oud all data):
889select lnameA, fnameA, dobA from fg order by dobA;
890-- you can observe that the highest cost is added by the sorting algorithm:
891-- now let's create an index that can access based on dobA:
892create index idx_dobA on fg(dobA, lnameA, fnameA);
893-- now if you do the same operation, you will see that there is no sorting algorithm:
894select lnameA, fnameA, dobA from fg order by dobA;
895-- not having to sort all the data, the cost is smaller now. (although is still big because 5 millions rows are returned).
896drop index idx_dobA;
897
898-- step 2,3,4,etc: we are interested in using 2 fields and an oreder by (this way you will see that the index is not all powerfull):
899create index idx_vala_doba on fg(valuea,doba);
900-- now let's do the following querry:
901select valuea, doba from fg where valuea=9 order by doba;
902-- what heppens if the search interval is larger:
903select valuea, doba from fg where valuea between 8 and 9 order by doba;
904-- it won;t work (needs sorting) because you have shuffled doba in the ranges of the two values 8 and 9.
905-- however, this will be ok (no sort again):
906select valuea, doba from fg where valuea between 8 and 9 order by valuea, doba;
907-- because the data are in the same order as in the index.
908-- also, we can scan the index backwards. so the following will also work on index and won;t need sorting:
909select valuea, doba from fg where valuea between 8 and 9 order by valuea desc, doba desc;
910-- however, this will need (again) sorting:
911select valuea, doba from fg where valuea between 8 and 9 order by valuea desc, doba asc;
912-- this is because the order needed is not the same as in the index.
913drop index idx_vala_doba;
914-- let's recreate the index for the las querry to avoid sorting: you can do any of the following
915create index idx_vala_doba on fg(valuea desc ,doba asc);
916create index idx_vala_doba on fg(valuea asc, doba desc);
917-- this will now work withut sorting:
918select valuea, doba from fg where valuea between 8 and 9 order by valuea desc, doba asc;
919drop index idx_vala_doba;
920
921
922
923
924
925-------N: Group by
926--Step 1: create the index on the grouping cryteria:
927create index idx_grades on grades(grading_date, id_student);
928select count(*) from grades where grading_date between to_date('01-01-2015','DD-MM-YYY') and to_date('01-06-2015','DD-MM-YYY') group by id_student;
929-- actually, the upper scenario uses hash group by.... maybe is there a way to force sort group by ?
930select /*+ NO_USE_HASH_AGGREGATION */ count(*) from grades where grading_date between to_date('01-01-2015','DD-MM-YYY') and to_date('01-06-2015','DD-MM-YYY') group by id_student;
931drop index idx_grades
932
933
934
935
936-------O: Fetch first...
937SELECT * FROM (SELECT doba FROM fg where valuea=9 ORDER BY doba) WHERE rownum <= 10;
938-- and now create the index:
939create index idx_doba on fg(valuea,doba);
940-- this index will allow the execution to be pipelined and to only get the first 10 rows:
941-- try this again:
942SELECT * FROM (SELECT doba FROM fg where valuea=9 ORDER BY doba) WHERE rownum <= 10;
943--after getting the first page, that contains 10 rows, you need the rows 11 to 20:
944SELECT * FROM (SELECT tmp.*, rownum rn FROM (SELECT * FROM fg WHERE valuea=9 ORDER BY doba DESC ) tmp WHERE rownum <= 20) WHERE rn > 10;
945-- this will get the first 20 rows and filter out the first 10.
946-- the dissaadvantage when using offset method is that on a nth page you have to get n*10 records and discard (n-1)*10.
947drop index idx_doba;
948
949
950
951
952
953
954-------P: Fetch 11-20 using window:
955create index idx_doba on fg(doba,id);
956SELECT * FROM (
957 SELECT fg.* , ROW_NUMBER() OVER
958 (ORDER BY doba DESC ,
959 id DESC) rn
960 FROM fg) tmp
961 WHERE rn between 11 and 20
962 ORDER BY doba DESC, id DESC;
963-- even though he estimates to execute the window with a cost of 65k (in my case), the execution time is 0 seconds
964drop index idx_doba;
965
966
967
968
969-- some usefull commands are commented below:
970--commit;
971--select index_name from user_indexes;
972--desc fg;