Home » SQL & PL/SQL » SQL & PL/SQL » Tuning Select statement (11g)
Tuning Select statement [message #441824] Wed, 03 February 2010 05:02 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12321
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
cookiemonster
Messages: 12321
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 #441863 is a reply to message #441824] Wed, 03 February 2010 09:30 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member

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;

./fa/1940/0/
Re: Tuning Select statement [message #441867 is a reply to message #441863] Wed, 03 February 2010 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 12321
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;

./fa/1940/0/


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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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;
icon2.gif  Re: Tuning Select statement [message #441989 is a reply to message #441955] Thu, 04 February 2010 04:22 Go to previous messageGo to next message
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
Re: Tuning Select statement [message #441991 is a reply to message #441824] Thu, 04 February 2010 04:32 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just remove the line in bold.
Re: Tuning Select statement [message #442126 is a reply to message #441991] Thu, 04 February 2010 19:42 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Thanks @cm
Previous Topic: Inserting a blank line to a clob
Next Topic: inserting a row into a table
Goto Forum:
  


Current Time: Sat Oct 01 07:37:23 CDT 2016

Total time taken to generate the page: 0.21933 seconds