Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 выборка по непроиндексированной колонке  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Привет всем!

Есть таблица table1, проиндексирована по колонке date1, на данный момент всего 74 542 506 записей

select date '2012-12-31' date1, date '2012-12-30' date2, 1 avalue from dual union all 
select date '2012-12-31' date1, null              date2, 1 avalue from dual union all
select date '2012-12-31' date1, null              date2, 2 avalue from dual


Требуется выполнить выборку по колонке date2 (если это значение заполнено для данной записи) или по date1 (если значение date2 незаполнено для данной записи).

С условием " where date1 = date '2012-12-31' " выполняется за 3 секунды,

с условием " where nvl(date2, date1) = date '2012-12-31' " выполняется уже минут десять и еще в процессе.

Кто подскажет, что можно сделать? Индекс по колонке date2 не могу создать, не моя схема.
2 янв 13, 00:35    [13715212]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
miksoft
Member

Откуда:
Сообщений: 38553
ecivgamer
Индекс по колонке date2 не могу создать, не моя схема.
Запросите создание индекса у того кто может.
2 янв 13, 00:43    [13715227]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
24:57 mins за одну дату.
А нужно за месяц...
Подскажите плз, что можно сделать.
2 янв 13, 00:43    [13715228]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
miksoft,

запрошу, конечно - может пока запрос выполнят (если выполнят), кто-то еще что подскажет...
2 янв 13, 00:44    [13715233]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
ecivgamer
запрошу, конечно - может пока запрос выполнят (если выполнят), кто-то еще что подскажет...


Индекс по date2 не поможет. Если date1 NOT NULL, то может помочь индекс на date2,date1:

SQL> create table test as
  2  select date '2012-12-31' date1, date '2012-12-30' date2, 1 avalue from dual
 union all
  3  select date '2012-12-31' date1, null              date2, 1 avalue from dual
 union all
  4  select date '2012-12-31' date1, null              date2, 2 avalue from dual

  5  /

Table created.

SQL> create index test_idx1 on test(date1)
  2  /

Index created.

SQL> explain plan for
  2  select  *
  3    from  test
  4    where date1 = date '2012-12-31'
  5  /

Explained.

SQL> @?\rdbms\admin\utlxpls

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

Plan hash value: 2624864549

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     3 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     3 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     3 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

---------------------------------------------------

   2 - access("DATE1"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> explain plan for
  2  select  *
  3    from  test
  4    where date2 = date '2012-12-31'
  5       or (date2 is null and date1 = date '2012-12-31')
  6  /

Explained.

SQL> @?\rdbms\admin\utlxpls

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

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    62 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     2 |    62 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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


   1 - filter("DATE2"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') OR "DATE2" IS NULL AND "DATE1"=TO_DATE(' 2012-12-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

SQL> alter table test modify date1 not null;

Table altered.

SQL>  create index test_idx2 on test(date2,date1);

Index created.

SQL> explain plan for
  2  select  *
  3    from  test
  4    where date2 = date '2012-12-31'
  5       or (date2 is null and date1 = date '2012-12-31')
  6  /

Explained.

SQL> @?\rdbms\admin\utlxpls

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

Plan hash value: 2674620042

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     3 |    93 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST      |     2 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_IDX2 |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| TEST      |     1 |    31 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | TEST_IDX2 |     1 |       |     1   (0)| 00:00:01 |

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

------------------------------------------------------------------------------------------

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

   3 - access("DATE2" IS NULL AND "DATE1"=TO_DATE(' 2012-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))
       filter("DATE1"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("DATE2"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(LNNVL("DATE2" IS NULL) OR LNNVL("DATE1"=TO_DATE(' 2012-12-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

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


Note
-----
   - dynamic sampling used for this statement (level=2)

26 rows selected.

SQL>


Ну а если date1 NULLable, то только FBI на nvl(date2, date1).

SY.

Сообщение было отредактировано: 2 янв 13, 02:48
2 янв 13, 02:40    [13715505]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
SY,


SY
Индекс по date2 не поможет.
ну почему столь категорично, ведь может помочь:
+
DB11G/XTENDER> create table test as
  2  select date '2012-12-31' date1, date '2012-12-30' date2, 1 avalue from dual union all
  3  select date '2012-12-31' date1, null              date2, 1 avalue from dual union all
  4  select date '2012-12-31' date1, null              date2, 2 avalue from dual;

Table created.

DB11G/XTENDER> create index ix_test_1 on test(date1);

Index created.

DB11G/XTENDER> create index ix_test_2 on test(date2);

Index created.

DB11G/XTENDER> explain plan for
  2  select
  3    *
  4  from test
  5  where date2 = date '2012-12-31'
  6        or (date2 is null and date1 = date '2012-12-31');

Explained.

DB11G/XTENDER> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1856938139

----------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |     2 |    62 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID     | TEST      |     2 |    62 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |           |       |       |            |          |
|   3 |    BITMAP OR                     |           |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|           |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IX_TEST_2 |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|           |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | IX_TEST_1 |       |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - filter("DATE2"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR
              "DATE2" IS NULL AND "DATE1"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("DATE2"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("DATE1"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

DB11G/XTENDER> explain plan for
  2  select/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */
  3    *
  4  from test
  5  where date2 = date '2012-12-31'
  6        or (date2 is null and date1 = date '2012-12-31');

Explained.

DB11G/XTENDER> @xplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1412852006

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     3 |    93 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |           |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TEST      |     2 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IX_TEST_1 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| TEST      |     1 |    31 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IX_TEST_2 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - filter("DATE2" IS NULL)
   3 - access("DATE1"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - filter(LNNVL("DATE2" IS NULL) OR LNNVL("DATE1"=TO_DATE(' 2012-12-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   5 - access("DATE2"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   
2 янв 13, 05:00    [13715537]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
хотя порядок лучше поменять:
+
DB11G/XTENDER> explain plan for
  2  select--+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
  3    *
  4  from test
  5  where (date2 is null and date1 = date '2012-12-31')
  6        or date2 = date '2012-12-31';

Explained.

DB11G/XTENDER> @xplan
Plan hash value: 3633702934

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     3 |    93 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST      |     1 |    31 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IX_TEST_2 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| TEST      |     2 |    62 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IX_TEST_1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   3 - access("DATE2"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - filter("DATE2" IS NULL AND LNNVL("DATE2"=TO_DATE(' 2012-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
   5 - access("DATE1"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
2 янв 13, 05:06    [13715540]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54390
ecivgamer
24:57 mins за одну дату.
А нужно за месяц...
Подскажите плз, что можно сделать.
ну и делай запрос сразу за месяц
2 янв 13, 08:54    [13715613]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Получил ответ от владельца схемы:


индекс не рекомендуем, так как у это партиционированная табличка
лучше в запрос внести еще одно условие отбора по date1, чтобы ограничить данные одной/двумя партициями

например:

and date1 between date '2012-10-01' and date '2012-10-01' + 35




что на это скажете?
4 янв 13, 16:06    [13722804]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ecivgamer,

если запрос разовый (/ редкий) - то терпИте.
если многоразовый, но данные за запрашиваемый (~ прошедший) период не изменяются - материализуйте.
ну а вообще - вы пытаетесь изваять паровоз, который ездит не по рельсам, а по шпалам и ещё хотите чтобы ездил быстро
4 янв 13, 16:15    [13722827]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
orawish
если запрос разовый (/ редкий) - то терпИте.


запрос ежедневный

если многоразовый, но данные за запрашиваемый (~ прошедший) период не изменяются - материализуйте.


данные за прошедший период - таки да - изменяются

ну а вообще - вы пытаетесь изваять паровоз, который ездит не по рельсам, а по шпалам и ещё хотите чтобы ездил быстро


дык вопрос касается корректирующих проводок, это не велосипед, тривиальная (с экономической точки зрения) задача.
4 янв 13, 17:00    [13722973]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ecivgamer
orawish
если запрос разовый (/ редкий) - то терпИте.


запрос ежедневный

если многоразовый, но данные за запрашиваемый (~ прошедший) период не изменяются - материализуйте.


данные за прошедший период - таки да - изменяются

ну а вообще - вы пытаетесь изваять паровоз, который ездит не по рельсам, а по шпалам и ещё хотите чтобы ездил быстро


дык вопрос касается корректирующих проводок, это не велосипед, тривиальная (с экономической точки зрения) задача.

секции какого интервала?
какой процент секций изменяется на день?
ежели запрос промышленный - почему про него забыли, когда проектировали таблицу?
4 янв 13, 17:13    [13723009]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ecivgamer,

+еще важно понимать процент строк, в которых date2 нотнулл
4 янв 13, 17:21    [13723037]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
секции какого интервала?
какой процент секций изменяется на день?


спросил, жду ответа (в основном отвечают не быстро)

ежели запрос промышленный - почему про него забыли, когда проектировали таблицу?


без комментариев

+еще важно понимать процент строк, в которых date2 нотнулл


8716 / 74542506 = 0,000116926576093377
4 янв 13, 18:50    [13723412]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
[quot ecivgamer]
..

+еще важно понимать процент строк, в которых date2 нотнулл


8716 / 74542506 = 0,000116926576093377

дык, очевидно
1) совершенно зря вы засунули эти (разные по атрибутике и гораздо разнокалиберные) данные в одну и ту же таблицу.
2) затычка - локальный маленький fbi
5 янв 13, 03:18    [13725196]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
Нико0411
Member

Откуда: Алматы
Сообщений: 18
  --Если можешь создать таблицу, может это поможет
  CREATE TABLE t#temp( ID INT 
                     , date2 DATE
                     --N1, N2
                     );
   
  CREATE INDEX I#id ON t#temp(ID);
  CREATE INDEX I#DATE2 ON T#TEMP(DATE2);   
  
  --1) Переливай job-ом данные так
  
  TRUNCATE TABLE t#temp;                       
  INSERT INTO t#temp 
  SELECT * FROM TABLE_R
  WHERE DATE1 IS NULL;    
  --или так))                   
  INSERT INTO t#temp
  SELECT * FROM TABLE_R 
  WHERE t.DATE1 IS NULL
    AND NOT EXISTS(SELECT 1 FROM t#temp t
                    WHERE t.id = r.id)
  
  --Это твой запрос
  SELECT * FROM TABLE_R 
  WHERE DATE1 = DATE '2012-12-31'
  UNION  
  SELECT * FROM t#temp
  WHERE DATE2 = DATE '2012-12-31';
7 янв 13, 19:45    [13732594]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
init.ora
Member

Откуда: гетто
Сообщений: 317
может как-нибудь так?

create table ttt parallel 8
as 
select /*+ parallel(t 8)*/ coalesce(date2,date1) c, value 
from table1 t
where date1 between date '2012-10-01' and date '2012-10-01' + 35 ;

create index ttt_idx on ttt( c );

select *
from ttt where c = date '2012-12-31'
8 янв 13, 03:35    [13735077]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ecivgamer
24:57 mins за одну дату.
А нужно за месяц...
Подскажите плз, что можно сделать.
собственно за месяц столько же приблизительно и будет выпооняться если одним запросом сразу за нужный месяц
8 янв 13, 03:41    [13735082]     Ответить | Цитировать Сообщить модератору
 Re: выборка по непроиндексированной колонке  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Нико0411,
init.ora,

Уж проще в коллекцию загнать
8 янв 13, 03:44    [13735084]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить