DENSE_RANK

Purpose: DENSE_RANK computes the rank of a row in an ordered group of rows and returns the
rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest
rank value is the number of unique values returned by the query. Rank values are not
skipped in the event of ties. Rows with equal values for the ranking criteria receive the
same rank. This function is useful for top-N and bottom-N reporting.
This function accepts as arguments any numeric datatype and returns NUMBER.

As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical
row identified by the arguments of the function with respect to a given sort
specification. The arguments of the function must all evaluate to constant
expressions within each aggregate group, because they identify a single row
within each group. The constant argument expressions and the expressions in the
order_by_clause of the aggregate match by position. Therefore, the number of
arguments must be the same and types must be compatible.
■ As an analytic function, DENSE_RANK computes the rank of each row returned
from a query with respect to the other rows, based on the values of the value_
exprs in the order_by_clause.

RANK

Purpose: RANK calculates the rank of a value in a group of values. The return type is NUMBER
Rows with equal values for the ranking criteria receive the same rank. Oracle Database
then adds the number of tied rows to the tied rank to calculate the next rank.
Therefore, the ranks may not be consecutive numbers. This function is useful for top-N
and bottom-N reporting.

Query Tactics

https://www.databasejournal.com/features/oracle/article.php/3912486/Top-5-Query-Tactics-Questions-for-the-PLSQL-Developer-Job-Interview.htm


http://yoginijoshi.blogspot.com/2010/03/oracle-plsql-interview-questions.html

Oracle Database SQL Tuning Guide

https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL357

How to check compiler log in SQL developer?

Press: Control-shift-L 


https://www.softwaretestinghelp.com/plsql-interview-questions/---IMP 30 PLSQL faqs

Materialized Views

SYNTEX:

CREATE MATERIALIZED VIEW VIEW_NAME

BUILD [IMMIDIATE | DEFFERED]

REFREASH [FAST | COMPLETE | FORCE]

ON [COMMIT | DEMAND]

WITH PRIMARY KEY [ROWID]

[[ENABLE | DISABLE] QUERY REWRITE]

AS

SELECT …..;

BUILD Clause

                -IMMEDIATE: The materialized view is populated immediately

                -DEFFRRED: The materialized view is populated on the first requested refresh

Refresh Types

                -Refresh fast

                -Refresh Complete

                -Refresh force

                - Never Refresh: MV will not refresh at any given time. It will create at the time MV create

Refresh Complete:  

It is the simplest way and expensive if there are more rows

It will recreate all the rows in MV by default. ROWID values will get changed

Oracle DB will re execute the MV query to refresh the MV

Refresh syntax:

Exec dbms_mview.refresh(‘mv’,’c’);

Here ‘mv’ is materialized view name and ‘c’ represents as refresh completely.

Refresh COMPLETE on COMIT:

ORA-12054: Cannot set the ON COMMIT refresh attribute for the MV.

To avoid this error ORA-12054, use MV WITH key word.

For example:

By default DEMAND MV refresh

CREATE MATERIALIZED VIEW MKVIEW

REFREASH COMPLETE ON COMMIT

WITH PRIMARY KEY /ROWID

As select * from EMP;

Any DML changes happed in base table, without refresh changes will be populated in the MV. That is the advantage of Refresh complete on commit command.

BUILD DEFERRED:

The value will not be populated in the MV but it will be populated on first requested refresh.

MV scheduled refresh:

 The MV will refresh based on the scheduled time. User/ developer will not use on COMMIT or refresh of the MV.

Syntax:

CREATE MATERIALIZED VIEW MKVIEW

REFREASH COMPLETE

START WITH (SYSDATE)

NEXT (SYSDATE +1/1440) ---24 HRS * 60 MINS ( Refresh will happen at this time)

As

Select * from EMP;

ROWID:

It’s unique ID, Oracle system itself will create and unique and 18 byte length

ENABLE QUERY REWRITE:

It will be used if we are performing any aggregate function in advance

For Example:

CREATE MATERIALIZED VIEW MKVIEW

REFREASH COMPLETE

ENABLE QUERY REWRITE

As

SELECT DEPT_ID, SUM (SAL) FROM EMP GROUP BY DEPT_ID HAVING SUM (SAL)>1000;

INCREMENTAL / REFRESH FAST:

MV will be fast refreshed only if there is any change in the base table. To maintain history of the base table, it requires a log table. It is known as MV log. It is named as MLOG$_<BASE_TABLE>.

If you specify refresh fast , then create MV will check for the MV log for each of the tables referenced by the MV, else it will fail to create.

When DML changes are made to master table data, Oracle database stores rows describing those changes in the MV log to refresh MVs based on the master table.

MV logs are located in master database in the same schema as master table. A master table can have only one MV log defined on it.

Refresh fast will perform refresh according to the changes occurred in the master table.

Syntax:

CREATE MATERIALIZED VIEW MKVIEW

REFREASH FAST

As

SELECT * FROM EMP;

It will through error because of there is no MV logs are present for the master table. So user has to create MV logs.

Syntax:

CREATE MATERIALIZED VIEW LOG ON EMP

WITH PRIMARY KEY or ROWID;

CREATE MATERIALIZED VIEW MKVIEW

REFREASH FAST

WITH ROWID ( By default it will refer PRIMARY KEY as we don’t have PRIMARY KEY ON base table so we have to use with clause with ROWID)

As

SELECT * FROM EMP;

Note: ROWID will not change REFRESH FAST FOR unchanged records

Benefits of Refresh Fast:

It will not create entire new result set using new ROWID like refresh complete

Values will be updated in MV without changing the ROWID

Once MV is fast refreshed then entries mentioned in the MV log will be removed

Refresh FORCE:

First it will try to do fast refresh. If MV log is either corrupted or not available then it will fail to refresh it fast and it will do refresh complete.

Syntax:

CREATE MATERIALIZED VIEW MKVIEW

REFREASH FORCE

WITH PRIMARY KEY

As

SELECT * FROM EMP;

EXEC DBMS_MVIEW.REFRESH (‘MKVIEW’,’?’); (here ‘?’ represents, we don’t know whether it will refresh either refresh fast or complete)

First time it will behave as refresh complete after then it will behave like refresh fast because of first time we don’t have log files.

Never Refresh:

In this Method, MV will never get refreshed. We include never refresh statement while crating MV. Later if you want to refresh/sync with the base table then you have to alter MV and then refresh it using DBMS_MVIEW.REFRESH.

Never Refresh will always use ‘REFRESH COMPLETE’ only.

Syntax:

CREATE MATERIALIZED VIEW MKVIEW

NEVER REFRESH

As

SELECT * FROM EMP;

Shell Scripting!!!!!!

https://www.youtube.com/watch?v=Y5mnE64WEDA&list=PL7B7FA4E693D8E790&index=2