databasejournal.com databasejournal.com

Using Index Hints in SQL statements - 'cause we know more about our application than Oracle does

Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements. In this article, we shall see how to specify INDEX hints and what the advantages of...

databasejournal.com databasejournal.com

Materialized Views in Oracle

A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots. A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication...

databasejournal.com databasejournal.com

Get the init.ora parameter value using DBMS_UTILITY

"Init.ora" is a text file, which can be viewed using a text editor. Alternatively, we can use DBMS_UTILITY, a PL/SQL package that comes along with the standard database installation. The DBMS_UTILITY contains many useful functions, including GET_PARAMETER_VALUE. The GET_PARAMETER_VALUE procedure gets "init.ora" parameter values. The parameter name is supplied as an input to the procedure. DBMS_UTILITY.GET_PARAMETER_VALUE ( ...

databasejournal.com databasejournal.com

Recompiling PL/SQL Objects using DBMS_DDL package

In this article, we shall see how we can recompile PL/SQL objects, which are already stored in the database, using the ALTER_COMPILE procedure of DBMS_DDL package. ALTER_COMPILE Procedure This procedure is equivalent to the following SQL statement: ALTER PROCEDURE|FUNCTION|PACKAGE [<schema>.] <name> COMPILE [BODY] Syntax DBMS_DDL.ALTER_COMPILE ( type VARCHAR2, schema VARCHAR2, ...