Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Does selecting a primary key column in select , from a table do an automatic order by

Does selecting a primary key column in select , from a table do an automatic order by

From: RaviAlluru <ravi.alluru_at_gmail.com>
Date: 2 Feb 2006 17:00:01 -0800
Message-ID: <1138928401.432042.268120@z14g2000cwz.googlegroups.com>


Hello All,

   We had a query (written by an app guy ) to do some call usage processing (telecom billing system ) ...

These were the 2 queries ,,,(in a perl script )

Needless to say he was doing it very inefficiently and the report he was trying to generate took more than 12 hours to come back.

(we are dealing with a 2TB database, and the driving tables have > 100 million rows each).

So we stepped in to try and tune his query .

We did the following :

SELECT /*+ FIRST_ROWS */

	   a.quantity,
	   a.amount, a.discount,
 	   c.usage_class,
	   c.usage_type,
	   a.resource_id,
	   DECODE(b.timezone,'OP2','OP',b.timezone) AS timezone
FROM EVENT_BAL_IMPACTS_T a,
	   AUR_EVENT_DELAY_BAL_EXTINFO_T b,
	   AUR_EVENT_DELAY_EXT_INFO_T c,
	   TMP_AIRTIME_ITEMS_T  ii
WHERE  ii.item_poid = a.item_obj_id0
AND	   ii.processed = 'N'

AND (a.rate_tag IS NULL OR a.rate_tag <> 'Offset') AND a.obj_id0 = b.obj_id0 AND a.rec_id = b.rec_id AND a.obj_id0 = c.obj_id0
AND (b.charge_descr = 'A' OR (b.charge_descr = 'O' AND b.discount_type = 'A'))
AND (c.basic_service = 'SASK' OR (c.basic_service = 'INCL' AND c.call_type = 'H'))

all the above tables have > 5 million rows, with the EVENT_BAL_IMPACTS_T table having > 300 million rows.

Using the first_rows hint the process took less than 2 hours to finish.

The only problem was this being call detail data, it wasnt ordered properly and hence the summarization of the results (some calculations that happen were way off) as we couldnt do an order .

So I sort of cheated and rewrote the above query as

SELECT /*+ FIRST_ROWS */

       e.POID_ID0,
	   a.quantity,
	   a.amount, a.discount,
 	   c.usage_class,
	   c.usage_type,
	   a.resource_id,
	   DECODE(b.timezone,'OP2','OP',b.timezone) AS timezone
FROM   EVENT_T e,
       EVENT_BAL_IMPACTS_T a,
	   AUR_EVENT_DELAY_BAL_EXTINFO_T b,
	   AUR_EVENT_DELAY_EXT_INFO_T c,
	   TMP_AIRTIME_ITEMS_T  ii
WHERE  ii.item_poid = a.item_obj_id0
AND	   ii.processed = 'N'

AND (a.rate_tag IS NULL OR a.rate_tag <> 'Offset') AND a.obj_id0 = b.obj_id0 AND a.rec_id = b.rec_id AND a.obj_id0 = c.obj_id0
AND (b.charge_descr = 'A' OR (b.charge_descr = 'O' AND b.discount_type = 'A'))
AND (c.basic_service = 'SASK' OR (c.basic_service = 'INCL' AND c.call_type = 'H'))
and e.POID_ID0 = a.OBJ_ID0

Now the EVENT_T table is a master table and you have a 1-> many relationship between EVENT_T and EVENT_BAL_IMPACTS_T.

So I was hoping that I could still use the first_rows hint and still keep a proper order for the e.POID_ID0 , (hoping that I am selecting the primary key column as the first column in the select and it should just do a proper order by implicitly ) and still get the performance based on the first_rows hint.

So far in my small test environment , it does seem to work properly , But I need to be absolutely sure if this would do the ordr by imlicitly.

Any ideas if my assumptions are correct.

Thanks
Ravi Received on Thu Feb 02 2006 - 19:00:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US