Select Top-N in Oracle

Use DENSE_RANK to select top-n of a table in Oracle

SELECT *
FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
DENSE_RANK () OVER (ORDER BY SALARY DESC) TOPRANK
FROM HR.EMPLOYEES)
WHERE TOPRANK <= 3

Advertisements

Select Last Row in Oracle

SELECT *
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = (SELECT MAX (EMPLOYEE_ID)
FROM HR.EMPLOYEES);

Manipulate NULL column values for fast sql performance

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).

^_^

First Day and Last Day, First Date and Last Date

SELECT TO_NUMBER (TO_CHAR (TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1)) + 1), ‘DD’)) FIRST,
TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), ‘DD’)) LAST
FROM DUAL

SELECT LAST_DAY (ADD_MONTHS (SYSDATE, -1)) + 1 FIRST, LAST_DAY (SYSDATE) LAST
FROM DUAL

Create or Export data from MDB to DBF

To export data that have been query by “SELECT … FROM” in mdb file to dbf file, you can use this sample query :

SELECT * INTO [dBase IV;database=C:\].newdbf FROM bank