Home » RDBMS Server » Performance Tuning » How to rewrite a query
How to rewrite a query [message #133680] Sun, 21 August 2005 10:22 Go to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
I have a query - very simple, but takes a long-long time.
There are indexes on each of the join fields. All indexes are analyzed in the last 2 hours with GATHER_TABLE_STATS.
Everybody said :"Use EXIST/Not exists" - it's better... But because of NOT EXISTS the index is not in use...
Here is the query and the execution plan:
SELECT inv_id
FROM p_inventory pi, p_project_unit ppu
WHERE pi.unit_num = ppu.unit_num
AND pi.project_id = ppu.project_id
AND pi.project_id = 240
AND pi.season_code = :b1
AND ppu.unit_type_code = :b2
AND oeb_code = :b3
AND inv_status_code = 0
AND NOT EXISTS (
SELECT /*+ INDEX(P_CONTRACT_PURCHASE CONTRPR_INV_FK_I)*/ 1
FROM p_contract_purchase pcp, p_contract pc
WHERE pcp.inv_id = pi.inv_id
AND pc.contr_num = pcp.contr_num
AND pc.current_contr_status NOT IN ('ICL', 'CXL'))
AND ROWNUM <= 1
ORDER BY inv_id ASC;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 636
SORT ORDER BY 1 5 K 636
COUNT STOPKEY
HASH JOIN ANTI 161 5 K 633 :Q174489006 P->S QC (RANDOM)
HASH JOIN 161 5 K 28 :Q174489004 P->P HASH
TABLE ACCESS FULL PREMIER.P_PROJECT_UNIT 436 5 K 4 :Q174489000 S->P HASH
TABLE ACCESS FULL PREMIER.P_INVENTORY 953 20 K 23 :Q174489002 P->P HASH
VIEW SYS.VW_SQ_1 524 K 1 M 604 :Q174489005 P->P HASH
HASH JOIN 524 K 7 M 604 :Q174489005 PCWP
INDEX FAST FULL SCAN PREMIER.P_CONTRACT_CURRENT_STATUS_I 483 K 3 M 249 :Q174489001 S->P HASH
TABLE ACCESS FULL PREMIER.P_CONTRACT_PURCHASE 878 K 5 M 216 :Q174489003 P->P HASH

I would appreciate every idea how to rewrite it.
Thanks a lot,mj

Report message to a moderator

Re: How to rewrite a query [message #133689 is a reply to message #133680] Sun, 21 August 2005 14:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
It is unclear what you want the query to do. You have rownum <= 1 and an order by clause at the same level, which means that the rownum <= 1 will return any random row and the order by clause is useless. If you want to order the results then select the first row, if any, then you need to put the order by within an inner subquery and have the rownum <= 1 in the outer query. However, this amounts to the same thing as selecting the min value, so it might be better to just use min. I would rewrite the not exists to an outer join and is null, as shown below. It would have helped if you had provided create table and insert statements, sample data, and an example of the desired results based on that data. If the following does not help, then please provide the missing information. 

SELECT MIN (pi.inv_id)
FROM   p_inventory pi, p_project_unit ppu,
       (SELECT pcp.inv_id
        FROM   p_contract_purchase pcp, p_contract pc
        WHERE  pc.contr_num = pcp.contr_num
        AND    pc.current_contr_status NOT IN ('ICL', 'CXL')) p
WHERE  pi.unit_num = ppu.unit_num
AND    pi.project_id = ppu.project_id
AND    pi.project_id = 240
AND    pi.season_code = :b1
AND    ppu.unit_type_code = :b2
AND    oeb_code = :b3
AND    inv_status_code = 0
AND    pi.inv_id = p.inv_id (+)
AND    p.inv_id IS NULL
/





Report message to a moderator

Re: How to rewrite a query [message #133691 is a reply to message #133689] Sun, 21 August 2005 16:05 Go to previous messageGo to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
Thank you very much,Barbara.
I'm sorry I did not include more details. The query will return 1 or more rows and I need just one of them - it doesn't matter which...
I had something similar to what you suggested - outer joining the 2 tables in themain query + ppu, and only "not exists" on p_contract. The problem is that this even is slower then the original query. The sample you provided is also 1 to 2 sec. slower then the original one, although has a much lower cost.
The questions is that I cannot spen 10-11 sec on this query - it's too much.
I guess the problem is how to make the optimizer to use an index on pi and ppu... no hint works because ppu is a small table. There are 3 full table scan here and cannot prevent no one of them.
I'm including all without p_contract - we really do not need to worry about it.
Thanks again for your help.
CREATE TABLE P_INVENTORY (
INV_ID NUMBER(12) NOT NULL,
PROJECT_ID VARCHAR2(4 BYTE) NOT NULL,
UNIT_NUM VARCHAR2(6 BYTE) NOT NULL,
SEASON_CODE VARCHAR2(3 BYTE) NOT NULL,
INV_STATUS_CODE VARCHAR2(3 BYTE) NOT NULL,
ELEMENT VARCHAR2(3 BYTE) NOT NULL,
OEB_CODE VARCHAR2(1 BYTE) NOT NULL);
CREATE TABLE P_CONTRACT_PURCHASE (
CONTR_NUM NUMBER(12) NOT NULL,
PURCHASE_ID NUMBER(3) NOT NULL,
PROJECT_ID VARCHAR2(4 BYTE),
INV_ID NUMBER(12));
CREATE INDEX CONTRPR_INV_FK_I ON P_CONTRACT_PURCHASE
(INV_ID);

CREATE TABLE P_PROJECT_UNIT (
PROJECT_ID VARCHAR2(4 BYTE) NOT NULL,
UNIT_NUM VARCHAR2(6 BYTE) NOT NULL);


CREATE UNIQUE INDEX PRJUNT_PK ON P_PROJECT_UNIT
(PROJECT_ID, UNIT_NUM);
CREATE UNIQUE INDEX INV_PK ON P_INVENTORY
(INV_ID);
CREATE UNIQUE INDEX INV_UK ON P_INVENTORY
(PROJECT_ID, UNIT_NUM, ELEMENT, OEB_CODE);

Report message to a moderator

Re: How to rewrite a query [message #133693 is a reply to message #133691] Sun, 21 August 2005 16:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
Usage of an index is not necessarily the fastest execution method. If the table is small, then a full table scan is usually faster, which is why the optimizer chooses to do a full table scan, intead of using an index. When comparing queries, the best measure is timed tests, not whether it is using an index or what the cost is. Since you do not care which row is returned, then you do not need the min, so you might test the following.

SELECT pi.inv_id
FROM   p_inventory pi, p_project_unit ppu,
       (SELECT pcp.inv_id
        FROM   p_contract_purchase pcp, p_contract pc
        WHERE  pc.contr_num = pcp.contr_num
        AND    pc.current_contr_status NOT IN ('ICL', 'CXL')) p
WHERE  pi.unit_num = ppu.unit_num
AND    pi.project_id = ppu.project_id
AND    pi.project_id = 240
AND    pi.season_code = :b1
AND    ppu.unit_type_code = :b2
AND    oeb_code = :b3
AND    inv_status_code = 0
AND    pi.inv_id = p.inv_id (+)
AND    p.inv_id IS NULL
AND    ROWNUM <= 1
/

Report message to a moderator

Re: How to rewrite a query [message #133694 is a reply to message #133691] Sun, 21 August 2005 17:10 Go to previous message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
You can try adding rownum > 0 to the inline view to materialize the view, so that usage of an index becomes more likely and see how that performs.

SELECT pi.inv_id
FROM   p_inventory pi, p_project_unit ppu,
       (SELECT pcp.inv_id
        FROM   p_contract_purchase pcp, p_contract pc
        WHERE  pc.contr_num = pcp.contr_num
        AND    pc.current_contr_status NOT IN ('ICL', 'CXL')
        AND    ROWNUM > 0) p
WHERE  pi.unit_num = ppu.unit_num
AND    pi.project_id = ppu.project_id
AND    pi.project_id = 240
AND    pi.season_code = :b1
AND    ppu.unit_type_code = :b2
AND    oeb_code = :b3
AND    inv_status_code = 0
AND    pi.inv_id = p.inv_id (+)
AND    p.inv_id IS NULL
AND    ROWNUM <= 1
/

Report message to a moderator

Previous Topic: Hints on a poor performing view
Next Topic: precise question: use a histogram here?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 16:20:22 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.