Oracle 1Z0-515 Online Practice Questions and Exam Preparation

Oracle 1Z0-515 Online Questions & Answers

• Question 51:

  How can you implement near real time data integration with Oracle Data Integrator?

  A. By accessing Change Data Capture records from logs
  B. By using Exchange Partition
  C. By mining Oracle UNDO segments
  D. By reading operating system logs
  A. By accessing Change Data Capture records from logs

  ---

  Conventional "Extract, Transform, Load" (ETL) tools closely intermix data transformation rules with integration process procedures, requiring the development of both data transformations and data flow. Oracle Data Integrator (ODI) takes a different approach to integration by clearly separating the declarative rules (the "what") from the actual implementation (the "how"). With ODI, declarative rules describing mappings and transformations are defined graphically, through a dragand-drop interface, and stored independently from the implementation. ODI automatically generates the data flow, which can be fine- tuned if required. This innovative approach for declarative design has also been applied to ODI's framework for Changed Data Capture. ODI's CDC moves only changed data to the target systems and can be integrated with Oracle GoldenGate, thereby enabling the kind of real time integration that businesses require.

• Question 52:

  What are three advantages provided by proper partitioning in a data warehouse?

  A. Partition pruning will occur
  B. Faster sorting
  C. Efficient parallel joins
  D. Efficient data loading
  E. Reduced disk usage
  A. Partition pruning will occur
  C. Efficient parallel joins
  D. Efficient data loading

  ---

  There are three major advantages of partitioning.

  *

  Partition Pruning - Oracle only accesses a limited set of table partitions if the FROM and WHERE clause permit it to.

  *

  Partition-wise Joins - Where two tables that have compatible partitioning schemes are joined , Oracle improves the efficiency of parallel operations by performing the join between individual partitions of the tables.

  *

  Manageability - Partitioning allows DDL operations on a large subset of table rows with some element of commonality defined through the partitioning type.

• Question 53:

  Indentify the dimension that appears most often in queries in a data warehouse.

  A. Product dimension
  B. Time dimension
  C. Cost dimension
  D. Location dimension
  B. Time dimension

  ---

  In a data warehouse, a dimension is a data element that categorizes each item in a data set into non- overlapping regions. A data warehouse dimension provides the means to "slice and dice" data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric measures. For example, "Customer", "Date", and "Product" are all dimensions that could be applied meaningfully to a sales receipt. A dimensional data element is similar to a categorical variable in statistics. The primary function of dimensions is threefold: to provide filtering, grouping and labeling. For example, in a data warehouse where each person is categorized as having a gender of male, female or unknown, a user of the data warehouse would then be able to filter or categorize each presentation or report by either filtering based on the gender dimension or displaying results broken out by the gender.

• Question 54:

  Which is NOT among Oracle SQL Analytic functions included in Oracle Database 11g?

  A. Ranking functions
  B. Substring functions
  C. Window aggregate functions
  D. LAG/LEAD functions
  B. Substring functions

  ---

  Substring functions are not analytic.

• Question 55:

  What are Oracle Data Integrator templates used for?

  A. To model SAP applications
  B. To define how to transform data
  C. As reports to monitor ETL activity
  D. None of these
  B. To define how to transform data

  ---

  Oracle Data Integrator streamlines the highperformance movement and transformation of data between disparate systems in batch, real-time, synchronous, and asynchronous modes. Knowledge Modules are at the core of the Oracle Data Integrator architecture. They make all Oracle Data Integrator processes modular, flexible, and extensible. Knowledge Modules implement the actual data flows and define the templates for generating code across the multiple systems involved in each process. Knowledge Modules are generic, because they allow data flows to be generated regardless of the transformation rules. And they are highly specific, because the code they generate and the integration strategy they implement are finely tuned for a given technology. Oracle Data Integrator provides a comprehensive library of Knowledge Modules, which can be tailored to implement existing best practices (for example, for highest performance, for adhering to corporate standards, or for specific vertical know-how). By helping companies capture and reuse technical expertise and best practices, Oracle Data Integrator's Knowledge Module framework reduces the cost of ownership. It also enables metadata-driven extensibility of product functionality to meet the most demanding data integration challenges.

