Db2 practice 4 sol - Practical computer exercises and solutions for lecture 4 of Databases 2. PDF

Preview

Summary

Practical computer exercises and solutions for lecture 4 of Databases 2....

Description

Oracle indices, IOTs, partitions, clusters -----------------------------------------Give the tables (table_name) which has a column indexed in descending order. (In the solutions only objects of Nikovits are concerned.) SELECT * FROM dba_ind_columns WHERE descend='DESC' AND index_owner='NIKOVITS'; See the name of the column. Why is it so strange? -> DBA_IND_EXPRESSIONS SELECT * FROM dba_ind_columns WHERE index_name='EMP2' AND index_owner='NIKOVITS'; SELECT * FROM dba_ind_expressions WHERE index_name='EMP2' AND index_owner='NIKOVITS'; -----------------------------------Give the indexes (index name) which are composite and have at least 9 columns (expressions). SELECT index_owner, index_name FROM dba_ind_columns GROUP BY index_owner, index_name HAVING count(*) >=9; -- confirm one of them SELECT * FROM dba_ind_columns WHERE index_owner='SYS' AND index_name='I_OBJ2'; -----------------------------------------Give the bitmap indexes on table SH.SALES. SELECT * FROM dba_indexes WHERE table_owner='SH' AND table_name='SALES' AND index_type='BITMAP'; -----------------------------------------Give the indexes which has at least 2 columns and are functionbased. SELECT index_owner, index_name FROM dba_ind_columns GROUP BY index_owner, index_name HAVING count(*) >=2 INTERSECT SELECT index_owner, index_name FROM dba_ind_expressions; ----------------------------------------Give for one of the above indexes the expression for which the index was created. SELECT * FROM dba_ind_expressions WHERE index_owner='OE'; ----------------------------------------Give the index organized tables of user NIKOVITS.

SELECT owner, table_name, iot_name, iot_type FROM dba_tables WHERE owner='NIKOVITS' AND iot_type = 'IOT'; Find the table_name, index_name and overflow name (if exists) of the above tables. SELECT table_name, index_name, index_type FROM dba_indexes WHERE table_owner='NIKOVITS' AND index_type LIKE '%IOT%TOP%'; SELECT owner, table_name, iot_name, iot_type FROM dba_tables WHERE owner='NIKOVITS' AND iot_type = 'IOT_OVERFLOW'; Which objects of the previous three has data_object_id in DBA_OBJECTS? ------------------------------------------Give the names and sizes (in bytes) of the partitions of table SH.COSTS SELECT * FROM dba_part_tables WHERE owner='SH' AND table_name='COSTS'; SELECT * FROM dba_tab_partitions WHERE table_owner='SH' AND table_name='COSTS'; SELECT segment_name, partition_name, segment_type, bytes FROM dba_segments WHERE owner='SH' AND segment_name LIKE 'COSTS%' AND segment_type LIKE 'TABLE%'; ------------------------------------------Which is the biggest partitioned table (in bytes) in the database? It can have subpartitions as well. SELECT * FROM (SELECT owner, segment_name, sum(bytes) FROM dba_segments WHERE segment_type LIKE 'TABLE%PARTITION' GROUP BY owner, segment_name ORDER BY sum(bytes) DESC) WHERE ROWNUM = 1; -----------------------------------------Give a cluster whose cluster key consists of 3 columns. It can have more tables on it!!! SELECT owner, cluster_name FROM dba_clu_columns GROUP BY owner, cluster_name HAVING count(DISTINCT clu_column_name) = 3; -- confirm one of them SELECT * FROM dba_clu_columns WHERE owner='NIKOVITS' AND cluster_name='CL1'; -----------------------------------------How many clusters we have in the database which uses NOT THE DEFAULT hash function? (So the creator defined a hash expression.) SELECT * FROM dba_cluster_hash_expressions;...