Oracle 12c Upgrade: 17 – Index and Table Enhancements

Card Set Information

Author:
Tralala
ID:
297511
Filename:
Oracle 12c Upgrade: 17 – Index and Table Enhancements
Updated:
2015-03-04 02:39:21
Tags:
Index Table Enhancements
Folders:
Oracle 12c Upgrade
Description:
Oracle 12c Upgrade: 17 – Index and Table Enhancements
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  1. What is an Invisible column
    One that is only visible when explicitly specified
  2. What happens when a SELECT * is issued on a table with invisible columns?
    They are not returned
  3. TO INSERT into a visible column, what must the syntax be?
    INSERT INTO tab1 (col1, col2, inviscol)...
  4. Where can't invisible columns be defined?
    • External Tables
    • Cluster Tables
    • Temporary Tables
  5. Where are invisible columns found in data_dictionary
    • HIDDEN_COLUMN and USER_GENERATED columns in
    • ADU_TAB_COLUMNS
  6. How is an invisible column defined?
    • CREATE TABLE tab1 (id number, myinvcol1 VARCHAR2(20) INVISIBLE);
    • ALTER TABLE tab1 ADD (myinvcol1 VARCHAR2(20) INVISIBLE);
  7. How do Invisible Columns appear in DESCRIBE?
    • They don't, unless SET COLINVISIBLE ON is declared before the DESCRIBE is run
    • Or must reference data dictionary
  8. What does SET COLINVISIBLE do?
    A switch declaring whether DESCRIBE will show Invisible COlumns
  9. What does CREATE TABLE tab1 (myinvcol1 VARCHAR2(20) INVISIBLE); fail?
    ORA-54039: table must have at least one column that is not invisible
  10. In 12c. if an index is defined on columns that already have an index, what happens
    Generally, the index is created, Multiple Indexes are permitted on the same set of columns
  11. How does the Optimizer deal with deal with multiple index columns?
    • It can't only pick the "best" index
    • Only one index may be visible at any one time
  12. What is the major constraint in multiple index columns?
    Only one index may be visible at any time
  13. To create a second or third index on the same columns, what must happen?
    Make the other indexes invisible
  14. When multiple index columns are used, which index(s) is maintained?
    All of them
  15. What must be ensured to create multiple index columns?
    • At least one of the following is met
    • 1. Indexes are of different types (ie B-Tree/Bitmap not B-Tree/B-Tree)
    • 2. Different types of partitioning (ie B-Tree Global/B-Tree Local is OK)
    • 3. Indexes had different uniqueness (ie B-Tree unique/B-Tree non-unique is OK)
  16. What happens if ALTER INDEX invind_ix1 VISIBLE; is run on a table with a visible index?
    • ORA-14147: There is an existing VISIBLE index defined on the same set of
    • columns.
  17. What does DROP INDEX test1.index ONLINE; allow?
    The index can be dropped while DML operations are occurring on the underlying table
  18. What does ALTER TABLE cust DROP CONSTRAINT cust_email ONLINE; allow?
    To drop the integrity constraint cust_email while DML operations are occurring on the underlying table
  19. To what types of constraint are DROP CONSTRAINT ONLINE operation permitted?
    Integrity Constraint
  20. What happens if ALTER TABLE cust DROP CONSTRAINT ref_orders CASCADE ONLINE; is run?
    • An error is thrown.
    • Can't drop a referential constraint online
    • Can't drop a constraint cascade online
  21. What does ALTER INDEX temp1 UNUSABLE ONLINE; allow?
    The index can be made unusable while DML operations are occurring on the underlying table
  22. Can an invisible column be used as the partitioning key on a partitioned table?
    Yes it can
  23. What does ALTER TABLE emps SET UNUSED (aka) ONLINE; allow?
    The column aka will be be made inaccessible while DML operations are occurring on the table
  24. What does COPY_VPD_OPT parameter in DBMS_REDEFINITION.START_REDEF_TABLE allow?
    TO handle any VPD policies on the table during online redefinition
  25. In Online Redefinition with VPD, what does setting COPY_VPD_OPT to DBMS_REDEFINITION: NONE achieve?
    • This is default
    • This is for tables with no VPD policies
    • If VPD policies exist, an error is generated
  26. In Online Redefinition with VPD, what does setting COPY_VPD_OPT to DBMS_REDEFINITION.CONS_VPD_AUTO achieve?
    Copies the current VPD policies on the table to the new table
  27. In Online Redefinition with VPD, what does setting COPY_VPD_OPT to DBMS_REDEFINITION.CONS_VPD_MANUAL achieve?
    The DBA will manually copy the VPD policies during the process
  28. In 12c, what is now possible with VPD when redefining a table?
    Online Redefinition
  29. When should manually copying the VPD policies during Online Redefinition with VPD be done?
    • When there are column mapping between the current table and the interim table.
    • You want to modify or add VPD policies during the online redefinition.
  30. In Online Redefinition with VPD, that does the DML_LOCK_TIMEOUT parameter of FINISH_REDEF_TABLE allow?
    • The original table will be locked briefly at the end on redefinition
    • Procedure will wait for all pending DML to commit
    • DML_LOCK_TIMEOUT is how long to wait (secs) for all pending DML to commit
  31. What are min and max for DML_LOCK_TIMEOUT?
    • NULL - no automatic time out of the FINISH_REDEF_TABLE procedure
    • 0 - Not waiting
    • 1,000,000 - Wait indefinitely

What would you like to do?

Home > Flashcards > Print Preview