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

Лента новостей по категориям

ну вот например я делаю набор данных примерно так :
+
CREATE table test_table(id NUMBER, low_cardinality NUMBER, medium_cardinality NUMBER, high_cardinality NUMBER);

/*with generator as (
  select rownum id
  from dual
  connect by rownum <= 1000000)*/
INSERT into test_table (id,low_cardinality,medium_cardinality,high_cardinality)
  select id,CAST((DBMS_RANDOM.VALUE()*10) AS NUMBER(6,0)),CAST((DBMS_RANDOM.VALUE()*100) AS NUMBER(6,0)), CAST((DBMS_RANDOM.VALUE()*10000) AS NUMBER(6,0))
from (
  select rownum id
  from dual
  connect by rownum <= 1000000) generator;
  
create index test_table_low_cardinality_key on test_table(low_cardinality);
create index test_table_m_cardinality_key on test_table(medium_cardinality);
create index test_table_h_cardinality_key on test_table(high_cardinality);

-- наш индекс
create index test_table_lh_cardinality_key on test_table(low_cardinality,high_cardinality );
--analyze test_table;

потом хочу прикинуть, как оно работает с win ф-ями
+
SELECT * FROM (
SELECT t.*, row_number() over (PARTITION BY low_cardinality ORDER BY low_cardinality DESC, high_cardinality DESC ) AS rn 
FROM test_table t 
) foo WHERE rn<5;

--судя по всему - тоже лупит вдоль всей таблицы
|   0 | SELECT STATEMENT         |            |   990K|    61M|  5839 |
|   1 |  VIEW                    |            |   990K|    61M|  5839 |
|   2 |   WINDOW SORT PUSHED RANK|            |   990K|    14M|  5839 |
|   3 |    TABLE ACCESS FULL     | TEST_TABLE |   990K|    14M|   758 |

а как в оракле реализнуть быстрый спуск на глубину 4 второго уровня по индексу (из 11 корней индексного дерева) ?
ткните ссылкой, плз -- мы сами не местныя
17 сен 12, 10:44    [13174335]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
#bnn#
Guest
чиёрт, я даже не могу его простым юнионом нормально заставить пощитаться:
+
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=0 ORDER BY  /*low_cardinality DESC,*/ high_cardinality DESC) foo WHERE rownum<5;
-- нормально вроде так
------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                          | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                               |     4 |   208 |     9 |
|   1 |  COUNT STOPKEY                 |                               |       |       |       |
|   2 |   VIEW                         |                               |     5 |   260 |     9 |
|   3 |    TABLE ACCESS BY INDEX ROWID | TEST_TABLE                    | 90909 |  1331K|     9 |
|   4 |     INDEX RANGE SCAN DESCENDING| TEST_TABLE_LH_CARDINALITY_KEY |     5 |       |     3 |
------------------------------------------------------------------------------------------------
-- как только 
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=0 ORDER BY  /*low_cardinality DESC,*/ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=1 ORDER BY  low_cardinality DESC, high_cardinality DESC) foo WHERE rownum<5;
-- так сразу TABLE ACCESS FULL
-----------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |     8 |   416 |  2454 |
|   1 |  UNION-ALL               |            |       |       |       |
|   2 |   COUNT STOPKEY          |            |       |       |       |
|   3 |    VIEW                  |            | 90909 |  4616K|  1227 |
|   4 |     SORT ORDER BY STOPKEY|            | 90909 |  1331K|  1227 |
|   5 |      TABLE ACCESS FULL   | TEST_TABLE | 90909 |  1331K|   758 |
|   6 |   COUNT STOPKEY          |            |       |       |       |
|   7 |    VIEW                  |            | 90909 |  4616K|  1227 |
|   8 |     SORT ORDER BY STOPKEY|            | 90909 |  1331K|  1227 |
|   9 |      TABLE ACCESS FULL   | TEST_TABLE | 90909 |  1331K|   758 |
-----------------------------------------------------------------------

-- г-да ораклоиды, тут что, надо какие-то пассы руками делать, чтобы оно как-то дотумкало не скатываться к TABLE ACCESS FULL ?
а как скажем тот же скромный постгрес 13172619 считало.
может быть ахалай-махалай какой ? <и хвостом вот эдак...>

не подскажете, что почитать-то надо ? (а то мы тут сами не местные)
или я всё неправильно читаю ? и не надо в этот план верить ?
17 сен 12, 14:14    [13175843]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
компа под рукой нет, но возможно
create index test_table_lh_cardinality_key on test_table(low_cardinality DESC,high_cardinality DESC);

а вообще low_cardinality DESC в order by лишнее, по нему и так партицируется
17 сен 12, 14:28    [13175970]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
Чухня
Guest
#bnn#
over (PARTITION BY low_cardinality ORDER BY low_cardinality DESC, high_cardinality DESC )
Не надо лезть в дебри, пока не поймешь более простые вещи.
17 сен 12, 14:50    [13176231]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
#bnn#
Guest
bdsm_sql
компа под рукой нет, но возможно
create index test_table_lh_cardinality_key on test_table(low_cardinality DESC,high_cardinality DESC);

а вообще low_cardinality DESC в order by лишнее, по нему и так партицируется

спасибо, добрый человек,
ишь, какой он оказывается капризный, этот оракел

действительно:
+
create index test_table_lhd_cardinality_key on test_table(low_cardinality DESC,high_cardinality DESC);
--
--и имеем:
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=0  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=1  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=2  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=3  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=4  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=5  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=6  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=7  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=8  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=9  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
UNION ALL
SELECT * FROM (SELECT * FROM test_table WHERE low_cardinality=10  ORDER BY   /* low_cardinality DESC, */ high_cardinality DESC) foo WHERE rownum<5
;
--
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                           | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                                |    44 |  2288 |  9314 |
|   1 |  UNION-ALL                      |                                |       |       |       |
|   2 |   COUNT STOPKEY                 |                                |       |       |       |
|   3 |    VIEW                         |                                | 90909 |  4616K|   847 |
|   4 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|   5 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|   6 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|   7 |   COUNT STOPKEY                 |                                |       |       |       |
|   8 |    VIEW                         |                                | 90909 |  4616K|   847 |
|   9 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  10 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  11 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  12 |   COUNT STOPKEY                 |                                |       |       |       |
|  13 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  14 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  15 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  16 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  17 |   COUNT STOPKEY                 |                                |       |       |       |
|  18 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  19 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  20 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  21 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  22 |   COUNT STOPKEY                 |                                |       |       |       |
|  23 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  24 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  25 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  26 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  27 |   COUNT STOPKEY                 |                                |       |       |       |
|  28 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  29 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  30 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  31 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  32 |   COUNT STOPKEY                 |                                |       |       |       |
|  33 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  34 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  35 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  36 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  37 |   COUNT STOPKEY                 |                                |       |       |       |
|  38 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  39 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  40 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  41 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  42 |   COUNT STOPKEY                 |                                |       |       |       |
|  43 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  44 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  45 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  46 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  47 |   COUNT STOPKEY                 |                                |       |       |       |
|  48 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  49 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  50 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  51 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
|  52 |   COUNT STOPKEY                 |                                |       |       |       |
|  53 |    VIEW                         |                                | 90909 |  4616K|   847 |
|  54 |     SORT ORDER BY STOPKEY       |                                | 90909 |  1331K|   847 |
|  55 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE                     | 90909 |  1331K|   378 |
|  56 |       INDEX RANGE SCAN          | TEST_TABLE_LHD_CARDINALITY_KEY |   364 |       |    14 |
--------------------------------------------------------------------------------------------------
а "вообще лишнее" -- я уже потом туда впендюрил - чтобы типа оракл догадался сам обратить индекс. [Index Scan Backward using test_table_low_high_cardinality_key]
17 сен 12, 14:51    [13176247]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
#bnn#
Guest
Чухня
#bnn#
over (PARTITION BY low_cardinality ORDER BY low_cardinality DESC, high_cardinality DESC )
Не надо лезть в дебри, пока не поймешь более простые вещи.
ну, если вам сложно убедиться что при честном
over (PARTITION BY low_cardinality ORDER BY high_cardinality DESC )
мы имеем то же самое, то надувайте своё ЧСВ, я не против.

для особо умных чухонцев поясню, ORDER BY const, fld - сортирует вполне себе по fld.
17 сен 12, 14:59    [13176333]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
#bnn#
Чухня
пропущено...
Не надо лезть в дебри, пока не поймешь более простые вещи.
ну, если вам сложно убедиться что при честном
over (PARTITION BY low_cardinality ORDER BY high_cardinality DESC )
мы имеем то же самое, то надувайте своё ЧСВ, я не против.

