Home » RDBMS Server » Performance Tuning » Help Tune My query
Help Tune My query [message #212494] Fri, 05 January 2007 09:49 Go to next message
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
a3.seq = 1
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 using
Dbms_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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Help Tune My query [message #213077 is a reply to message #212908] Tue, 09 January 2007 06:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not a problem - no more than 10-15 addresses per customer.
Previous Topic: Query performance
Next Topic: with out enforcing index i want use how to do this
Goto Forum:
  


Current Time: Tue Dec 03 08:55:01 CST 2024