CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Dipanshu Shekhar
Dipanshu Shekhar

Reputation: 45

Efficient way of filtering data from column with mobile number separated by special characters. column has number with varchar2 datatype

I have a table customer with columns cid, mobile_no. cid is unique. mobile_no has more than one mobile numbers separated by comma or inverted commas. The table contains over 100k records. Now if i get a single phone number as input for one customer and on the basis i need to fetch cid and then map it with another table customer detail to get values, how should i proceed? I cannot use like operator as it will be very heavy for a table with over 100k records.

Ex:

ID mobile_no

cid_1 9263532727,"9738262783"....
cid_2 '8745362664,"'6384683633'";...

The above example can be considered as one of the scenario for mobile number. Now if i get 6384683633 as mobile number on the basis of which i need to fetch cid and map with other table to get the detail of the customer.

I could use like operator with '%%' but that will leave the query inefficient.

Is there any other way to do it.

Upvotes: 0

Views: 610

Answers (2)

Littlefoot
Littlefoot

Reputation: 143003

First of all, yes - change data model.

This is written after seeing @Dipanshu's comment, asking for yet another option.

The idea of this approach is to split mobile telephone numbers into rows (that's what SPLIT_ME does), and then simply select whether there's any information that matches search string.

SQL> with test (id, mobile_no) as
  2    (select 1, '9263532727,9738262783' from dual union
  3     select 2, '8745362664,6384683633,9876543210' from dual
  4    ),
  5  split_me as
  6    (select id, regexp_substr(mobile_no, '[^,]+', 1, column_value) mobile_no
  7     from test,
  8          table(cast(multiset(select level from dual
  9                              connect by level <= regexp_count(mobile_no, ',') + 1
 10                             ) as sys.odcinumberlist))
 11    )
 12  select * From split_me
 13  where mobile_no = &search_mobile_no;
Enter value for search_mobile_no: 9876543210

        ID MOBILE_NO
---------- --------------------------------
         2 9876543210

SQL>

Upvotes: 2

Connor McDonald
Connor McDonald

Reputation: 11591

Recommendation #1:

Don't go with this design

Recommendation #2:

If you have to go with this design, you can use an Text index to parse out the values for indexing, eg

SQL> create table t (  cid int, mobs varchar2(1000));

Table created.

SQL>
SQL> insert into t
  2  select c, listagg(num,',') within group ( order by num )
  3  from
  4  ( select mod(rownum,1000) c, trunc(dbms_random.value(1000000,9999999)) num from dual connect by level <= 4000 )
  5  group by c;

1000 rows created.

SQL>
SQL> select * from t
  2  where rownum <= 10;

       CID MOBS
---------- --------------------------------------------------
       710 1309670,2250891,2446498,6381871
       711 6441062,7421544,8803290,8980956
       712 5216092,6664995,7971562,8615836
       713 2699908,4060187,7669498,8998292
       714 2565735,2621571,4071919,9443984
       715 1284533,2445570,6669291,8675917
       716 2368712,4074744,7804389,9798070
       717 1037619,4071820,4235448,7026192
       718 2955831,6282004,7131543,9027507
       719 4040856,4761599,4859829,9179022

10 rows selected.

SQL>
SQL> begin
  2        ctx_ddl.drop_preference('my_lexer');
  3        ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER');
  4        ctx_ddl.set_attribute('my_lexer', 'numgroup', '~');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> create index ix on t ( mobs ) indextype is ctxsys.context PARAMETERS('lexer my_lexer');

Index created.

SQL> set autotrace on
SQL> select * from t
  2  where contains(mobs,'7804389') > 0;

       CID MOBS
---------- --------------------------------------------------
       716 2368712,4074744,7804389,9798070

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1339481741

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   527 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |   527 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | IX   |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("MOBS",'7804389')>0)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
        196  recursive calls
          0  db block gets
        152  consistent gets
          1  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>

Upvotes: 1

Related Questions