Advertisement
ovalerio

Testing the behavior of marking a field as unused in Oracle.

Oct 1st, 2019
969
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- TESTING THE RESULT OF MARKING A TABLE FIELD AS UNUSED IN ORACLE DATABASE
  2.  
  3. -- CREATING A TABLE
  4. CREATE TABLE test_unused
  5. ( field_one NUMBER,
  6.   field_two NUMBER,
  7.   field_three VARCHAR(1));
  8.  
  9. -- POPULATING WITH DATA
  10. INSERT INTO test_unused VALUES ( 1, 10, 'a');
  11. INSERT INTO test_unused VALUES ( 2, 20, 'b');
  12. INSERT INTO test_unused VALUES ( 3, 30, 'c');
  13.  
  14. -- CREATING AN INDEX ON FIELD ONE
  15. CREATE INDEX field_one_ndx ON test_unused(field_one);
  16.  
  17. SELECT index_name, table_name, status
  18. FROM user_indexes WHERE table_name = 'TEST_UNUSED';
  19.  
  20. -- CREATING A SYNONYM OF THE TABLE
  21. CREATE SYNONYM tu FOR test_unused;
  22.  
  23. -- CREATING A VIEW OF THE TABLE  
  24. CREATE VIEW tu_view AS
  25.     SELECT field_one, field_three FROM test_unused;
  26.  
  27. SELECT object_name, status FROM user_objects
  28. WHERE object_type = 'VIEW' AND object_name = 'TU_VIEW';
  29.  
  30. -- QUERYING TABLE CONTENTS USING VIEW AND SYNONYM
  31. SELECT * FROM test_unused;
  32. SELECT * FROM tu_view;
  33. SELECT * FROM tu;
  34.  
  35. -- SETTING FIELD ONE AS UNUSED
  36. ALTER TABLE test_unused SET unused(field_one);
  37.  
  38. -- PROOFING STATUS OF THE TABLE VIEW
  39. SELECT object_name, status FROM user_objects
  40. WHERE object_type = 'VIEW' AND object_name = 'TU_VIEW';
  41.  
  42. -- FIND OUT IF THERE IS STILL AN INDEX
  43. SELECT index_name, table_name, status
  44. FROM user_indexes WHERE table_name = 'TEST_UNUSED';
  45.  
  46. -- SYNONYM SHOULD BE WORKING FINE
  47. SELECT * FROM tu;
  48.  
  49. -- VIEW WILL STATE IT IS INVALID AND ASK TO BE RECOMPILED
  50. SELECT * FROM tu_view;
Advertisement
RAW Paste Data Copied
Advertisement