Home » RDBMS Server » Performance Tuning » Help Tune My query
Help Tune My query [message #212494] |
Fri, 05 January 2007 09:49 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm stumped. I'm writing a view and if I select from the view by one column, everything works fine, whereas if I use another (the one I'll actually be using in real life) it turns into a heap of poo.
I’ve pruned the query in the view right back to this:
SELECT a3.address_id, a3.address_id_2,l.customer_id FROM
FPS_PROP_CUST_LINK l
,(SELECT row_number() OVER (PARTITION BY customer_id ORDER BY address_id) seq
,address_id
,lead(address_id) OVER (PARTITION BY customer_id ORDER BY address_id) address_id_2
,customer_id
FROM FPS_ADDRESSES) a3
WHERE l.link_type = 'C'
AND a3.customer_id = l.customer_id
AND a3.seq = 1
--AND l.customer_id = 377672
AND l.proposal_id = 363640
Basically, I'm trying to build up a composite record containing customer details, and the details for the two most recent addresses for that customer. The messy looking query a3 gets adjacent pairs of addresses, and the gets the most recent pair.
Table Structure is
CREATE TABLE "FPS"."FPS_PROP_CUST_LINK"
( "PROPOSAL_ID" NUMBER(8,0),
"CUSTOMER_ID" NUMBER(8,0),
"BUSINESS_ID" NUMBER(8,0),
"LINK_TYPE" VARCHAR2(1),
"SEQ" NUMBER(2,0),
"RELATION" VARCHAR2(1))
CREATE TABLE "FPS"."FPS_ADDRESSES"
( "ADDRESS_ID" NUMBER(10,0) NOT NULL,
"FLAT" VARCHAR2(100),
"HOUSE_NAME" VARCHAR2(100),
"HOUSE_NUMBER" VARCHAR2(40),
"STREET_NAME" VARCHAR2(100),
"DISTRICT" VARCHAR2(100),
"TOWN" VARCHAR2(100),
"COUNTY" VARCHAR2(100),
"POSTCODE" VARCHAR2(10),
"TIME_AT_ADDRESS" NUMBER(10,0),
"ADDRESS_SEQ" NUMBER(10,0),
"CUSTOMER_ID" NUMBER(10,0),
"QAS_MATCHED" VARCHAR2(1));
FPS_PROP_CUST_LINK has no PK, an index on proposal_id, link_type, customer_id, and a unique index + constraint on customer_id
FPS_Addresses has a PK on Address_id, an index on customer_id,address_id, and a FK link from customer_id to fps_prop_cust_link.customer_id.
If I run the query and select for a proposal id (what I'll be doing 99% of the time), I get this plan, with a whacking great Full scan on a million row table
SELECT STATEMENT Optimizer Mode=CHOOSE 3 3224
MERGE JOIN 3 192 3224
VIEW 961 K 47 M 3219
WINDOW BUFFER 961 K 23 M 3219
INDEX FULL SCAN FPS.FPS_ADDR_CUST_ID_IDX 961 K 23 M 3219
SORT JOIN 1 12 5
INDEX RANGE SCAN FPS.TEMP_PCL_PROP_CUST_IDX 1 12 3
Whereas if I select for a customer_id (commented out in the example above) I get this far superior plan
SELECT STATEMENT Optimizer Mode=CHOOSE 1 17
NESTED LOOPS 1 59 17
TABLE ACCESS BY INDEX ROWID FPS.FPS_PROP_CUST_LINK 1 7 2
INDEX UNIQUE SCAN FPS.TEMP_PCL_UNQ_CUST_IDX 1 1
VIEW 1 52 15
WINDOW BUFFER 9 K 244 K 15
INDEX RANGE SCAN FPS.FPS_ADDR_CUST_ID_IDX 9 K 244 K 15
Stats are gathered usingDbms_stats,gather_table_stats(ownname => null
,tabname => '<table name>'
,method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
,cascade => true);
[Updated on: Fri, 05 January 2007 09:49] Report message to a moderator
|
|
|
Re: Help Tune My query [message #212564 is a reply to message #212494] |
Fri, 05 January 2007 17:57 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
When you query on Customer ID, the transitive relationship a3.customer_id = l.customer_id is applied and the derived predicate a3.customer_id = 377672 is pushed into the inline view. The resultant query that is optimised is
SELECT a3.address_id, a3.address_id_2,l.customer_id FROM
FPS_PROP_CUST_LINK l
,(SELECT row_number() OVER (PARTITION BY customer_id ORDER BY address_id) seq
,address_id
,lead(address_id) OVER (PARTITION BY customer_id ORDER BY address_id) address_id_2
,customer_id
FROM FPS_ADDRESSES
WHERE customer_id = 377672) a3
WHERE l.link_type = 'C'
AND a3.customer_id = l.customer_id
AND a3.seq = 1
AND l.customer_id = 377672
When you query on Proposition ID, there is no transitivity, so no predicate can be pushed into the inline view.
What you are hoping for (in vain) is for the inline view to be merged with the outer table. View merging does not work with analytic functions, or a host of other cool things.
You could try joining the two tables first and THEN apply the analytic function. In this way, you could filter the FPS_PROP_CUST_LINK, nested loops join to FPS_ADDRESSES, window sort, and then filter all but the first row of each group.
However I think it will be more efficient to go back to old-fashioned joins and sub-queries.
SELECT /*+ use_nl(l, a1, a2)*/
a1.address_id
, a2.address_id_2
, l.customer_id
FROM FPS_PROP_CUST_LINK l
, FPS_ADDRESSES a1
, FPS_ADDRESSES a2
WHERE l.proposal_id = 363640
AND a1.address_id = (
SELECT min(address_id)
FROM FPS_ADDRESSES
WHERE customer_id = l.customer_id)
AND a2.address_id = (
SELECT min(address_id)
FROM FPS_ADDRESSES
WHERE customer_id = a1.customer_id
AND address_id > a1.address_id)
This will only work where 2 or more addresses exist. You might have to muck about with outer joins, but I'm not sure whether you can outer join to a sub-query. You could try changing the last subquery to:AND a2.address_id = (
SELECT nvl(min(address_id), a1.address_id)
...
The advantage of this method is that those subqueries are the equivalent of a unique index scan - they just pick a single row off the front of an index range.
Ross Leishman
|
|
|
Re: Help Tune My query [message #212691 is a reply to message #212564] |
Sun, 07 January 2007 15:54 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Had another idea, make the sub-queries scalar and stick them in the SELECT clause. eg.
SELECT table_name
, (
SELECT min(index_name)
FROM user_indexes
WHERE table_name = t.table_name
) AS index1
, (
SELECT min(index_name)
FROM user_indexes
WHERE table_name = t.table_name
AND index_name > (
SELECT min(index_name)
FROM user_indexes
WHERE table_name = t.table_name
)
) AS index2
FROM user_tables t
/
Ross Leishman
|
|
|
Re: Help Tune My query [message #212753 is a reply to message #212691] |
Mon, 08 January 2007 03:25 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Looks like you're spot on as usual.
I ended up using the first suggestion from your first email - works fine.
Thanks a lot.
|
|
|
Re: Help Tune My query [message #212908 is a reply to message #212753] |
Mon, 08 January 2007 15:49 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
... and will continue to do so providing you don't have any skewed customers with thousands of addresses. If that's going to be a problem, the last version will work better.
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 08:55:01 CST 2024
|