Oracle Interview Questions - 8
Sunday, May 24, 2009
What is precedence in Oracle?
It is the order in which Oracle evaluates different conditions in the same expression
What is a predicate in SQL?
Syntax that specifies a subset of rows to be returned. Predicates are specified in the WHERE clause of a SQL statement.
What is a primary key?
The column or set of columns included in the definition of a table's PRIMARY KEY constraint. A primary key's values uniquely identify the rows in a table. Only one primary key can be defined for each table.
What is a Primary Key Constraint?
This constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and Oracle will create a unique index on the target primary key.
What is Resource Definition Framework (RDF)?
A set of rules (a sort of language) for creating descriptions of information, especially information available on the World Wide Web. RDF could be used to describe a collection of books, or artists, or a collection of web pages as in the RSS data format which uses RDF to create machine-readable summaries of web sites.
RDF is also used in XPFE applications to define the relationships between different collections of elements, for example RDF could be used to define the relationship between the data in a database and the way that data is displayed to a user.
What is a redo log?
A set of files that protect altered database data in memory that has not been written to the datafiles. The redo log can consist of two parts: the online redo log and the archived redo log.
What is a REF CURSOR? Compare strong and week ref cursor types.
A REF CURSOR is a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
A strong ref cursor type definition specifies a return type, a weak definition does not.
Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.
What is referential datatype?
A variable can have either a simple or "scalar" datatype, such as NUMBER or VARCHAR2. Alternately, a variable can have a referential datatype that uses reference to a table column to derive its datatype
What is a schema?
Collection of logical structures of data, or schema objects
What is Semantic Web?
The Semantic Web is an extension of the current Web that will allow you to find, share, and combine information more easily. It relies on machine-readable information and metadata expressed in RDF.
What is a Shared pool?
Portion of the system global area that contains shared memory constructs such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database.
flush shared pool – clear all SQL statements that are in the Shared Pool Area
What is SQL trace?
The SQL Trace facility provides performance information on individual SQL statements. It generates the following statistics for each statement:
-Parse, execute, and fetch counts
-Physical reads and logical reads
-Username under which each parse occurred
You can enable the SQL Trace facility for a session or for an instance. When the SQL Trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files.
What is SQLCODE?
The function SQLCODE returns the number code of the most recent exception
What is statistics?
Statistics calculate the data distribution and storage characteristics of tables, columns, indexes, and partitions. The cost-based optimization approach uses these statistics to calculate the selectivity of predicates and to estimate the cost of each execution plan. Selectivity is the fraction of rows in a table that the SQL statement's predicate chooses. The optimizer uses the selectivity of a predicate to estimate the cost of a particular access method and to determine the optimal join order and join method.
The statistics are stored in the data dictionary and can be exported from one database and imported into another
What is a table function?
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement.
Example: select * from table(a_function(a_parm));
What is tkprof?
TKPROF program formats the contents of the trace file and place the output into a readable output file.
What difference is between TRANSLATE and REPLACE functions?
TRANSLATE('char','search_str','replace_str')
Replace every occurrence of search_str with replace_str
Unlike REPLACE() if replace_str is NULL the function returns NULL
What is an UNIQUE key constraint?
A data integrity constraint requiring that every value in a column or set of columns (key) be unique--that is, no two rows of a table have duplicate values in a specified column or set of columns.
A logical representation of another table or combination of tables
0 comments:
Post a Comment