Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
69 Cards in this Set
- Front
- Back
|
What types of data can be stored in a table with LOB columns?
|
-digital audio files
-pdf or other format documents -images |
|
|
What is a good use for index-organized tables?
|
table in which most columns are indexed within the primary key and the others are relatively small and static
|
|
|
which deteriorates quicker, an index-organized table or a regular BTree index?
|
index-organized table
|
|
|
What are the four data-types that can be used for an LOB column?
|
-BLOB
-CLOB -NCLOB -BFILE |
|
|
What does BLOB stand for?
|
Binary Large Object
|
|
|
What does CLOB stand for?
|
Character Large Object
|
|
|
What does NCLOB stand for?
|
Unicode Character Large Object
|
|
|
What is the BFILE data type?
|
a pointer to an externally stored multimedia file
|
|
|
What data type(s) are Internal LOB, and how are they copied?
|
-BLOB, CLOB, NCLOB
-copy semantics, meaning entire LOB with all its data is copied |
|
|
What data type(s) are External LOB and how are they copied?
|
-BFILE
-reference semantics, meaning that only the pointer is actually copied, not the original file |
|
|
What is the name of the special PL/SQL package which simplifies the manipulation of data in a LOB column?
|
DBMS_LOB
|
|
|
what is the name of the pointer to an LOB value for internal and external LOBs?
|
Internal LOBs: LOB locator
External LOBs: BFILE locator |
|
|
why is it advisable to name the LOB data segment?
|
it makes queries on the data dictionary views more readable, using the data segment name you've used instead of a system-generated name
|
|
|
What is the name of the parameter than sets the number of bytes allocated for working with the LOB values?
|
CHUNK parameter
|
|
|
Rather than data blocks, how does Oracle 10g allocate space for out of line LOB values?
|
by chunks
|
|
|
What 3 restrictions are given to chunk size?
|
-Must be a multiple of the database block size
-Must be smaller than the INITIAL and NEXT sizes in either the tablespace or LOB storage clause -Maximum is 32Kb |
|
|
What is the default chunk size?
|
one data block
|
|
|
When is a smaller chunk size more efficient and why?
|
LOBs which have sizes that vary from row to row, because it reduces unused space allocated to LOB storage
|
|
|
When is a larger chunk size more efficient and why?
|
very large LOBs because of a need for high volume I/O
|
|
|
which method of undo is the acceptable and default method, and which one is no longer supported?
|
Automatic undo is the default
Manual undo is not supported |
|
|
When is the only time it is sensible to cache LOB objects?
|
When they are very small
|
|
|
what are two names for the opposite of an index-organized table?
|
heap-organized table
relational table |
|
|
How are the rows in an index-organized structure stored?
|
Every row is stored in a BTree index structure
|
|
|
what is the primary advantage of an index-organized table over a heap-organized table?
|
queries based on the primary key are faster
|
|
|
what is the primary disadvantage of an index-organized table over a heap-organized table and why?
|
Inserts, updates and deletes are slower, because they may cause an imbalance in the BTree structure, requiring Oracle 10g to shuffle rows into different index blocks to rebalance the structure.
|
|
|
What four types of changes can be made to an existing table's structure?
|
-change the storage settings
-reorganize the table online -drop columns -truncate or drop the table |
|
|
what should you do before making changes to a table's structure?
|
analyze the data so that you better understand the table's makeup
|
|
|
why should you analyze tables in a schema periodically?
|
to give the optimizer up-to-date info for optimizing queries and other SQL commands.
|
|
|
How often should you analyze a table?
|
-if data changes every day, analyze weekly or more often
-If data changes slowly, monthly or quarterly analysis suffices |
|
|
What are the two reasons for analyzing a table?
|
-provides accurate info for the cost-optimizer
-gives the DBA more in depth info for making decisions about storage and column setting changes |
|
|
what is a query plan?
|
a list of steps taken to retrieve data for a query
|
|
|
Are queries always executed according to the query plan?
|
No, sometimes the db engine automatically changes it if the optimizer got it all wrong, or if hints are used in the SQL statements.
|
|
|
what can be used to allow you to freeze the method by which a query will be executed?
|
stored outlines
|
|
|
what is the term that implies that a query is always executed in the way which the outline has previously executed a query?
|
Plan stability
|
|
|
What type of optimizer is the only type now available to Oracle 10g?
|
cost-based optimizer
|
|
|
What type of optimizer is no longer available?
|
rule-based optimizer
|
|
|
What command and clause is used to release unused data blocks above the high watermark of a table?
|
ALTER TABLE
DEALLOCATE UNUSED |
|
|
What is the high watermark?
|
The boundary between used data blocks and unused data blocks, marking the last data block that is formatted for data.
|
|
|
WHAT clause can be added to the DEALLOCATE UNUSED clause to tell Oracle 10g how many bytes of storage above the high watermark should be kept within the table?
|
KEEP <nn>
|
|
|
What command and clause is used to changed a table from decompressed to compressed data storage (and vice versa)?
|
ALTER TABLE
COMPRESS (NOCOMPRESS) |
|
|
What command and clause is used to decrease the amount of space used by a table?
|
ALTER TABLE
SHRINK SPACE |
|
|
What command and clause is used to decrease the amount of space used by an index?
|
ALTER INDEX
SHRINK SPACE |
|
|
Does shrinking a table directly affect its attached indexes?
|
No
|
|
|
What are the phases in online table redefinition?
|
1. creation of an interim table
2. redefinition of a table 3. application of the redefinition back to the original table |
|
|
What command and clause is used strictly for designating how Oracle 10g handles an upgrade of a table structure for object tables and relational tables, with object-type columns?
|
ALTER TABLE
(NOT) INCLUDING DATA |
|
|
What four clauses are used with ALTER TABLE to make changes to columns?
|
ADD
MODIFY DROP SET UNUSED |
|
|
What is the advantage and disadvantage of using "SET UNUSED"?
|
-it is faster than DROP COLUMN
-it cannot be undone |
|
|
When will unused columns be dropped?
|
ALTER TABLE command:
1. DROP UNUSED COLUMNS 2. DROP COLUMN (any column being dropped will also drop all unused columns in the table) |
|
|
Name a restriction on changing data type or length of columns, and what can be done to overcome it?
|
-if all existing data cannot be automatically translated into the intended data type or length, then it cannot be done.
-Instead, create a new column, write an UPDATE command to translate the old column data into the new column, drop the old column, and rename the new column. |
|
|
What is the advantage and disadvantage of using DELETE FROM?
|
it is slow but reversible
|
|
|
What is the advantage and disadvantage of using TRUNCATE TABLE command?
|
it is fast, but not reversible
|
|
|
What clause can be used with TRUNCATE TABLE command to keep the space allocated to the table for future inserts and updates?
|
REUSE STORAGE
|
|
|
What is the primary difference between dropping a table and truncating a table?
|
truncating preserves a table's structure, dropping does not.
|
|
|
What clause can be used in a DROP TABLE command to prevent saving the table to the recycle bin?
|
PURGE
|
|
|
What command can be used to recover a table in the recycle bin to its state prior to being dropped?
|
FLASHBACK TABLE
TO BEFORE DROP |
|
|
Why might one want to disable row movement in a table?
|
Because moving a row changes its ROWID which could cause a problem if ROWIDs are stored in a reference table for looking up data
|
|
|
What happens when CACHE is set for a table?
|
Data blocks read are placed in the "most recently used" section of the buffer, keeping them stored in the buffer longer than the default NOCACHE setting.
|
|
|
What are the three clause options for the point of recovery for a flashback table?
|
TO SCN | TIMESTAMP
TO RESTORE POINT BEFORE DROP |
|
|
What option, setting, and command/clause are used to enable encryption?
|
Oracle Advanced Security option
setting an encryption key CREATE | ALTER TABLE USING 'encryption algorithm' |
|
|
What are the two most commonly used table-related data dictionary views?
|
DBA_TABLES
DBA_TAB_COLUMNS |
|
|
What should be run on a regular basis to enable the optimizer to function more efficiently and predict more accurately which plan offers the fastest performance?
|
ANALYZE
or DBMS_STATS |
|
|
What is an implicit commit and what type of command issues one?
|
a commit action caused by something other than the commit statement, causing any uncommitted DML commands to automatically commit
DDL commands (such as CREATE TABLE) |
|
|
Which type of table really helps in range-scans using the primary key, and what is an abbreviation for it?
|
Index-organized tables (IOT)
|
|
|
Which type of table doesn't insert in a particular order, just inserts where it finds space?
|
Heap-organized tables (HOT)
|
|
|
What are two things you should do after doing a massive delete?
|
-reorganize indexes
-update statistics |
|
|
What is the current recommendation concerning the ANALYZE command and why?
|
don't use it... it is no longer the best way, and will be going away in future versions. It locks tables... should not be done in real time.
|
|
|
What affect does TRUNCATE have on the high watermark (hwm)?
|
brings it to zero
|
|
|
What affect does DELETE have on the high watermark (hwm)?
|
leaves it where it was
|
|
|
What affect does the high watermark have on full tablescans?
|
Full tablescans scan all data blocks below the hwm.
|
