Use DENSE_RANK to select top-n of a table in Oracle
FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
DENSE_RANK () OVER (ORDER BY SALARY DESC) TOPRANK
WHERE TOPRANK <= 3
By default, NULL values means “not present”. Therefore, Oracle indexes will not include NULL values.
If you wonder about how slow your sql query, you must check whether your sql include where condition that use IS NULL.
When you use IS NOT NULL, your sql run normally. But if where condition include IS Null, sql performance slow down,espesially when there are join table condition.
So, i try to change it with use of index that change NULL values to ‘NULL’ string values.
for example, usually we use this,
create index ename_idx on emp (ename);
select * from emp where ename is null;
so, we can change it, this way
create index ename_idx on emp (nvl(ename,’NULL’));
select * from emp where nvl(ename,’NULL’) = ‘NULL’;
then as conclusion, it can said as
1. IS NOT NULL in WHERE clause use an index
2. IS NULL in WHERE clause doesn’t use an index
the solution is create an index with manipulate column with NULL values to another values
(string values or number values or any type of data).
SELECT TO_NUMBER (TO_CHAR (TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1)) + 1), ‘DD’)) FIRST,
TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), ‘DD’)) LAST
SELECT LAST_DAY (ADD_MONTHS (SYSDATE, -1)) + 1 FIRST, LAST_DAY (SYSDATE) LAST