Home>Question Details


George -- Thanks for the question regarding "Compressing multiple UNION ALL query", version 10.2.0

Submitted on 22-Oct-2007 14:19 Central time zone
Tom's latest followup | Bookmark | Bottom
Last updated 9-Sep-2008 7:20

You Asked

Dear Tom,

The query below has be gnawing at me for a year.
It works well but is so huge! (often the recordset it returns is larger than the query itself).

So my question to you is:
Q:Is it possible to write it in more compact form but WITHOUT SACRIFICING PERFORMANCE, and if only read-only access to the database is possible (e.g. no create table, no iserts, no updates, etc...) ?

Assume the table 'customer' contains typical distribution of first names and surnames, there is a composite index on fname,sname,id (in that order), and the column 'id' is unique and non-null (columns 'fname and 'sname' are nullable and non-unique).

The query returns 10 records of fname beginning with each letter of the alphabet, sorted by fname, sname, id.


I tried, I read, I asked around, and eventually came to you... 


SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='A' AND fname<'B' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='B' AND fname<'C' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='C'  AND AND fname<'D' rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='D'  AND fname<'E' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='E' AND AND fname<'F' rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='F'  AND fname<'G' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='G' AND fname<'H' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='H' AND fname<'I' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='I' AND fname<'J'  AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='J'  AND fname<'K' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='K' AND fname<'L' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='L' AND fname<'M' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='M' AND fname<'N' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='N' AND fname<'O' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='O' AND fname<'P' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='P' AND fname<'Q'  AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='Q' AND fname<'R' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='R' AND fname<'S' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='S' AND fname<'T' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='T'  AND fname<'U' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='U' AND fname<'V' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='V' AND fname<'W' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='W' AND fname<'X' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='X' AND fname<'Y' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='Y' AND fname<'Z' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='Z' AND rownum <= 10
ORDER BY fname,sname,id)

and we said...

well, technically, your query is "wrong" 

select * 
  from (select * from t1 order by x)
union all
select * 
  from (select * from t2 order by y)



is very very different from 

select 1 order_col, t1.* from t1
union all
select 2 order_col, t2.* from t2
order by order_col, x


You have to expect that upon a query plan change (out of YOUR control), the optimizer if free to ignore your order by's in the inline views - they are not necessary to return the correct answer - it can be optimized away - so be aware of that.

Also, you compute row_number but seem to use rownum????? and you use rownum before you sort - hence you get 10 rows and sort, instead of sorting and taking the first ten.

So, I believe this is not really your query after all is it? It looks a bit like it - but it is wrong...


You wrote:

The query returns 10 records of fname beginning with each letter of the alphabet, sorted by fname, sname, id.

but that query above does not do that 

UNION ALL
SELECT * FROM
(SELECT /*+ INDEX(customer customer(fname,sname,id)) */
  row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  fname,
  sname,
  id
FROM customer
WHERE fname>='Y' AND fname<'Z' AND rownum <= 10
ORDER BY fname,sname,id)
UNION ALL


that gets 10 rows, then sorts them. You are maybe getting lucky because it is accidentally getting the data sorted due to an index hint (which may or may not be obeyed as things change...)

this is a slippery slope you are on - lots of reliance on accidents to get the right answer.

I think your query bits should be: 

UNION ALL
select * 
  from (
select fname, sname, id, 
       row_number() over (partition by fname,sname order by fname,sname,id) i
  from customer
 where fname >= 'A' and fname < 'B'
 order by fname, sname, id
       )
 where rownum <= 10


and and the END of the entire thing: 

order by fname, sname, id


to ensure it is sorted.

and given your rather unique reqirements - it is likely that your implementation once fixed will do the least amount of work in the database.

A 7bit ascii database only of course given that names do not have to start with A, B, C, ... Z in real life (there is that as well... but maybe that is OK for you)

Reviews    
3 stars Why not like this?   October 23, 2007 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: martina from vienna, austria
I would write the whole query like that:

select * 
  from (
select fname, sname, id, 
       row_number() over (partition by substr(fname,1,1)  order by fname,sname,id) i
  from customer
       )
 where i <= 10
 order by fname, sname, id

regards, martina


