Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Does selecting a primary key column in select , from a table do an automatic order by
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'
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'
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