Re: Conditionally use different table to query against

On Feb 14, 1:37 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 14, 5:34 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
>
>
>
> > On Feb 14, 5:08 am, "mwmann" <mwm..._at_gmail.com> wrote:
>
> > > Hi anyone/everyone
>
> > > I have a problem, which I know could be easily done using PL/SQL with
> > > 2 separate queries, and at run time using IF..THEN to determine which
> > > of the 2 to use.
>
> > > However this is not an option and I need to try do this in 1 SQL
> > > query, for various reasons- There must be a solution (no matter how
> > > odd/work around it is).
>
> > > I have 2 tables TRANSACTION_DATA and TRANSACTION_DATA_HISTORY.
>
> > > My query needs to query TRANSACTION_DATA if inputted numbers of days
> > > is < 50 ,otherwise use TRANSACTION_DATA_HISTORY.
>
> > > Any help would be greatly appreciated.
>
> > > This is pretty URGENT- if anyone can be a Knight In Shining Armour,
> > > now is your time !!!!!!
>
> > Something like this might work:
> > SELECT
> > TD.COL1,
> > TD.COL2,
> > TD.COL3,
> > TD.COL4
> > FROM
> > TRANSACTION_DATA TD
> > WHERE
> > :NUM_DAYS < 50
> >UNIONALL
> > SELECT
> > TDH.COL1,
> > TDH.COL2,
> > TDH.COL3,
> > TDH.COL4
> > FROM
> > TRANSACTION_DATA_HISTORY TDH
> > WHERE
> > :NUM_DAYS >= 50;
>
> > Where :NUM_DAYS is the user input for the number of days. For
> > example, if the user enters 100, the SQL statement would be evaluated
> > like this:
> > SELECT
> > TD.COL1,
> > TD.COL2,
> > TD.COL3,
> > TD.COL4
> > FROM
> > TRANSACTION_DATA TD
> > WHERE
> > 100 < 50
> >UNIONALL
> > SELECT
> > TDH.COL1,
> > TDH.COL2,
> > TDH.COL3,
> > TDH.COL4
> > FROM
> > TRANSACTION_DATA_HISTORY TDH
> > WHERE
> > 100 >= 50;
>
> > The WHERE clause automatically omits all rows from one SELECT in the
> >UNIONALL. I am not sure if Oracle's optimizer is smart enough to not
> > scan the data in one of the two SELECTs - if you use bind variables
> > (:NUM_DAYS), Oracle will likely have to scan the data in both tables.
> > Testing will tell you whether or not this is the case.
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> A quick test case:
> CREATE TABLE T1 (
> COL1 NUMBER(10),
> COL2 NUMBER(10),
> COL3 NUMBER(10),
> COL4 NUMBER(10));
>
> CREATE TABLE T2 (
> COL1 NUMBER(10),
> COL2 NUMBER(10),
> COL3 NUMBER(10),
> COL4 NUMBER(10));
>
> INSERT INTO T1
> SELECT
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000))
> FROM
> DBA_OBJECTS
> WHERE
> ROWNUM<=10000;
>
> INSERT INTO T2
> SELECT
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000))
> FROM
> DBA_OBJECTS
> WHERE
> ROWNUM<=10000;
>
> INSERT INTO T2
> SELECT
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000))
> FROM
> DBA_OBJECTS
> WHERE
> ROWNUM<=10000;
>
> INSERT INTO T2
> SELECT
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000))
> FROM
> DBA_OBJECTS
> WHERE
> ROWNUM<=10000;
>
> COMMIT;
>
> We now have 10,000 rows in T1 and 30,000 rows in T2. Now the test SQL
> statement:
> SELECT
> COUNT(COL1)
> FROM
> T1
> WHERE
> 100<50UNIONALL
> SELECT
> COUNT(COL1)
> FROM
> T2
> WHERE
> 100>=50;
>
> COUNT(COL1)
> ===========
> 0
> 30000
>
> DBMS XPLAN:
> SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(COL1) FROM T1 WHERE
> 100<50
>
> UNIONALL SELECT COUNT(COL1) FROM T2 WHERE 100>=50
>
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
> | Buffers |
> | 1 | UNION-ALL | | 1 | | 2 |00:00:00.01 |
> 107 |
> | 2 | SORT AGGREGATE | | 1 | 1 | 1 |
> 00:00:00.01 | 0 |
> |* 3 | FILTER | | 1 | | 0 |00:00:00.01
> | 0 |
> | 4 | TABLE ACCESS FULL| T1 | 0 | 10000 | 0 |
> 00:00:00.01 | 0 |
> | 5 | SORT AGGREGATE | | 1 | 1 | 1 |
> 00:00:00.01 | 107 |
> | 6 | TABLE ACCESS FULL | T2 | 1 | 27331 | 30000 |
> 00:00:00.01 | 107 |
>
> Predicate Information (identified by operation id):
> 3 - filter(NULL IS NOT NULL)
>
> Note
> - dynamic sampling used for this statement
>
> Note that the original filter predicates are gone (100<50, and
> 100>=50), and a new filter predicate NULL IS NOT NULL was generated.
> It is somewhat interesting that Oracle predicted that it would visit
> only 27331 rows of T2, yet that it would visit 10,000 rows of T1
>
> Test case #2:
> SELECT
> COL1,
> COL2,
> COL3,
> COL4
> FROM
> T1
> WHERE
> 100<50UNIONALL
> SELECT
> COL1,
> COL2,
> COL3,
> COL4
> FROM
> T2
> WHERE
> 100>=50;
>
> PLAN_TABLE_OUTPUT
> =================
> SELECT COL1,COL2,COL3,COL4 FROM T1 WHERE 100<50UNIONALL SELECT
> COL1,COL2,COL3,COL4 FROM T2 WHERE 100>=50
>
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
> Buffers |
> | 1 | UNION-ALL | | 1 | | 30000 |00:00:00.12 | 405 |
> |* 2 | FILTER | | 1 | | 0 |00:00:00.01 | 0 |
> | 3 | TABLE ACCESS FULL| T1 | 0 | 10000 | 0 |00:00:00.01
> | 0 |
> | 4 | TABLE ACCESS FULL | T2 | 1 | 27331 | 30000 |00:00:00.06
> | 405 |
>
> Predicate Information (identified by operation id):
> 2 - filter(NULL IS NOT NULL)
>
> Note
> - dynamic sampling used for this statement
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Thanks a million, this works but is still not ideal, as the query takes an unreasonable amount of time to run. Going back to client - to potentially change requirements, and build temp tables before running report.

Thanks for you input though. Received on Tue Feb 20 2007 - 02:04:00 CST