1) What is dense ranking? 2) Difference between Substring and Instring? 3) Cas

1) What is dense ranking?
2) Difference between Substring and Instring?
3) Case and Decode?
4) Pseudo column?
5) View vs Materialized views?
6) SQl performance tuning?

Question Posted / suresh kumar

2 Answers
5816 Views
Accenture, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

1) What is dense ranking? 2) Difference between Substring and Instring? 3) Case and Decode? 4) Ps..

Answer / ssk

1. DENSE_RANK is an Analytical Function which doesn't leave
gaps between the records. Coming to Rank function it leaves
gaps between the records.

Eg:

(RANK) (DENSE_RANK)

Sno Marks Ranks Sno Marks Ranks
----- ----- ----- ----- ----- -----
1 100 1 1 100 1
2 100 1 2 100 1
3 200 3 3 200 2
4 300 4 4 300 3
5 300 4 5 300 3
6 400 6 6 400 4

2. Substr will displays the text from position to TO position.
Instr returns the position of the character.

Eg: SELECT SUBSTR('ORACLE',2,4) FROM DUAL;
O/P : RAC

SELECT INSTR('ORACLE IS A DATABASE','A',1,2) from dual;
O/P : 11

3. PSEUDO Column : A column which is not an actual column in
the table. Eg : ROWNUM,ROWID,SYSDATE,USER,UID..

Is This Answer Correct ?

9 Yes

0 No

1) What is dense ranking? 2) Difference between Substring and Instring? 3) Case and Decode? 4) Ps..

Answer / usingh

1.>> Both are the analytical function and these function gives rank based on some column.

RANK : RANK gives you the ranking within your ordered portion. Ties are assigned the same rank, with the next ranking skipped.

RANK
1
2
3
3 [missed the 4th Rank]
5
6

DENSE_RANK : In this the Ranks are consecutive. No ranks are skipped if there are same ranks.

DENSE_RANK
1
2
3
3 [does not miss the Rank]
4
5

2.>> SUBSTR : The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long.
Example: SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL; =>CDEF
SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL; => CDEF
The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this
occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters.
Example: INSTR('Tech on the net','e') => 2; the first occurrence of 'e'
INSTR('Tech on the net', 'e', 1, 2)=> 11; the second occurrence of 'e'

3.>> DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc.
DECODE works with expressions which are scalar values. CASE can work with predicates and subqueries in searchable form.
CASE executes faster in the optimizer than does DECODE.
CASE is a statement while DECODE is a function.
In decode we cant use for range(like 2000 to 4000 etc.), but case we can.
DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.
DECODE can be used Only inside SQL statement.... 5. But CASE can be used any where even as a parametr of a function/procedure
CASE handles NULL values differently.

4.>>A pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but you cannot insert, update, or delete their values.
EX : ROWNUM,ROWID,SYSDATE,USER,UID, SEQUENCE... ect

ROWID : ROWID is alphanumaric value which represents an address of required.
- ROWID is generated by the oracle for each record inserted in the table.
- ROWID Removed when the record get deleted from table.

ROWNUM : It is serial no. or sequential no. which is genetated as when the record retried.
- ROWNUM keeps on chenging.
- It is generated on the Runtime.

5.>> - View will not store any data whereas Materialized view will store data.
- View is mainly used for security purpose, Materialized view is used for performance sake.
- Base table is droped, the view will not be accessible, whereas Materialized view will be still be accessible.
- DML Operation can perform directly in view, whereas in Materialized view can not perform directly.

Is This Answer Correct ?

1 Yes

0 No

Post New Answer