Hi Tom ;
Thank you , I've been using your site for 2 years now, resolved many issues based on your answers.
Case Scenario : Customer having multiple addresses , only one is active ; some cases ALL the addresses of a customer could be inactive. The View has to display the active customer and the active address info.
If the active customer does not have an active address , still have to display the customer info.
ISSUE : Performance is slow using an outer-join between the customer table and the WITH clause query. LIVESQL Script :
https://livesql.oracle.com/apex/livesql/s/g89d3dhdjcdcbee2w7sowl4gf Here's the view script :
CREATE VIEW vw1 AS
WITH addr as ( select t2.customerid,
t3.address,
t3.country
from t2,t3
where t2.active = 'Y'
and t2.addrid = t3.addrid )
SELECT t1.name,
t1.email,
addr.address,
addr.country
FROM t1,
addr
WHERE addr.customerid (+) = t1.customerid;Here is the original statement explain plan details :
PlanSELECT STATEMENT ALL_ROWS Cost: 169,756 Bytes: 1,683,219,820 Cardinality: 4,261,316
10 HASH JOIN RIGHT OUTER Cost: 169,756 Bytes: 1,683,219,820 Cardinality: 4,261,316
8 VIEW VW1 Cost: 151,669 Bytes: 245 Cardinality: 1
7 FILTER
6 HASH GROUP BY Cost: 151,669 Bytes: 132 Cardinality: 1
5 HASH JOIN Cost: 150,844 Bytes: 745,357,536 Cardinality: 5,646,648
3 HASH JOIN Cost: 39,009 Bytes: 293,200,180 Cardinality: 5,638,465
1 TABLE ACCESS FULL TABLE T2 Cost: 11,725 Bytes: 110,754,514 Cardinality: 4,259,789
2 TABLE ACCESS FULL TABLE T2 Cost: 11,725 Bytes: 110,754,514 Cardinality: 4,259,789
4 TABLE ACCESS FULL TABLE T3 Cost: 57,221 Bytes: 682,904,560 Cardinality: 8,536,307
9 TABLE ACCESS FULL TABLE T1 Cost: 18,025 Bytes: 639,197,400 Cardinality: 4,261,316 Any other way i can achieve this that have a better performance ?.
If the stats in the plan are accurate, you're processing and returning over 4 million rows. That's going to take a while!
You can see this in the cardinality sections (e.g. Cardinality: 4,259,789 ).
If a relatively small number of addresses are active, then an index on:
t2 ( addrid, active )
or vice-versa may help.
But ultimately, if you want this query to be faster you need to fetch a substantially smaller data set. The act of sending millions of rows from the database to the client will take a while.
If you need more help on this, get the execution plan for the query. This includes information about how many rows it processed at each step. Tracing the query will also show you how long it's spending waiting for things like transferring the data. For details on how to get the plan, see:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan