Oracle 12c Upgrade: 17 – Index and Table Enhancements
Home > Preview
The flashcards below were created by user
on FreezingBlue Flashcards.
What is an Invisible column
One that is only visible when explicitly specified
What happens when a SELECT * is issued on a table with invisible columns?
They are not returned
TO INSERT into a visible column, what must the syntax be?
INSERT INTO tab1 (col1, col2, inviscol)...
Where can't invisible columns be defined?
- External Tables
- Cluster Tables
- Temporary Tables
Where are invisible columns found in data_dictionary
- HIDDEN_COLUMN and USER_GENERATED columns in
How is an invisible column defined?
- CREATE TABLE tab1 (id number, myinvcol1 VARCHAR2(20) INVISIBLE);
- ALTER TABLE tab1 ADD (myinvcol1 VARCHAR2(20) INVISIBLE);
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
What does SET COLINVISIBLE do?
A switch declaring whether DESCRIBE will show Invisible COlumns
What does CREATE TABLE tab1 (myinvcol1 VARCHAR2(20) INVISIBLE); fail?
ORA-54039: table must have at least one column that is not invisible
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
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
What is the major constraint in multiple index columns?
Only one index may be visible at any time
To create a second or third index on the same columns, what must happen?
Make the other indexes invisible
When multiple index columns are used, which index(s) is maintained?
All of them
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)
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
What does DROP INDEX test1.index ONLINE; allow?
The index can be dropped while DML operations are occurring on the underlying table
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
To what types of constraint are DROP CONSTRAINT ONLINE operation permitted?
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
What does ALTER INDEX temp1 UNUSABLE ONLINE; allow?
The index can be made unusable while DML operations are occurring on the underlying table
Can an invisible column be used as the partitioning key on a partitioned table?
Yes it can
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
What does COPY_VPD_OPT parameter in DBMS_REDEFINITION.START_REDEF_TABLE allow?
TO handle any VPD policies on the table during online redefinition
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
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
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
In 12c, what is now possible with VPD when redefining a table?
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.
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
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