Home » SQL & PL/SQL » SQL & PL/SQL » Tuning Select statement (11g)
| Tuning Select statement [message #441824] |
Wed, 03 February 2010 05:02  |
malhi
Messages: 96
Registered: December 2009
Location: Karachi
|
Member |
|
|
My query is on column called "old_nic_no " it format is 999-99-999999, i want to determine such values where there are all 0 or 1 or 2 or .... i.e. 111-11-111111 or 222-22-222222......
i have written following query, it is working but i think this a long code there must be a way where small code can do all this, your guidance is require ....
My code is
SELECT old_nic_no, COUNT (*)
FROM core_business.cb_pensioner a
WHERE (REGEXP_INSTR (old_nic_no, '^[9]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[9]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[9]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[8]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[8]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[8]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[7]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[7]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[7]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[6]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[6]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[6]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[5]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[5]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[5]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[4]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[4]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[4]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[3]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[3]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[3]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[2]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[2]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[2]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[1]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[1]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[1]{6}') = 1
)
OR (REGEXP_INSTR (old_nic_no, '^[0]{3}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 5, 2), '^[0]{2}') = 1
AND REGEXP_INSTR (SUBSTR (old_nic_no, 8, 6), '^[0]{6}') = 1
)
OR REGEXP_INSTR (old_nic_no, '[0-9]{3}-[0-9]{2}-[0-9]{6}') = 0
GROUP BY old_nic_no;
CM: added code tags, please do so yourself next time - see the orafaq forum guide if you are not sure how.
Also removed a load of unecessary blank lines
[Updated on: Wed, 03 February 2010 05:14] by Moderator Report message to a moderator |
|
|
|
| Re: Tuning Select statement [message #441831 is a reply to message #441824] |
Wed, 03 February 2010 05:17  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Well one obvious thing to do is use replace to remove all the dashes, then you'll just have the number part left and you won't need all the substr's. I'm sure it can be simplified a lot further than that but I've never really got my head around regular expressions.
|
|
|
|
| Re: Tuning Select statement [message #441836 is a reply to message #441831] |
Wed, 03 February 2010 05:58 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Untested code....
SELECT old_nic_no, COUNT(*)
FROM (
SELECT old_nic_no, REPLACE(old_nic_no, '-', NULL) AS old_nic
FROM core_business.cb_pensioner a
WHERE REPLACE(old_nic_no, '-', NULL)
)
WHERE REPLACE(old_nic, '1', NULL) IS NULL
OR REPLACE(old_nic, '2', NULL) IS NULL
OR REPLACE(old_nic, '3', NULL) IS NULL
OR REPLACE(old_nic, '4', NULL) IS NULL
OR REPLACE(old_nic, '5', NULL) IS NULL
OR REPLACE(old_nic, '6', NULL) IS NULL
OR REPLACE(old_nic, '7', NULL) IS NULL
OR REPLACE(old_nic, '8', NULL) IS NULL
OR REPLACE(old_nic, '9', NULL) IS NULL
OR REPLACE(old_nic, '0', NULL) IS NULL
GROUP BY old_nic_no
Ross Leishman
|
|
|
|
| Re: Tuning Select statement [message #441845 is a reply to message #441824] |
Wed, 03 February 2010 06:25 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Another way:
WITH num_list AS (SELECT (ROWNUM -1) check_num FROM dual CONNECT BY LEVEL <= 10),
cb_pensioner AS (SELECT '111-11-111111' old_nic_no FROM dual
UNION ALL
SELECT '111-11-111112' old_nic_no FROM dual
UNION ALL
SELECT '222-22-222222' old_nic_no FROM dual
UNION ALL
SELECT '333-33-333333' old_nic_no FROM dual
UNION ALL
SELECT '333-33-333333' old_nic_no FROM dual
UNION ALL
SELECT '333-33-336333' old_nic_no FROM dual
UNION ALL
SELECT '999-99-999999' old_nic_no FROM dual)
SELECT old_nic_no, COUNT(*)
FROM (SELECT old_nic_no, REPLACE(old_nic_no, '-', NULL) mod_old_nic_no
FROM cb_pensioner),
num_list
WHERE REGEXP_INSTR (mod_old_nic_no, '['||to_char(num_list.check_num)||']{11}') = 1
GROUP BY old_nic_no;
Though I suspect Ross's will perform better.
|
|
|
|
|
|
| Re: Tuning Select statement [message #441867 is a reply to message #441863] |
Wed, 03 February 2010 09:41 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
LKBrwn_DBA wrote on Wed, 03 February 2010 15:30
Better try this:
SELECT old_nic_no, COUNT (*)
FROM core_business.cb_pensioner a
WHERE TRANSLATE(old_nic_no,'~-0123456789','~') IS NULL
GROUP BY old_nic_no;
Better not.
Your version:
SQL> WITH cb_pensioner AS (SELECT '111-11-111111' old_nic_no FROM dual
2 UNION ALL
3 SELECT '111-11-111112' old_nic_no FROM dual
4 UNION ALL
5 SELECT '222-22-222222' old_nic_no FROM dual
6 UNION ALL
7 SELECT '333-33-333333' old_nic_no FROM dual
8 UNION ALL
9 SELECT '333-33-333333' old_nic_no FROM dual
10 UNION ALL
11 SELECT '333-33-336333' old_nic_no FROM dual
12 UNION ALL
13 SELECT '999-99-999999' old_nic_no FROM dual)
14 SELECT old_nic_no, COUNT (*)
15 FROM cb_pensioner a
16 WHERE TRANSLATE(old_nic_no,'~-0123456789','~') IS NULL
17 GROUP BY old_nic_no;
OLD_NIC_NO COUNT(*)
------------- ----------
999-99-999999 1
111-11-111112 1
111-11-111111 1
333-33-333333 2
333-33-336333 1
222-22-222222 1
6 rows selected.
SQL>
My version:
SQL> WITH num_list AS (SELECT (ROWNUM -1) check_num FROM dual CONNECT BY LEVEL <= 10),
2 cb_pensioner AS (SELECT '111-11-111111' old_nic_no FROM dual
3 UNION ALL
4 SELECT '111-11-111112' old_nic_no FROM dual
5 UNION ALL
6 SELECT '222-22-222222' old_nic_no FROM dual
7 UNION ALL
8 SELECT '333-33-333333' old_nic_no FROM dual
9 UNION ALL
10 SELECT '333-33-333333' old_nic_no FROM dual
11 UNION ALL
12 SELECT '333-33-336333' old_nic_no FROM dual
13 UNION ALL
14 SELECT '999-99-999999' old_nic_no FROM dual)
15 SELECT old_nic_no, COUNT(*)
16 FROM (SELECT old_nic_no, REPLACE(old_nic_no, '-', NULL) mod_old_nic_no
17 FROM cb_pensioner),
18 num_list
19 WHERE REGEXP_INSTR (mod_old_nic_no, '['||to_char(num_list.check_num)||']{11}') = 1
20 GROUP BY old_nic_no;
OLD_NIC_NO COUNT(*)
------------- ----------
999-99-999999 1
111-11-111111 1
333-33-333333 2
222-22-222222 1
SQL>
|
|
|
|
| Re: Tuning Select statement [message #441955 is a reply to message #441867] |
Thu, 04 February 2010 01:12 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
SELECT old_nic_no, COUNT(*)
FROM (
SELECT old_nic_no, REPLACE(old_nic_no, '-', NULL) AS old_nic
FROM core_business.cb_pensioner a
WHERE REPLACE(old_nic_no, '-', NULL)
)
WHERE REPLACE(old_nic, substr(old_nic,1,1), NULL) IS NULL
AND substr(old_nic,1,1) BETWEEN '0' AND '9'
GROUP BY old_nic_no
Ross Leishman
|
|
|
|
| Re: Tuning Select statement [message #441966 is a reply to message #441955] |
Thu, 04 February 2010 02:52 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or you can simplify that even further, albeit at the cost of some readability:select old_nic_no,count(*)
from cb_pensioner
where replace(replace(old_nic_no,'-',''),substr(old_nic_no,1,1),'') is null
group by old_nic_no; |
|
|
|
 Re: Tuning Select statement [message #441989 is a reply to message #441955] |
Thu, 04 February 2010 04:22 |
malhi
Messages: 96
Registered: December 2009
Location: Karachi
|
Member |
|
|
Kindly complete the where clause........
SELECT old_nic_no, COUNT(*)
FROM (
SELECT old_nic_no, REPLACE(old_nic_no, '-', NULL) AS old_nic
FROM core_business.cb_pensioner a
WHERE REPLACE(old_nic_no, '-', NULL)
)
WHERE REPLACE(old_nic, substr(old_nic,1,1), NULL) IS NULL
AND substr(old_nic,1,1) BETWEEN '0' AND '9'
GROUP BY old_nic_no
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Aug 22 14:05:51 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
