Saturday, June 28, 2008

OTN - Discussion Forums in a great new look

Dear Readers,

Morning (GMT+3) when I tried to login to OTN-Discussion forum, it was down. But later when I checked it again, it's with a great new look.

With the new look comes new features. Following are the new features I have noticed:

* Great new look

* Add smiley's to messages

* Spell Check

* Formatting

* Reward points

* Save messages

* Tag messages and

* Abuse alerts

It should be easier for the OTN-newbie's to post their questions in a well formatted and readable format.

Enjoy discussions !!!

Tuesday, June 24, 2008

Why my index is not used?

Well, this question keeps popping up now and then. Yesterday, one of my colleagues also came up with this question: "Why is it that Oracle is not using index even though I am selecting less than 10% of data?".

We ran the query with autotrace enabled and the execution plan showed a Full Table Scan. This table contains over 29 Million records and by adding the predicate, result set is reduced to 2.3 Million records, which is 8% of total records.

SQL> set autotrace traceonly exp
SQL> SELECT *
  2      FROM quint_sec_tbl
  3      WHERE quint_type = 'XX06FR';

Execution Plan
----------------------------------------------------------
Plan hash value: 3917650069

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |  3440 |   618K|   454K  (1)| 01:30:55 |
|*  1 |  TABLE ACCESS FULL| QUINT_SEC_TBL      |  3440 |   618K|   454K  (1)| 01:30:55 |
----------------------------------------------------------------------------------------

Cary Millsap's very old (but still valid) article "When to Use an Index" greatly helped me in this regard. This article unveils that Index consideration is based on block selectivity and not on row selectivity.

He also defines what Row Selectivity and Block Selectivity are in the article.

Row Selectivity:

You can define the row selectivity of a given where-clause predicate as the number of rows returned by the predicate (r) divided by the total number of rows in the table (R):


        P(r) =  r / R

Block Selectivity:

You can define the block selectivity of a given where-clause predicate analogously, as the number of data blocks containing at least one row matching the predicate condition (b) divided by the total number of data blocks below the high-water mark (B):


        P(b) = b / B

We can calculate block selectivity and row selectivity using SQL provided in this article. I used this SQL against my query and following are the results:

SQL> @hds
TableOwner : MYPRODUSER
TableName : QUINT_SEC_TBL
ColumnList : QUINT_TYPE
WhereClause: 
PageSize : 100

Table blocks below hwm    Table rows
         (B)                 (R)
---------------------- ----------------
             1,672,704       29,270,757
More: 

       Block selectivity  Block count    Row selectivity     Row count
QUINT_    (pb = b/B)          (b)          (pr = r/R)           (r)
------ ----------------- -------------- ----------------- ----------------
TT34DV            45.03%        753,277            37.99%       11,120,869
FG76SC            44.47%        743,788            13.67%        4,000,205
LH23Q2            42.78%        715,558             9.44%        2,762,284
XX06FR            42.32%        707,894             8.02%        2,346,846
:
:
:

Output of this SQL is sorted in descending order of block selectivity.

Looking at this output, row selectivity is only 8% but to fetch these 8% of rows Oracle has to visit 42% of blocks (block selectivity). That means, nearly half of the table's blocks contain at least one row for which QUINT_TYPE='XX06FR'. Instead of going through the hard path of Index Access, it’s more efficient for the optimizer to do a Full Table Scan.

So, now we know why the index was ignored and a Full Table Scan was preferred.

P.S.: Due to security reasons Username, Table name, column name and column values are modified.

Wednesday, June 18, 2008

SQL for Loan Payment

Dear all,

Inspired by "SQL for Buying a New Car", I thought of converting a very old excel sheet of mine into an SQL query. This excel sheet lists equated monthly installment (EMI), principal amount and the interest amount for duration of loan payment. I feed Rate of Interest, Loan Amount and Period of Loan (in years) to the excel sheet. This was very convenient for me to analyze what part of my EMI is going towards interest payment and what portion goes as principal repayment. It is extremely important to understand what goes for interest and what goes for principal repayment when you are planning for a loan.

Output from Excel sheet:

This can easily be converted into an SQL query using MODEL clause introduced in Oracle 10g.

The output of this query includes a summary of total interest to be paid, principal to be paid and the net payment to be made. Ofcourse, we need to ignore the Summary on "Remaining" column as this is meaningless.