5 stars More compact SQL   October 23, 2007 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: Keith from Ohio
If I understand the requirements correctly, I think this is a more compact way of writing
the query, and has the advantage of allowing first characters to be something other
than A..Z. I'm not certain, however, if it will perform as well: 

select * from 
(
  select fname, sname, id
         row_number() over (partition by substr(fname,1,1)
                            order by fname, sname, id) i
   from customer
)
where i <=10
order by fname, sname, id


3 stars   October 23, 2007 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: George Robinson 
Thanks for pointing out how "lucky" I was that this query worked at all.  I thought that the order 
of the UNION ALL subqueries was maintained in the resulting recordset.


Also, I have an additional side question:
Q: Is it possible to write this query without repeating the almost identical queries 26 times (one 
for each letter of the alphabet), without sacrificing performace ?

6KB query seems a little silly to me. (especially one that produces such simple output).

In other RDBMS systems, I can use a list of constants in lieu of an inner query, in order to feed 
an outer query with variable parameters, such as:
{(A,B),(B,C),(C,D),(D,E), (E,F),...,(Y,Z)} 
Can Oracle do that ?

I am looking for a general solution to such problems, not one relevant to my specific task, so 
let's assume 7-bit database for simplicity, where lowercase and unicode are unheard of.

Thanks for your time,
George


3 stars   October 23, 2007 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: George Robinson 
Dear Maritna & Keith,


Thank you for your input.

Unfortunately the compact query below perfoms 10x slower than the original 26-part UNION ALL query.

The Full Index Scan and Window Sort Pushed Rank is dragging it down.

Also the meaning if the "i" column is different in the query you proposed.  In the original query 
"i" is a number in the fname,sname,id sort order that distinguishes between the same pairs of 
fname,sname. 


select * from 
(
  select /*+ INDEX(customer customer(fname,sname,id)) */ 
    fname,
    sname,
    id
    row_number() over (partition by substr(fname,1,1)     order by fname, sname, id) i
  from customer
)
where i <=10
order by fname, sname, id

Regards,
George


Followup   October 23, 2007 - 11am Central time zone:

they are also trying to point out your use of rownum is entirely wrong!!!! they thought they were fixing a bug of yours

Given your rather unique requirement of A...Z only - your query is correct and appropriate, stop losing sleep over it, but read about ROWNUM please!!! 


http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html



if the size of the query is not pleasing, just hide it in a view.... it doesn't have to be in the code itself.


5 stars Maybe something, Maybe nothing.   October 25, 2007 - 4pm Central time zone
Bookmark | Bottom | Top
Reviewer: Tyler from Vancouver BC.
Hey George, 

I'm not sure if this can be of any use to you or not (apologies if it falls into the latter).

WITH AlphaBet AS
(
   SELECT 
      CHR((ROWNUM + 64)) AS Character 
   FROM DUAL 
   CONNECT BY LEVEL <= 26
), 
   LetterSets AS 
(
   SELECT 
      Character AS CurrentLetter,
      LEAD(Character) OVER (ORDER BY 1) AS NextLetter
   FROM AlphaBet
   ORDER BY Character ASC
)
SELECT *
FROM LetterSets;

Would give you a paring of letters like used in your 26 queries now, then you would do something 
along the lines of...

FROM customer c, LetterSets l
WHERE c.fname >= l.CurrentLetter 
AND (fname < l.NextLetter OR l.NextLetter IS NULL)


4 stars   October 30, 2007 - 12pm Central time zone
Bookmark | Bottom | Top
Reviewer: George Robinson 
Dear Tyler,

It's rather something than nothing!

Thanks for addressing the core of the problem rather than the dumb mistake I made with rownum.

Indeed your method is the thing I've been hoping for as an alternative to the list of constants 
available in other rdbmses.

I pretty much undestand all of your query except the 'ORDER BY 1' clause.  Could you elaborate ?

Regards,
George


Followup   October 30, 2007 - 1pm Central time zone:

I think his use of "order by 1" is a "bug" waiting to happen.

He has coded:

SELECT
Character AS CurrentLetter,
LEAD(Character) OVER (ORDER BY 1) AS NextLetter
FROM AlphaBet
ORDER BY Character ASC

and I think he wants to have each row have the currentletter and the next letter sorted alphabetically.

But, order by 1 in this context is just like "order by 'x'" or "order by <ANY_CONSTANT>"

consider: 


ops$tkyte%ORA10GR2> create table t as select * from scott.emp order by hiredate;

Table created.

ops$tkyte%ORA10GR2> select ename, lead(ename) over (order by 1) from scott.emp
  2  /

ENAME      LEAD(ENAME
---------- ----------
           MILLER
MILLER     SMITH
SMITH      ALLEN
ALLEN      WARD
WARD       JONES
JONES      MARTIN
MARTIN     BLAKE
BLAKE      CLARK
CLARK      KING
KING       TURNER
TURNER     ADAMS
ADAMS      JAMES
JAMES      FORD
FORD       SCOTT
SCOTT

15 rows selected.

ops$tkyte%ORA10GR2> select ename, lead(ename) over (order by 1) from t
  2  /

ENAME      LEAD(ENAME
---------- ----------
SMITH
           WARD
WARD       JONES
JONES      BLAKE
BLAKE      CLARK
CLARK      TURNER
TURNER     MARTIN
MARTIN     KING
KING       FORD
FORD       JAMES
JAMES      MILLER
MILLER     SCOTT
SCOTT      ADAMS
ADAMS      ALLEN
ALLEN

15 rows selected.


arguably - we have the SAME data (t is a copy of scott.emp after all) but because of the random ordering of the data on disk we get different answers (if you order by CONSTANT, the sorted data can be in ANY order of course!!!)


I think he meant: order by Character
5 stars   October 30, 2007 - 5pm Central time zone
Bookmark | Bottom | Top
Reviewer: George Robinson 
Tom, Tyer

Indeed the accident happened just like Tom predicted.

I had a table with less than ideal ordering of data, and the result differed from the one intended.

Ordering by 'Character' fixed it.
Nonetheless, excellent solution !

Does Oracle have anything like a list of constants that can be used in place of a subquery?

If not, do you think it would be a useful feature ?  e.g.:

SELECT fname, sname
FROM customer, list{'E', 'T', 'A', 'O', 'N', 'I'} a
OUTER JOIN customer a
ON substr(customer.fname,1,1) = a.list

...or some similar syntax.


Regards,
George


Followup   November 1, 2007 - 3pm Central time zone:

ops$tkyte%ORA10GR2> create or replace type myVarcharList as table of varchar2(30)
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from table( myVarcharList( 'Hello', 'world', 'how', 'are', 'you' ) );

COLUMN_VALUE
------------------------------
Hello
world
how
are
you


you would need an order by on that (order by column_value) if order was relevant
4 stars For George   October 31, 2007 - 3pm Central time zone
Bookmark | Bottom | Top
Reviewer: SeánMacGC from Ireland
Apologies George, if this is not what you're intending,

But instead of:

SELECT fname, sname
FROM customer, list{'E', 'T', 'A', 'O', 'N', 'I'} a
OUTER JOIN customer a
ON substr(customer.fname,1,1) = a.list

(which actually JOINs to the table customer twice in both an ANSI and non-ANSI way)

Do you not mean something simply like:

SELECT fname, sname
  FROM customer
 WHERE SUBSTR(customer.fname,1,1) IN ('E', 'T', 'A', 'O', 'N', 'I')


5 stars 26 Range scans   October 31, 2007 - 5pm Central time zone
Bookmark | Bottom | Top
Reviewer: George Robinson 
Tyler,

Do you have an idea how to merge your query with the other query below so Oracle returns 10 first 
fnames >= to each letter of the AlphaBet, using 26 individual concatenated index range scans and 26 
separate Count Stopkeys ?

/*---------------------------------------------*/
WITH AlphaBet AS
(
   SELECT 
      CHR((ROWNUM + 64)) AS Character 
   FROM DUAL 
   CONNECT BY LEVEL <= 26
), 
   LetterSets AS 
(
   SELECT 
      Character AS CurrentLetter,
      LEAD(Character) OVER (ORDER BY Character) AS NextLetter
   FROM AlphaBet
   ORDER BY Character ASC
)
SELECT *
FROM LetterSets;

/*---------------------------------------------*/

SELECT
    i,fname,sname
FROM    (  
            SELECT /*+ INDEX(customer customer(fname,sname,id)) FIRST_ROWS */
                row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
                fname,
                sname
            FROM customer
            WHERE fname>=LetterSets.currentletter AND fname< LetterSets.nextletter
            ORDER BY fname,sname,id
        ) a
WHERE rownum<=10

/*---------------------------------------------*/

When I try to make a nested query, I always get a full index scan at best.

From the experience with my huge 26-part UNION ALL query, I know that 26 index range scans and 
Stopkeys are much faster in this case.


Regards,
George


Followup   November 2, 2007 - 11am Central time zone:

George, your original query is the way to do your exact thing. It is "very precise"

Hide it in a view, then it can "look nice" in the code as well.
5 stars Query For George   October 31, 2007 - 9pm Central time zone
Bookmark | Bottom | Top
Reviewer: Tyler from Vancouver
Hey George,

First off, thanks to Tom for pointing out my error in not ordering the LEAD portion properly, wasn't really production ready code :D)

As for incorporating the query to meet your requirement i had envisioned something along the lines of...


WITH AlphaBet AS
(
SELECT
CHR((ROWNUM + 64)) AS Character
FROM DUAL
CONNECT BY LEVEL <= 26
),
LetterSets AS
(
SELECT
Character AS CurrentLetter,
LEAD(Character) OVER (ORDER BY Character ASC) AS NextLetter
FROM AlphaBet
ORDER BY Character ASC
),
FinalResultz AS
(
SELECT
c.fname,
c.sname,
L.currentletter,
ROW_NUMBER() OVER (PARTITION BY c.fname, c.sname, L.currentletter ORDER BY c.fname, c.sname, c.id) AS The_RowNum
FROM customer c, LetterSets L
WHERE c.fname >= L.currentletter
AND (c.fname < L.nextletter OR L.nextletter IS NULL)
)
SELECT *
FROM FinalResultz
WHERE The_RowNum <= 10
ORDER BY currentletter, fname, sname;


You'd need the L.nextletter IS NULL portion because when you're on Z your next letter will be NULL, so you need to account for that.

I don't think it'll be fair to compare the execution paths of this to the original since they will produce different results (your original query had rownum in the "wrong" place) so the 26 range scans with stopkeys probably shouldn't ever have been something you used as your benchmark :D).

Hopefully the query runs (don't have your tables so can't run it here) and produces the correct results in a decent amount of time.

Hope that's useful!

4 stars   November 1, 2007 - 12pm Central time zone
Bookmark | Bottom | Top
Reviewer: George Robinson 
Tyler,

Thanks for giving it a try :) I didn't think about a three part WITH clause.

Unfortunately this query does not limit the output properly.
For example there should be only:
- 10 records with fname between 'A' and 'B'
- 10 records with fname between 'B' and 'C'
- 10 records with fname between 'C' and 'D'
etc...

Also, the purpose of the 'i' column in my original query ('The_RowNum' in yours) is to disambiguate 
and count records having the same 'fname' and 'sname', as definded by the sorting order 
'fname,sname,id' taking advantage of the fact that 'id' is unique.


For the record: When posting the original 26-part UNION ALL query, I made a mistake by blindly 
copying from an old file (from the times when I did not understand rownum yet) 
It should've been:

SELECT * FROM
   (  
   SELECT /*+ INDEX(customer customer(fname,sname,ID)) */
     row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,ID) i,
     fname,
     sname,
     ID
   FROM customer
   WHERE fname>='A' AND fname<'B'
   ORDER BY fname,sname,ID
   )
