index/full scan help
-
I have a query I cannot tune up. It uses the indexes, but the cost it's still very high. There's an indexes on si.sales_item_id and sid.sales_item_id, and hotel_date...
I was thinking for a inline view for si.sales_item_id. Something like select * from sid where sid.sales_item_id in (select sales_item_id from si where...), but the cost is still high...
What else I can do?
Thanks a lot for the help!
Here is the query and the plan:
SELECT
SUM(NVL(sid.chg_amt,0) - NVL(sid.base_amt_paid,0)) begin_bal
FROM
p_sales_item_detail sid,
p_sales_item si
WHERE si.sales_item_id = sid.sales_item_id
AND hotel_date < TRUNC(sysdate-10) Ex. Plan:
HASH JOIN 598 K 13 M 21705
INDEX FAST FULL SCAN PI_HD_SII_I 481 K 6 M 3782
TABLE ACCESS FULL P_SALES_ITEM_DETAIL 12 M 114 M 13706 -
Have you tried rewriting the query to use a join rather than doing it via your current method? SQL will evaluate the new script differently to your current one.
Something like this
SELECT SUM(NVL(sid.chg_amt,0) - NVL(sid.base_amt_paid,0)) begin_bal
FROM dbo.p_sales_item_detail as sid
INNER JOIN dbo.p_sales_item as si on sid.sales_item_id = si.sales_item_id
WHERE hotel_date < TRUNC(sysdate-10)
You may need to add the table name that hotel_date resides in to the where clause but give it a go and post back.
-
NVL() and TRUNC() are Oracle functions, right ?
-
That's definately an oracle query. Trunc, nvl, and sysdate are all Oracle functions. It shouldn't work at all on SQL Server and you shouldn't be able to get an execution plan back either.
Did you make a mistake in the post?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
This is an Oracle query which I'm running from a DTS package to pull the data and feed my crystal report and staging tables.
Except what is the difference if it's Oracle or SQL server, or Sybase? This is a join issue between master and child table Pk->FK...
-
That'll teach me to read the rest of the query rather than just jumping in with both feet
As mjschwenger (odd name
) said it was a problem with the query rather than the commands used in it, I know this is a sql forum but to be honest I don't mind helping out others if they need it (maybe we can get the oracle community to see the error of their ways, lol)Mike
-
There is nothing WRONG with posting an Oracle question here, but like Mike suggested you might get better results at an Oracle forum.
I have worked with Oracle in the past and the first thing I would suggest is that you check the statistics to be sure that they are present and up to date.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
The statistics were showing fine, but they were update with analyze command - old way of doing it... I run the new gather_table_stats and suddenly a miracle - the cost jumded down to 1000... But I'm not an Oracle person, so I was worried like a nubie if what I'm doing is correct... My problem was that I could not see anything worng with the join - PK-FK with all right indexes and could not explain why is this big cost and timing out on my side - but we all learn... everuday
-
Glad I could help. Just for future reference, Oracle does not automatically keep statistics up to date (unless they have changed that in 10...) So if you have large loads that occur or a table that gets a lot of INSERT, UPDATE, or DELETE operations you should periodically analyze the table.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply