Сгруппировать маски номеров

Сотрудник вводит несколько масок для номеров сотовых телефонов. Формат:  89087ХХХХХХ, 8908673ХХХХ, 890867ХХХХХ и т.п. 

Необходимо исключить из списка те маски, которые входят в более широкий спектр номеров. Например в 890867ХХХХХ входят 8908671ХХХХ и 8908672ХХХХ. В результате должно остаться только 890867ХХХХХ. 

Решение 1:

WITH list_str AS (SELECT regexp_substr('8908673,89086759,89086737,8908673,890867597', '[^,]+', 1, LEVEL) cl FROM dual

CONNECT BY LEVEL <= regexp_count('8908673,89086759,89086737,8908673,890867597', ',') + 1) SELECT DISTINCT (SELECT MIN(t.cl) keep(dense_rank FIRST ORDER BY t.cl) FROM list_str t WHERE instr(ts.cl, t.cl) = 1) AS roots FROM list_str ts;

Решение 2:

WITH xml_root AS (SELECT regexp_substr('8908673,89086759,89086737,8908673,890867597', '[^,]+', 1, LEVEL) cl FROM dual

CONNECT BY LEVEL <= regexp_count('8908673,89086759,89086737,8908673,890867597', ',') + 1) SELECT DISTINCT cl FROM (SELECT cl, connect_by_isleaf AS root FROM xml_root t CONNECT BY nocycle instr(PRIOR t.cl, t.cl) = 1) WHERE root = 1;

Oracle Warehousing: использование IOT для миграции данных

Основное отличие индекс-организованной таблицы в том, все данные могут храниться в структуре индекса. Это свойство полезно использовать в том случае, если в таблице очень много строк и очень мало колонок, которые необходимо использовать в запросе. Польза будет проявляться в экономии места на диске: ИОТ будет занимать меньше места, чем обычная таблица с данными и индекс. 

Такая особенность полезна в миграции данных. Например, когда необходимо использовать большую remote таблицу в сложном пересчёте значений в локальных таблицах. Не будем при  каждом пересчёте значений в очередной таблице загружать несколько гигабайт данных с удалённой таблицы. Возьём только те столбцы, которые участвуют в пересчёте и зальём их в локальную индекс-организованную таблицу. 

Узнать какой объём памяти занимает индекс-организованная таблица можно из dba_segments, указав в качестве имени сегмента не наименование таблицы, а наименование индекса для первичного ключа этой таблицы, потому что все данные таблицы будут храниться в индексе. 

Oracle Warehousing: констрэйнт NOT NULL на большой таблице

Есть таблица с большим количеством строк. Необходимо изменить поле, применив к нему констрэйнт NOT NULL. Это поле не индексировано. Трудность в том, что если просто навесить констрэйнт NOT NULL, будет считана вся таблица и проверено поле в каждой строке на значение. И это может занять несколько часов, если таблица действительно большая. 

Предлагаемое решение: создать в параллели BITMAP INDEX на этой колонке, затем создать констрэйнт NOT NULL и в конце дропнуть BITMAP INDEX. В этом алгоритме большую часть времени займёт операция создания BITMAP индекса - несколько минут, создание констрэйнта происходит мгновенно. Для успешной реализации этого приёма необходимо свободное место на диске.

Использование PARTITION LEFT JOIN

Есть таблица с перечнем временных зон (days):

Есть данные о начислениях по абоненту по временным зонам (usrs):

Необходимо получить результат: если начисления по абоненту по данной временной зоне не было, то выдавать название абонента, временную зону и начисление, равное 0. Т.е.:

Решение:

SELECT u.ur,

       d.name,

       nvl(u.n, 0) as val

FROM   days d

LEFT   JOIN usrs u PARTITION BY(u.ur) ON d.name = u.d;

Нарастающий итог с условием

Есть таблица (hnull) с начислениями какой-либо величины за каждый год:

Необходимо без применения PL/SQL посчитать величину начисления нарастающим итогом (growth) в порядке увеличения года (year). При этом если начисление текущего года (val) меньше нарастающего итога предыдущих годов, то этот месяц не вносит свой вклад в нарастающий итог. Т.е. результат будет:

