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
P(b) = b / B
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.
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 !!!