Thanks for the question, George.

Asked: October 22, 2007 - 2:19 pm UTC

Last updated: September 09, 2008 - 7:20 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

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 Tom 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)

Rating

(13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why not like this?

martina, October 23, 2007 - 10:08 am UTC

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

More compact SQL

Keith, October 23, 2007 - 10:09 am UTC

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

George Robinson, October 23, 2007 - 10:13 am UTC

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

George Robinson, October 23, 2007 - 10:33 am UTC

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

October 23, 2007 - 11:08 am UTC

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!!!

https://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.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.

Maybe something, Maybe nothing.

Tyler, October 25, 2007 - 4:31 pm UTC

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)

George Robinson, October 30, 2007 - 12:49 pm UTC

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

October 30, 2007 - 1:30 pm UTC

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

George Robinson, October 30, 2007 - 5:55 pm UTC

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

November 01, 2007 - 3:45 pm UTC

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

For George

SeánMacGC, October 31, 2007 - 3:52 pm UTC

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')

26 Range scans

George Robinson, October 31, 2007 - 5:28 pm UTC

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

November 02, 2007 - 11:49 am UTC

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.

Query For George

Tyler, October 31, 2007 - 9:43 pm UTC

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!

George Robinson, November 01, 2007 - 12:33 pm UTC

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

OR and UNION

A reader, September 07, 2008 - 3:17 pm UTC

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.

September 08, 2008 - 3:53 pm UTC

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)

A reader, September 08, 2008 - 4:07 pm UTC

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

September 09, 2008 - 7:20 am UTC

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....

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Why not like this?

More compact SQL

Maybe something, Maybe nothing.

For George

26 Range scans

Query For George

OR and UNION

More to Explore

Analytics

Compressing multiple UNION ALL query

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Why not like this?

More compact SQL

Maybe something, Maybe nothing.

For George

26 Range scans

Query For George

OR and UNION

More to Explore

Analytics