ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Число хранится как текст. Как написать выборку с использованием between?
14 сообщений из 89, страница 4 из 4
  4  все
Число хранится как текст. Как написать выборку с использованием between?
    #39537840
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Elic,

Ага, спасибо, отличное решение, помню его и про NLS-зависимость.
В моих тестах при binary и практическом отсутствии повторов на миллионе, оно самое быстрое:
 
1. Elapsed: 00:00:35.62
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
set echo on timing on;
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/
alter table prefixes add len number generated always as (length(prefix))
/
create unique index ix_prefixes on prefixes(len,prefix,descr)
/
begin
   for r in (
      with 
        n as (
          select/*+ inline */ (select max(len) from prefixes)-level+1 N
          from dual 
          connect by level<=(select max(len) from prefixes)
       )
      select
        str
        ,(select 
             (select p.descr from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n))
          from N
          where (select p.descr from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n)) is not null
            and rownum=1
         )
      from test
   )loop null;end loop;
end;
/
set echo off timing off;

 
2. Elapsed: 00:00:11.29
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
set echo on timing on;
drop table test purge;
drop table prefixes purge;
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/
alter table prefixes add len number generated always as (length(prefix))
/
create unique index ix_prefixes on prefixes(len,prefix,descr)
/
create or replace function max_prefix_len return int result_cache deterministic as
   res int;
begin
   select max(len) into res from prefixes;
   return res;
end;
/
create or replace function get_prefix(str in varchar2) return rowid result_cache deterministic as
   ml int := max_prefix_len;
   res rowid;
   cursor c is 
     with 
       n as (
       select/*+ inline */ ml-level+1 N
       from dual 
       connect by level<=ml
     )
     select 
       (select p.rowid from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n))
     from N
     where (select p.rowid from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n)) is not null;

begin
   open c;
   fetch c into res;
   close c;
   return res;
end;
/
begin
   for r in (
      select
        str,p.*
      from test t
          ,prefixes p
      where p.rowid=get_prefix(substr(t.str,1,max_prefix_len))
   )loop null; end loop;
end;
/
begin
   for r in (
      select
        str,p.*
      from test t
          ,prefixes p
      where p.rowid=get_prefix(substr(t.str,1,max_prefix_len))
   )loop null; end loop;
end;
/
set echo off timing off;

 
Elic. Elapsed: 00:00:09.67
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
set echo on timing on;
drop table test purge;
drop table prefixes purge;
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/
commit
/
begin for r in (
select 
  t.str, lat.*
from test t
    ,lateral(
       select v.* 
       from (select/*+ index_desc(p (prefix)) */ p.* 
             from prefixes p 
             where p.prefix<=t.str 
             order by p.prefix desc) v 
       where rownum=1
    ) lat
)loop null; end loop;
end;
/
set echo off timing off;



PS. Генератор в моем первом решении съедает практически все время...

Модератор: Вложение удалено.
...
Рейтинг: 0 / 0
18.10.2017, 09:29
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537844
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Fogel
я так и не понял, почему до сих пор не выяснили, А1 и А2 - это числа или нет? )))



Код: plsql
1.
2.
A1 INTEGER;
A2 INTEGER;



.....
stax
...
Рейтинг: 0 / 0
18.10.2017, 09:34
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537889
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
xtender
Код: plsql
1.
2.
drop table test purge;
create table test as

Саян, я на Verification Code собаку съел. Cleanup располагается в конце скрипта, чтобы вернуть всё к состоянию, как будто его (скрипта) здесь не было.
Просьба учесть.
...
Рейтинг: 0 / 0
18.10.2017, 10:39
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537946
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
StaxFogelя так и не понял, почему до сих пор не выяснили, А1 и А2 - это числа или нет? )))



Код: plsql
1.
2.
A1 INTEGER;
A2 INTEGER;



.....
stax

