10 oracle tips part2

 1) comparing dates. How do I compare dates in Oracle?

char VS char
select dateaniv from test2 where to_char(dateaniv,'dd/mm/yyyy')='12/06/1977';

date VS date (better)
select dateaniv from test2 where dateaniv=to_date('12/06/1977','dd/mm/yyyy')

Comparing dates the wrong way

These Oracle date comparisons works, but no index cannot be used because date2 is invalidated with the trunc function (unless you create a function-based index on trunc(date2,'YYYY:MM').

where to_char(DATE1,'YYYY:MM') >= to_char(DATE2,'YYYY:MM')

where trunc(date1,'mm') >= trunc(date2,'mm');

2) UNION and UNION ALL

 There are exhaustive notes on how the Oracle SQL UNION operator is used to merge separate SQL queries, but there is an important difference between UNION and UNION ALL.

The Oracle SQL UNION differs from the Oracle UNION ALL primarily because it does not filter out duplicate rows.

The UNION SQL operator returns only the unique rows that appear in either result, while the UNION ALL operator returns all rows in both queries, including duplicate rows.


3) WITH Clause : Subquery Factoring in Oracle


Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.

-- Non-ANSI Syntax
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc
WHERE  e.deptno = dc.deptno;

-- ANSI Syntax
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e
       JOIN (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc
         ON e.deptno = dc.deptno;


Using a WITH clause this would look like the following.

-- Non-ANSI Syntax
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.deptno = dc.deptno;

-- ANSI Syntax
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e
       JOIN dept_count dc ON e.deptno = dc.deptno;

The difference seems rather insignificant here.

What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.

-- Non-ANSI Syntax
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc1,
       emp m,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

-- ANSI Syntax
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e
       JOIN (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc1
         ON e.deptno = dc1.deptno
       JOIN emp m ON e.mgr = m.empno
       JOIN (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc2
         ON m.deptno = dc2.deptno;

Using the WITH clause this would look like the following.



-- Non-ANSI Syntax
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

-- ANSI Syntax
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e
       JOIN dept_count dc1 ON e.deptno = dc1.deptno
       JOIN emp m ON e.mgr = m.empno
       JOIN dept_count dc2 ON m.deptno = dc2.deptno;

So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.

If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple references to the subquery more efficient.
The MATERIALIZE and INLINE optimizer hints can be used to influence the decision.
The undocumented MATERIALIZE hint tells the optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

WITH dept_count AS (
  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

Even when there is no repetition of SQL, the WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.

WITH
  dept_costs AS (
    SELECT dname, SUM(sal) dept_total
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
    GROUP BY dname),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
    FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;

In the previous example, the main body of the query is very simple, with the complexity hidden in the WITH clause.


4)

inner join or cross join?
cross join= cartesian product
inner join or join (regular join)

5) GROUP BY WITH VARCHAR COLUMN
select dateaniv,sum(to_number(num_users)) from test2
  2  group by dateaniv;

6) differences between CROSS JOIN AND INNER JOIN
cross join = cartesian product
inner join = join
inner join uses ON + where
cross join uses WHERE

7) fake join
SQL> select * from dept d, test2 a
  2  where d.deptno=to_number(num_users||'0');

    DEPTNO DNAME          LOC           DATEANIV   DATEANIV2  NUM_USERS
---------- -------------- ------------- ---------- ---------- ----------
        10 ACCOUNTING     NEW YORK      12/06/1977 01/02/1977 1
        10 ACCOUNTING     NEW YORK      01/01/1977 01/02/1977 1
        10 ACCOUNTING     NEW YORK      01/01/1978            1
        10 ACCOUNTING     NEW YORK      01/12/1977 01/02/1977 1
        10 ACCOUNTING     NEW YORK      01/10/1977 01/02/1977 1
        10 ACCOUNTING     NEW YORK      01/01/1977 01/01/1977 1
        10 ACCOUNTING     NEW YORK      01/01/1977 01/01/1977 1

7 rows selected.

8) null handling


SQL> select * from test2
  2  where dateaniv2 is null;

DATEANIV   DATEANIV2  NUM_USERS
---------- ---------- ----------
01/01/1978            1


SQL> select * from test2
  2  where dateaniv2 is not null;

DATEANIV   DATEANIV2  NUM_USERS
---------- ---------- ----------
12/06/1977 01/02/1977 1
01/01/1977 01/02/1977 1
01/12/1977 01/02/1977 1
01/10/1977 01/02/1977 1
01/01/1977 01/01/1977 1
01/01/1977 01/01/1977 1

6 rows selected.

SQL> select nvl(dateaniv2,'01/01/1900') from test2
  2  where dateaniv2 is null;

NVL(DATEAN
----------
01/01/1900

SQL> select DECODE(dateaniv2,NULL,'01/01/1900') from test2
  2  /

DECODE(DAT
----------


01/01/1900





7 rows selected.


SQL> select nvl2(dateaniv2,dateaniv2,'01/01/1900') from test2
  2  /

NVL2(DATEA
----------
01/02/1977
01/02/1977
01/01/1900
01/02/1977
01/02/1977
01/01/1977
01/01/1977

7 rows selected.


The LNNVL function has been available since at least Oracle 9i, but was undocumented until Oracle 11g. It is used in a where clause to evaluate a condition. If this condition evaluates to false or unknown, it returns true. If the condition evaluates to true, it returns false.

SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id;

        ID COL3
---------- ----------
         1 THREE

1 row selected.

SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 = 'TWO') ORDER BY id;

        ID COL3
---------- ----------
         3 THREE
         4 THREE

2 rows selected.

SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id;

        ID COL3
---------- ----------
         1 THREE
         2 THREE
         3 THREE
         4 THREE

4 rows selected.

NULLIF

The NULLIF function was introduced in Oracle 9i. It accepts two parameters and returns null if both parameters are equal. If they are not equal, the first parameter value is returned.

In our test table the values of COL3 and COL4 are equal in row 4, so we would only expect null returned for that row using the following query.

SQL> SELECT id, NULLIF(col3, col4) AS output FROM null_test_tab ORDER BY id;

        ID OUTPUT
---------- ----------
         1 THREE
         2 THREE
         3 THREE
         4

4 rows selected.

SQL>




9)after creating a user, we need to grant roles:

GRANT CONNECT, RESOURCE, DBA TO books_admin;
GRANT UNLIMITED TABLESPACE TO books_admin;
GRANT CREATE SESSION GRANT ANY PRIVILEGE TO books_admin;
GRANT
  SELECT,
  INSERT,
  UPDATE,
  DELETE
ON
  schema.books
TO
  books_admin;





10) system tables

all tablespaces, all users, dba_x

select table_name, tablespace_name from sys.all_tables;