• Question 56:

  Identify the statement about Oracle OLAP that is NOT true.

  A. Oracle OLAP cubes are stored in the Oracle relational database
  B. Oracle OLAP uses standard Oracle database security.
  C. Meta data for Oracle OLAP is accessible in an external data dictionary
  D. Oracle OLAP can be deployed using RAC.
  C. Meta data for Oracle OLAP is accessible in an external data dictionary

  ---

  All metadata for cubes and dimensions is stored in the Oracle database.

• Question 57:

  What is the difference between an ETL (Extraction Transformation Load) approach and an ELT (Extraction Load Transformation) approach to data integration? Select one.

  A. ETL can operate between heterogeneous data sources.
  B. ELT requires a separate transformation server.
  C. ELT transforms data on the target server.
  D. ELT cannot be used for incremental data loading.
  C. ELT transforms data on the target server.

  ---

  There are two approaches to consider for data integration: ELT and ETL. The difference between ETL and ELT lies in the environment in which the data transformations are applied. In traditional ETL, the transformation takes place when the data is en route from the source to the target system. In ELT, the data is loaded into the target system, and then transformed within the target system environment.

• Question 58:

  For which task would you NOT use Oracle Data Mining?

  A. Predicting customer behavior
  B. Associating factors with a business issue
  C. Determining associations within a population
  D. Reducing the amount of data used in a data warehouse
  D. Reducing the amount of data used in a data warehouse

  ---

  Data mining does not reduce the amount of data in the warehouse.

  Note:

  Data mining (the analysis step of the knowledge discovery in databases process, or KDD), a relatively young and interdisciplinary field of computer science is the process of discovering new patterns from large data sets involving methods at

  the intersection of artificial intelligence, machine learning, statistics and database systems. The overall goal of the data mining process is to extract knowledge from a data set in a human-understandable structure and besides the raw analysis

  step involves database and data management aspects, data preprocessing, model and inference considerations, interestingness metrics, complexity considerations, post-processing of found structure, visualization and online updating.

• Question 59:

  What are two ways in which query performance can be improved with partitioning?

  A. Partition pruning
  B. Partition optimization
  C. Partition compression
  D. Partition-wise joins
  A. Partition pruning
  D. Partition-wise joins

  ---

  A: Even when you don't name a specific partition in a SQL statement, the fact that a table is partitioned might still influence the manner in which the statement accesses the table. When a SQL statement accesses one or more partitioned tables, the Oracle optimizer attempts to use the information in the WHERE clause to eliminate some of the partitions from consideration during statement execution. This process, called partition pruning, speeds statement execution by ignoring any partitions that cannot satisfy the statement's WHERE clause. To do so, the optimizer uses information from the table definition combined with information from the statement's WHERE clause.

  D: A partition wise join is a join between (for simplicity) two tables that are partitioned on the same column with the same partitioning scheme. In shared nothing this is effectively hard partitioning locating data on a specific node / storage combo. In Oracle is is logical partitioning.

  If you now join the two tables on that partitioned column you can break up the join in smaller joins exactly along the partitions in the data. Since they are partitioned (grouped) into the same buckets, all values required to do the join live in the equivalent bucket on either sides. No need to talk to anyone else, no need to redistribute data to anyone else... in short, the optimal join method for parallel processing of two large data sets.

• Question 60:

  Identify the type of refresh that is NOT supported by materialized views.

  A. Deferred
  B. Incremental
  C. Full
  D. Heuristic
  D. Heuristic

  ---

  Use the CREATE MATERIALIZED VIEW statement to create a materialized view. A materialized view is a database object that contains the results of a query.

  Incorrect answer:

  A: Specify DEFERRED to indicate that the materialized view is to be populated by the next REFRESH operation.

  B: Oracle Database uses the default index to speed up incremental (FAST) refresh of the materialized view.

  C: By default, Oracle Database creates a primary key materialized view with refresh on demand only. If a materialized view log exists on the table, then the column can be altered to be capable of fast refresh. If no such log exists, then only full refresh of the column is possible.