Re: How to find the rows that are different between a table and it's sub view.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 12 Jun 2007 15:38:04 -0700
Message-ID: <1181687884.755088.188920@g37g2000prf.googlegroups.com>

On Jun 12, 5:44 pm, Nis Jørgensen <n..._at_superlativ.dk> wrote:
> Charles Hooper skrev:
> > For instance, if the original SQL statement looked like
> > this:
> > SELECT
> > IP,
> > PORT
> > FROM
> > TABLE_A
> > UNION ALL
> > SELECT
> > IP,
> > PORT
> > FROM
> > TABLE_B;
>
> > The above finds all rows in common between the two tables (one table
> > and one view actually).
>
> No it doesn't. For that you would use "INTERSECT", not "UNION ALL".
> Furthermore, I am as surprised as the other responder that you think a
> view is significantly slower than the SQL from the view definition.

Nice catch - poor editing and divided attention is to blame for that mistake.

> I could see how
>
> SELECT * FROM A
> MINUS
> SELECT * FROM A WHERE P
>
> would be slower in some cases than
>
> SELECT * FROM A
> WHERE NOT P
>
> though.
>
> Pop quiz: In which cases do these two queries give different results?
>
> /Nis

That looks like a hard problem. Let's try this: CREATE TABLE T1 (
  C1 VARCHAR2(20)); CREATE TABLE T2 (
  C1 VARCHAR2(20));

INSERT INTO T1 VALUES ('A');
INSERT INTO T1 VALUES ('B');
INSERT INTO T1 VALUES ('C');
INSERT INTO T1 VALUES ('D');
INSERT INTO T1 VALUES ('D');
INSERT INTO T1 VALUES ('E');
INSERT INTO T1 VALUES ('F');
INSERT INTO T1 VALUES (NULL);

INSERT INTO T2 VALUES ('A');
INSERT INTO T2 VALUES ('B'); SELECT
  *
FROM
  T1
MINUS
SELECT
  *
FROM
  T2;

C1

---

C
D
E
F

5 ROWS SELECTED SELECT
  *
FROM
  T1
WHERE
  C1 NOT IN (
    SELECT
      C1
    FROM
      T2);

C1

---

C
D
D
E
F

5 ROWS SELECTED I received 5 result rows both times, so what is the problem? :-)

Regarding the performance of views compared to the SQL code being directly placed in the calling SQL statement... views limit the transformation options available to the cost based optimizer, some optimizations are hidden from view, other optimizations are implemented, even if the expected cost of the transformation will be greater.

Paraphrased from "Cost-Based Oracle Fundamentals": Complex view merging allows Oracle to merge the SQL code from a view into the calling statement before optimizing the SQL statement. This is disabled for 8i, enabled for 9i even when it generates a more expensive plan, and enabled for 10g but only takes effect when a less expensive plan is generated.

Paraphrased from "Oracle Database Performance Tuning Guide for 10g" Use of views can make it difficult for the optimizer to generate the optimal execution plan.

Reusing a view designed for one purpose for something else can also a negative impact on system performance, in some cases severe. This is mentioned at least a couple times in books written by Tom Kyte. As an example, assume that I have the following view defined: CREATE VIEW
  PURC_ORDER_OPEN
AS
SELECT
  PO.VENDOR_ID,
  V.NAME,
  PO.ID PURC_ORDER_ID,
  POL.LINE_NO PURC_ORDER_LINE_NO,
  PLD.DEL_SCHED_LINE_NO,

  PO.CONTACT_FIRST_NAME,
  PO.CONTACT_LAST_NAME,
  PO.CONTACT_PHONE,
  PO.CONTACT_FAX,

  POL.PART_ID,
  P.DESCRIPTION PART_DESCRIPTION,
  DECODE(POL.VENDOR_PART_ID,NULL,POL.PART_ID,POL.VENDOR_PART_ID) VENDOR_PART_ID,
  DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY, 0),NVL(PLD.ORDER_QTY,0)) ORDER_QTY,
  DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY, 0),NVL(PLD.RECEIVED_QTY,0)) TOTAL_RECEIVED_QTY,

  PLD.ORDER_QTY DEL_ORDER_QTY,
  PLD.RECEIVED_QTY DEL_RECEIVED_QTY,
  POL.DESIRED_RECV_DATE PO_LINE_DATE,

  PO.DESIRED_RECV_DATE PO_DATE,
  PLD.DESIRED_RECV_DATE DEL_DATE,   DECODE(PLD.DESIRED_RECV_DATE,NULL,DECODE(POL.DESIRED_RECV_DATE,NULL,PO.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE),PLD.DESIRED_RECV_DATE) WANT_DATE,

  P.PRIMARY_WHS_ID,
  P.PLANNER_USER_ID,
  P.BUYER_USER_ID,
  P.PRIMARY_LOC_ID,

  POL.SERVICE_ID,
  DECODE(PLD.PURC_ORDER_LINE_NO,NULL,TO_CHAR(POL.PROMISE_DATE,'MM/DD/ YYYY'),PLD.USER_1) PROMISE_DATE
FROM
  PURCHASE_ORDER PO,
  PURC_ORDER_LINE POL,
  PURC_LINE_DEL PLD,
  PART P,
  VENDOR V
WHERE
  PO.STATUS IN ('F','R')
  AND PO.ID=POL.PURC_ORDER_ID

  AND POL.LINE_STATUS='A'
  AND POL.PURC_ORDER_ID=PLD.PURC_ORDER_ID(+)
  AND POL.LINE_NO=PLD.PURC_ORDER_LINE_NO(+)

  AND DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,

0),NVL(PLD.ORDER_QTY,0)) >
DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,
0),NVL(PLD.RECEIVED_QTY,0))

  AND POL.PART_ID=P.ID(+)
  AND PO.VENDOR_ID=V.ID; The above view is hitting five tables (oddly enough, this view is much more efficient than the original version). Assume that I want to use this view to determine a distinct list of PO.IDs where PO.STATUS IN ('F','R') - to essentially reuse the view for another purpose. Would using the view execute slower than:
SELECT DISTINCT
  PO.ID
FROM
  PURCHASE_ORDER PO
WHERE
  PO.STATUS IN ('F','R'); Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jun 12 2007 - 17:38:04 CDT