Stax, это был стёб по наблюдению обсуждения.
...
Рейтинг: 0 / 0
18.10.2017, 11:53
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538166
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
xtenderandrey_anonymousВ телекоме коды ABC/DEF именно таковы.
Я на них в свое время "собаку съел", решая задачу эффективной привязки зоны к номеру по наиболее полному соответствию.кстати, да, очень интересная и полезная задачка. Я уже тут видел похожие топики и даже что-то решал, но сейчас что-то не осилил поиском найти твои...
Может отдельный топик создашь про нее со своими решениями? Туда и другие варианты накидаем :)
Я уже в другой области работаю, искать исходники лень.
Решение Виталия - очень эффективно для одиночных поисков.
Для _массовой привязки (десятки-сотни миллионов звонков) я либо преобразовывал входящую таблицу префиксов (чуть-чуть более толстую, чем в примерах и с учетом истории изменений , что вообще никто не показывает на форумах :) ) для привязки по равенству, либо - через похожее преобразование, но без нормализации длины префикса загонял в индексированную pl/sql таблицу и привязку делал в pipelined (через .prior())
...
Рейтинг: 0 / 0
18.10.2017, 15:47
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538437
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
SQL> begin for r in (
  2  select
  3    t.str, lat.*
  4  from test t
  5      ,lateral(
  6         select v.*
  7         from (select/*+ index_desc(p (prefix)) */ p.*
  8               from prefixes p
  9               where p.prefix<=t.str
 10               order by p.prefix desc) v
 11         where rownum=1
 12      ) lat
 13  )loop null; end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.75
SQL> begin for r in (
  2  select
  3    t.str, phone_pkg.get_code(t.str)
  4  from test t
  5  )loop null; end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.77


Функция phone_pkg.get_code взята у меня из книги.
When PL/SQL is better than vanilla SQL (стр 133)
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
create or replace package phone_pkg is

  type tp_phone_code is table of int index by varchar2(10);
  g_phone_code tp_phone_code;
  function get_code(p_num in varchar2) return varchar2 deterministic;
  l_max_l int;

end phone_pkg;
/
create or replace package body phone_pkg is

  function get_code(p_num in varchar2) return varchar2 deterministic is
    l_num varchar2(10);
    pragma udf;
  begin
    l_num := substr(p_num, 1, l_max_l);
  
    while (l_num is not null) and (not g_phone_code.exists(l_num)) loop
      l_num := substr(l_num, 1, length(l_num) - 1);
    end loop;
  
    return l_num;
  end;

begin
  for cur in (select * from prefixes) loop
    g_phone_code(cur.prefix) := 1;
  end loop;

  select max(length(prefix)) into l_max_l from prefixes;
end phone_pkg;
/

...
Рейтинг: 0 / 0
19.10.2017, 00:36
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538440
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop,

Я попытался сделать как у меня, т.е. чтобы можно было приджойнить поля из PREFIXES.
Но как-то долго вышло... Я что-то сделал не так?
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/
call dbms_stats.gather_table_stats('','TEST');
call dbms_stats.gather_table_stats('','PREFIXES');
create or replace package phone_pkg is

  type tp_phone_code is table of rowid index by varchar2(10);
  g_phone_code tp_phone_code;
  function get_code(p_num in varchar2) return rowid deterministic;
  l_max_l int;

end phone_pkg;
/
create or replace package body phone_pkg is

  function get_code(p_num in varchar2) return rowid deterministic is
    l_num varchar2(10);
    pragma udf;
  begin
    l_num := substr(p_num, 1, l_max_l);
  
    while (l_num is not null) and (not g_phone_code.exists(l_num)) loop
      l_num := substr(l_num, 1, length(l_num) - 1);
    end loop;
  
    return g_phone_code(l_num);
  end;

begin
  for cur in (select p.prefix,p.rowid rid from prefixes p) loop
    g_phone_code(cur.prefix) := cur.rid;
  end loop;

  select max(length(prefix)) into l_max_l from prefixes;
end phone_pkg;
/
begin
   for r in (
      select
        str,p.*
      from test t
          ,prefixes p
      where p.rowid=phone_pkg.get_code(t.str)
   )loop null; end loop;
end;
/

...
Рейтинг: 0 / 0
19.10.2017, 01:07
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538442
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender,

От соединения надо уходить, descr можно засунуть в ассоциативный массив.
...
Рейтинг: 0 / 0
19.10.2017, 01:11
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538443
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop,

добавил в твой пакет еще функцию с возвратом max_length, чтобы уменшить кол-во вызова за счет кэширования deterministic и получилось дико быстро ~ 3.5 сек:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
create or replace package phone_pkg is

  type tp_phone_code is table of rowid index by varchar2(10);
  g_phone_code tp_phone_code;
  function max_length return int deterministic;
  function get_code(p_num in varchar2) return rowid deterministic;
  l_max_l int;

end phone_pkg;
/
create or replace package body phone_pkg is

  function max_length return int deterministic is
    pragma udf;
  begin
     return l_max_l;
  end;

  function get_code(p_num in varchar2) return rowid deterministic is
    l_num varchar2(10);
    pragma udf;
  begin
    l_num := substr(p_num, 1, l_max_l);
  
    while (l_num is not null) and (not g_phone_code.exists(l_num)) loop
      l_num := substr(l_num, 1, length(l_num) - 1);
    end loop;
  
    return g_phone_code(l_num);
  end;

begin
  for cur in (select p.prefix,p.rowid rid from prefixes p) loop
    g_phone_code(cur.prefix) := cur.rid;
  end loop;

  select max(length(prefix)) into l_max_l from prefixes;
end phone_pkg;
/
begin
   for r in (
      select
        str,p.*
      from test t
          ,prefixes p
      where p.rowid=phone_pkg.get_code(substr(t.str,1,phone_pkg.max_length))
   )loop null; end loop;
end;
/

...
Рейтинг: 0 / 0
19.10.2017, 01:12
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538444
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshopxtender,

От соединения надо уходить, descr можно засунуть в ассоциативный массив.просто как-то неожиданно долго стало, хотя там был HJ:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SQL_ID  4ptv4zztvfwzw, child number 0
-------------------------------------
SELECT STR,P.* FROM TEST T ,PREFIXES P WHERE
P.ROWID=PHONE_PKG.GET_CODE(T.STR)

Plan hash value: 1937684794

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |   468 (100)|          |
|*  1 |  HASH JOIN         |          |   360K|  6679K|   468   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PREFIXES |    36 |   432 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST     |  1000K|  6835K|   462   (1)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("P".ROWID=CHARTOROWID("PHONE_PKG"."GET_CODE"("T"."STR")))
...
Рейтинг: 0 / 0
19.10.2017, 01:14
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538445
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
А вообще по теме кэширования: да, там действительно дикий оверхэд у result_cache - у меня всегда получалось, что ручной кэш в пакетных переменных намного быстрее. В ПСБ когда у меня была проблема еще и с непонятной инвалидацией, я даже сделал замороченный пакет в котором использовал и result_cache, и пакетные переменные:
1) была простая функция с result_cache чисто для инвалидации/сброса пакетных переменных
2) сами кэшируемые данные были в пакетных переменных, при инвалидации result_cache, вызывалась функция пересбора кэша
...
Рейтинг: 0 / 0
19.10.2017, 01:19
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538563
XMLer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
XMLer
Участник
ElicPL SQL НовичокТимлид сказал, что в этом селекте я допустил грубую ошибку, так как здесь идёт неявное преобразование строки в числоГрубую ошибку сделал архитектор. Само по себе неявное преобразование не так страшно. Но в данном случае RECEIPT преобразуется к числу и как следствие:
1) не может быть использован индекс;
2) если где-то в RECEIPT есть нечисло, то запрос всегда будет падать.
Это всё следствие быдлоархитектуры, но со вторым можно бороться.

Не факт что архитектор сделал грубую ошибку. Возможно это сознательное решение, связанное с особенностями генерации номера чека, призванное предотвратить обработку номеров как чисел. Например, он хотел этим сказать следующее: если вам нужно выбрать чеки выданные между двумя другими то даже не думайте искать по соответствию номеров. Ищите только по дате выдачи чека.
...
Рейтинг: 0 / 0
19.10.2017, 11:21
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538568
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
xtenderА вообще по теме кэширования: да, там действительно дикий оверхэд у result_cache - у меня всегда получалось, что ручной кэш в пакетных переменных намного быстрее.
Это до тех пор, пока данные в uga помещаются, дальше сложнее.
Я когда-то из спортивного интереса даже реализовал алгоритм ARC на PL/SQL, но невозможность отделить сам алгоритм от данных делает идею нерентабельной.
...
Рейтинг: 0 / 0
19.10.2017, 11:23
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538612
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
XMLerНапример, он хотел этим сказать следующее:Прибежали гадалки с вокзала?
...
Рейтинг: 0 / 0
19.10.2017, 12:18
| Ответить | Цитировать | Написать  
14 сообщений из 89, страница 4 из 4
  4  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Число хранится как текст. Как написать выборку с использованием between?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39538445&tid=1885079]:
 0ms
get settings:
 9ms
get forum list:
 18ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 165ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 72ms
get tp. blocked users:
 2ms
others: 236ms
total:  524ms
созд. / загр.: 524ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]