API tools faq
paste
Advertisement
kanciastopantalones

klastry

kanciastopantalones
Nov 25th, 2016
183
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 51.81 KB | None | 0 0
raw download clone embed print report
  1. Klastry
  2.  
  3. Robert Sokolowski
  4.  
  5. Zad 1.
  6. create user succeeded.
  7. grant resource succeeded.
  8. grant create succeeded.
  9. grant SELECT_CATALOG_ROLE succeeded.
  10. alter user sh succeeded.
  11. alter user sh succeeded.
  12.  
  13.  
  14.  
  15. CREATE TABLE succeeded.
  16. CREATE TABLE succeeded.
  17. CREATE TABLE succeeded.
  18. CREATE TABLE succeeded.
  19. alter session set succeeded.
  20. 1 rows inserted
  21. 1 rows inserted
  22. (...)
  23.  
  24.  
  25.  
  26.  
  27. 2 Odczyt informacji o klasteryzowanych tabelach
  28.  
  29. 2.1 odczytac nazwy i typy atrybutów tabeli WYPLATY i TEMATY
  30. desc nazwa_tabeli;
  31.  
  32.  
  33. desc wyplaty;
  34. Name Null Type
  35. ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  36. NR_PRAC NUMBER(38)
  37. NR_TEM NUMBER(38)
  38. DATA_NALICZ DATE
  39. DATA_WYPL DATE
  40. KWOTA NUMBER(10,1)
  41.  
  42. 5 rows selected
  43.  
  44.  
  45. desc tematy;
  46. Name Null Type
  47. ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  48. NR_TEM NUMBER(38)
  49. TEMAT VARCHAR2(30)
  50. DATA_ROZP DATE
  51. DATA_ODB DATE
  52. NR_PRAC_KT NUMBER(38)
  53. NR_TEM_NADRZ NUMBER(38)
  54.  
  55.  
  56.  
  57. 2.2 oszacowac dlugosci poszczególnych kolumn tabel
  58. select avg(vsize(atrybut)) from nazwa_tabeli;
  59.  
  60. select avg(vsize(nr_prac)),avg(vsize(nr_tem)),avg(vsize(data_nalicz)),avg(vsize(data_wypl)),avg(vsize(kwota)) from wyplaty;
  61.  
  62. AVG(VSIZE(NR_PRAC)) AVG(VSIZE(NR_TEM)) AVG(VSIZE(DATA_NALICZ)) AVG(VSIZE(DATA_WYPL)) AVG(VSIZE(KWOTA))
  63. ---------------------- ---------------------- ----------------------- ---------------------- ----------------------
  64. 2 2.22058823529411764705882352941176470588 7 7 2.85294117647058823529411764705882352941
  65.  
  66. 1 rows selected
  67.  
  68.  
  69. select avg(vsize(nr_tem)),avg(vsize(temat)),avg(vsize(data_rozp)),avg(vsize(data_odb)),avg(vsize(nr_prac_kt)), avg(vsize(nr_tem_nadrz)) from tematy;
  70.  
  71. AVG(VSIZE(NR_TEM)) AVG(VSIZE(TEMAT)) AVG(VSIZE(DATA_ROZP)) AVG(VSIZE(DATA_ODB)) AVG(VSIZE(NR_PRAC_KT)) AVG(VSIZE(NR_TEM_NADRZ))
  72. ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------------------------
  73. 2.15555555555555555555555555555555555556 17.02222222222222222222222222222222222222 7 7 2 2
  74.  
  75. 1 rows selected
  76.  
  77.  
  78.  
  79. 2.3 Odczytac ilosc róznych kluczy klastra
  80. select count(distinct atrybut) from nazwa_tabeli;
  81.  
  82.  
  83. select count(distinct NR_PRAC), count(distinct nr_tem), count(distinct data_nalicz), count(distinct data_wypl), count(distinct kwota) from wyplaty;
  84.  
  85. COUNT(DISTINCTNR_PRAC) COUNT(DISTINCTNR_TEM) COUNT(DISTINCTDATA_NALICZ) COUNT(DISTINCTDATA_WYPL) COUNT(DISTINCTKWOTA)
  86. ---------------------- ---------------------- -------------------------- ------------------------ ----------------------
  87. 35 34 57 57 65
  88.  
  89. 1 rows selected
  90.  
  91.  
  92. select count(distinct NR_TEM), count(distinct TEMAT), count(distinct DATA_ROZP), count(distinct DATA_ODB), count(distinct NR_PRAC_KT), count(distinct NR_TEM_NADRZ) from tematy;
  93.  
  94. COUNT(DISTINCTNR_TEM) COUNT(DISTINCTTEMAT) COUNT(DISTINCTDATA_ROZP) COUNT(DISTINCTDATA_ODB) COUNT(DISTINCTNR_PRAC_KT) COUNT(DISTINCTNR_TEM_NADRZ)
  95. ---------------------- ---------------------- ------------------------ ----------------------- ------------------------- ---------------------------
  96. 45 45 45 34 18 2
  97.  
  98. 1 rows selected
  99.  
  100.  
  101. 2.4 Odczytac ilosc wierszy przypadajacych na klucz klastra
  102. select avg(count(*)) from nazwa_tabeli group by atrybut_kluczowy;
  103.  
  104. select avg(count(*)) from wyplaty group by NR_PRAC;
  105.  
  106. AVG(COUNT(*))
  107. ----------------------
  108. 5.82857142857142857142857142857142857143
  109.  
  110. 1 rows selected
  111.  
  112.  
  113. select avg(count(*)) from tematy group by NR_TEM;
  114.  
  115. AVG(COUNT(*))
  116. ----------------------
  117. 1
  118.  
  119. 1 rows selected
  120.  
  121.  
  122.  
  123. 2.5 Odczytac wartosci parametrów: INI_TRANS, PCT_FREE z perspektywy USER_TABLES
  124.  
  125. select INI_TRANS, PCT_FREE from USER_TABLES
  126.  
  127. INI_TRANS PCT_FREE
  128. ---------------------- ----------------------
  129. 1 10
  130. 1 10
  131. 1 10
  132. 1 10
  133.  
  134. 4 rows selected
  135.  
  136.  
  137.  
  138.  
  139. 1. Utwórz tabele S_PRAC w przestrzeni danych USERS jako kopie tabeli pracownicy:
  140. create table s_prac as select nr_prac, nazwisko, nr_zesp from pracownicy;
  141.  
  142. create table succeeded.
  143.  
  144.  
  145. 2. Utwórz w przestrzeni USERS (opcja tablespace USERS) klaster haszujacy PRAC_CLUSTER o 100
  146. wartosciach kluczy (opcja hashkeys1
  147. ) opartych o kolumne typu int. Kazdy klucz ma miec 80 bajtów (opcja
  148. size).
  149.  
  150. CREATE CLUSTER PRAC_CLUSTER (id int)
  151. HASHKEYS 100
  152. HASH IS id
  153. SIZE 80
  154. TABLESPACE USERS
  155.  
  156. CREATE CLUSTER succeeded.
  157.  
  158. 3. W klastrze PRAC_CLUSTER utwórz tabele H_PRAC z kolumnami jak w tabeli pracownicy:
  159. create table h_prac
  160. cluster prac_cluster (nr_prac) as select * from pracownicy;
  161.  
  162. create table succeeded.
  163.  
  164.  
  165. 4. Sprawdz efektywnosc klastra, okreslajac najpierw liczbe bitów zarezerwowanych dla kazdego klucza
  166. klastra (key_size):
  167.  
  168.  
  169. ANALYZE TABLE h_prac COMPUTE STATISTICS;
  170. SELECT cluster_name, cluster_type, key_size, hashkeys, avg_blocks_per_key
  171. FROM user_clusters;
  172. SELECT count(*) as keycount, avg(c) as mean, stddev(c) as stddev
  173. FROM (
  174. SELECT count(*) as c
  175. FROM H_PRAC
  176. GROUP BY NR_PRAC
  177. );
  178.  
  179.  
  180. ANALYZE TABLE h_prac succeeded.
  181. CLUSTER_NAME CLUSTER_TYPE KEY_SIZE HASHKEYS AVG_BLOCKS_PER_KEY
  182. ------------------------------ ------------ ---------------------- ---------------------- ----------------------
  183. PRAC_CLUSTER HASH 80 101 1
  184.  
  185. 1 rows selected
  186.  
  187. KEYCOUNT MEAN STDDEV
  188. ---------------------- ---------------------- ----------------------
  189. 42 1 0
  190.  
  191. 1 rows selected
  192.  
  193. 5. Utwórz klaster indeksowy o nazwie DEPT_PRAC_CLUSTER. Kolumna klucza klastra (np. o nazwie klucz)
  194. powinna byc typu int. Uzyj nizej wymienionych parametrów:
  195. size 55
  196. tablespace USERS
  197. storage (initial 5K)
  198.  
  199. CREATE CLUSTER DEPT_PRAC_CLUSTER (klucz int)
  200. TABLESPACE USERS
  201. SIZE 55
  202. STORAGE (initial 5K);
  203.  
  204. CREATE CLUSTER succeeded.
  205.  
  206.  
  207. 6. Skopiuj tabele s_prac do klastr DEPT_PRAC_CLUSTER uzywajac do grupowania kolumny nr_zesp.
  208. Nazwij kopie I_PRAC.
  209. create table i_prac cluster dept_prac_cluster (nr_zesp) as select * from s_prac;
  210. Co sie stalo?
  211.  
  212. Polecenia nie udalo sie wywolac z racji braku indeksu. Komunikat:
  213.  
  214. Error starting at line 1 in command:
  215. create table i_prac cluster dept_prac_cluster (nr_zesp) as select * from s_prac
  216. Error at Command Line:1 Column:73
  217. Error report:
  218. SQL Error: ORA-02032: clustered tables cannot be used before the cluster index is built
  219. 02032. 00000 - "clustered tables cannot be used before the cluster index is built"
  220. *Cause: User attempted to perform a DML statement on a clustered table
  221. for which no cluster index has yet been created.
  222. *Action: Create the cluster index.
  223.  
  224.  
  225.  
  226.  
  227. 7. Utwórz indeks klastra DEPT_PRAC_CLUSTER o nazwie DEPT_PRAC_CLUSTER_IDX. Ustaw przestrzen
  228. tabel na USERS, inne parametry zostaw domyslne.
  229.  
  230. create index DEPT_PRAC_CLUSTER_IDX on cluster DEPT_PRAC_CLUSTER;
  231. TABLESPACE USERS;
  232.  
  233. CREATE CLUSTER succeeded.
  234.  
  235.  
  236. 8. Ponownie skopiuj tabele s_prac do klastra DEPT_PRAC_CLUSTER uzywajac do grupowania kolumny
  237. nr_zesp. Nazwij kopie I_PRAC.
  238.  
  239. create table succeeded.
  240.  
  241.  
  242. 9. Dodaj do klastra DEPT_PRAC_CLUSTER tabele I_DEPT, bedaca kopia tabeli zespoly:
  243. create table i_dept cluster dept_prac_cluster (nr_zesp) as select * from zespoly ;
  244.  
  245. create table succeeded.
  246.  
  247.  
  248. 10. W perspektywie USER_SEGMENTS obejrzyj ile przestrzeni zajmuje kazdy z utworzonych obiektów
  249. w przestrzeni USERS.
  250.  
  251. select * from USER_SEGMENTS;
  252.  
  253. SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SEGMENT_SUBTYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE RETENTION MINRETENTION PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_POOL
  254. --------------------------------------------------------------------------------- ------------------------------ ------------------ --------------- ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- --------- ---------------------- ---------------------- ---------------------- ---------------------- -----------
  255. PRACOWNICY TABLE ASSM USERS 65536 8 1 65536 1 2147483645 2147483645 DEFAULT
  256. TEMATY TABLE ASSM USERS 65536 8 1 65536 1 2147483645 2147483645 DEFAULT
  257. WYPLATY TABLE ASSM USERS 65536 8 1 65536 1 2147483645 2147483645 DEFAULT
  258. ZESPOLY TABLE ASSM USERS 65536 8 1 65536 1 2147483645 2147483645 DEFAULT
  259. S_PRAC TABLE ASSM USERS 65536 8 1 65536 1 2147483645 2147483645 DEFAULT
  260. PRAC_CLUSTER CLUSTER ASSM USERS 65536 8 1 65536 1 2147483645 2147483645 DEFAULT
  261. DEPT_PRAC_CLUSTER CLUSTER ASSM USERS 65536 8 1 16384 1 2147483645 2147483645 DEFAULT
  262. DEPT_PRAC_CLUSTER_IDX INDEX ASSM USERS 65536 8 1 65536 1 2147483645 2147483645 DEFAULT
  263.  
  264. 8 rows selected
  265.  
  266.  
  267. 4 Sledzenie statystyk o utworzonym klastrze i pomiary czasu wykonania zapytan
  268.  
  269. 1. Zatwierdz strukture klastra, wszystkich jego tabel i wszystkich indeksów z nim zwiazanych, wlaczajac w to
  270. indeks klastra:
  271. analyze cluster prac_cluster validate structure cascade;
  272. …
  273. analyze table pracownicy estimate statistics;
  274. analyze table h_prac estimate statistics;
  275. analyze table i_prac estimate statistics;
  276.  
  277.  
  278. analyze cluster prac_cluster validate structure cascade;
  279. analyze cluster DEPT_PRAC_CLUSTER validate structure cascade;
  280. analyze table tematy validate structure cascade;
  281. analyze table pracownicy estimate statistics;
  282. analyze table h_prac estimate statistics;
  283. analyze table i_prac estimate statistics;
  284.  
  285. analyze cluster prac_cluster succeeded.
  286. analyze cluster DEPT_PRAC_CLUSTER succeeded.
  287. analyze table tematy succeeded.
  288. analyze table pracownicy succeeded.
  289. analyze table h_prac succeeded.
  290. analyze table i_prac succeeded.
  291.  
  292.  
  293. 2. Podlacz sie do bazy jako uzytkownik sys:
  294. connect sys/manager@orcl as sysdba;
  295. I odczytaj informacje z perspektywy dba_tables:
  296. select * from dba_tables where table_name ='PRACOWNICY' or table_name = 'H_PRAC';
  297.  
  298. OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING BACKED_UP NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE TEMPORARY SECONDARY NESTED BUFFER_POOL ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION SKIP_CORRUPT MONITORING CLUSTER_OWNER DEPENDENCIES COMPRESSION COMPRESS_FOR DROPPED READ_ONLY
  299. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------- --------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------------------------- ---------------------- ---------- ---------- ----- ---------- ---------------------- ------------------------- ----------- ------------ --------- --------- ------ ----------- ------------ ------------ ---------- --------------- ------------ ---------- ------------------------------ ------------ ----------- ------------------ ------- ---------
  300. ORA1 PRACOWNICY USERS VALID 10 1 255 65536 1 2147483645 YES N 42 5 3 7767 0 34 0 0 1 1 N ENABLED 42 25-NOV-16 NO N N NO DEFAULT DISABLED NO NO DISABLED YES DISABLED DISABLED NO NO
  301. ORA1 H_PRAC USERS PRAC_CLUSTER VALID 0 0 0 65536 1 2147483645 YES N 42 4 4 5381 0 35 0 0 1 1 N ENABLED 42 25-NOV-16 NO N N NO DEFAULT DISABLED NO NO DISABLED YES ORA1 DISABLED DISABLED NO NO
  302.  
  303. 2 rows selected
  304.  
  305.  
  306. 3. Podlacz sie do bazy ponownie jako uzytkownik orax:
  307. connect orax/orax@orcl;
  308.  
  309.  
  310. 4. W perspektywie USER_SEGMENTS sprawdz ile jest bloków w tabelach poklastrowanych
  311. i niepoklastrowanych:
  312. select bytes, blocks, extents, segment_name from user_segments where
  313. segment_name = 'PRAC_CLUSTER' or
  314. segment_name = 'PRACOWNICY' or
  315. segment_name = 'DEPT_PRAC_CLUSTER' or
  316. segment_name = 'ZESPOLY' or
  317. segment_name = 'S_PRAC' or
  318. segment_name = 'DEPT_PRAC_CLUSTER_IDX';
  319.  
  320.  
  321. BYTES BLOCKS EXTENTS SEGMENT_NAME
  322. ---------------------- ---------------------- ---------------------- ---------------------------------------------------------------------------------
  323. 65536 8 1 DEPT_PRAC_CLUSTER
  324. 65536 8 1 DEPT_PRAC_CLUSTER_IDX
  325. 65536 8 1 PRACOWNICY
  326. 65536 8 1 PRAC_CLUSTER
  327. 65536 8 1 S_PRAC
  328. 65536 8 1 ZESPOLY
  329.  
  330. 6 rows selected
  331.  
  332.  
  333. 5. Sprawdz plan wykonania zapytania, wykonujac: set autotrace on;
  334. a nastepnie wykonujac zapytanie SQL na tabeli (-ach) poklastrowanej (-ych) i niepoklastrowanej (-ych).
  335.  
  336.  
  337.  
  338.  
  339. set autotrace on;
  340.  
  341. AutoTrace Enabled
  342.  
  343. select * from s_prac;
  344. select * from h_prac;
  345. select * from i_prac;
  346. select * from zespoly;
  347. select * from i_dept;
  348. select * from tematy;
  349.  
  350. NR_PRAC NAZWISKO NR_ZESP
  351. ---------------------- --------------- ----------------------
  352.  
  353. 1 GRZYBEK 4
  354. 2 GRZYBIARZ 5
  355. 3 WIREK 6
  356. 4 JANECZEK 5
  357. 5 TADECZEK 4
  358. 6 PODWISLAK 3
  359. 7 WOJTECKA 2
  360. 8 WUJEK 1
  361. 9 TRUMAN 5
  362. 10 ZUBEK 2
  363. 11 SKRZYPEK 5
  364. 12 POPKO 6
  365. 13 NOWY 1
  366. 14 RAZOWA 3
  367. 15 FULAK 4
  368. 16 KOTULA 2
  369. 17 KULANEK 3
  370. 18 KULAWIK 6
  371. 19 SKOCZEK 5
  372. 20 WIEZYCKA 1
  373. 21 BUJAK 3
  374. 22 BIERNAT 5
  375. 23 LUBELSKI 1
  376. 24 IWAN 2
  377. 25 JASKOLA 5
  378. 26 REBUS 3
  379. 27 REDOKS 1
  380. 28 SKOREK 4
  381. 29 KIPER 5
  382. 30 REPIK 2
  383. 31 NIEZALEZNY 1
  384. 32 WILK 3
  385. 33 MISIURA 4
  386. 34 KRAUS 5
  387. 35 WILCZEK 6
  388. 36 WILCZYNSKI 7
  389. 37 GABKA 7
  390. 38 KRAK 8
  391. 39 SZCZERBIEC 8
  392. 40 PSTROWSKI 7
  393. 41 SZCZERBIEC 8
  394.  
  395. 42 rows selected
  396.  
  397. Plan hash value: 889985163
  398.  
  399. ----------------------------------------------------------------------------
  400. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  401. ----------------------------------------------------------------------------
  402. | 0 | SELECT STATEMENT | | 42 | 1806 | 3 (0)| 00:00:01 |
  403. | 1 | TABLE ACCESS FULL| S_PRAC | 42 | 1806 | 3 (0)| 00:00:01 |
  404. ----------------------------------------------------------------------------
  405.  
  406. Note
  407. -----
  408. - dynamic sampling used for this statement
  409.  
  410. Statistics
  411. -----------------------------------------------------------
  412. 0 recursive calls
  413. 0 db block gets
  414. 0 db block gets direct
  415. 0 consistent gets - examination
  416. 0 calls to kcmgcs
  417. 0 HSC OLTP recursive compression
  418. 0 HSC OLTP inline compression
  419. 0 HSC OLTP Drop Column
  420. 0 Heap Segment Array Updates
  421. 0 securefile allocation bytes
  422. NR_PRAC PLEC DATA_UR NAZWISKO NR_ZESP
  423. ---------------------- ---- ------------------------- --------------- ----------------------
  424. 1999-11-11
  425. 1 M 1948-09-12 GRZYBEK 4
  426. 2 K 1957-02-17 GRZYBIARZ 5
  427. 3 M 1965-11-06 WIREK 6
  428. 4 K 1959-10-07 JANECZEK 5
  429. 5 M 1961-03-15 TADECZEK 4
  430. 6 M 1949-09-17 PODWISLAK 3
  431. 7 K 1947-08-19 WOJTECKA 2
  432. 8 K 1948-07-28 WUJEK 1
  433. 9 K 1964-05-07 TRUMAN 5
  434. 10 K 1962-05-04 ZUBEK 2
  435. 11 M 1956-04-10 SKRZYPEK 5
  436. 12 K 1968-11-03 POPKO 6
  437. 13 M 1945-08-21 NOWY 1
  438. 14 K 1949-08-17 RAZOWA 3
  439. 15 K 1960-09-16 FULAK 4
  440. 16 M 1953-09-13 KOTULA 2
  441. 17 M 1951-01-25 KULANEK 3
  442. 18 M 1961-07-25 KULAWIK 6
  443. 19 M 1958-05-08 SKOCZEK 5
  444. 20 K 1952-11-04 WIEZYCKA 1
  445. 21 M 1953-02-23 BUJAK 3
  446. 22 K 1947-05-09 BIERNAT 5
  447. 23 M 1946-10-10 LUBELSKI 1
  448. 24 M 1963-11-03 IWAN 2
  449. 25 K 1959-03-17 JASKOLA 5
  450. 26 K 1950-10-06 REBUS 3
  451. 27 K 1947-04-09 REDOKS 1
  452. 28 K 1952-03-14 SKOREK 4
  453. 29 M 1955-10-11 KIPER 5
  454. 30 K 1957-10-09 REPIK 2
  455. 31 M 1964-11-11 NIEZALEZNY 1
  456. 32 M 1945-12-10 WILK 3
  457. 33 M 1934-10-05 MISIURA 4
  458. 34 M 1954-09-04 KRAUS 5
  459. 35 M 1934-12-25 WILCZEK 6
  460. 36 M 1945-12-10 WILCZYNSKI 7
  461. 37 M 1934-10-05 GABKA 7
  462. 38 M 1954-09-04 KRAK 8
  463. 39 M 1934-12-25 SZCZERBIEC 8
  464. 40 M 1984-10-05 PSTROWSKI 7
  465. 41 M 1934-12-25 SZCZERBIEC 8
  466.  
  467. 42 rows selected
  468.  
  469. Plan hash value: 1060896720
  470.  
  471. ----------------------------------------------------------------------------
  472. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  473. ----------------------------------------------------------------------------
  474. | 0 | SELECT STATEMENT | | 42 | 1134 | 3 (0)| 00:00:01 |
  475. | 1 | TABLE ACCESS FULL| H_PRAC | 42 | 1134 | 3 (0)| 00:00:01 |
  476. ----------------------------------------------------------------------------
  477.  
  478. Statistics
  479. -----------------------------------------------------------
  480. 0 recursive calls
  481. 0 db block gets
  482. 0 db block gets direct
  483. 0 consistent gets - examination
  484. 0 calls to kcmgcs
  485. 0 HSC OLTP recursive compression
  486. 0 HSC OLTP inline compression
  487. 0 HSC OLTP Drop Column
  488. 0 Heap Segment Array Updates
  489. 0 securefile allocation bytes
  490. NR_PRAC NAZWISKO NR_ZESP
  491. ---------------------- --------------- ----------------------
  492.  
  493. 1 GRZYBEK 4
  494. 2 GRZYBIARZ 5
  495. 3 WIREK 6
  496. 4 JANECZEK 5
  497. 5 TADECZEK 4
  498. 6 PODWISLAK 3
  499. 7 WOJTECKA 2
  500. 8 WUJEK 1
  501. 9 TRUMAN 5
  502. 10 ZUBEK 2
  503. 11 SKRZYPEK 5
  504. 12 POPKO 6
  505. 13 NOWY 1
  506. 14 RAZOWA 3
  507. 15 FULAK 4
  508. 16 KOTULA 2
  509. 17 KULANEK 3
  510. 18 KULAWIK 6
  511. 19 SKOCZEK 5
  512. 20 WIEZYCKA 1
  513. 21 BUJAK 3
  514. 22 BIERNAT 5
  515. 23 LUBELSKI 1
  516. 24 IWAN 2
  517. 25 JASKOLA 5
  518. 26 REBUS 3
  519. 27 REDOKS 1
  520. 28 SKOREK 4
  521. 29 KIPER 5
  522. 30 REPIK 2
  523. 31 NIEZALEZNY 1
  524. 32 WILK 3
  525. 33 MISIURA 4
  526. 34 KRAUS 5
  527. 35 WILCZEK 6
  528. 36 WILCZYNSKI 7
  529. 37 GABKA 7
  530. 38 KRAK 8
  531. 39 SZCZERBIEC 8
  532. 40 PSTROWSKI 7
  533. 41 SZCZERBIEC 8
  534.  
  535. 42 rows selected
  536.  
  537. Plan hash value: 2536723742
  538.  
  539. ----------------------------------------------------------------------------
  540. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  541. ----------------------------------------------------------------------------
  542. | 0 | SELECT STATEMENT | | 42 | 798 | 3 (0)| 00:00:01 |
  543. | 1 | TABLE ACCESS FULL| I_PRAC | 42 | 798 | 3 (0)| 00:00:01 |
  544. ----------------------------------------------------------------------------
  545.  
  546. Statistics
  547. -----------------------------------------------------------
  548. 0 recursive calls
  549. 0 db block gets
  550. 0 db block gets direct
  551. 0 consistent gets - examination
  552. 0 calls to kcmgcs
  553. 0 HSC OLTP recursive compression
  554. 0 HSC OLTP inline compression
  555. 0 HSC OLTP Drop Column
  556. 0 Heap Segment Array Updates
  557. 0 securefile allocation bytes
  558. NR_ZESP NAZWA_ZESP NR_PRAC_KZ NR_INST
  559. ---------------------- ------------------------------ ---------------------- ----------------------
  560. 1 OPROGRAMOWANIE 1 1
  561. 2 TEORIA INFORMATYKI 2 1
  562. 3 SIECI KOMPUTEROWE 3 1
  563. 4 BUDOWA 4 1
  564. 5 AUTOMATY 25 3
  565. 6 BUDOWA KOPARKI 10 5
  566. 7 PIECE 21 6
  567. 8 WEGIEL 29 5
  568. 9 KONSTRUKCJA 33 5
  569. 10 LIKWIDACJA 21 6
  570. 11 POZIOMZEROWY 37 5
  571.  
  572. 11 rows selected
  573.  
  574. Plan hash value: 4044335737
  575.  
  576. -----------------------------------------------------------------------------
  577. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  578. -----------------------------------------------------------------------------
  579. | 0 | SELECT STATEMENT | | 11 | 781 | 3 (0)| 00:00:01 |
  580. | 1 | TABLE ACCESS FULL| ZESPOLY | 11 | 781 | 3 (0)| 00:00:01 |
  581. -----------------------------------------------------------------------------
  582.  
  583. Note
  584. -----
  585. - dynamic sampling used for this statement
  586.  
  587. Statistics
  588. -----------------------------------------------------------
  589. 4 recursive calls
  590. 0 db block gets
  591. 0 db block gets direct
  592. 0 consistent gets - examination
  593. 0 calls to kcmgcs
  594. 0 HSC OLTP recursive compression
  595. 0 HSC OLTP inline compression
  596. 0 HSC OLTP Drop Column
  597. 0 Heap Segment Array Updates
  598. 0 securefile allocation bytes
  599. NR_ZESP NAZWA_ZESP NR_PRAC_KZ NR_INST
  600. ---------------------- ------------------------------ ---------------------- ----------------------
  601. 1 OPROGRAMOWANIE 1 1
  602. 2 TEORIA INFORMATYKI 2 1
  603. 3 SIECI KOMPUTEROWE 3 1
  604. 4 BUDOWA 4 1
  605. 5 AUTOMATY 25 3
  606. 6 BUDOWA KOPARKI 10 5
  607. 7 PIECE 21 6
  608. 8 WEGIEL 29 5
  609. 9 KONSTRUKCJA 33 5
  610. 10 LIKWIDACJA 21 6
  611. 11 POZIOMZEROWY 37 5
  612.  
  613. 11 rows selected
  614.  
  615. Plan hash value: 1153213081
  616.  
  617. ----------------------------------------------------------------------------
  618. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  619. ----------------------------------------------------------------------------
  620. | 0 | SELECT STATEMENT | | 11 | 396 | 3 (0)| 00:00:01 |
  621. | 1 | TABLE ACCESS FULL| I_DEPT | 11 | 396 | 3 (0)| 00:00:01 |
  622. ----------------------------------------------------------------------------
  623.  
  624. Statistics
  625. -----------------------------------------------------------
  626. 1 recursive calls
  627. 0 db block gets
  628. 0 db block gets direct
  629. 0 consistent gets - examination
  630. 0 calls to kcmgcs
  631. 0 HSC OLTP recursive compression
  632. 0 HSC OLTP inline compression
  633. 0 HSC OLTP Drop Column
  634. 0 Heap Segment Array Updates
  635. 0 securefile allocation bytes
  636. NR_TEM TEMAT DATA_ROZP DATA_ODB NR_PRAC_KT NR_TEM_NADRZ
  637. ---------------------- ------------------------------ ------------------------- ------------------------- ---------------------- ----------------------
  638. 1 ADA SRODOWISKO 1990-01-01 1991-12-31 32
  639. 2 ADA EDYTOR 1990-03-01 1990-12-31 33 1
  640. 3 ADA KOMPILATOR 1990-09-01 1991-10-31 34 1
  641. 4 PASCAL KOMPILATOR 1983-03-21 1984-03-20 35
  642. 5 BAZA DANYCH - SPOLEM 1987-04-20 1987-09-17 32
  643. 6 BAZA DANYCH - PZU 1986-03-10 1986-07-28 29
  644. 8 PROCEDURY GRAFICZNE 1992-04-24 1992-08-22 21
  645. 10 PROJEKTOWANIE UKLADOW 1983-12-11 1984-03-20 26
  646. 11 ANALIZATOR WIDMA 1987-06-19 1987-11-16 29
  647. 13 LISP - KOMPILATOR 1985-01-08 1985-03-19 31
  648. 14 PROLOG - KOMPILATOR 1992-06-23 1992-08-22 34
  649. 15 STEROWANIE GAZOCIAGIEM 1983-08-22 1983-11-10 11
  650. 16 STEROWANIE TASMOCIAGIEM 1984-02-09 1984-03-20 25
  651. 17 STEROWNIK TURBINY W ELEKTROWNI 1987-08-18 1987-10-17 35
  652. 18 KARTA EMULATORA PC 1986-07-08 1986-09-26 32
  653. 20 GENERATOR SYGNALOW W.CZ. 1992-08-22 1992-11-20 34
  654. 21 ANALIZATOR STANOW 1983-10-21 1983-11-10 11
  655. 22 KANAL WIZJI - OPROGRAMOWANIE 1984-04-09 1984-05-19 31
  656. 24 CZUJNIK CISNIENIA 1986-09-06 1986-10-26 10
  657. 25 ANALIZATOR STEZENIA JONOW 1985-05-08 1985-06-17 27
  658. 28 WZMACNIACZ W.CZ. 1984-06-08 1984-12-15 29
  659. 30 MONITOR ZNAKOWY 1986-11-05 1986-11-25 21
  660. 31 DYSK 1985-04-01 1985-08-09 31
  661. 32 STEROWANIE REAKTOREM 1994-07-01 1994-03-03 18
  662. 33 GUPTA 1999-06-25 1999-02-15 1
  663. 34 PRZETWARZANIE WEKTOROWE 1986-12-15 1986-07-28 36
  664. 35 PRZETWARZANIE ROWNOLEGLE 1985-08-16 1985-03-19 37
  665. 36 PRZETWARZANIE ROZPROSZONE 1993-01-29 1992-08-22 33
  666. 37 ASSEMBLERY 1984-03-29 1983-10-11 36
  667. 38 SYMULATOR 1984-09-16 1984-03-20 37
  668. 39 BEZPIECZENSTWO 1988-03-25 1988-06-13 32
  669. 40 ODTWARZANIE 1987-02-13 1987-06-23 38
  670. 41 ARCHIWIZACJA 1985-07-10 1984-12-12 38
  671. 42 SLABOPLATNY 2000-06-18 2000-07-08 23
  672. 43 ZEROWY 2000-06-28 1999-11-11 23
  673. 44 KANALY WIZJI 1984-06-07 1983-10-11 21
  674. 45 KARTA MONITORA 1985-11-24 1985-03-19 32
  675. 100 ZESTAW KOMPUTEROWY 1985-01-01 1988-10-31 31
  676. 101 KARTA PROCESORA 1988-10-15 1989-01-13 10 100
  677. 102 MONITOR GRAFICZNY 1987-06-17 1987-07-07 25 100
  678. 103 GENERATOR DZWIEKU 1987-06-27 1987-08-06 33 100
  679. 104 STEROWNIK DYSKU 1990-01-04 1990-03-05 35 100
  680. 105 KARTA MONITORA GRAFICZNEGO 1991-02-28 1991-04-19 33 100
  681. 106 MODUL I/O 1988-02-18 1988-03-29 1 100
  682. 107 ZASILACZ 1990-02-03 1990-03-05 32 100
  683.  
  684. 45 rows selected
  685.  
  686. Plan hash value: 2328567157
  687.  
  688. ----------------------------------------------------------------------------
  689. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  690. ----------------------------------------------------------------------------
  691. | 0 | SELECT STATEMENT | | 45 | 3330 | 3 (0)| 00:00:01 |
  692. | 1 | TABLE ACCESS FULL| TEMATY | 45 | 3330 | 3 (0)| 00:00:01 |
  693. ----------------------------------------------------------------------------
  694.  
  695. Note
  696. -----
  697. - dynamic sampling used for this statement
  698.  
  699. Statistics
  700. -----------------------------------------------------------
  701. 4 recursive calls
  702. 0 db block gets
  703. 0 db block gets direct
  704. 0 consistent gets - examination
  705. 0 calls to kcmgcs
  706. 0 HSC OLTP recursive compression
  707. 0 HSC OLTP inline compression
  708. 0 HSC OLTP Drop Column
  709. 0 Heap Segment Array Updates
  710. 0 securefile allocation bytes
  711.  
  712.  
  713. Wyraznie widac, ze dla tabel klastrowanych ilosc bajtow jest zdecydowanie mniejsza
  714.  
  715.  
  716. 5 Czynnosci koncowe
  717.  
  718. 1. Utwórz klaster haszowany TEST1_HASH w przestrzeni USERS, zakladajac, ze do pobrania rekordu
  719. uzywana jest glównie kolumna nr_prac typu int. Uzyj kolumny data_ur jako funkcji haszujacej (opcja hash
  720. is) i okresl 20 kluczy haszujacych (hashkeys). Zdefiniuj 55 [B] do przechowywania wierszy z ta sama
  721. wartoscia haszujaca. Co sie stalo?
  722.  
  723.  
  724.  
  725.  
  726. create cluster TEST1_HASH (nr_prac int)
  727. hashkeys 20
  728. HASH IS data_ur
  729. size 55
  730. tablespace USERS
  731.  
  732. Error starting at line 1 in command:
  733. create cluster TEST1_HASH (nr_prac int)
  734. hashkeys 20
  735. HASH IS data_ur
  736. size 55
  737. tablespace USERS
  738. Error at Command Line:1 Column:0
  739. Error report:
  740. SQL Error: ORA-02467: Column referenced in expression not found in cluster definition
  741. 02467. 00000 - "Column referenced in expression not found in cluster definition"
  742. *Cause: A column in the hash is expression was not present in cluster
  743. definition.
  744. *Action: Recreate the cluster and correct the error in hash expression.
  745.  
  746.  
  747.  
  748. 2. Utwórz klaster TEST2_HASH z kluczem nr_tem int, specyfikujac po opcji hash is atrybut nr_prac. Co sie
  749. stalo?
  750.  
  751.  
  752.  
  753. create cluster TEST2_HASH (nr_tem int)
  754. hashkeys 20
  755. HASH IS nr_prac
  756. Error starting at line 1 in command:
  757. create cluster TEST2_HASH (nr_tem int)
  758. hashkeys 20
  759.  
  760. HASH IS nr_prac
  761. Error at Command Line:1 Column:0
  762. Error report:
  763. SQL Error: ORA-02467: Column referenced in expression not found in cluster definition
  764. 02467. 00000 - "Column referenced in expression not found in cluster definition"
  765. *Cause: A column in the hash is expression was not present in cluster
  766. definition.
  767. *Action: Recreate the cluster and correct the error in hash expression.
  768.  
  769.  
  770. 3. Zmodyfikuj parametry pamieciowe klastra prac_cluster poleceniem ALTER CLUSTER podajac po slowie
  771. STORAGE: (next 400K pctincrease 50);
  772. Co sie stalo?
  773.  
  774.  
  775.  
  776. alter cluster prac_cluster STORAGE (next 400K pctincrease 50);
  777.  
  778. Error starting at line 1 in command:
  779. alter cluster prac_cluster STORAGE (next 400K pctincrease 50)
  780. Error report:
  781. SQL Error: ORA-25150: ALTERING of extent parameters not permitted
  782. 25150. 00000 - "ALTERING of extent parameters not permitted"
  783. *Cause: An attempt was made to alter the extent parameters for a segment
  784. in a tablespace with autoallocate or uniform extent allocation
  785. policy.
  786. *Action: Remove the appropriate extent parameters from the command.
  787.  
  788.  
  789.  
  790. 4. Spróbuj zmienic poleceniem ALTER CLUSTER parametr SIZE klastra prac_cluster na wartosc 100. Co sie
  791. stalo?
  792.  
  793.  
  794. alter cluster prac_cluster SIZE 100;
  795.  
  796. Error starting at line 1 in command:
  797. alter cluster prac_cluster SIZE 100
  798.  
  799.  
  800.  
  801. Error report:
  802. SQL Error: ORA-02466: The SIZE and INITRANS options cannot be altered for HASH CLUSTERS.
  803. 02466. 00000 - "The SIZE and INITRANS options cannot be altered for HASH CLUSTERS."
  804. *Cause: An attempt was made to change the SIZE and INITRANS options after
  805. the hash cluster was created.
  806. *Action: Do not specify this option.
  807.  
  808.  
  809.  
  810. 5. Spróbuj usunac klaster PRAC_CLUSTER. Co sie stalo?
  811.  
  812.  
  813.  
  814. drop cluster prac_cluster;
  815.  
  816. Error starting at line 1 in command:
  817. drop cluster prac_cluster
  818. Error report:
  819. SQL Error: ORA-00951: cluster not empty
  820. 00951. 00000 - "cluster not empty"
  821. *Cause:
  822. *Action:
  823.  
  824.  
  825.  
  826. 6. Wykonaj jeszcze raz polecenie z pkt. poprzedniego, dodajac opcje INCLUDING TABLES.
  827.  
  828.  
  829.  
  830. drop cluster prac_cluster INCLUDING TABLES;
  831.  
  832. drop cluster prac_cluster succeeded.
  833.  
  834. 7. Usun wszystkie stworzone przez siebie obiekty:
  835.  
  836.  
  837.  
  838. drop table i_prac;
  839. drop table s_prac;
  840. drop table i_dept;
  841. drop cluster dept_prac_cluster;
  842. drop index dept_prac_cluster_idx;
  843.  
  844. drop table i_prac succeeded.
  845. drop table s_prac succeeded.
  846. drop table i_dept succeeded.
  847. drop cluster dept_prac_cluster succeeded.
  848. drop index dept_prac_cluster_idx succeeded.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!