Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Задача выходного дня (group by)  [new]
Клоун'с
Guest
Дано:

CREATE TABLE departments
  (id1 NUMBER, id2 NUMBER
  );

CREATE TABLE employees
  (id1 NUMBER, id2 NUMBER
  );


INSERT INTO departments VALUES (1, 100 );
INSERT INTO departments VALUES (2, 200 );


INSERT INTO employees VALUES (1, 10 );
INSERT INTO employees VALUES (1, 20 );
INSERT INTO employees VALUES (1, 30 );
INSERT INTO employees VALUES (2, 10 );
INSERT INTO employees VALUES (2, 20 );
INSERT INTO employees VALUES (3, 10 );


SELECT *
  FROM departments d
  WHERE EXISTS
  (SELECT id1 FROM employees e
    WHERE d.id1 
    = e.id1);


Вопрос, почему при таком написании подзапрос отрабатывает

SELECT *
FROM departments d
WHERE EXISTS
  (SELECT e.id1
   FROM employees e
  WHERE d.id1=e.id1
  AND e.id1  = 2
  GROUP BY e.id1
  )



А при таком, даже в плане запроса отсутствует

SELECT *
FROM departments d
WHERE EXISTS
  (SELECT e.id1, min(e.id2)
   FROM employees e
  WHERE d.id1=e.id1
  AND e.id1  = 2
  GROUP BY e.id1
  )
23 май 09, 11:28    [7218470]     Ответить | Цитировать Сообщить модератору
 Re: Задача выходного дня (group by)  [new]
Клоун'с
Guest
Вот так c min подзапрос в плане появляется

SELECT *
FROM departments d
WHERE EXISTS
  (SELECT e.id1, min(e.id2)
   FROM employees e
  WHERE d.id1=e.id1
   GROUP BY e.id1
     HAVING e.id1  = 2 -- перенес
  )
23 май 09, 11:50    [7218495]     Ответить | Цитировать Сообщить модератору
 Re: Задача выходного дня (group by)  [new]
stax..
Guest
Клоун'с,

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
SQL> explain plan for
  2  SELECT *
  3  FROM d d
  4  WHERE EXISTS
  5    (SELECT e.id1
  6     FROM e e
  7    WHERE d.id1=e.id1
  8    AND e.id1  = 2
  9    GROUP BY e.id1
 10    )
 11  /

Explained.

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

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

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | D           |       |       |       |
|   3 |   SORT GROUP BY      |             |       |       |       |
|*  4 |    TABLE ACCESS FULL | E           |       |       |       |
--------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "E" "E" WHERE "E"."ID1"=2 AND
              "E"."ID1"=:B1 GROUP BY "E"."ID1"))
   4 - filter("E"."ID1"=2 AND "E"."ID1"=:B1)

Note: rule based optimization

19 rows selected.

SQL> explain plan for
  2  SELECT *
  3  FROM d d
  4  WHERE EXISTS
  5    (SELECT e.id1, min(e.id2)
  6     FROM e e
  7    WHERE d.id1=e.id1
  8    AND e.id1  = 2
  9    GROUP BY e.id1
 10    )
 11  /

Explained.

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

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

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | D           |       |       |       |
|   3 |   SORT GROUP BY      |             |       |       |       |
|*  4 |    TABLE ACCESS FULL | E           |       |       |       |
--------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "E" "E" WHERE "E"."ID1"=2 AND
              "E"."ID1"=:B1 GROUP BY "E"."ID1"))
   4 - filter("E"."ID1"=2 AND "E"."ID1"=:B1)

Note: rule based optimization

19 rows selected.

SQL> 

......
stax
23 май 09, 15:08    [7218742]     Ответить | Цитировать Сообщить модератору
 Re: Задача выходного дня (group by)  [new]
Клоун'с
Guest
В 9-ке да, согласен - все ОК.
В 10g - не работает так.
23 май 09, 15:30    [7218766]     Ответить | Цитировать Сообщить модератору
 Re: Задача выходного дня (group by)  [new]
stax..
Guest
Клоун'с
В 9-ке да, согласен - все ОК.
В 10g - не работает так.

в 11-ке (http://apex.oracle.com)
тож баг

если добавить
SELECT /*+ rule */ *
то в плане EMPLOYEES появляется но фильтры имхо неправильные
намутили они с етим :B1

дооптимизировались

.....
stax
23 май 09, 15:46    [7218782]     Ответить | Цитировать Сообщить модератору
 Re: Задача выходного дня (group by)  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

SQL> set linesize 132
SQL> explain plan for
  2  SELECT *
  3  FROM departments d
  4  WHERE EXISTS
  5    (SELECT e.id1, min(e.id2)
  6     FROM employees e
  7    WHERE d.id1=e.id1
  8    AND e.id1  = 2
  9    GROUP BY e.id1
 10    )
 11  
SQL> /

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4167016233

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

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement

12 rows selected.

SQL> alter session set optimizer_mode = choose;

Session altered.

SQL> explain plan for
  2  SELECT *
  3  FROM departments d
  4  WHERE EXISTS
  5    (SELECT e.id1, min(e.id2)
  6     FROM employees e
  7    WHERE d.id1=e.id1
  8    AND e.id1  = 2
  9    GROUP BY e.id1
 10    )
 11  
SQL> /

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1015098386

-------------------------------------------
| Id  | Operation           | Name        |
-------------------------------------------
|   0 | SELECT STATEMENT    |             |
|*  1 |  FILTER             |             |
|   2 |   TABLE ACCESS FULL | DEPARTMENTS |
|   3 |   SORT AGGREGATE    |             |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES   |
-------------------------------------------

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

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

   1 - filter( EXISTS (SELECT 0 FROM "EMPLOYEES" "E" WHERE "E"."ID1"=2
              AND "E"."ID1"=:B1))
   4 - filter("E"."ID1"=2 AND "E"."ID1"=:B1)

Note
-----
   - rule based optimizer used (consider using cbo)

22 rows selected.

SQL>  

SY.

Сообщение было отредактировано: 23 май 09, 16:33
23 май 09, 16:31    [7218833]     Ответить | Цитировать Сообщить модератору
 Re: Задача выходного дня (group by)  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Bug 7142215
24 май 09, 04:05    [7219534]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить