Re: Multiple datasets in one dataset

From: <roman.morokutti_at_googlemail.com>
Date: Thu, 18 Oct 2007 07:53:02 -0700
Message-ID: <1192719182.197874.74870@y27g2000pre.googlegroups.com>

> What is the execution plan? In SQL*Plus use the command (i hope i get
> this right) "SET AUTOT TRACE EXP STAT", and run the query, then post
> the output here.

Well, this was indeed right. Congratulations.

> Could those records bve ignored? If so, use a WHERE clause to exclude
> such a case, such as, AND A.Value1 > 0 AND A.Value2 > 0 ... AND
> A.Value10 > 0.

This should be discussed. But I think they should be shown also.

> (+) is Oracle specific, just like *= is SQL Server specific. All
> support the ANSI syntax, however.

I was aware of this, but to lazy to convert the statement into standard SQL ;-(

> An EXPLAIN PLAN would probably be helpful here as well.

Here it is:

---

| Id  | Operation                         | Name      | Rows  | Bytes

| Cost |

---

|   0 | SELECT STATEMENT                  |           |    12 |  4896
|    28 |
|   1 |  SORT ORDER BY                    |           |    12 |  4896
|    28 |
|   2 |   NESTED LOOPS OUTER              |           |    12 |  4896
|    27 |
|   3 |    NESTED LOOPS OUTER             |           |     4 |  1360
|    27 |
|   4 |     NESTED LOOPS OUTER            |           |     1 |   272
|    27 |
|   5 |      NESTED LOOPS OUTER           |           |     1 |   204
|    27 |
|   6 |       NESTED LOOPS OUTER          |           |     1 |   136
|    27 |
|   7 |        TABLE ACCESS BY INDEX ROWID| PR_LOG    |     1 |    68
|     4 |
|   8 |         INDEX RANGE SCAN          | PR_LOG_I1 |     1 |
|     3 |
|   9 |        TABLE ACCESS BY INDEX ROWID| PR_LOG    |     2 |   136
|    23 |
|  10 |         INDEX RANGE SCAN          | PR_LOG_I2 |    64 |
|     2 |
|  11 |       TABLE ACCESS BY INDEX ROWID | PR_LOG    |     3 |   204
|     0 |
|  12 |        INDEX RANGE SCAN           | PR_LOG_I2 |    64 |
|     2 |
|  13 |      TABLE ACCESS BY INDEX ROWID  | PR_LOG    |     3 |   204
|     0 |
|  14 |       INDEX RANGE SCAN            | PR_LOG_I2 |    64 |
|     2 |
|  15 |     TABLE ACCESS BY INDEX ROWID   | PR_LOG    |     3 |   204
|     0 |
|  16 |      INDEX RANGE SCAN             | PR_LOG_I2 |    64 |
|     2 |
|  17 |    TABLE ACCESS BY INDEX ROWID    | PR_LOG    |     3 |   204
|     0 |
|  18 |     INDEX RANGE SCAN              | PR_LOG_I2 |    64 |
|     2 |
-------------------------------------------------------------------------------

The execution plan does not reveal any problem, I guess, whereas the plain statement does. It never returns.

Regards
Roman Received on Thu Oct 18 2007 - 09:53:02 CDT