Thursday, November 17, 2011

RATIO_TO_REPORT Function For Percentage Calculation

Conventional way:
select table_name, sum(num_rows),sum(num_rows)/tot_rows*100 "PERCENTAGE"
  from dba_tables, (select sum(num_rows) tot_rows from dba_tables)
  where owner = 'SCOTT'
 group by table_name , tot_rows

Elapsed: 00:00:00.43

Using function:
select table_name, sum(num_rows), (ratio_to_report(sum(num_rows)) over())*100 "PERCENTAGE" from dba_tables
where owner = 'SCOTT'
group by table_name

Elapsed: 00:00:00.16

· The query produces the same answer but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer.

·Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.

·We achieved 27 msec just by using this built in function!


Counting Negative and Postive Numbers in Table Row


You have a result set with positive, negative and neutral values like this:

select * from result;

    KEY               VAL
    ---------- ----------
    first          -1.222
    second           -.03
    third            -.02
    fourth              0
    fifth             1.2
    sixth             .03

Now, you want to count how many negative, postitive and neutral values you have.
        3 negative
        1 no change
        2 positive

Solution:

Use the Oracle SIGN ( n ) function which returns -1 if n < 0. If n = 0, then the function returns 0. If n > 0, then SIGN returns 1.
    create table result (
      key    varchar2(10),
      val    number
    );

    insert into result (key,val) values ('first',-1.222);
    insert into result (key,val) values ('second',-.03);
    insert into result (key,val) values ('third',-.02);
    insert into result (key,val) values ('fourth',0);
    insert into result (key,val) values ('fifth',1.2);
    insert into result (key,val) values ('sixth',.03);

    select count(decode(sign(val),-1,-1)) neg,
           count(decode(sign(val),0,0)) zero,
           count(decode(sign(val),1,1)) pos
      from result;

           NEG       ZERO        POS
    ---------- ---------- ----------
             3          1          2


Oracle WITH Clause: Referencing Same Subquery Multiple Times


In complex queries that process the same subquery multiple times, is tempting to store the subquery results in a temporary table and perform additional queries against the temporary table. The WITH clause lets you factor out the subquery, give it a name, then reference that name multiple times within the original complex query.

This technique lets the optimizer choose how to deal with the subquery results -- whether to create a temporary table or inline it as a view.

For example, the following query joins two tables and computes the aggregate SUM(SAL) more than once. The bold text represents the parts of the query that are repeated.

Regular Subquery
----------------------------------------
    SELECT dname, SUM(sal) AS dept_total
     FROM emp, dept
      WHERE emp.deptno = dept.deptno
      GROUP BY dname HAVING
        SUM(sal) >
       (
          SELECT SUM(sal) * 1/3
            FROM emp, dept
           WHERE emp.deptno = dept.deptno
       )
       ORDER BY SUM(sal) DESC;

    DNAME          DEPT_TOTAL
    -------------- ----------
    RESEARCH            10875
    SALES                9400


    Execution Plan
    ------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   SORT (ORDER BY)
       2    1     FILTER
       3    2       SORT (GROUP BY)
       4    3         MERGE JOIN
       5    4           SORT (JOIN)
       6    5             TABLE ACCESS (FULL) OF 'DEPT'
       7    4           SORT (JOIN)
       8    7             TABLE ACCESS (FULL) OF 'EMP'
       9    2       SORT (AGGREGATE)
      10    9         MERGE JOIN
      11   10           SORT (JOIN)
      12   11             TABLE ACCESS (FULL) OF 'DEPT'
      13   10           SORT (JOIN)
      14   13             TABLE ACCESS (FULL) OF 'EMP'

You can improve the query by doing the subquery once, and referencing it at the appropriate points in the main query. The bold text represents the common parts of the subquery, and the places where the subquery is referenced.

    WITH
    summary AS
    (
     SELECT dname, SUM(sal) AS dept_total
     FROM emp, dept
     WHERE emp.deptno = dept.deptno
     GROUP BY dname
    )
    SELECT dname, dept_total
     FROM summary
      WHERE dept_total >
    (
      SELECT SUM(dept_total) * 1/3
     FROM summary
    )
    ORDER BY dept_total DESC;

    DNAME          DEPT_TOTAL
    -------------- ----------
    RESEARCH            10875
    SALES                9400


    Execution Plan
    ---------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    2     RECURSIVE EXECUTION OF 'SYS_LE_2_0'
       2    0   TEMP TABLE TRANSFORMATION
       3    2     SORT (ORDER BY)
       4    3       FILTER
       5    4         VIEW
       6    5           TABLE ACCESS (FULL) OF 'SYS_TEMP'
       7    4         SORT (AGGREGATE)
       8    7           VIEW
       9    8             TABLE ACCESS (FULL) OF 'SYS_TEMP'

How much Redo-Log does a Transaction Generates

Oracle Dictionary is a wonderful Pool of many hidden resorces. In the Oracle System Statistic Table V$SYSSTAT you can find the Number of blocks written to the Redo. This can be found in the Attribute STATISTIC# = 71. If you measure this value before and after the transaction, you can approximately find out how much Redo-Log the transaction generates. Of course this is approximately, because there may exist other transactions at the same time.

SQL> select * from v$sysstat where statistic#=71;

STATISTIC#  NAME
----------  ----------------------------------------
71               physical writes direct
Example
SQL> select value from v$sysstat where statistic#=71;
    VALUE
---------
      436

SQL> create table myhelp as select * from system.help;
Table created.

SQL> select value from v$sysstat where statistic#=71;
    VALUE
---------
      20000

So the bytes of logs generated by the specified query would be

(20000-436)/512 = 38.2109375 BYTES

How To Start an Oracle Database with Corrupted or Lost dbfile


Getting an error message telling one datafile is lost or corrupted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/db/test.dbf'

If the database is in ARCHIVELOG mode with a working online backup concept then, recover the database and everything is OK. But what can we do, if the database is in NOARCHIVELOG mode and no backup is present ?

a). If the database is still running do the following
1). Switch the damaged datafile to the RECOVER status 
ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;

SELECT file#, status,bytes,name FROM v$datafile;

  FILE# STATUS       BYTES      NAME
------- -------- --------- ------------------------------
      1   SYSTEM     104857600  /u01/db/SOL3/sys/SOL3_sys1.dbf
      2   
RECOVER     2097152     /u01/db/test.dbf

2). Stop and Start the database to verify that the database can be started without ' test.dbf '. 
SHUTDOWN IMMEDIATE;
STARTUP;

3). DROP the tablespace to which the datafile belongs
DROP TABLESPACE test INCLUDING CONTENTS;

b). If the database is not running do the following
The database can only be stopped with SHUTDOWN ABORT with a damaged or lost datafile and the datafile is still in ONLINE mode. Therefore it's better to switch the datafile to the RECOVER status as shown above before stopping the database. However there is a way to switch the datafile to the RECOVER status when the database is stopped.
1). Mount the database and switch the damaged datafile to the RECOVER status

STARTUP MOUNT;
ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
ALTER DATABASE OPEN;

2). DROP the tablespace to which the datafile belongs
DROP TABLESPACE test INCLUDING CONTENTS;

3). Stop and Start the database to verify that the database can be started without ‘test.dbf '. 
SHUTDOWN IMMEDIATE;
STARTUP;

Important note
·         The term ' OFFLINE DROP ' is misleading; it is not possible to drop a datafile with this command. The only purpose of this command is to startup a database with damaged or missing datafile and the databae is in NOARCHIVELOG mode.
·         The command ' ALTER DATABASE DATAFILE ... OFFLINE DROP ' changes the datafile status from ONLINE to RECOVER. In this mode, the database can be started even if the datafile is not present.
·         If space management (e.g. CREATE TABLE ..) occurs for this datafile, Oracle will try to allocate space in this ' dropped ' datafile and fails.
·         The only way to drop a datafile is to drop the tablespace to which the datafile belongs. Unfortunately you will lose data if you don't have an actual backup.