помоготе оптимизировать запрос

masterl77
Дата: 12.03.2011 21:00:24
есть таблица
create table tst (a int,b int,c int);
CREATE INDEX tst1 ON tst(b, c, a);
CREATE INDEX tst2 ON tst(a, b, c);
CREATE INDEX tst3 ON tst(a, c);
данные для примера
1,1,1
1,2,2
1,2,3
2,1,2
2,2,3
2,3,4
3,1,3
3,2,4
3,3,5
и так 100 лимонов

select a, c,
(select min(c) FROM tst t2 where t2.a=tst.a AND t2.c > tst.c and t2.b=2
and exists(select c from tst t3 where t3.b=1 and t3.a = t2.a and t3.c < t2.c)
)tst2

from tst
where rownum<1000
работает очень долго

что интересно на MsSql аналогичный работает намного быстрее
подскажите что можно поменять?
-2-
Дата: 12.03.2011 21:09:39
masterl77
что интересно на MsSql аналогичный работает намного быстрее
подскажите что можно поменять?
СУБД
masterl77
Дата: 12.03.2011 21:13:10
-2-
masterl77
что интересно на MsSql аналогичный работает намного быстрее
подскажите что можно поменять?
СУБД


oracle 10.2.0.1.0
dbms_photoshop
Дата: 12.03.2011 21:14:24
masterl77
что интересно на MsSql аналогичный работает намного быстрее
подскажите что можно поменять?

1. Научится писать тест кейс, смотреть и показывать план.
2. Научится хотя бы форматировать код.
select a,
       c,
       (select min(c)
          from tst t2
         where t2.a = tst.a
           and t2.c > tst.c
           and t2.b = 2
           and exists (select c
                  from tst t3
                 where t3.b = 1
                   and t3.a = t2.a
                   and t3.c < t2.c)) tst2
  from tst
 where rownum < 1000;
3. Научится формулировать что хочешь получить запросом.
4. Научится менять exists на join.
-2-
Дата: 12.03.2011 21:15:34
masterl77
-2-
пропущено...
СУБД
oracle 10.2.0.1.0
Поменяй на MSSQL 2008
dbms_photoshop
Дата: 12.03.2011 21:18:29
Тьфу, мягкий знак везде забыл...

Я сегодня добрый и все сделаю за тебя.
SQL> create table tst as select dbms_random.value a, dbms_random.value b, dbms_random.value c from dual connect by level <= 1000;

Table created.

SQL> CREATE INDEX tst1 ON tst(b, c, a);

Index created.

SQL> CREATE INDEX tst2 ON tst(a, b, c);

Index created.

SQL> CREATE INDEX tst3 ON tst(a, c);

Index created.

SQL>
SQL> begin
  2    dbms_stats.gather_table_stats(user,
  3                                  'TST',
  4                                  estimate_percent => null,
  5                                  method_opt       => 'FOR ALL COLUMNS SIZE SKEWONLY',
  6                                  cascade          => true);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> set autot traceonly
SQL>
SQL> select a,
  2         c,
  3         (select min(c)
  4            from tst t2
  5           where t2.a = tst.a
  6             and t2.c > tst.c
  7             and t2.b = 2
  8             and exists (select c
  9                    from tst t3
 10                   where t3.b = 1
 11                     and t3.a = t2.a
 12                     and t3.c < t2.c)) tst2
 13    from tst
 14   where rownum < 1000;

