1Z0-515 Online Practice Questions and Answers

Correct Answer: D

Explanation:

GOTOs cannot be used within the Oracle Data Integrator.

References:

Correct Answer: B

Explanation:

Substring functions are not analytic.

Correct Answer: C

Explanation: As its name suggests, the query result cache is used to store the results of SQL queries for re-use in subsequent executions. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs. References:

Correct Answer: A

Explanation:

Partition-wise joins can be full or partial. Oracle decides which type of join to use. A full partition-wise join

divides a large join into smaller joins between a pair of partitions from the two joined tables. To use this

feature, you must equipartition both tables on their join keys, or use reference partitioning.

Oracle Database can perform partial partition-wise joins only in parallel. Unlike full partition-wise joins,

partial partition-wise joins require you to partition only one table on the join key, not both tables.

Note: Partition-wise joins reduce query response time by minimizing the amount of data exchanged among

parallel execution servers when joins execute in parallel. This significantly reduces response time and

improves the use of both CPU and memory resources. In Oracle Real Application Clusters (RAC)

environments, partition-wise joins also avoid or at least limit the data traffic over the interconnect, which is

the key to achieving good scalability for massive join operations.

References:

Correct Answer: A

Explanation: CTAS refers to a CREATE TABLE AS statement - a new table is created and populated with row from a specified query.

The most common uses of CTAS are in these scenarios:

*

Creating a table identical to another table in structure, but with a filter criteria applied to its data.

*

Creating a table with small structural differences from an existing table.

For best performance use Direct Path Load. The conventional path uses standard insert statements whereas the direct path loader loads directly into the Oracle data files and creates blocks in Oracle database block format. During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.

References:

Correct Answer: A

Explanation:

Table storage requirements would increase, but the benefits are huge.

Oracle partitioning is a divide-and-conquer approach to improving Oracle maintenance and SQL performance. Anyone with un-partitioned databases over 500 gigabytes is courting disaster. Databases become unmanageable, and serious problems occur:

*

SQL may perform poorly - Without Oracle partitioning, SQL queries with full-table scans take hours to complete. In a full scan, the smaller the Oracle partition, the faster the performance. Also, index range scans become inefficient.

*

Recovery - Files recovery takes days, not minutes

*

Maintenance - Rebuilding indexes (important to re-claim space and improve performance)

Oracle partitioning has many benefits to improve performance and manageability:

*

Stable

*

Robust

*

Faster backups

*

Less overhead

*

Easier management Maintenance of Oracle partitioned tables is improved because maintenance can be focused on particular portions of tables. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks. (not D)

*

Faster SQL Oracle is partition-aware, and some SQL may improve is speed by several orders of magnitude (over 100x faster).

-Index range scans Oracle partitioning physically sequences rows in index-order causing a dramatic improvement (over 10x faster) in the speed of partition-key scans.

-Full-table scans Oracle partition pruning only accesses those data blocks required by the query.

-Table joins Oracle partition-wise joins take the specific sub-set of the query partitions, causing huge speed improvements on nested loop and hash joins. (not C)

-You can also improve the performance of massive join operations when large amounts of data (for example, several million rows) are joined together by using partition-wise joins. (not B)

-Updates Oracle parallel query for partitions improves batch load speed.

References:

Correct Answer: AB

Explanation:

Your applications sometime send repetitive queries to the database. To improve the response time of

repetitive queries, results of queries, query fragments, and PL/SQL functions can be cached in memory. A

result cache stores the results of queries shared across all sessions. When these queries are executed

repeatedly, the results are retrieved directly from the cache memory.

You must annotate a query or query fragment with a result cache hint to indicate that results are to be

stored in the query result cache.

The query result set can be cached in the following ways:

*

Server-side Cache

*

Client Result Cache

Oracle Database 11g Release 1 (11.1) provides support for server-side Result Set caching for both JDBC types. The server-side result cache is used to cache the results of the current queries, query fragments, and PL/SQL functions in memory and then to use the cached results in future executions of the query, query fragment, or PL/SQL function. The cached results reside in the result cache memory portion of the SGA. A cached result is automatically invalidated whenever a database object used in its creation is successfully modified. The server-side caching can be of the following two types:

*

SQL Query Result Cache (A)

*

PL/SQL Function Result Cache (B)

Correct Answer: A

Explanation:

Knowledge modules (KMs) in Oracle Data Integrator are components that implement reusable

transformation and ELT (extract, load, and transform) strategies across different technologies.

References:

Correct Answer: C

Explanation: A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.

Correct Answer: A

Explanation:

Without any redundancy (high performance is the only goal) you can use 100% of the disk space to data.

With disk mirroring (with lower performance) you can use 50% of the disk space to data.