Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Materialized vew и тип date  [new]
z1zeda
Guest
Есть очень большая таблица (примерно по 30 млн. строк в месяц).
Проблема такая что запросы к ней выполняются очень долго.. Вот структура таблицы:
-------------------------------------------------------------------------------------------------
create table BIG_TABLE
(
  RECT      VARCHAR2(50),
  A         VARCHAR2(35),
  B         VARCHAR2(35),
  R NUMBER,
  ROUTE1   NUMBER,
  ROUTE2   NUMBER,
  DATE_TIME     DATE,
  D   NUMBER
);

-------------------------------------------------------------------------------------------------
Таблица партицирована по дням.
Типичный отчет выглядит так:

SELECT COUNT(*), SUM(c.d) 
 FROM BIG_TABLE  c
 WHERE 
     c.DATE_TIME between 
            to_date('01.03.2008','dd.mm.yyyy')
     AND to_date('01.04.2008','dd.mm.yyyy')      
    AND 
 (
      (c.R = 1 AND c.route1 = 123) or
      (c.R = 7 AND c.1e in (12,13))
 )      
-------------------------------------------------------------------------------------------------
Чтобы сократить время выполнения запросов сделал следующее:
1. Query_rewrite = true;
2. QUery_rewrite_integrity = enforce;
3. Сделал материализированное представление:

CREATE MATERIALIZED VIEW BIG_TABLE_STAT
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT 
       COUNT(*) count 
      ,SUM(c.в) sum
      ,to_char(c.DATE_TIME, 'dd.mm.yyyy') day
      ,c.R
      ,c.route1
      ,c.route2

  FROM BIG_TABLE c

 GROUP BY to_char(c.DATE_TIME, 'dd.mm.yyyy')
         ,c.R
         ,c.route1
         ,c.route2
-------------------------------------------------------------------------------------------------
Время выполнения запроса реально сократилось - он мгновенно их выполняет.
Но с оговоркой. Допустим такой отчет он выполнить быстро:

SELECT COUNT(*), SUM(c.d) 
 FROM BIG_TABLE  c
 WHERE 
     
 (
      (c.R = 1 AND c.route1 = 123) or
      (c.R = 7 AND c.1e in (12,13))
 )  

но если приписать сюда дату:

SELECT COUNT(*), SUM(c.d) 
 FROM BIG_TABLE  c
 WHERE 
     c.DATE_TIME between 
            to_date('01.03.2008','dd.mm.yyyy')
     AND to_date('01.04.2008','dd.mm.yyyy')      
    AND 
 (
      (c.R = 1 AND c.route1 = 123) or
      (c.R = 7 AND c.1e in (12,13))
 )  

то он снова сканирует всю таблицу.
Собственно вопрос, почему он это представление не использует ?
14 май 08, 12:53    [5661055]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
я вот чего не понимаю: есть у вас матвью, почему же из него не читаете
(напрямую, безо всякого реврайта)?
14 май 08, 12:59    [5661120]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
а по сабжу - биндинг используйте (лучше - явный, но хотя бы и принудительный_на_стороне_сервера) и всё наладится
14 май 08, 13:02    [5661149]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Zeda
Member

Откуда: USSR
Сообщений: 215
orawish
я вот чего не понимаю: есть у вас матвью, почему же из него не читаете
(напрямую, безо всякого реврайта)?

Потому, что имя таблицы в каждом месяце новое.
14 май 08, 13:10    [5661231]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Zeda
Member

Откуда: USSR
Сообщений: 215
orawish
а по сабжу - биндинг используйте (лучше - явный, но хотя бы и принудительный_на_стороне_сервера) и всё наладится

Биндинг используется.
Мне интересно почему он с датами не хочет матвю использовать..
Может я мат-вью не правильно создал ? может в форматах чего не так ...
14 май 08, 13:12    [5661250]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Zeda
orawish
я вот чего не понимаю: есть у вас матвью, почему же из него не читаете
(напрямую, безо всякого реврайта)?

Потому, что имя таблицы в каждом месяце новое.

не понял.. Где? У вас секционирование самопальное что ли?
14 май 08, 13:22    [5661350]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
и еще - где в матвью дата?
14 май 08, 13:24    [5661366]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
_мухомор
Guest
to_date('01.03.2008','dd.mm.yyyy') -> DATE
to_char(c.DATE_TIME, 'dd.mm.yyyy') -> строка

заменяем в MV to_char(c.DATE_TIME, 'dd.mm.yyyy') на c.DATE_TIME, наслаждаемся.
14 май 08, 13:52    [5661593]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
_мухомор
to_date('01.03.2008','dd.mm.yyyy') -> DATE
to_char(c.DATE_TIME, 'dd.mm.yyyy') -> строка

заменяем в MV to_char(c.DATE_TIME, 'dd.mm.yyyy') на c.DATE_TIME, наслаждаемся.

ну, только, наверное, на trunc(c.DATE_TIME)
14 май 08, 13:56    [5661626]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
А индекс у вас партиционируется?
14 май 08, 14:48    [5662063]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Zeda
Member

Откуда: USSR
Сообщений: 215
Сергей Арсеньев
А индекс у вас партиционируется?

Индексов на табличке нет. Слишком большая она эта табличка..
мне кажется на мат-вью проблему решить легче.
14 май 08, 15:06    [5662202]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Zeda
Сергей Арсеньев
А индекс у вас партиционируется?

Индексов на табличке нет. Слишком большая она эта табличка..
мне кажется на мат-вью проблему решить легче.

Мат вью тоже быстро создается?
А размер не использование индесксов (по R, route и 1e) обусловлено нежеланием отводить еще кучу места?
Все-таки партиционированные индексы имхо здесь не помешают.
14 май 08, 15:20    [5662308]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Zeda
Member

Откуда: USSR
Сообщений: 215
Сергей Арсеньев
Zeda
Сергей Арсеньев
А индекс у вас партиционируется?

Индексов на табличке нет. Слишком большая она эта табличка..
мне кажется на мат-вью проблему решить легче.

Мат вью тоже быстро создается?
А размер не использование индесксов (по R, route и 1e) обусловлено нежеланием отводить еще кучу места?
Все-таки партиционированные индексы имхо здесь не помешают.

Ситуация такая, что если на табличку повесить индексы:
1. Эти индексы будут не очень эффективны (т.к. значения полей не особо сильно отличаются
друг от друга в разрезе этого множества).
2. Как ты и сказал, это займет очень много места.
3. Это в целом скажится на производительности при работе с этой таблицей
4. Если че нить наебнется то эти индексы еще один гемморой...
Поэтому не хочу делать индексы на ней.
А мат-вью создается примерно за пол часа. А отчет который выше по сабжу (с датами) - он больше часа выполняется..
14 май 08, 16:09    [5662688]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Zeda
Member

Откуда: USSR
Сообщений: 215
_мухомор
to_date('01.03.2008','dd.mm.yyyy') -> DATE
to_char(c.DATE_TIME, 'dd.mm.yyyy') -> строка

заменяем в MV to_char(c.DATE_TIME, 'dd.mm.yyyy') на c.DATE_TIME, наслаждаемся.



заменяем в MV to_char(c.DATE_TIME, 'dd.mm.yyyy') на c.DATE_TIME, наслаждаемся.
Не катит. Мат-вью не делает - говорит что в темпе мало места. И при этом как я и предпологал он и по времени группирует.. а мне это не надо.
14 май 08, 16:16    [5662765]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Zeda
_мухомор
to_date('01.03.2008','dd.mm.yyyy') -> DATE
to_char(c.DATE_TIME, 'dd.mm.yyyy') -> строка

заменяем в MV to_char(c.DATE_TIME, 'dd.mm.yyyy') на c.DATE_TIME, наслаждаемся.



заменяем в MV to_char(c.DATE_TIME, 'dd.mm.yyyy') на c.DATE_TIME, наслаждаемся.
Не катит. Мат-вью не делает - говорит что в темпе мало места. И при этом как я и предпологал он и по времени группирует.. а мне это не надо.

а вы все ответы читаете или через два третий?
14 май 08, 17:18    [5663434]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
G.M.
Member

Откуда:
Сообщений: 342
У вас же в базовой таблице DATE_TIME - поле типа DATE, а в матвью - типа VARCHAR2. Какая нахрен перезапись???
14 май 08, 17:27    [5663508]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Zeda
Member

