Subquery versus Joins:
The main difference between subquery and join is:
• subquery is faster when we have to retrieve data from large number of tables. Because it becomes tedious to join more tables. Join is faster to retrieve data from database when we have less number of tables
• subquery retrieve the data depending on certain condition or manipulation in inner query. Where as joins will join the entire data depending on the conditions.
While using Order by Operator:
• Don't use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead.
While Using AND operator:
If you have a WHERE clause that includes expressions connected by two or more AND operators, Oracle Server will evaluate them from left to right in the order they are written.
Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time. If both parts of an AND expression are equally likely being false, put the least complex AND expression first.
Using Where Clause:
• When you have a choice of using the IN or the EXISTS clause in your SQL, Use the EXISTS clause, as it is usually more efficient and its performance is good.
• If you use LIKE in your WHERE clause, try to use one or more leading character in the clause, if at all possible For example, use: LIKE 'm%' not: LIKE '%m‘.
• If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an index to perform the query.
• If the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a table scan must be run, which will reduce performance and will take more time.
• When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause, as it is much more efficient.
• For example:
SELECT emp_id, emp_name FROM employee WHERE emp_id IN (1000, 1001, 1002, 1003, 1004);
is much less efficient than this:
SELECT emp_id, emp_name FROM employee WHERE emp_id BETWEEN 1000 and 1004;
If your SELECT statement includes an IN operator along with a list of values to be tested in the query :-
• Order the list of values so that the most frequently found values are placed at the first of the list, and the less frequently found values are placed at the end of the list.
• This can speed performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes.
Returning data from Select Statement:
• Do not return the column data that you do no need in you queries.
• You should avoid using SELECT * when returning data from tables.
• Select only the columns that you need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column.
• In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance.
Using the UNION statement:
• The UNION statement performs equivalent of a SELECT DISTINCT on the final result set.
• UNION takes the results of two recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. Example :-
SELECT column_name1, column_name2 FROM table_name1 WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2 FROM table_name1 WHERE column_name2 = some_value;
• You can write the same query as:
SELECT DISTINCT column_name1, column_name2 FROM table_name1 WHERE column_name1 = some_value OR column_name2 = some_value
It will boost the performance of your sql queries.
Wednesday, July 29, 2009
Wednesday, July 15, 2009
ORACLE Recyclebin
There are two recyclebin views present in ORACLE :
1. USER_RECYCLEBIN and
2. DBA_RECYCLEBIN.
For convenience, when we are firing queries at RECYCLEBIN which is a public synonym that points to USER_RECYCLEBIN. The RECYCLEBIN is enabled by default in 10g, but one can turn it on or off with the RECYCLEBIN init parameter, at the session level or system level.
Let’s say RECYCLEBIN is enabled, any tables that we drop are not actually getting deleted. Instead, Oracle just renames that particular table which has been dropped and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.
For example, consider this simple table:
SQL> Create table test (num number);
Table created.
SQL> Insert into test values (1);
1 row created.
SQL> Insert into test values (2);
1 row created.
SQL> Insert into test values (3);
1 row created.
SQL> Select * from test;
NUM
----------
1
2
3
If the RECYCLEBIN init parameter is set to ON (the default in 10g), then the dropped table will placed in the RECYCLEBIN:
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_DROP CAN_PURGE DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- ------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:50:33
What has happened to the table is that when we dropped it that has got renamed. The table data is still there and can be queried just like a normal table:
Note: the table name must be in quotes (“ “)
SQL> select * from "BIN$brwK19W3F/7gRAADuhyf0w==$0";
NUM
----------
1
2
3
Since the table data is still there, we can "undrop" the table. This operation is known as a "flashback drop". This operation will simply rename the BIN$... table to its original name:
SQL> Flashback table "BIN$brwK19W3F/7gRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
NUM
----------
1
2
3
It's important to know that after you've dropped a table, it has only been renamed; the table segments still exist there in your tablespace, unchanged, occupying space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
Object_name original_name type can_undrop can_purge droptime
--------------------------------------------------------------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-09:12:26:22
SQL> purge table “BIN$brwK19W3F/7gRAADuhyf0w==$0” ;
Table purged.
SQL> select * from recyclebin;
No rows returned.
You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER.
Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.
Dealing with dropped table versions
Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin also. For example, if we create and drop the TEST table twice, we'll have two versions in the recyclebin:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values('Version 2');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime fro
m recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brrGkpUUF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:05
BIN$brrGkpUVF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:31
Now query the two dropped tables to verify that whether they are different:
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we are going to issue a FLASHBACK DROP command for TEST, which version will Oracle restore?
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 2
Oracle will always restore the most recent version of the dropped object. If we want the previous version then we can select the new BIN$... name of the TEST from the recyclebin and we can perform flashback query operation on the BIN$... table as follows. This will restore the required version of the TEST table.
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we want to restore the Version 1 table then we need to capture the BIN$... name of the TEST table i.e.,
BIN$brrGkpUUF7vgRAADuhyf0w==$0
Now issue the flash back query as follows it will restore the required version of the TEST table.
SQL> flashback table "BIN$brrGkpUUF7vgRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 1
Going with DEPENDENT OBJECTS
In a modern RDBMS, Most will have indexes, constraints, and/or triggers some stand alone. When a table is dropped ORALCE drops the dependent objects associated with it. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, will be invalidated.)
For example:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> create index ind_test_col on test(test_col);
Index created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WxF/7gRAADuhyf0w==$0 IND_TEST_COL INDEX NO YES 2009-07-15:10:18:46 53649 53650
BIN$brwK19WyF/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:18:46 53649 53649
SQL> flashback table test to before drop;
Flashback complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
no rows selected
The RECYCLEBIN views have a few other columns that make the relationship between TEST and IND_TEST_COL clear:
The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TEST_COL is 53650. Note the value of the BASE_OBJECT column for IND_TEST_COL: 53649, the object number of the associated version of the TEST table.
If we FLASHBACK DROP the TEST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:
SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------
BIN$brwK19WxF/7gRAADuhyf0w==$0
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WzF/7gRAADuhyf0w==$1 BIN$brwK19WxF/7gRAADuhyf0w==$0 INDEX NO YES 2009-07-15:10:43:33
BIN$brwK19W0F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:43:33 53649 53649
Note the values in the CAN_UNDROP and CAN_PURGE columns for the index. An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:
SQL> purge index "BIN$brwK19WzF/7gRAADuhyf0w==$1";
Index purged.
Now, if we restore the table, it will be restored without the index:
SQL> select index_name from user_indexes where table_name='TEST';
no rows selected
LIMITATIONS
A few types of dependent objects are not handled like the simple index above.
o Bitmap join indexes are not put in the recyclebin when their base table is dropped, and not retrieved when the table is restored with FLASHBACK DROP.
o The same applies for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
o Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.
If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.
1. USER_RECYCLEBIN and
2. DBA_RECYCLEBIN.
For convenience, when we are firing queries at RECYCLEBIN which is a public synonym that points to USER_RECYCLEBIN. The RECYCLEBIN is enabled by default in 10g, but one can turn it on or off with the RECYCLEBIN init parameter, at the session level or system level.
Let’s say RECYCLEBIN is enabled, any tables that we drop are not actually getting deleted. Instead, Oracle just renames that particular table which has been dropped and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.
For example, consider this simple table:
SQL> Create table test (num number);
Table created.
SQL> Insert into test values (1);
1 row created.
SQL> Insert into test values (2);
1 row created.
SQL> Insert into test values (3);
1 row created.
SQL> Select * from test;
NUM
----------
1
2
3
If the RECYCLEBIN init parameter is set to ON (the default in 10g), then the dropped table will placed in the RECYCLEBIN:
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_DROP CAN_PURGE DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- ------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:50:33
What has happened to the table is that when we dropped it that has got renamed. The table data is still there and can be queried just like a normal table:
Note: the table name must be in quotes (“ “)
SQL> select * from "BIN$brwK19W3F/7gRAADuhyf0w==$0";
NUM
----------
1
2
3
Since the table data is still there, we can "undrop" the table. This operation is known as a "flashback drop". This operation will simply rename the BIN$... table to its original name:
SQL> Flashback table "BIN$brwK19W3F/7gRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
NUM
----------
1
2
3
It's important to know that after you've dropped a table, it has only been renamed; the table segments still exist there in your tablespace, unchanged, occupying space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
Object_name original_name type can_undrop can_purge droptime
--------------------------------------------------------------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-09:12:26:22
SQL> purge table “BIN$brwK19W3F/7gRAADuhyf0w==$0” ;
Table purged.
SQL> select * from recyclebin;
No rows returned.
You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER.
Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.
Dealing with dropped table versions
Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin also. For example, if we create and drop the TEST table twice, we'll have two versions in the recyclebin:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values('Version 2');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime fro
m recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brrGkpUUF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:05
BIN$brrGkpUVF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:31
Now query the two dropped tables to verify that whether they are different:
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we are going to issue a FLASHBACK DROP command for TEST, which version will Oracle restore?
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 2
Oracle will always restore the most recent version of the dropped object. If we want the previous version then we can select the new BIN$... name of the TEST from the recyclebin and we can perform flashback query operation on the BIN$... table as follows. This will restore the required version of the TEST table.
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we want to restore the Version 1 table then we need to capture the BIN$... name of the TEST table i.e.,
BIN$brrGkpUUF7vgRAADuhyf0w==$0
Now issue the flash back query as follows it will restore the required version of the TEST table.
SQL> flashback table "BIN$brrGkpUUF7vgRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 1
Going with DEPENDENT OBJECTS
In a modern RDBMS, Most will have indexes, constraints, and/or triggers some stand alone. When a table is dropped ORALCE drops the dependent objects associated with it. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, will be invalidated.)
For example:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> create index ind_test_col on test(test_col);
Index created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WxF/7gRAADuhyf0w==$0 IND_TEST_COL INDEX NO YES 2009-07-15:10:18:46 53649 53650
BIN$brwK19WyF/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:18:46 53649 53649
SQL> flashback table test to before drop;
Flashback complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
no rows selected
The RECYCLEBIN views have a few other columns that make the relationship between TEST and IND_TEST_COL clear:
The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TEST_COL is 53650. Note the value of the BASE_OBJECT column for IND_TEST_COL: 53649, the object number of the associated version of the TEST table.
If we FLASHBACK DROP the TEST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:
SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------
BIN$brwK19WxF/7gRAADuhyf0w==$0
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WzF/7gRAADuhyf0w==$1 BIN$brwK19WxF/7gRAADuhyf0w==$0 INDEX NO YES 2009-07-15:10:43:33
BIN$brwK19W0F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:43:33 53649 53649
Note the values in the CAN_UNDROP and CAN_PURGE columns for the index. An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:
SQL> purge index "BIN$brwK19WzF/7gRAADuhyf0w==$1";
Index purged.
Now, if we restore the table, it will be restored without the index:
SQL> select index_name from user_indexes where table_name='TEST';
no rows selected
LIMITATIONS
A few types of dependent objects are not handled like the simple index above.
o Bitmap join indexes are not put in the recyclebin when their base table is dropped, and not retrieved when the table is restored with FLASHBACK DROP.
o The same applies for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
o Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.
If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.
Thursday, July 9, 2009
Finding the “Floating” calendar dates:
Using the SQL we can find the floating calendar dates, such as the second Tuesday of the month, with modular arithmetic and brute force reasoning.
To calculate the second Tuesday of the month, this depends on the day of the week of the first day of the month. But we need to know which month is this? Obviously, all we need to know are the month and year, but it’s easier if we start with a date as such. For testing purposes, the following table can be used.
Create table month_date (month_date date);
Insert into month_date values(date ‘2009-04-01’);
Insert into month_date values(date ‘2009-05-01’);
Insert into month_date values(date ‘2009-06-01’);
Insert into month_date values(date ‘2009-07-01’);
So, given a date, the first day of a month, what date is the second Tuesday of that month? The process begins by calculating the day of the week for the first day of the month.
Day-of-week function:
Even though standard SQL doesn’t provide any function to give the day of the week for any date, most of the database systems do.
ORACLE provides a function:
TO_CHAR(DATE,’D’);
The essence of this function is that it will return a number between 0 and 6 or 1 and 7.Sometimes 0 or 1 is Sunday and 6 or 7 is Saturday, and sometime 0 or 1 is Monday and 6 or 7 is Sunday. This depends on how the NLS_TERRITORY parameter is set.
Note: We follow that 1 is Sunday and 7 is Saturday in our example.
-----
The Formula:
To convert the first day of the month into second Tuesday of the month simply involves manipulating the day of the week of the first day with in an arithmetic formula. Before we see the formula, we should know what happens when the first day of the month falls on each day of the week from Sunday through Saturday.
Lets see this:
1. A Sunday, the third is a Tuesday, so the tenth is the second Tuesday
2. A Monday, the second is Tuesday, so the ninth is the second Tuesday
3. A Tuesday, the eighth is the second Tuesday
4. A Wednesday, the seventh is the next Tuesday and fourteenth is the second Tuesday.
5. A Thursday, the sixth is the next Tuesday and thirteenth is the second Tuesday
6. A Friday, the fifth is the next Tuesday and the twelfth is the second Tuesday
7. A Saturday, the fourth is the next Tuesday and the eleventh is the second Tuesday
This gives us all the possibilities. So now we have to reduce these facts to formula.
The first column (A) is the day of the week of the first day of the month; and the second column is the numerical equivalent of this day using the range from 1 to 7.
The important data in the above table is the last column; which is the number of days to add to the date of the first day of the month.
So, the formula can be derived as follows.
1. Find B, the day of the week of the first day of the month, using
1-Sunday……7-Saturday.
2. Subtract this number from 10 to get C:
With Sunday -1 …. Saturday – 7, Tuesday would be 3.
The number 3-B is the offset (relative to the first of the month) for a Tuesday, but it might be in the current month or the previous month; 10-B is also a Tuesday, and so are 17-B and 24-B.
You should choose to subtract 10 because you want C to be positive for all inputs. Because you need D to be positive in the next step, but a negative value for C would result in a negative value for D. this is because -1%7 gives -1 on most systems.
3. Divide by 7 and keep remainder to get D value.
D is also the offset for a Tuesday, and D in the range of 0 to 6. every day in the first week has an offset between 0 to 6. So D is the first Tuesday of the month.
4. Add 7 to get E.
That takes the range of E from 7 to 13. Every day in the second week has an offset in the range of 7-13.
5. Take the result and add that number of days to the date of the first day of the month.
Implementing this on ORACLE database:
SELECT TO_CHAR (month_date, 'dd-mon-yyyy') AS first_day_of_month,
TO_CHAR (( (month_date)
+ MOD ((10 - TO_CHAR ((month_date), 'd')), 7)
+ 7
),
'dd-mon-yyyy'
) AS second_tuesday_of_month
FROM month_date
OUTPUT
*******
Hope this is useful for you guys.
To calculate the second Tuesday of the month, this depends on the day of the week of the first day of the month. But we need to know which month is this? Obviously, all we need to know are the month and year, but it’s easier if we start with a date as such. For testing purposes, the following table can be used.
Create table month_date (month_date date);
Insert into month_date values(date ‘2009-04-01’);
Insert into month_date values(date ‘2009-05-01’);
Insert into month_date values(date ‘2009-06-01’);
Insert into month_date values(date ‘2009-07-01’);
So, given a date, the first day of a month, what date is the second Tuesday of that month? The process begins by calculating the day of the week for the first day of the month.
Day-of-week function:
Even though standard SQL doesn’t provide any function to give the day of the week for any date, most of the database systems do.
ORACLE provides a function:
TO_CHAR(DATE,’D’);
The essence of this function is that it will return a number between 0 and 6 or 1 and 7.Sometimes 0 or 1 is Sunday and 6 or 7 is Saturday, and sometime 0 or 1 is Monday and 6 or 7 is Sunday. This depends on how the NLS_TERRITORY parameter is set.
Note: We follow that 1 is Sunday and 7 is Saturday in our example.
-----
The Formula:
To convert the first day of the month into second Tuesday of the month simply involves manipulating the day of the week of the first day with in an arithmetic formula. Before we see the formula, we should know what happens when the first day of the month falls on each day of the week from Sunday through Saturday.
Lets see this:
1. A Sunday, the third is a Tuesday, so the tenth is the second Tuesday
2. A Monday, the second is Tuesday, so the ninth is the second Tuesday
3. A Tuesday, the eighth is the second Tuesday
4. A Wednesday, the seventh is the next Tuesday and fourteenth is the second Tuesday.
5. A Thursday, the sixth is the next Tuesday and thirteenth is the second Tuesday
6. A Friday, the fifth is the next Tuesday and the twelfth is the second Tuesday
7. A Saturday, the fourth is the next Tuesday and the eleventh is the second Tuesday
This gives us all the possibilities. So now we have to reduce these facts to formula.
| A | B | C | D | E |
|---|---|---|---|---|
| 1ST | WEEK DAY | 10-B | C MOD 7 | D+7 |
| SUN | 1 | 9 | 2 | 9 |
| MON | 2 | 8 | 1 | 8 |
| TUE | 3 | 7 | 0 | 7 |
| WED | 4 | 6 | 6 | 13 |
| THU | 5 | 5 | 5 | 12 |
| FRI | 6 | 4 | 4 | 11 |
| SAT | 7 | 3 | 3 | 10 |
The first column (A) is the day of the week of the first day of the month; and the second column is the numerical equivalent of this day using the range from 1 to 7.
The important data in the above table is the last column; which is the number of days to add to the date of the first day of the month.
So, the formula can be derived as follows.
1. Find B, the day of the week of the first day of the month, using
1-Sunday……7-Saturday.
2. Subtract this number from 10 to get C:
With Sunday -1 …. Saturday – 7, Tuesday would be 3.
The number 3-B is the offset (relative to the first of the month) for a Tuesday, but it might be in the current month or the previous month; 10-B is also a Tuesday, and so are 17-B and 24-B.
You should choose to subtract 10 because you want C to be positive for all inputs. Because you need D to be positive in the next step, but a negative value for C would result in a negative value for D. this is because -1%7 gives -1 on most systems.
3. Divide by 7 and keep remainder to get D value.
D is also the offset for a Tuesday, and D in the range of 0 to 6. every day in the first week has an offset between 0 to 6. So D is the first Tuesday of the month.
4. Add 7 to get E.
That takes the range of E from 7 to 13. Every day in the second week has an offset in the range of 7-13.
5. Take the result and add that number of days to the date of the first day of the month.
Implementing this on ORACLE database:
SELECT TO_CHAR (month_date, 'dd-mon-yyyy') AS first_day_of_month,
TO_CHAR (( (month_date)
+ MOD ((10 - TO_CHAR ((month_date), 'd')), 7)
+ 7
),
'dd-mon-yyyy'
) AS second_tuesday_of_month
FROM month_date
OUTPUT
*******
| FIRST_DAY_OF_MONTH | SECOND_TUESDAY_OF_MONTH |
|---|---|
| 01-apr-2009 | 14-apr-2009 |
| 01-may-2009 | 12-may-2009 |
| 01-jun-2009 | 09-jun-2009 |
| 01-jul-2009 | 14-jul-2009 |
Hope this is useful for you guys.
Subscribe to:
Posts (Atom)