Решение:

SELECT val, year, growth

FROM  hnull

MODEL

DIMENSION BY (YEAR)

MEASURES (val, 0 growth)

RULES(

       growth [YEAR] = case

          when val[cv()] <  nvl(growth[cv()-1],0) then growth[cv()-1]

        else

          val[cv()] + nvl(growth[cv()-1],0)

       end

)

ORDER  BY YEAR;

Расписание запусков

Есть матрица расписания запусков:

Первая строка – 15-и минутные интервалы, вторая строка часовые интервалы, третья строка
дни недели, четвертая дни месяца, пятая месяцы года. С помощью данной матрицы задается периодичность запусков.

Требуется написать функцию на Oracle PL/SQL, которая бы возвращала дату следующего запуска (тип Date) от двух входных параметров:

Первый параметр (тип Date): дата, от которой ведется отчет;

Второй параметр (тип Varchar2): это текстовая переменная, в которой перечислены все выбранные ячейки. Ячейки разделены «,» (запятой), а строки разделены «;» (точкой с запятой), например, для данного рисунка расписание будет выглядеть следующим образом: 0,45;0,4,8,12,17,22;2,6;1,2,3,4,5,11,18,24;1,2,3,9,11;

Контрольный пример:

Дата отсчета: 09.07.2010 23:36

Строка: 0,45;12;1,2,6;3,6,14,18,21,24,28;1,2,3,4,5,6,7,8,9,10,11,12;

Результат: 18.07.2010 12:00

Примечание. В данном примере, используется американский календарь, в котором 1 – это воскресенье, 2 – понедельник и т.д.

Решение: 

DECLARE

    p_interval   VARCHAR2(250);

    d1           TIMESTAMP := SYSDATE;

    d2           TIMESTAMP := d1;

    p_result_day TIMESTAMP;

    v_byminute  VARCHAR2(50);

    v_byhour    VARCHAR2(50);

    v_byweekday VARCHAR2(50);

    v_bymontday VARCHAR2(50);

    v_bymonth   VARCHAR2(50);

    v_slr_name  VARCHAR2(30) := 'slr' || to_char(systimestamp, 'yyymmddhh24missff');

BEGIN

    p_interval  := '0,15,45;0,4,8,12,17,22;2,4,5,6;1,2,3,4,5,11,18,24;1,2,3,9,11;'; -- в конце можно не ставить ;

    v_byminute  := regexp_substr(p_interval, '[^;]+', 1, 1);

    v_byhour    := regexp_substr(p_interval, '[^;]+', 1, 2);

    v_byweekday := regexp_substr(p_interval, '[^;]+', 1, 3);

    v_bymontday := regexp_substr(p_interval, '[^;]+', 1, 4);

    v_bymonth   := regexp_substr(p_interval, '[^;]+', 1, 5);

    -- преобразуем дни в наименования

    SELECT listagg(regexp_substr('SUN|MON|TUE|WED|THU|FRI|SAT', '[^|]+', 1, dy),',') within GROUP(ORDER BY dy)

    INTO   v_byweekday

    FROM   (SELECT regexp_substr(v_byweekday, '[^,]+', 1, LEVEL) dy

            FROM   dual

            CONNECT BY LEVEL <= regexp_count(v_byweekday, ',') + 1);

    -- создаем уникальный календарь

    dbms_scheduler.create_schedule(schedule_name => v_slr_name,

    repeat_interval => 'FREQ=YEARLY;BYMONTHDAY='||v_bymontday||';BYDAY=' || v_byweekday||';BYHOUR='||v_byhour||';BYMINUTE='||v_byminute);

    dbms_scheduler.evaluate_calendar_string(calendar_string   => v_slr_name,

                                            start_date        => d1,

                                            return_date_after => d2,

                                            next_run_date     => p_result_day);

    dbms_output.put_line('Next date is ' || to_char(p_result_day, 'dd.mm.yyyy hh24:mi:ss'));

    -- удаляем созданный календарь

    dbms_scheduler.drop_schedule(schedule_name => v_slr_name);

END;

/