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;
45 Cards in this Set
- Front
- Back
|
What command squeezes out unused space?
|
COALESCE
|
|
|
What is Relational table?
|
They are the most common form of table in an Oracle DB.
Also, they reside in a single segment within a single tablespace, uless the table is partitioned. |
|
|
What is Index-Organized table?
|
They always have a primary key and use the key to arrange the physical order of the rows.
|
|
|
What is an Object Table?
|
They have rows made up of an object type, which in turn is made up of attributes. It is similar to a relational table, except that each row is a single unit of data defined by an object type.
|
|
|
What is an object type?
|
It is a set of column definitions that is defined ahead of time for use in objects.
Ex. the object type's columns are called attributes. |
|
|
What is Temporary Table?
|
It is seen only by one user, and created for private data.
|
|
|
What is External Tables?
|
These are read-only and are used to retrieve data from outside the database.
|
|
|
What is Nested table?
|
It is a table within a single column or within an attribute of an object table.
|
|
|
What is a XML table?
|
It has a single column of the XML type datatype and contains XML formated data.
|
|
|
What is a cluster?
|
A cluster is not a table, but a group of tables stored together as if they were one table.
|
|
|
What is a table?
|
It is made up of one segment, which contains one or more extents, each containing a group of data block.
|
|
|
What is Locally Managed tablespaces?
|
They use the storage of a table but control the extent sizes and how are inserted and updates automatically.
|
|
|
List the Data Block Componets:
|
1. Common and variable header
2. Table directory 3. Row directory 4. Free space 5. Row data (Study Figure 7-1 on page 297) |
|
|
What is a chained row?
|
A row that spans multiple blocks because it is too large to fit into one block is a Chained row.
|
|
|
What is Migrated row?
|
It is a row that has been moved from one block to another (retaining only a ROWID pointer address in the original block) is known as a migrated row.
|
|
|
Explain why rows are migrated?
|
Rows are migrated as a result of an update where the rows data no longer fits within the original block.
|
|
|
What is INITRANS?
|
It allocates space in a data block's header specifying how many slots are held for concurrent transactions accessing a data block.
|
|
|
What is Physical ROWID?
|
Identifies a row by its physical location in a datafile.
It can be extended or restricted. |
|
|
What is Logical ROWID?
|
It is used to locate rows in an indexed-organized table using the primary key.
|
|
|
When is a ROWID created?
|
It is created only when an index on a table is created, or when you query the pseudoculumn ROWID, in which case the ROWID is constructed.
|
|
|
Can a ROWID be change by a user?
|
NO.
|
|
|
How can you query the row's ROWID value?
|
With the ROWID pseudocolumn.
|
|
|
Why use ROWID?
|
Because, ROWID provide the fastest possible database access, however be careful because it is unreliable.
|
|
|
What is Row made up of?
|
A row is made up of row header and column data.
|
|
|
What is Common and variable header?
|
Identifying information, such as the type of block and block location.
|
|
|
What is Table Directory?
|
Information about the table that has data in the block.
|
|
|
What is Row directory?
|
A list of row identifiers for rows stored in the block. This grows as more rows are inserted into the block.
|
|
|
What is Free space?
|
Bytes of storage space left unallocated. This shrinks as rows are inserted or updated with more data, causing the row directory and the row data to consume more space.
|
|
|
What is Row data?
|
Bytes of storage used for rows inserted or updated in the data block.
|
|
|
What is a chained row?
|
It is a row split between blocks. A migrated row is transferred in its entirety to another block, retaining only a pointer to the new block in the original block.
|
|
|
List 3 Storage parameters for a table and their default settings?
|
1. PCTFREE=10
2. PCTUSED=40 3. INITRANS=1 (1 for table and 2 for indexes). |
|
|
What are contiguous block?
|
These are blocks that are located physically adjancent to each other on disk. This makes for more rapid accessing related data because it is on disk in the same place.
|
|
|
Where is the ROWID stored?
|
Only stored when an index on the table is created, or when you query the pseudocolumn ROWID, in which case the ROWID is constructed.
|
|
|
What is another example of pseudocolumn?
|
Sysdate, which returns the current data and time.
|
|
|
What is a Constraints?
|
are rules that define data integrity for a column or group of columns.
|
|
|
What is a Row Header?
|
This stores the number of columns contained in the column data area, some overhead, and the ROWID pointing to a chained or migrated row (if any).
|
|
|
What is a Rowid?
|
contains the physical or logical address of the row.
|
|
|
List five ways to partitioning.
|
1. Range
2. Hash 3. List 4. Composite range-hash 5. Composite range-list |
|
|
Explain partitioned table?
|
- You can partition any table (except if part of a cluster)
- Partitions can be stored in separate tablespaces |
|
|
Can a Partitioned table store each partition in a separate tablespace?
|
YES.
|
|
|
Another name for object table?
|
Object table instance.
|
|
|
List the different kinds of tables.
|
1. Relational table
2. Index-organized table 3. Object tables 4. Temporary table 5. External table 6. Nested table 7. XML table 8. Cluster 9. Partitions |
|
|
How should the BUFFER POOL setting be set for data that is frequently used?
|
BUFFER POOL keeps causes data blocks to stay inside the buffer longer than normal... until it is full, at which time the least recently used blocks are replaced first.
|
|
|
How should the BUFFER POOL setting be set for infrequently used tables?
|
BUFFER POOL RECYCLE keeps the used table's data blocks from overwriting data blocks in the DEFAULT buffer.
|
|
|
What two storage parameters can be set to control row migration and how are they managed?
|
(PCTFREE + PCTUSED) <= 100
Higher PCTFREE setting keeps rows from migrating, lower allows more space to be used up (less wasted space). Higher PCTUSED reuses space faster. |