для особо умных чухонцев поясню, ORDER BY const, fld - сортирует вполне себе по fld.

order by const думаю игнорируется

в случае с PARTITION BY low_cardinality ORDER BY high_cardinality DESC - все равно имеем два поля, причем второе сортируется по DESC.
оракле умеет разворачивать сортировку индекса, но не отдельного поля внутри составного.

т.е. для PARTITION BY low_cardinality ORDER BY high_cardinality DESC тебе все равно понадобится индекс по (low_cardinality, high_cardinality DESC), или (low_cardinality DESC, high_cardinality DESC)
17 сен 12, 15:04    [13176392]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
#bnn#
Guest
bdsm_sql
в случае с PARTITION BY low_cardinality ORDER BY high_cardinality DESC - все равно имеем два поля, причем второе сортируется по DESC.
оракле умеет разворачивать сортировку индекса, но не отдельного поля внутри составного.

т.е. для PARTITION BY low_cardinality ORDER BY high_cardinality DESC тебе все равно понадобится индекс по (low_cardinality, high_cardinality DESC), или (low_cardinality DESC, high_cardinality DESC)
ну так больше ничего и не требуется при
a =const ORDER BY b DESC
достаточно обратить индекс (a,b) целиком, (а не отдельно поле b внутри)
(то же верно для серии a =const ORDER BY b DESC+ UNION ALL )
-- просто пока не пощупаешь руками сам - не все особости ясны. я ж ему не в упрёк.
17 сен 12, 15:11    [13176462]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
xtender
Member

Откуда: Мск
Сообщений: 4331
да можно было просто index_desc, а поводу фулскана - надо было сделать not null. Вообще по-хорошему нужно таблицу с этими low_cardinality, а в данном случае лучше всего будет так:
+
alter table test_table modify low_cardinality not null;
alter table test_table modify high_cardinality not null;
create index test_table_lh_key_desc on test_table(low_cardinality,high_cardinality desc);

with 
  pre as (
   select s.rid
   from (
       select/*+ index(tt test_table_lh_key_desc) */ 
              tt.rowid rid
             ,row_number()over(partition by tt.low_cardinality order by tt.high_cardinality desc) rn
       from test_table tt
    ) s
   where rn<=5
)
select/*+ leading(p t) use_nl(p t) */
t.* 
from test_table t,pre p
where t.rowid=p.rid
17 сен 12, 15:13    [13176475]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
xtender
Member

Откуда: Мск
Сообщений: 4331
или так - не надежно
+
with vals as(
                select distinct low_cardinality lc from test_table
)
select v.*,tt.*
from vals v
    ,table(
          cast(
               multiset(
                        select/*+ index(tt test_table_lh_key_desc) */ tt.rowid rid
                        from test_table tt
                        where tt.low_cardinality=v.lc
                          and rownum<=5
                        order by tt.high_cardinality desc
                       )
               as sys.odcivarchar2list
              )
          ) rids
    ,test_table tt
where tt.rowid=rids.column_value
17 сен 12, 15:27    [13176641]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
#bnn#
Guest
вот тут обновление решения появилось для postgresql
13191025
а можно что-то наподобие этого в оракле через CONNECT BY нарисовать ?
что-то мне фантазии не хватает.
20 сен 12, 16:00    [13197178]     Ответить | Цитировать Сообщить модератору
 Re: задачка  [new]
xtender
Member

Откуда: Мск
Сообщений: 4331
#bnn#,

да, неплохо... вот мое предыдущее решение допиленное с учетом рекурсии:
+
with t_rec( a,lc ) as (
              select 1 a,min(t1.low_cardinality)
              from test_table t1
              union all
              select a+1,(select min(t1.low_cardinality) from test_table t1 where t1.low_cardinality>t.lc)
              from t_rec t
              where lc is not null
)
select *
from t_rec v
    ,table(
          cast(
               multiset(
                        select/*+ index(tt test_table_lh_key_desc) */ tt.rowid rid
                        from test_table tt
                        where tt.low_cardinality=v.lc
                          and rownum<=5
                        order by tt.high_cardinality desc
                       )
               as sys.odcivarchar2list
              )
          ) rids
    ,test_table tt
where tt.rowid=rids.column_value
20 сен 12, 16:52    [13197501]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить