Re: Conditionally use different table to query against

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 14 Feb 2007 03:37:04 -0800
Message-ID: <1171453024.642902.255420@m58g2000cwm.googlegroups.com>

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
> UNION ALL
> 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
> UNION ALL
> 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
> UNION ALL. 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<50
UNION ALL
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

UNION ALL 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<50
UNION ALL
SELECT

  COL1,
  COL2,
  COL3,

  COL4
FROM
  T2
WHERE
  100>=50;

PLAN_TABLE_OUTPUT

|   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. Received on Wed Feb 14 2007 - 05:37:04 CST