999 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2502515299

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   999 | 41958 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |   124 |            |          |
|   2 |   NESTED LOOPS     |      |     1 |   124 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| TST1 |     1 |    62 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| TST1 |     1 |    62 |     1   (0)| 00:00:01 |
|*  5 |  COUNT STOPKEY     |      |       |       |            |          |
|   6 |   TABLE ACCESS FULL| TST  |  1000 | 42000 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - access("T2"."B"=2 AND "T2"."C">:B1 AND "T2"."A"=:B2 AND "T2"."C"
              IS NOT NULL)
       filter("T2"."A"=:B1)
   4 - access("T3"."B"=1 AND "T3"."A"=:B1 AND "T3"."C"<"T2"."C")
       filter("T3"."A"=:B1 AND "T3"."A"="T2"."A")
   5 - filter(ROWNUM<1000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1146  consistent gets
          0  physical reads
          0  redo size
      53036  bytes sent via SQL*Net to client
       1076  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

Ничего в плане не смущает?
dbms_photoshop
Дата: 12.03.2011 21:27:53
dbms_photoshop
Ничего в плане не смущает?
Хм... заметил в твоем сообщении, что имеются некоторые закономерности в данных, которые я в тест кейсе не учел.
Так что все же опиши особенности данных и то, что хочешь получить.
dbms_photoshop
Дата: 12.03.2011 22:21:14
Чего не сделаешь от скуки...

alter session set statistics_level = all;

Session altered.

Elapsed: 00:00:00.00

create table tst as
select trunc((rownum+2)/3) a, trunc(dbms_random.value(1, 3+1)) b, trunc((rownum+2)/3)+mod((rownum+2),3) c
from dual
connect by level <= 1000000;

Table created.

Elapsed: 00:00:07.93

CREATE INDEX tst1 ON tst(b, c, a);

Index created.

Elapsed: 00:00:03.17
--CREATE INDEX tst2 ON tst(a, b, c);
--CREATE INDEX tst3 ON tst(a, c);

begin
  dbms_stats.gather_table_stats(user,
                                'TST',
                                estimate_percent => null,
                                method_opt       => 'FOR ALL COLUMNS SIZE SKEWONLY',
                                cascade          => true);
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.84

select sum(a + c + tst2) result
  from (select a,
               c,
               (select min(c)
                  from tst t2
                 where t2.a = tst.a
                   and t2.c > tst.c
                   and t2.b = 2
                   and exists (select c
                          from tst t3
                         where t3.b = 1
                           and t3.a = t2.a
                           and t3.c < t2.c)) tst2
          from tst
         where rownum < 1000);

    RESULT
----------
     64432

Elapsed: 00:01:09.53

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cxhf9vhdvxdnc, child number 0
-------------------------------------
select sum(a + c + tst2) result   from (select a,                c,
    (select min(c)                   from tst t2                  where t2.a =
tst.a                    and t2.c > tst.c                    and t2.b = 2
              and exists (select c                           from tst t3
                   where t3.b = 1                            and t3.a = t2.a
                         and t3.c < t2.c)) tst2           from tst
where rownum < 1000)

Plan hash value: 2135431802

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |      |    999 |      1 |    999 |00:01:09.49 |    1115K|
|   2 |   NESTED LOOPS       |      |    999 |      1 |    174 |00:01:09.48 |    1115K|
|*  3 |    INDEX RANGE SCAN  | TST1 |    999 |      1 |    344 |00:01:09.46 |    1114K|
|*  4 |    INDEX RANGE SCAN  | TST1 |    344 |      1 |    174 |00:00:00.02 |     693 |
|   5 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:01:09.50 |    1115K|
|   6 |   VIEW               |      |      1 |    999 |    999 |00:01:09.50 |    1115K|
|*  7 |    COUNT STOPKEY     |      |      1 |        |    999 |00:00:00.01 |       6 |
|   8 |     TABLE ACCESS FULL| TST  |      1 |   1000K|    999 |00:00:00.01 |       6 |
---------------------------------------------------------------------------------------

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

   3 - access("T2"."B"=2 AND "T2"."C">:B1 AND "T2"."A"=:B2 AND "T2"."C" IS NOT
              NULL)
       filter("T2"."A"=:B1)
   4 - access("T3"."B"=1 AND "T3"."A"=:B1 AND "T3"."C"<"T2"."C")
       filter(("T3"."A"=:B1 AND "T3"."A"="T2"."A"))
   7 - filter(ROWNUM<1000)


35 rows selected.

Elapsed: 00:00:00.27

select sum(a + c + tst2) result
  from (select tst.a, tst.c, min(t2.c) tst2
          from (select a, c from tst where rownum < 1000) tst
          left join tst t2
            on t2.a = tst.a
           and t2.c > tst.c
           and t2.b = 2
           and exists (select null
                  from tst t3
                 where t3.b = 1
                   and t3.a = t2.a
                   and t3.c < t2.c)
         group by tst.a, tst.c);

    RESULT
----------
     64432

Elapsed: 00:01:29.04

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0zxg9x9jp8b4z, child number 0
-------------------------------------
select sum(a + c + tst2) result   from (select tst.a, tst.c, min(t2.c) tst2           from (select a, c from
tst where rownum < 1000) tst           left join tst t2             on t2.a = tst.a            and t2.c >
tst.c            and t2.b = 2            and exists (select null                   from tst t3
   where t3.b = 1                    and t3.a = t2.a                    and t3.c < t2.c)          group by
tst.a, tst.c)

Plan hash value: 2041982616

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE         |      |      1 |      1 |      1 |00:01:29.03 |    1551K|       |    |     |
|   2 |   VIEW                  |      |      1 |   7992 |    999 |00:01:29.03 |    1551K|       |    |     |
|   3 |    HASH GROUP BY        |      |      1 |   7992 |    999 |00:01:29.03 |    1551K|   904K|   904K| 1292K (0)|
|   4 |     NESTED LOOPS OUTER  |      |      1 |   7992 |   1009 |00:01:29.02 |    1551K|       |    |     |
|   5 |      VIEW               |      |      1 |    999 |    999 |00:00:00.01 |       6 |       |    |     |
|*  6 |       COUNT STOPKEY     |      |      1 |        |    999 |00:00:00.01 |       6 |       |    |     |
|   7 |        TABLE ACCESS FULL| TST  |      1 |   1000K|    999 |00:00:00.01 |       6 |       |    |     |
|   8 |      VIEW               |      |    999 |      8 |    138 |00:01:29.01 |    1551K|       |    |     |
|*  9 |       HASH JOIN SEMI    |      |    999 |      8 |    138 |00:01:29.01 |    1551K|  1023K|  1023K|  338K (0)|
|* 10 |        INDEX RANGE SCAN | TST1 |    999 |    167 |    344 |00:01:04.32 |    1115K|       |    |     |
|* 11 |        TABLE ACCESS FULL| TST  |    303 |    333K|     58M|00:00:00.01 |     435K|       |    |     |
---------------------------------------------------------------------------------------------------------------------

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

   6 - filter(ROWNUM<1000)
   9 - access("T3"."A"="T2"."A")
       filter("T3"."C"<"T2"."C")
  10 - access("T2"."B"=2 AND "T2"."C">"TST"."C" AND "T2"."A"="TST"."A" AND "T2"."C" IS NOT NULL)
       filter("T2"."A"="TST"."A")
  11 - filter("T3"."B"=1)


36 rows selected.

Elapsed: 00:00:00.27

select sum(a + c + tst2) result
  from (select tst.a,
               tst.c,
               case
                 when count(t3.a) > 0 then
                  min(t2.c)
                 else
                  null
               end tst2
          from (select a, c from tst where rownum < 1000) tst
          left join tst t2
            on t2.a = tst.a
           and t2.c > tst.c
           and t2.b = 2
          left join tst t3
            on t3.b = 1
           and t3.a = t2.a
           and t3.c < t2.c
         group by tst.a, tst.c);

    RESULT
----------
     64432

Elapsed: 00:00:00.32

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gv5h9x8s1hpa6, child number 0
-------------------------------------
select sum(a + c + tst2) result   from (select tst.a,                tst.c,                case
   when count(t3.a) > 0 then                   min(t2.c)                  else                   null
       end tst2           from (select a, c from tst where rownum < 1000) tst           left join tst t2
       on t2.a = tst.a            and t2.c > tst.c            and t2.b = 2           left join tst t3
    on t3.b = 1            and t3.a = t2.a            and t3.c < t2.c          group by tst.a, tst.c)

Plan hash value: 2010606776

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE          |      |      1 |      1 |      1 |00:00:00.30 |    4978 |       |    |             |
|   2 |   VIEW                   |      |      1 |    999 |    999 |00:00:00.30 |    4978 |       |    |             |
|   3 |    HASH GROUP BY         |      |      1 |    999 |    999 |00:00:00.30 |    4978 |   869K|   869K| 1215K (0)|
|*  4 |     HASH JOIN OUTER      |      |      1 |    999 |   1076 |00:00:00.30 |    4978 |  1114K|  1114K| 1261K (0)|
|*  5 |      HASH JOIN OUTER     |      |      1 |    999 |   1040 |00:00:00.15 |    2492 |  1269K|  1269K| 1292K (0)|
|   6 |       VIEW               |      |      1 |    999 |    999 |00:00:00.01 |       6 |       |    |             |
|*  7 |        COUNT STOPKEY     |      |      1 |        |    999 |00:00:00.01 |       6 |       |    |             |
|   8 |         TABLE ACCESS FULL| TST  |      1 |   1000K|    999 |00:00:00.01 |       6 |       |    |             |
|*  9 |       TABLE ACCESS FULL  | TST  |      1 |    333K|    333K|00:00:00.01 |    2486 |       |    |             |
|* 10 |      TABLE ACCESS FULL   | TST  |      1 |    333K|    333K|00:00:00.01 |    2486 |       |    |             |
----------------------------------------------------------------------------------------------------------------------

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

   4 - access("T3"."A"="T2"."A")
       filter("T3"."C"<"T2"."C")
   5 - access("T2"."A"="TST"."A")
       filter("T2"."C">"TST"."C")
   7 - filter(ROWNUM<1000)
   9 - filter("T2"."B"=2)
  10 - filter("T3"."B"=1)


36 rows selected.

Elapsed: 00:00:00.12
masterl77
Дата: 13.03.2011 02:21:32
dbms_photoshop
Чего не сделаешь от скуки...


большое спасибо
опробую
masterl77
Дата: 13.03.2011 22:12:42
решил не создавать новую тему вопрос совершенно другой
есть пример таблицы
drop table tst;
create table tst(t int);
insert into tst values (1);
insert into tst values (2);
insert into tst values (3);

простейший запрос
select t,
(select min(t) from tst t2 where t2.t>tst.t)
from tst
where tst.t=2
даст само собой 2,3

а вот почему дает как по мне странный результат 2,1
select t,
(select max(z) from
(
select min(t2.t) z from tst t2 where t2.t>tst.t
union
select 1 from dual
) z)
from tst
where tst.t=2