WHERE rownum <=10
UNION ALL
SELECT * FROM
   (  
   SELECT /*+ INDEX(customer customer(fname,sname,ID)) */
     row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,ID) i,
     fname,
     sname,
     ID
   FROM customer
   WHERE fname>='B' AND fname<'C'
   ORDER BY fname,sname,ID
   )
WHERE rownum <=10
UNION ALL
etc...
etc...
etc...
etc...
etc... 26 times...


Regards,
George


5 stars OR and UNION   September 7, 2008 - 3pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader 
Hi Tom

I have a query like this :

SELECT 
       cic.inventory_item_id ITEM_ID,
       msi.segment1 ITEM_NUMBER,
       msi.item_type ITEM_TYPE,
       msi.primary_uom_code UOM_CODE,
       DECODE (TO_CHAR (msi.planning_make_buy_code), 1, 'M', 2, 'B' ) MAKE_BUY_CODE,
       SUBSTR (msi.description, 1, 100) DESCRIPTION,
       NVL (cic.material_cost, 0.00000) + NVL (cic.outside_processing_cost, 0.00000) MATERIAL_COST,
       NVL (cic.material_overhead_cost, 0.00000) MATERIAL_OVH_COST,
       NVL (cic.resource_cost, 0.00000) RESOURCE_COST,
       NVL (cic.overhead_cost, 0.00000) OVH_COST, 0.00000 VAR_OVH_COST,
       NVL (cic.item_cost, 0.00000) ITEM_COST,
      -- :b6 cost_type,
       cic.last_update_date LAST_UPD_DATE,
       a.itm_id_type PART_FLAG,
       a.itm_material_class  MATERIAL_CLASS,
       a.itm_software_flg    SOFTWARE_FLAG,
       a.itm_prim_source_fk  PRIMARY_SOURCE,
       a.trp_cur_tran_price,
       a.trp_cur_mmicv  ,
       a.itm_item_num_pk
from   apps.cst_item_costs cic,
       apps.mtl_system_items_b msi
       ,cost_temp a
where  cic.organization_id =  2550
and    cic.cost_type_id = 1  -- b6
and   
( cic.last_update_date >=  sysdate-15  or
            a.trp_cur_last_updt_dt >=  sysdate-15   )
and    msi.inventory_item_id = cic.inventory_item_id
and    msi.organization_id   = cic.organization_id
and    msi.segment1 = a.itm_item_num_pk(+)
and    msi.item_type =  'GP CONSUMABLES'  --b2
and    msi.inventory_asset_flag = 'Y'
and    msi.costing_enabled_flag = 'Y'
and    msi.organization_id = 2550

it takes at least an hour to give the results (200 rows).
when i re-wrote that query like below then it runs in few seconds.

SELECT leading(cic) parallel(cic,4) */ 
       cic.inventory_item_id ITEM_ID,
       msi.segment1 ITEM_NUMBER,
       msi.item_type ITEM_TYPE,
       msi.primary_uom_code UOM_CODE,
       DECODE (TO_CHAR (msi.planning_make_buy_code), 1, 'M', 2, 'B' ) MAKE_BUY_CODE,
       SUBSTR (msi.description, 1, 100) DESCRIPTION,
       NVL (cic.material_cost, 0.00000) + NVL (cic.outside_processing_cost, 0.00000) MATERIAL_COST,
       NVL (cic.material_overhead_cost, 0.00000) MATERIAL_OVH_COST,
       NVL (cic.resource_cost, 0.00000) RESOURCE_COST,
       NVL (cic.overhead_cost, 0.00000) OVH_COST, 0.00000 VAR_OVH_COST,
       NVL (cic.item_cost, 0.00000) ITEM_COST,
      -- :b6 cost_type,
       cic.last_update_date LAST_UPD_DATE,
       a.itm_id_type PART_FLAG,
       a.itm_material_class  MATERIAL_CLASS,
       a.itm_software_flg    SOFTWARE_FLAG,
       a.itm_prim_source_fk  PRIMARY_SOURCE,
       a.trp_cur_tran_price,
       a.trp_cur_mmicv  ,
       a.itm_item_num_pk
from   apps.cst_item_costs cic,
       apps.mtl_system_items_b msi
       ,cost_temp a
where  cic.organization_id =  2550
and    cic.cost_type_id = 1  -- b6
and    cic.last_update_date >=  sysdate-15  
and    msi.inventory_item_id = cic.inventory_item_id
and    msi.organization_id   = cic.organization_id
and    msi.segment1 = a.itm_item_num_pk(+)
and    msi.item_type =  'GP CONSUMABLES'  --b2
and    msi.inventory_asset_flag = 'Y'
and    msi.costing_enabled_flag = 'Y'
and    msi.organization_id = 2550
union 
SELECT 
       cic.inventory_item_id ITEM_ID,
       msi.segment1 ITEM_NUMBER,
       msi.item_type ITEM_TYPE,
       msi.primary_uom_code UOM_CODE,
       DECODE (TO_CHAR (msi.planning_make_buy_code), 1, 'M', 2, 'B' ) MAKE_BUY_CODE,
       SUBSTR (msi.description, 1, 100) DESCRIPTION,
       NVL (cic.material_cost, 0.00000) + NVL (cic.outside_processing_cost, 0.00000) MATERIAL_COST,
       NVL (cic.material_overhead_cost, 0.00000) MATERIAL_OVH_COST,
       NVL (cic.resource_cost, 0.00000) RESOURCE_COST,
       NVL (cic.overhead_cost, 0.00000) OVH_COST, 0.00000 VAR_OVH_COST,
       NVL (cic.item_cost, 0.00000) ITEM_COST,
      -- :b6 cost_type,
       cic.last_update_date LAST_UPD_DATE,
       a.itm_id_type PART_FLAG,
       a.itm_material_class  MATERIAL_CLASS,
       a.itm_software_flg    SOFTWARE_FLAG,
       a.itm_prim_source_fk  PRIMARY_SOURCE,
       a.trp_cur_tran_price,
       a.trp_cur_mmicv   ,
       a.itm_item_num_pk 
from   apps.cst_item_costs cic,
       apps.mtl_system_items_b msi
       ,cost_temp  a
where  cic.organization_id =  2550
and    cic.cost_type_id = 1  -- b6
and    a.trp_cur_last_updt_dt >=  sysdate-15   
and    msi.inventory_item_id = cic.inventory_item_id
and    msi.organization_id   = cic.organization_id
and    msi.segment1 = a.itm_item_num_pk(+)
and    msi.item_type =  'GP CONSUMABLES'  --b2
and    msi.inventory_asset_flag = 'Y'
and    msi.costing_enabled_flag = 'Y'
and    msi.organization_id = 2550  

Few facts  -the table cic and msi have millions of rows.
the table cost_temp has 500K rows.

there are indexes 

cic - > inventory_item_id,organization_id,cost_type_id
msi - > inventory_item_id,organization_id
msi - > segment1
cost_temp ->itm_item_num_pk
cost_temp -> trp_cur_last_updt_dt 

there is no index on last_update_date colum of cic.

my question is :

please note that the numner rows last_update_date >= sysdate-15 will return will not be more than 
200

and similary a.trp_cur_last_updt_dt >=  sysdate-15  will also not return more than 100 rows..

sometime trp_cur_last_updt_date can be equal to cic.last_update_date...that is why i used UNION.

questiones :
1. do you think this is a valid approach.
2. is there any other way to write the original query.

thanks

PS - i will post the tkprof tommorow.


Followup   September 8, 2008 - 3pm Central time zone:

ps: about the tkprof (Please don't, it is, well, too large to look at as a review/followup and would take create tables, indexing schemes, etc etc etc etc - and that is not appropriate here)
5 stars   September 8, 2008 - 4pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader 
Tom,
sorry about PS part.

Can i make it a just concept question and ask you here.

like the example above..

if i have 2 big tables , and then I am joining them on the indexed colums and then I have

a condtion like this

(a.last_update_date >= sysdate-1 OR b.last_update_date >= sysdate-1) 

a.last_update_date and b.last_update_date are most selective filters used as the filetr.

so how would oracle handles this..considering both the data fields are not indexed.

is OR alwaya equal to UNION.
in my case - answer I found is NO.is it due to data.
can you please elobarete what could be that data condition.

i wish i could post it as a new question but then it is really hard to find the opportunity to post 
even i am in India .

Regards


Followup   September 9, 2008 - 7am Central time zone:

a) last_update_date and b.last_update_date are most selective filters used as
the filetr.

so how would oracle handles this..considering both the data fields are not
indexed.


it would have to join AND THEN filter.


b) a UNION adds "distinct"

since

select DISTINCT .... from ....

is not the same as

select .... from ....

union and or are in general not "swappable" 


ops$tkyte%ORA11GR1> select * from t;

         X
----------
         5
         5
         6
         6
         7

ops$tkyte%ORA11GR1> select * from t where x = 5 or x = 6;

         X
----------
         5
         5
         6
         6

ops$tkyte%ORA11GR1> select * from t where x = 5 union select * from t where x = 6;

         X
----------
         5
         6


c) i am in India . where most of the questions actually seem to originate. I don't work on a fixed schedule either and cover many time zones....


All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.

About Oracle | Legal Notices and Terms of Use | Privacy Statement