Oracle 1Z0-873 Study Guide, Money Back Guarantee Oracle 1Z0-873 Exam Dumps Covers All Key Points
Flydumps Oracle 1Z0-873 exam questions which contain almost 100% correct answers are tested and approved by senior Oracle lecturers and experts. They have been devoting themselves to providing candidates with the best study materials to make sure what they get are valuable.
QUESTION 25
Suppose you install a server with a service name of "MySQL5" rather than the default. What sections in the option files will the server use for configuration?
A. [service]
B. [MySQL5]
C. [service MySQL5]
D. [mysqld]
E. [mysqld MySQL5]
Correct Answer: BD Explanation
Explanation/Reference:
Explanation:
24.2.
If you install a server using a service name other than MySQL and do not specify a --defaults-file option,
the server reads options in the standard option files from the [my_service] group in addition to options from
the [mysqld] group.
QUESTION 26
Assume you compile MySQL from source and invoke configure with the following options.
--with-charset=latin1 --with-extra-charsets=utf8,ucs2
Compared to a standard binary installation that contains many more character sets, which of the following
statements is/are true?
A. The compiled version will use less disk space, because only a few character sets will be installed on disk.
B. The compiled version will use less memory, because only a few character sets will be loaded by the server.
C. The compiled version will use less file handles, because only a few files need to be opened when the server is started.
Correct Answer: AB Explanation
Explanation/Reference:
Explanation: 27.1. Performance Issues To reduce the amount of disk space required by character sets for your MySQL installation and the amount of memory used by the server as it runs, don't select unneeded character sets when you configure MySQL. This requires that you compile MySQL from source rather than using a precompiled binary distribution.
QUESTION 27
Which of the following can be influenced by the choice of character set?
A. Disk usage when storing data.
B. Syntax when writing queries involving JOINs
C. The time taken to read & write table rows on disk.
D. Memory usage.
Correct Answer: ACD Explanation
Explanation/Reference:
QUESTION 28
Suppose you have a column in which most records are going to be between 30 and 32 characters. Which of the following column types would be most efficient?
A. VARCHAR
B. CHAR
C. TEXT
D. Either VARCHAR or CHAR
Correct Answer: B Explanation
Explanation/Reference:
Explanation:
27.2. Choosing Data Types for Character Columns If stored string values all have the same length, use a fixed-length type rather than a variable-length type. To store values that are always 32 characters long, CHAR(32) requires 32 characters each, whereas VARCHAR
(32) requires 32 characters each, plus an extra byte to store the length. In this case, VARCHAR requires one byte more per value than CHAR.
QUESTION 29
You are using a multi-byte character set with variable-length encoding. You need to store records whose values are always 20 characters. Which of the following column types would be the most efficient to use in terms of storage space?
A. CHAR
B. VARCHAR
C. The storage requirements for CHAR or VARCHAR would be the same
Correct Answer: B Explanation
Explanation/Reference:
Explanation:
27.2. Choosing Data Types for Character Columns For multi-byte character sets that have variable-length encoding, a variable-length data type may be appropriate even if stored values always have the same number of characters. The utf8 character set uses one to three bytes per characters. For fixed-length data types, three bytes per character must always be allocated to allow for the possibility that every character will require the "widest" encoding. Thus, CHAR
(32) requires 96 bytes, even if most stored values contain 32 single-byte characters. For variable-length data types, only as much storage is allocated as required. In a VARCHAR(32) column, a 32-character string that consists entirely of three-byte characters requires 96 bytes plus a length byte, whereas it requires only 32 bytes plus a length byte if the string consists entirely of single-byte characters.
QUESTION 30
Another user has gotten a lock using GET_LOCK. You inquire on the status of the lock by using
A. RELEASE_LOCK
B. IS_FREE_LOCK
C. IS_USED_LOCK
D. Another GET_LOCK
E. LOCK TABLES
Correct Answer: BC Explanation
Explanation/Reference:
Explanation:
28.3. Advisory Locking
Two other functions are available for checking the status of advisory locks:
IS_FREE_LOCK(lock_name) returns 1 if the name is not locked, 0 if it is locked, and NULL if an error
occurs.
IS_USED_LOCK(lock_name) returns the connection ID of the client that holds the lock on the name, or
NULL if the name is not locked.
QUESTION 31
Another user has issued the statement LOCK TABLE pets FOR WRITE You can...
A. Update table pets
B. SELECT from table pets
C. UPDATE and SELECT from table pets
D. None of the above
Correct Answer: D Explanation
Explanation/Reference:
Explanation:
28.2. Explicit Table Locking When a table is locked for reading, other clients can read from the table at the same time, but no client can write to it. Once acquired, only the client holding the write lock can read from or write to the table. Other clients can neither read from nor write to it. No other client can lock the table for either reading or writing.
QUESTION 32
Index analysis and optimization using ANALYZE and OPTIMIZE statements should...
A. generally never be run manually
B. be run once the table reaches 100,000 rows or above
C. be run when more than 5% of the rows are changed by a single statement
D. be run when EXPLAIN SELECT shows that an inordinate amount of rows is expected to be read during query execution
E. be run when you suspect that a table is heavily fragmented
Correct Answer: D Explanation Explanation/Reference:
Explanation:
30.2. SQL Statements for Table Maintenance The ANALYZE TABLE statement updates a table with information about the distribution of key values in the table. This information is used by the optimizer to make better choices about query execution plans. This statement works for MyISAM and InnoDB tables. The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting it. This involves reclaiming unused space resulting from deletes and updates, and coalescing records that have become split and stored non-contiguously. OPTIMIZE TABLE also sorts the index pages if they are out of order and updates the index statistics.
QUESTION 33
Which of the following best describes the scope of explicitly and implicitly set locks?
A. Explicitly set locks may span several commands.
B. Implicitly set locks may span several commands.
C. Implicitly set locks will span only one statement or transaction.
D. Explicitly set locks will span only one statement or transaction.
Correct Answer: A Explanation
Explanation/Reference:
Explanation:
28.1. Locking Concepts For a client that does nothing special to acquire locks, the MySQL server implicitly acquires locks as necessary to process the client's statements safely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. Explicit locking may be necessary when a client needs to perform an operation that spans multiple statements and that must not be interrupted by other clients.
QUESTION 34
When you acquire an advisory lock using GET_LOCK(), the lock is released if
A. You issue another GET_LOCK() statement
B. You issue a RELEASE_LOCK() statement
C. Your connection to the server terminates
D. None of the above
Correct Answer: ABC Explanation
Explanation/Reference:
Explanation:
28.3. Advisory Locking A client that has acquired an advisory lock can release it by calling RELEASE_LOCK(). An advisory lock also is released if the client makes another call to GET_LOCK() or closes its connection to the server.
QUESTION 35
which of the following best describes why table locking is often not desirable compared to page or row locking?
A. Table locks can have deadlocks.
B. Table locks create concurrency issues.
C. Table locks prevent other clients from making any changes to the table until released.
D. Table locks can cause data corruption issues if more than one client tries to make changes while locked.
Correct Answer: B Explanation
Explanation/Reference:
Explanation:
28.1. Locking Concepts Table locking is not as desirable as page or row locking for concurrency in a mixed read/write environment. A table lock prevents other clients from making any changes to the table, even if the client that holds the lock is not accessing the parts of the table that other clients want to modify. With page and row locks, a client that locks a page or row does not prevent changes by other clients to other pages or rows. Deadlock cannot occur with table locking as it can with page or row locking.
QUESTION 36
With MyISAM table locking, deadlocks do not occur because:
A. All tables to be locked are sorted in an internally defined order
B. If a table is locked with a read lock and a write lock, the write lock is set before the read lock
C. Tables are locked one table at a time until the thread gets all the locks
D. None of the above
Correct Answer: D Explanation
Explanation/Reference:
Explanation:
28.1. Locking Concepts With table locking, the server can determine what locks are needed and acquire them before executing a statement, so deadlock never occurs.
QUESTION 37
Which of the following (series of) statements will leave the three tables A, B and C locked for reading, writing and reading respectively once all statements have been executed?
A. mysql> LOCK TABLES A;mysql> LOCK TABLES B;mysql> LOCK TABLES C;
B. mysql> LOCK TABLES A READ;mysql> LOCK TABLES B WRITE;mysql> LOCK TABLES C READ;
C. mysql> LOCK TABLES A READ, B WRITE, C READ;
D. LOCK TABLES A, B, C READ, WRITE, READ;
Correct Answer: C Explanation
Explanation/Reference:
Explanation:
28.2. Explicit Table Locking If you need to use multiple tables while holding an explicit lock, you must lock all of them at the same time because you cannot use any unlocked tables while you hold explicit locks. Also, you must lock all the tables with a single LOCK TABLES statement. LOCK TABLES releases any locks that you already hold, so you cannot issue it multiple times to acquire multiple locks.
QUESTION 38
You want to lock the three tables a, b and c, and issue the following statements:
mysql> LOCK TABLES a READ; mysql> LOCK TABLES b READ; mysql> LOCK TABLES c READ; Which tables are now locked?
A. Tables a, b and c
B. Table a only
C. Table c only
D. None of the tables are locked
Correct Answer: C Explanation
Explanation/Reference:
Explanation:
28.2. Explicit Table Locking If you need to use multiple tables while holding an explicit lock, you must lock all of them at the same time because you cannot use any unlocked tables while you hold explicit locks. Also, you must lock all the tables with a single LOCK TABLES statement. LOCK TABLES releases any locks that you already hold, so you cannot issue it multiple times to acquire multiple locks.
QUESTION 39
When choosing a storage engine for each of your tables, which things are to consider?
A. Locking Characteristics: Some storage engines lock on row level, some on page level, some on table level.
B. Transactions support: Some storage engines support transactions, some don't.
C. Storage media: Some storage engines store data on disk, some in memory.
D. Licenses: Some storage engines cannot be used in commercial environments, others can.
E. Backup methods: Some storage engines support online backup and point in time recovery, some don't.
Correct Answer: ABC Explanation
Explanation/Reference:
Explanation:
Point in time recovery is not a feature of a storage engine. http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
QUESTION 40
Which of the following variables specify the default storage engine to use if no storage engine is specified when creating a table?
A. default_engine
B. storage_default
C. storage_engine
D. default_storage_engine
Correct Answer: C Explanation
Explanation/Reference:
Explanation:
29.1. MySQL Storage Engines If you create a table without using an ENGINE option to specify a storage engine explicitly, the MySQL server creates the table using the default engine, which is given by the value of the storage_engine system variable.
QUESTION 41
Which of the following methods can be used to determine the storage engine of your table named "Country"?
A. SHOW CREATE TABLE Country
B. SHOW STORAGE ENGINE Country
C. SHOWTABLE STATUS LIKE 'Country'
D. SELECT ENGINEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'Country'
Correct Answer: ACD Explanation
Explanation/Reference:
QUESTION 42
Which of the following storage engines cannot be disabled?
A. InnoDB
B. MyISAM
C. FEDERATED
D. MEMORY
E. MERGE
Correct Answer: B Explanation
Explanation/Reference:
Explanation:
29.2. The MyISAM Engine MyISAM was introduced in MySQL 3.23.0 and has been the built-in default storage engine since (although you can change the default engine at server startup or while the server runs). Because MyISAM is the built-in default engine, it is always available and cannot be disabled.
QUESTION 43
Which of the following features are supported by MyISAM tables?
A. Foreign key constraints
B. Transactions
C. Auto_increment columns
D. Fulltext indexes
E. Assembly of multiple MyISAM tables to a MERGE table
F. Row level locking
G. Table level locking
Correct Answer: CDEG Explanation
Explanation/Reference:
Explanation:
29.2. The MyISAM Engine
MyISAM has the most flexible AUTO_INCREMENT column handling of all the storage engines.
MyISAM tables can be used to set up MERGE tables.
MyISAM tables can be converted into fast, compressed, read-only tables to save space. MyISAM supports
FULLTEXT searching and spatial data types.
QUESTION 44
Which of the following actions are performed during an RPM installation of the MySQL server package?
A. Setup a mysql user
All most all IT professionals are familiar with the Oracle 1Z0-873 exam and dream to have that top most demanding certification. This is the top level certification from CISCO that is accepted universally. You can get your desired career which you dreamed with passing Oracle 1Z0-873 test and getting the certificate.