Откуда: USSR
Сообщений: 215
orawish
Zeda
_мухомор
to_date('01.03.2008','dd.mm.yyyy') -> DATE
to_char(c.DATE_TIME, 'dd.mm.yyyy') -> строка

заменяем в MV to_char(c.DATE_TIME, 'dd.mm.yyyy') на c.DATE_TIME, наслаждаемся.



заменяем в MV to_char(c.DATE_TIME, 'dd.mm.yyyy') на c.DATE_TIME, наслаждаемся.
Не катит. Мат-вью не делает - говорит что в темпе мало места. И при этом как я и предпологал он и по времени группирует.. а мне это не надо.

а вы все ответы читаете или через два третий?

Все сообщения читаю :)
Вот так пробовал:

CREATE MATERIALIZED VIEW BIG_TABLE_STAT
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT 
       COUNT(*) count 
      ,SUM(c.в) sum
      ,trunc(c.DATE_TIME) day
      ,c.R
      ,c.route1
      ,c.route2

  FROM BIG_TABLE c

 GROUP BY trunc(c.DATE_TIME)
         ,c.R
         ,c.route1
         ,c.route2

и так тоже:
CREATE MATERIALIZED VIEW BIG_TABLE_STAT
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT 
       COUNT(*) count 
      ,SUM(c.в) sum
      ,to_date(c.DATE_TIME,'dd.mm.yyyy') day
      ,c.R
      ,c.route1
      ,c.route2

  FROM BIG_TABLE c

 GROUP BY to_date(c.DATE_TIME,'dd.mm.yyyy')
         ,c.R
         ,c.route1
         ,c.route2

все равно не катит - если поставить даты то думает долго ..

автор
У вас же в базовой таблице DATE_TIME - поле типа DATE, а в матвью - типа VARCHAR2. Какая нахрен перезапись???

тут оно уже не варчар...
14 май 08, 22:18    [5664699]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
Ты все-таки подумай насчет trunc
Запрос, естественно, придется изменить немного :)
tst> create table t1(a number, b number, c date);

Table created.

tst> insert into t1 select trunc(dbms_random.value(1, 10)),
  2  trunc(dbms_random.value(1, 5)),
  3  sysdate - dbms_random.value(1, 10)
  4  from dual connect by level <= 1000000;

1000000 rows created.

tst> create materialized view t1_mv
  2  refresh force on commit
  3  enable query rewrite as
  4  select a, b, trunc(c) tc, count(*) count
  5  from t1 group by a, b, trunc(c);

Materialized view created.

tst> exec dbms_stats.gather_schema_stats(user, cascade=>true);

PL/SQL procedure successfully completed.

tst> explain plan for
  2  select count(*) from t1
  3  where trunc(c) between trunc(sysdate-5) and trunc(sysdate-4)
  4  and a=3 and b=2;

Explained.

tst> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    18 |     2 |
|   1 |  SORT AGGREGATE      |             |     1 |    18 |       |
|*  2 |   FILTER             |             |       |       |       |
|*  3 |    TABLE ACCESS FULL | T1_MV       |     1 |    18 |     2 |
--------------------------------------------------------------------

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

   2 - filter(TRUNC(SYSDATE@!-5)<=TRUNC(SYSDATE@!-4))
   3 - filter("T1_MV"."TC">=TRUNC(SYSDATE@!-5) AND
              "T1_MV"."TC"<=TRUNC(SYSDATE@!-4) AND "T1_MV"."A"=3 AND
              "T1_MV"."B"=2)

Note: cpu costing is off

19 rows selected.

tst> select count(*) from t1
  2  where trunc(c) between trunc(sysdate-5) and trunc(sysdate-4)
  3  and a=3 and b=2;

  COUNT(*)
----------
      6121
15 май 08, 04:44    [5665293]     Ответить | Цитировать Сообщить модератору
 Re: Materialized vew и тип date  [new]
Zeda
Member

Откуда: USSR
Сообщений: 215
Ура! Заработало.
оракл не использовал моё мат вью потому, что исходная таблица не была проанализирована. Вот и все :).

Всем спасибо огромное :)
особенно Вячеславу.
15 май 08, 13:10    [5667579]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить