RE: Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Fri, 5 Aug 2011 14:30:59 -0500
Message-ID: <C5533BD628A9524496D63801704AE56D6A315EBA72_at_SPOBMEXC14.adprod.directory>

My bad...appears it is a SORT UNIQUE.

Some info for the UNION statement for those interested.

all count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.65       0.67          0          0          0           0
Fetch     5994   1057.75    1107.17     810969  113249228          0       89887


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total     5996   1058.42    1107.86     810969  113249228          0       89887

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40 (TAYLORCD)

Rows Row Source Operation
------- ---------------------------------------------------
89887 SORT UNIQUE (cr=113249228 pr=810969 pw=626505 time=493052784 us) 55660314 UNION-ALL (cr=113249228 pr=810969 pw=626505 time=1072529374 us) 55638945 FILTER (cr=112930742 pr=627121 pw=626460 time=849610294 us)

55638945     NESTED LOOPS OUTER (cr=112930742 pr=627121 pw=626460 time=793971330 us)
55638945      HASH JOIN OUTER (cr=1620540 pr=627107 pw=626460 time=237581831 us)
55638945       HASH JOIN  (cr=1620291 pr=647 pw=0 time=71036269 us)
168038        NESTED LOOPS  (cr=1620128 pr=647 pw=0 time=13021563 us)
168074         NESTED LOOPS  (cr=1115868 pr=612 pw=0 time=11175307 us)
170294          NESTED LOOPS  (cr=571996 pr=156 pw=0 time=5009839 us)
176499           HASH JOIN  (cr=41639 pr=68 pw=0 time=1099154 us)
  86158            TABLE ACCESS FULL PS_PO_LINE_DISTRIB (cr=41476 pr=68 pw=0 time=517091 us)
  12127            INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=12283 us)(object id 252516)
170294           TABLE ACCESS BY INDEX ROWID PS_RECV_LN_SHIP (cr=530357 pr=88 pw=0 time=4124289 us)
176615            INDEX RANGE SCAN PSARECV_LN_SHIP (cr=353813 pr=18 pw=0 time=2363954 us)(object id 146221)
168074          TABLE ACCESS BY INDEX ROWID PS_VOUCHER_LINE (cr=543872 pr=456 pw=0 time=6177518 us)
202880           INDEX RANGE SCAN PSCVOUCHER_LINE (cr=341256 pr=17 pw=0 time=2063029 us)(object id 3415975)
168038         TABLE ACCESS BY INDEX ROWID PS_PO_HDR (cr=504260 pr=35 pw=0 time=2990556 us)
168074          INDEX UNIQUE SCAN PS_PO_HDR (cr=336150 pr=14 pw=0 time=1497933 us)(object id 254380)
  12127        INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=24348 us)(object id 252516)
  14543       INDEX FAST FULL SCAN PSAVENDOR (cr=249 pr=0 pw=0 time=159 us)(object id 294592)
55638945      INDEX RANGE SCAN PSJVOUCHER (cr=111310202 pr=14 pw=0 time=457216175 us)(object id 7430349)
  21369    HASH GROUP BY (cr=318486 pr=183848 pw=45 time=23546310 us)
  21570     FILTER  (cr=318486 pr=183803 pw=0 time=23499224 us)
  21570      HASH JOIN  (cr=318486 pr=183803 pw=0 time=23477629 us)
  21570       NESTED LOOPS  (cr=318242 pr=183803 pw=0 time=23234968 us)
  21799        NESTED LOOPS  (cr=252843 pr=183720 pw=0 time=29737359 us)
  21701         HASH JOIN  (cr=187611 pr=183197 pw=0 time=19122601 us)
   6184          SORT UNIQUE (cr=163 pr=0 pw=0 time=10305 us)
  12127           INDEX FAST FULL SCAN PS_ING_BARGESPECIF (cr=163 pr=0 pw=0 time=112 us)(object id 252516)
  27191          TABLE ACCESS FULL PS_DISTRIB_LINE (cr=187448 pr=183197 pw=0 time=22200559 us)
  21799         TABLE ACCESS BY INDEX ROWID PS_VOUCHER_LINE (cr=65232 pr=523 pw=0 time=3169056 us)
  21799          INDEX RANGE SCAN PS_VOUCHER_LINE (cr=43518 pr=139 pw=0 time=1077691 us)(object id 4760299)
  21570        TABLE ACCESS BY INDEX ROWID PS_VOUCHER (cr=65399 pr=83 pw=0 time=859260 us)
  21799         INDEX UNIQUE SCAN PS_VOUCHER (cr=43600 pr=65 pw=0 time=535339 us)(object id 257125)
  14543       INDEX FAST FULL SCAN PS0VENDOR (cr=244 pr=0 pw=0 time=82 us)(object id 154642)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    5994        0.00          0.01
  db file sequential read                      1459        0.21          7.08
  direct path write temp                      41767        0.38         13.77
  latch free                                      1        0.00          0.00
  direct path read temp                       41767        0.59         11.49
  db file scattered read                       4126        0.03         14.94
  latch: library cache                            3        0.00          0.00
  SQL*Net message from client                  5994       19.73        363.35
********************************************************************************

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor_at_ingrambarge.com<mailto:chris.taylor_at_ingrambarge.com>

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Friday, August 05, 2011 1:52 PM
To: Taylor, Chris David
Cc: 'oracle-l_at_freelists.org'
Subject: Re: Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??

What Oracle version?
Is the UNION using a sort or a "HASH UNIQUE"?

On 2011-08-05, at 12:35 PM, Taylor, Chris David wrote:

I've got a query that uses a UNION statement to join 2 large data sets.

When I run the statement it takes 20 minutes to execute (using UNION), returns 89,887 rows and consumes 5GB of TEMP tablespace.

Now, I changed it to a UNION ALL statement to see what I have and I get 89,963 rows (too many), 2 minutes to execute and 0 TEMP tablespace. (I know there's some caching going on here as well but we'll ignore that for the moment.)

Now, I put the query containing the UNION ALL in an INNER select, and I select all columns from it PLUS the ROW_NUMBER() function partitioning by all the columns and applying an order by to the function and call this column "ROW_KEY" (not very original I know).

Finally, I wrap that query into another subselect and this type I append a where clause to the outside "WHERE ROW_KEY = 1".

Now, I get my correct 89,887 rows in right at 2 minutes and 0 TEMP tablespace (again some caching here I'm sure).

I'm curious if anyone has tried this before, or if it is of interest to anyone.

I have a pretty good test case we could play with if anyone is interested? (I also have plenty of trace files etc)

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor_at_ingrambarge.com<mailto:chris.taylor_at_ingrambarge.com>

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 05 2011 - 14:30:59 CDT

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message