Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Question Unanswered: Full Join on Inline Views Problem

    I'm working on a query that is not returning anything. The query just keeps processing with out finishing. I'm not the dba so I can't check certain things, I'm a developer.

    Here is the query. It works fine until I do a full join on the eleventh inline view. I'm doing the test in a test database server with only 3 records that could be returned.

    SELECT col1, col2, col3, col4, id, user, date, code
    FROM
    (SELECT indicator as col1, id, user, date, code
    FROM audit_table_1
    WHERE id = 1
    AND TRUNC(date) BETWEEN TO_DATE('30-01-2008','dd-mm-yyyy') AND TO_DATE('30-01-2008','dd-mm-yyyy')) A
    FULL JOIN
    (SELECT indicator as col2, id, user, date, code
    FROM audit_table_1
    WHERE id = 2
    AND TRUNC(date) BETWEEN TO_DATE('30-01-2008','dd-mm-yyyy') AND TO_DATE('30-01-2008','dd-mm-yyyy')) B
    ON A.id = B.id
    AND A.user = B.user
    AND A.code = B.code
    FULL JOIN
    (ETC.....);

    Any help will be appreciated!
    Last edited by nwyork; 02-04-08 at 15:27.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    I do not know the "certain things", however you can post at least cardinality of AUDIT_TABLE_1 (total number of rows and number of rows for one day) and all indexes created on that table. Explain plan would show you whether they are used.
    Be aware, that when using TRUNC(date), index on DATE will not be used; you also cannot benefit from partition pruning (at least on 10g). Using
    Code:
        date >= TO_DATE('30-01-2008','dd-mm-yyyy')
    AND date < TO_DATE('31-01-2008','dd-mm-yyyy')
    would be better.
    Posting Oracle version would be good too (as some features are missing in older versions).

    Code:
    SELECT col1, col2, col3, col4, id, user, date, code
    FROM
    (SELECT indicator as col1, id, user, date, code
    FROM audit_table_1
    WHERE id = 1 AND <...>) A FULL JOIN
    (SELECT indicator as col2, id, user, date, code
    FROM audit_table_1
    WHERE id = 2 AND <...>) B ON A.id = B.id AND <...> FULL JOIN
    (ETC.....);
    Seems to me you at least simplified it, as USER, DATE and CODE columns are ambiguous. Also not sure what you want to achieve, but as 1 <> 2, no rows will be inner joined and you will get just outer join results, the same as
    Code:
    SELECT indicator as col1, null as col2, <...>, user, date, code
    FROM audit_table_1
    WHERE id = 1 AND <...> UNION ALL
    SELECT null as col1, indicator as col2, <...>, user, date, code
    FROM audit_table_1
    WHERE id = 2 AND <...> UNION ALL
    (ETC.....);
    or, better
    Code:
    SELECT DECODE(id, 1, indicator) as col1,
      DECODE(id, 2, indicator) as col2, <...>, user, date, code
    FROM audit_table_1
    WHERE id IN (1, 2, <...>)
    AND <...>(that is all);
    Just reminded me data model described in Query on design thread on AskTom. I am afraid that when it contains more rows, the only way to improve queries on it is to change the design.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •