Oracle Interview Questions - 7

What is a hash function?

A hash function returns a value for an input, and the output is generally shorter or more compact than the input

What is an index?

Optional structure associated with tables and clusters. You can create indexes on one or more columns of a table to speed access to data on that table.

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

The B-tree index is the most-used type of index that Oracle provides. It provides fast lookup of rows containing a desired key value. It is not suitable if the column(s) being indexed are of low cardinality (number of distinct values). For those situations, a bitmap index is very useful, but be aware that bitmap indexes are very expensive to update when DML is performed on the indexed table.

What is an Oracle instance?

A system global area (SGA) and the Oracle background processes constitute an Oracle database instance. Every time a database is started, a system global area is allocated and Oracle background processes are started. The SGA is deallocated when the instance shuts down.

After starting an instance, Oracle associates the instance with the specified database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users.

What is integrity constraint?

Declarative method of defining a rule for a column of a table. Integrity constraints enforce the business rules associated with a database and prevent the entry of invalid information into tables.

What is JDBC?

Java Database Connectivity is an API (Applications Programming Interface) that allows Java to send SQL statements to an object-relational database such as Oracle.

What are Oracle logical structures

Logical structures of an Oracle database include tablespaces, schema objects, data blocks, extents, and segments. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures

What is a materialized view

A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.

What is MERGE statement in Oracle?

The MERGE statement (AKA "UPSERT") released in Oracle 9i is possibly one of the most useful ETL-enabling technologies built into the Oracle kernel. It enables us to either UPDATE or INSERT a row into a target table in one statement. You tell Oracle your rules for determining whether a target row should UPDATEd or INSERTed from the source

What is a mutating table?

A table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.

The error is encountered when a row-level trigger accesses the same table on which it is based, while executing.

What is a nested table?

Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column

Describe the normalization.

The process of organizing data to minimize redundancy. Normalization usually involves dividing a database into tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

-First Normal Form (1NF): Each field in a table contains different information. No Repeating groups.

-Second Normal Form (2NF):

Primary key can not be subdivided into separate logical entities.

Every non-key attribute is fully dependent on the key.

-Third Normal Form (3NF)

No functional dependencies on non-key fields.

What is an outer join?

An outer join does not require each record in the two joined tables to have a matching record in the other table

What is Web Ontology Language?

An OWL ontology may include descriptions of classes, along with their related properties and instances. OWL is designed for use by applications that need to process the content of information instead of just presenting information to humans. It facilitates greater machine interpretability of Web content than that supported by XML, RDF, and RDF Schema

What is Oracle package?

A schema object that groups logically related PL/SQL types, items, and subprograms. Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.

What are parsing and its phases? What is soft parse?

The first two phases of the parse are Syntax Check and Semantic Analysis happen for each and every SQL statement within the database. Then Oracle database needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.

If yes, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation (it is soft parse)

What is partitioning?

Partitioning is a method of splitting large tables and indexes into smaller, more manageable pieces.

What are Oracle physical structures?

Physical database structures of an Oracle database include datafiles, redo log files, and control files.

Describe general concepts of PL/SQL.

PL/SQL is Oracle's procedural extension to SQL. With PL/SQL, you can manipulate data with SQL statements, and control program flow with procedural constructs such as IF-THEN and LOOP. You can also declare constants and variables, define procedures and functions, use collections and object types, and trap run-time errors.

Applications written using any of the Oracle programmatic interfaces can call PL/SQL stored procedures and send blocks of PL/SQL code to the server for execution.

Because it runs inside the database, PL/SQL code is very efficient for data-intensive operations, and minimizes network traffic in client/server applications.

Describe PLS_INTEGER datatype.

PLS_INTEGER and BINARY_INTEGER are identical datatypes and are only available in PL/SQL. You cannot create a column in a table with either of these data types. PLS_INTEGER is a highly efficient integer 32-bit data type. You will most commonly see PLS_INETGER (and BINARY_INETGER) in PL/SQL routines as an index variable. An associative array (INDEX BY TABLE) index. Both PLS_INTEGER and BINARY_INTEGER allow whole numbers only. Decimal fractions are rounded to the nearest whole number.

What is a pragma?

Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler like PRAGMA AUTONOMOUS_TRANSACTION