SQL> set line 10000
SQL> set pages 500
SQL> set verify off
SQL>
SQL> column interest  format 999,999,999.99
SQL> column principal format 999,999,999.99
SQL> column remaining format 999,999,999.99
SQL>
SQL> define roi = 10          -- Rate of Interest
SQL> define years = 2         -- Payment Period
SQL> define amt = 100000      -- Loan Amount
SQL> define period = 12       -- Mode of payment.
SQL>                          --     Monthly=12, Bi-monthly=48, Quarterly=4, Half-yearly=2, Yearly=1, Adhoc=n
SQL>
SQL>
SQL> Select inst_no,
  2         sum(new_emi) emi,
  3         sum(new_interest) interest,
  4         sum(principal) principal,
  5         sum(remaining) remaining
  6    From ( Select inst_no + 1 inst_no,
  7                  new_emi,
  8                  interest,
  9                  decode( inst_no + 1, &period*&years, interest - remaining, interest)  new_interest,
 10                  decode( inst_no + 1, &period*&years, principal + remaining, principal)  principal,
 11                  decode( inst_no + 1, &period*&years, 0, remaining) remaining
 12             From  (select  rownum inst_no, roi, yrs, amt, period,
 13                            round( (roi * amt/(period * 100)) /
 14                                   (1 - power((1 + roi/(period * 100)), -yrs * period)), 2) emi
 15                      From (select &roi roi, &years yrs, &amt amt, &period period
 16                              From Dual) )
 17    MODEL
 18    DIMENSION BY ( inst_no )
 19    MEASURES     ( roi, yrs, amt, emi , period, 0 as new_emi, 0 as interest, 0 as principal, 0 as remaining)
 20    RULES
 21      UPSERT
 22      ITERATE (1000)
 23      (
 24        new_emi[ITERATION_NUMBER] = emi[1],
 25        interest[ITERATION_NUMBER]  = round(&roi/(period[1] * 100) *
 26                                      nvl(remaining[ITERATION_NUMBER-1], amt[1]), 2),
 27        principal[ITERATION_NUMBER] = emi[1] - interest[cv()],
 28        remaining[ITERATION_NUMBER] = nvl(remaining[ITERATION_NUMBER-1], amt[1]) - principal[cv()]))
 29  Where interest > 0
 30  Group by rollup( inst_no)
 31  order by  inst_no;

   INST_NO        EMI        INTEREST       PRINCIPAL       REMAINING
---------- ---------- --------------- --------------- ---------------
         1    4614.49          833.33        3,781.16       96,218.84
         2    4614.49          801.82        3,812.67       92,406.17
         3    4614.49          770.05        3,844.44       88,561.73
         4    4614.49          738.01        3,876.48       84,685.25
         5    4614.49          705.71        3,908.78       80,776.47
         6    4614.49          673.14        3,941.35       76,835.12
         7    4614.49          640.29        3,974.20       72,860.92
         8    4614.49          607.17        4,007.32       68,853.60
         9    4614.49          573.78        4,040.71       64,812.89
        10    4614.49          540.11        4,074.38       60,738.51
        11    4614.49          506.15        4,108.34       56,630.17
        12    4614.49          471.92        4,142.57       52,487.60
        13    4614.49          437.40        4,177.09       48,310.51
        14    4614.49          402.59        4,211.90       44,098.61
        15    4614.49          367.49        4,247.00       39,851.61
        16    4614.49          332.10        4,282.39       35,569.22
        17    4614.49          296.41        4,318.08       31,251.14
        18    4614.49          260.43        4,354.06       26,897.08
        19    4614.49          224.14        4,390.35       22,506.73
        20    4614.49          187.56        4,426.93       18,079.80
        21    4614.49          150.67        4,463.82       13,615.98
        22    4614.49          113.47        4,501.02        9,114.96
        23    4614.49           75.96        4,538.53        4,576.43
        24    4614.49           38.06        4,576.43             .00
            110747.76       10,747.76      100,000.00    1,189,739.34

25 rows selected.

SQL>

Results of this query can be verified here.

References:

1. Loan Calculator.

2. How to calculate EMI.

Happy reading.

Friday, June 06, 2008

Create a Unique Constraint Ignoring Existing Duplicate Data

Hi,

There was an interesting post on OTN forums, where the OP asks:

Can we create a unique index on a column having duplicate values which can enforce the uniqueness for future values.?

Well, this is possible as Oracle is rich in features. It can be achieved by creating a unique constraint with DEFERRABLE and NOVALIDATE keywords. By default, constraints are created as NON DEFERRABLE and VALIDATE. When we create a constraint with DEFERRABLE and NOVALIDATE, we instruct Oracle to defer checking existing data for uniqueness but always check the new or modified rows.

Here's how we can do this:
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t
  2  ( x int );

Table created.

SQL>
SQL>
SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1);

1 row created.

SQL>
SQL> alter table t add constraint t_uk unique (x)
  2    deferrable initially immediate novalidate;

Table altered.

SQL> select * from t;

         X
----------
         1
         1

SQL>
SQL> alter table t modify constraint t_uk enable novalidate;

Table altered.

SQL>
SQL> insert into t values ( 1);
insert into t values ( 1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T_UK) violated


SQL> select * from t;

         X
----------
         1
         1

SQL> insert into t values (2);

1 row created.

SQL>

Happy reading !!!