Home » RDBMS Server » Performance Tuning » Optimizing the query containing 7 table joins
| Optimizing the query containing 7 table joins [message #426926] |
Tue, 20 October 2009 06:19  |
manzoor14
Messages: 5
Registered: October 2009
|
Junior Member |
|
|
hi,
I have a query which is taking almost 20 minutes to retrieve the data from DB. let me know how can i further optimize the query.. the tables contains huge amount of data
Table1 a -> 1040131 rows
Table2 b -> 1040131 rows
Table3 c -> 2080262 rows
Table4 d -> 2749 rows
Table5 e -> 1040131 rows,
Table6 f -> 93819 rows
Table7 g -> 99203 rows
My query is
SELECT a.lid, g.image, f.product , d.manufacturer, b.desc, c.price, c.abbr, c.currency, c.class
FROM
Table1 a,
Table2 b,
Table3 c,
Table4 d,
Table5 e,
Table6 f,
Table7 g
WHERE (UPPER(b.desc) like '%TEST%' OR UPPER(b.desc) like '%BEST%')
and a.line = b.line
AND a.line = c.line
AND c.subset = 576
AND a.manufacturer = d.manufacturer
AND a.line = e.line
and a.product = f.product
and e.image = g.image
Please tell me how can i optimize this query further to work faster
|
|
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #426932 is a reply to message #426926] |
Tue, 20 October 2009 06:34  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
UPPER(b.desc) like '%TEST%' OR UPPER(b.desc) like '%BEST%'
goes for full table scan. You can avoid this by creating INDEX TYPE
Search here by the keyword " CONTAINS"...
Oracle sometimes can not find the best path when there is more joins between tables. You can have a look at the Performance Tuning sticky in this forum
Thanks
Ved
[Updated on: Tue, 20 October 2009 06:37] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #426958 is a reply to message #426953] |
Tue, 20 October 2009 07:10 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@Ved:
I didn't see your post when I hit Reply.
@Bala:Quote:Index can still be used in this case ..
LIKE '%TEST%'
I don't think that's the case. I don't think a normal index can be used in this case at all.
If you believe otherwise, I'd love to see a test case.
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #426961 is a reply to message #426926] |
Tue, 20 October 2009 07:15 |
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
|
Member |
|
|
Ved,
Requesting to pls go through what i have replied . i said LIKE 'TEST%' index will be used in this case . It means we should suffix % at the end of the word then index can be used.
I hope i am clear if not we will walk through a test case.
Regards
Bala
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #426963 is a reply to message #426926] |
Tue, 20 October 2009 07:19 |
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
|
Member |
|
|
Ved,
SQL> drop table emp_tmp purge;
Table dropped.
SQL> create table emp_tmp as select * from emp;
Table created.
SQL> create index emp_tmp_idx on emp_tmp(ename);
Index created.
SQL> exec dbms_Stats.gather_Table_Stats('','EMP_TMP',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select * from emp_tmp where ename like 'CLA%';
Execution Plan
----------------------------------------------------------
Plan hash value: 4259130087
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TMP | 1 | 37 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_TMP_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME" LIKE 'CLA%')
filter("ENAME" LIKE 'CLA%')
SQL> select * from emp_tmp where ename like '%CLA%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2242264577
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_TMP | 1 | 37 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME" LIKE '%CLA%')
|
|
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #426969 is a reply to message #426926] |
Tue, 20 October 2009 07:41 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Let's get this thread back to the point shall we? See if we can speed it up without resorting oracle text or changing the results.
@manzoor14 - you need to give us an explain plan for this query. Details of any indexes on the tables would also be usefull along with the total number of records the query should return.
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #426981 is a reply to message #426969] |
Tue, 20 October 2009 08:13 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
cookiemonster wrote on Tue, 20 October 2009 07:41
@manzoor14 - you need to give us an explain plan for this query. Details of any indexes on the tables would also be usefull along with the total number of records the query should return.
Also, it would be helpful to have the below information:
select count(*) from Table2 b where UPPER(b.desc) like '%TEST%';
select count(*) from Table2 b where UPPER(b.desc) like '%BEST%';
Make sure that stats are upto data.Verify LAST_ANALYZED column of USER_TABLES/USER_INDEXES
Thanks
[Updated on: Tue, 20 October 2009 08:15] Report message to a moderator |
|
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427080 is a reply to message #427071] |
Wed, 21 October 2009 00:30 |
manzoor14
Messages: 5
Registered: October 2009
|
Junior Member |
|
|
Thank you all for the response till now.. Here is the exact query thats taking time..
SELECT a.line_item_id, g.image_file_location, f.product_name , d.manufacturer_name, b.short_desc, c.price_1, c.uom_abbr, c.currency_abbr, c.class_code
FROM
line_item a,
loc_line_item b,
line_item_price c,
loc_manufacturer d,
line_item_image e,
loc_product f,
image g
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id
AND a.line_item_id = c.line_item_id
AND c.catalog_subset_id = 576
AND a.manufacturer_id = d.manufacturer_id
AND a.line_item_id = e.line_item_id
and a.product_id = f.product_id
and e.image_id = g.image_id
Also.. the following is the data some of u guys needed..
line_item a -> 1040131 records,
loc_line_item b -> 1040131 records ,
line_item_price c -> 2080262 records ,
loc_manufacturer d -> 2749 records,
line_item_image e -> 1040131 records,
loc_product f -> 93819 records,
image g -> 99203 records
select count(*) from loc_line_item b where (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
COUNT(*)
----------------------
1768
1 rows selected
select count(*) from line_item_price c where c.catalog_subset_id = 576
COUNT(*)
----------------------
1040131
1 rows selected
also , the tables have the following indexes..
1. CREATE INDEX "IX_LOC_LINE_ITEM_ID" ON "LOC_LINE_ITEM" ("LINE_ITEM_ID")
2. CREATE INDEX "IX_MANUFACTURER_ID" ON "LINE_ITEM" ("MANUFACTURER_ID")
3. CREATE INDEX "IX_PRODUCT_ID_1" ON "LINE_ITEM" ("PRODUCT_ID")
4. CREATE INDEX "IX_LINE_ITEM_ID_STATUS" ON "LINE_ITEM" ("LINE_ITEM_ID", "STATUS")
This query is for a "keyword" search functionality.. so the number of records returned depends on the keyword given in the Front end.
below is the execution plan
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE (Cost=2410 Card=55 Bytes=13 K)
1 0 HASH JOIN (Cost=2410 Card=55 Bytes=13)
2 1 NESTED LOOPS (Cost=2406 Card=55 Bytes=12 K)
3 2 NESTED (Cost=2351 Card=55 Bytes=9 K)
4 3 NESTED (Cost=2296 Card=55 Bytes=8 K)
5 4 HASH JOIN (Cost=1172 Card=562 Bytes=31 K)
6 5 NESTED LOOPS (Cost=1126 Card=562 Bytes=19 K)
7 6 TABLE ACCESS BY INDEX ROWID CIM.LINE_ITEM_PRICE (Cost=2 Card=562 Bytes=10 K)
8 7 INDEX RANGE SCAN CIM.IX_CATALOG_SUBSET (Cost=1 Card=225)
9 6 TABLE ACCESS BY INDEX ROWID CIM.LINE_ITEM (Cost=2 Card=378 Bytes=5 K)
10 9 INDEX UNIQUE SCAN CIM.PK_LINE_ITEM (Cost=1 Card=1)
11 5 TABLE ACCESS FULL CIM.LOC_PRODUCT (Cost=40 Card=93 K Bytes=1 M)
12 4 TABLE ACCESS BY INDEX ROWID CIM.LOC_LINE_ITEM (Cost=2 Card=1 Bytes=100)
13 12 INDEX RANGE SCAN CIM.IX_LOC_LINE_ITEM_ID (Cost=1 Card=1)
14 3 INDEX RAN CIM.PK_LINE_ITEM_IMAGE (Cost=1 Card=1 Bytes=10)
15 2 TABLE ACCESS BY INDEX ROWID CIM.IMAGE (Cost=1 Card=1 Bytes=57)
16 15 INDEX UNIQUE SCAN CIM.PK_IMAGE (Card=1)
17 1 TABLE ACCESS FULL CIM.LOC_MANUFACTURER (Cost=3 Card=2 K Bytes=64 K)
please let me know if any further details are required.
Thanks
[addrd code tags to display explain plan in a readable format]
[Updated on: Wed, 21 October 2009 09:23] by Moderator Report message to a moderator |
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427089 is a reply to message #427080] |
Wed, 21 October 2009 01:24 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
@manzoor14,
Are the stats upto date ?
11 5 TABLE ACCESS FULL CIM.LOC_PRODUCT (Cost=40 Card=93 K Bytes=1 M)
Is there any index created LOC_PRODUCT ?
Also can you provide the plan for the sql with the hint.Just want
to see how the plan is when Oracle use RBO (Would suggest not to use this hint but to tune the sql with other alternatives )
SELECT /*+ rule */ a.line_item_id, g.image_file_location, f.product_name , d.manufacturer_name, b.short_desc, c.price_1, c.uom_abbr, c.currency_abbr, c.class_code
FROM
line_item a,
loc_line_item b,
line_item_price c,
loc_manufacturer d,
line_item_image e,
loc_product f,
image g
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id
AND a.line_item_id = c.line_item_id
AND c.catalog_subset_id = 576
AND a.manufacturer_id = d.manufacturer_id
AND a.line_item_id = e.line_item_id
and a.product_id = f.product_id
and e.image_id = g.image_id
@Bala
Quote:
I know that its not useful in this context but you have insisted me in writing and we started a debate on usage of index when using like operator.
huh???
I am not here to debate but to help others with little knowledge I have and just to learn from people here (also from you  ).
I know that I may provide wrong answers at times but whats bad in trying? I belive that some people with immense knowledge are here in this forum to correct me
and to learn this way.I love when someone criticize at the same time should have the reason for it
Thanks
[Updated on: Wed, 21 October 2009 02:07] Report message to a moderator |
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427120 is a reply to message #426926] |
Wed, 21 October 2009 04:29 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
@manzoor14
When posting code and explain plans can you please use code tags - see the orafaq forum guide if you're not sure how - it makes code a lot eaiser to read.
Can you post the results of the following queries:
SELECT count(distinct(catalog_subset_id)) FROM line_item_price;
SELECT count(*)
FROM
line_item a,
loc_line_item b,
line_item_price c,
loc_manufacturer d,
line_item_image e,
loc_product f,
image g
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id
AND a.line_item_id = c.line_item_id
AND c.catalog_subset_id = 576
AND a.manufacturer_id = d.manufacturer_id
AND a.line_item_id = e.line_item_id
and a.product_id = f.product_id
and e.image_id = g.image_id;
Can you also post the rest of the index defintions, you've missed PK_IMAGE, PK_LINE_ITEM_IMAGE, PK_LINE_ITEM and IX_CATALOG_SUBSET.
Can you also re-run the explain plan using the following:
EXPLAIN PLAN FOR <your select statement>;
SELECT * FROM table(dbms_xplan.display);
It gives the explain plan in a nicer format.
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427199 is a reply to message #427089] |
Wed, 21 October 2009 09:31 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@manzoor14
Are the statistics up to date on these tables? Your initial post idicates that the tables all have in the region of 100,000 - 1,000,000 rows, and yet your cardinality values and costs suggest much smaller tables than that.
as @Ved points out, you're probably missing an index on Loc_Product.Product_id, plus the list of indexes you've provided doesn't include indexes like IX_CATALOG_SUBSET on line_item_price - can we have an accurate list of indexes please.
@bala:Quote:
I know that its not useful in this context but you have insisted me in writing and we started a debate on usage of index when using like operator.
No - you brougt up an irrelevant point about indexes, demonstrating that if we were looking at a different query , then there might be a problem. Sadly, we weren't looking at a different query, we're looking at this one.
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427311 is a reply to message #426926] |
Wed, 21 October 2009 23:39 |
manzoor14
Messages: 5
Registered: October 2009
|
Junior Member |
|
|
Hi,
Thanks for the responses till now.. here is the list of the missing indexes on the tables used in the query.
CREATE UNIQUE INDEX "PK_IMAGE" ON "IMAGE" ("IMAGE_ID")
CREATE UNIQUE INDEX "PK_LINE_ITEM_IMAGE" ON "LINE_ITEM_IMAGE" ("LINE_ITEM_ID", "IMAGE_ID")
CREATE UNIQUE INDEX "PK_LINE_ITEM" ON "LINE_ITEM" ("LINE_ITEM_ID")
CREATE INDEX "IX_CATALOG_SUBSET" ON "LINE_ITEM_PRICE" ("CATALOG_SUBSET_ID" DESC)
CREATE INDEX "IX_PRODUCT_NAME" ON "LOC_PRODUCT" ("PRODUCT_NAME")
CREATE INDEX "IX_PRODUCT_ID_1" ON "LINE_ITEM" ("PRODUCT_ID")
Following is the query results that some of the people required
SELECT count(distinct(catalog_subset_id)) FROM line_item_price;
COUNT(DISTINCT(CATALOG_SUBSET_ID))
----------------------------------
37
1 rows selected
SELECT count(*)
FROM
line_item a,
loc_line_item b,
line_item_price c,
loc_manufacturer d,
line_item_image e,
loc_product f,
image g
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id
AND a.line_item_id = c.line_item_id
AND c.catalog_subset_id = 576
AND a.manufacturer_id = d.manufacturer_id
AND a.line_item_id = e.line_item_id
and a.product_id = f.product_id
and e.image_id = g.image_id;
COUNT(*)
----------------------
1768
1 rows selected
ps: the above query took 184 seconds to execute :(
below is the result after running the explain plan query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 249 | 1695 |
| 1 | TABLE ACCESS BY INDEX ROWID | LOC_MANUFACTURER | 1 | 24 | 2 |
| 2 | NESTED LOOPS | | 1 | 249 | 1695 |
| 3 | NESTED LOOPS | | 1 | 225 | 1693 |
| 4 | NESTED LOOPS | | 1 | 125 | 1691 |
| 5 | NESTED LOOPS | | 1 | 103 | 1689 |
| 6 | NESTED LOOPS | | 1 | 46 | 1688 |
| 7 | NESTED LOOPS | | 562 | 20232 | 1126 |
| 8 | TABLE ACCESS BY INDEX ROWID| LINE_ITEM_PRICE | 562 | 11240 | 2 |
|* 9 | INDEX RANGE SCAN | IX_CATALOG_SUBSET | 225 | | 1 |
| 10 | TABLE ACCESS BY INDEX ROWID| LINE_ITEM | 378 | 6048 | 2 |
|* 11 | INDEX UNIQUE SCAN | PK_LINE_ITEM | 1 | | 1 |
|* 12 | INDEX RANGE SCAN | PK_LINE_ITEM_IMAGE | 1 | 10 | 1 |
| 13 | TABLE ACCESS BY INDEX ROWID | IMAGE | 1 | 57 | 1 |
|* 14 | INDEX UNIQUE SCAN | PK_IMAGE | 1 | | |
| 15 | TABLE ACCESS BY INDEX ROWID | LOC_PRODUCT | 1 | 22 | 2 |
|* 16 | INDEX RANGE SCAN | IX_LOC_PRODUCTID_STATUS | 1 | | 1 |
|* 17 | TABLE ACCESS BY INDEX ROWID | LOC_LINE_ITEM | 1 | 100 | 2 |
|* 18 | INDEX RANGE SCAN | IX_LOC_LINE_ITEM_ID | 1 | | 1 |
|* 19 | INDEX RANGE SCAN | PK_LOC_MANUFACTURER | 1 | | 1 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access(SYS_OP_DESCEND("C"."CATALOG_SUBSET_ID")=HEXTORAW('3DF9B2FF') )
filter("C"."CATALOG_SUBSET_ID"=576)
11 - access("A"."LINE_ITEM_ID"="C"."LINE_ITEM_ID")
12 - access("A"."LINE_ITEM_ID"="E"."LINE_ITEM_ID")
filter("A"."LINE_ITEM_ID"="E"."LINE_ITEM_ID")
14 - access("E"."IMAGE_ID"="G"."IMAGE_ID")
16 - access("A"."PRODUCT_ID"="F"."PRODUCT_ID")
17 - filter(UPPER("B"."SHORT_DESC") LIKE '%BLADES%' OR UPPER("B"."SHORT_DESC") LIKE '%DIAM
OND%')
18 - access("A"."LINE_ITEM_ID"="B"."LINE_ITEM_ID")
19 - access("A"."MANUFACTURER_ID"="D"."MANUFACTURER_ID")
Note: cpu costing is off
42 rows selected
I appreciate your effort on helping me optimize the query.
Thanks
Manzoor
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427336 is a reply to message #427311] |
Thu, 22 October 2009 02:11 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Thanks for providing the information.
What was the response time when u ran the aboove 2nd sql ?
Also can you provide the plan for the sql with the hint.Just want
to see how the plan is when Oracle use RBO (Would suggest not to use this hint but to tune the sql with other alternatives )
SELECT /*+ rule */ a.line_item_id, g.image_file_location, f.product_name , d.manufacturer_name, b.short_desc, c.price_1, c.uom_abbr, c.currency_abbr, c.class_code
FROM
line_item a,
loc_line_item b,
line_item_price c,
loc_manufacturer d,
line_item_image e,
loc_product f,
image g
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id
AND a.line_item_id = c.line_item_id
AND c.catalog_subset_id = 576
AND a.manufacturer_id = d.manufacturer_id
AND a.line_item_id = e.line_item_id
and a.product_id = f.product_id
and e.image_id = g.image_id
Or can you provide the show plan for the sql below with index hint?
SELECT /*+ INDEX(c IX_CATALOG_SUBSET)*/ a.line_item_id, g.image_file_location, f.product_name , d.manufacturer_name, b.short_desc, c.price_1, c.uom_abbr, c.currency_abbr, c.class_code
FROM
line_item a,
loc_line_item b,
line_item_price c,
loc_manufacturer d,
line_item_image e,
loc_product f,
image g
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id
AND a.line_item_id = c.line_item_id
AND c.catalog_subset_id = 576
AND a.manufacturer_id = d.manufacturer_id
AND a.line_item_id = e.line_item_id
and a.product_id = f.product_id
and e.image_id = g.image_id
Another concern here may be
9 - access(SYS_OP_DESCEND("C"."CATALOG_SUBSET_ID")=HEXTORAW('3DF9B2FF') )
filter("C"."CATALOG_SUBSET_ID"=576)
sys_op_descend
--------------
An internal function that takes a value and
returns the form that would be stored for
that value in a descending index. Essentially
doing a one's complement on the bytes and
appending an 0xFF byte
CREATE INDEX "IX_CATALOG_SUBSET" ON "LINE_ITEM_PRICE" ("CATALOG_SUBSET_ID" DESC)
Why this index created as DESC? Can be the reason for the sql being slow.
My concern here is that stats are not upto date. Can you provide the plan for the rule hint and index hint I have mentioned?
I want to know how the plan is when Oracle uses RBO.
Are statistics upto date?
Thanks
[Updated on: Thu, 22 October 2009 04:31] Report message to a moderator |
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427368 is a reply to message #427336] |
Thu, 22 October 2009 04:32 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I definitely think the stats are off.
Considering that there are 2080262 records in line_item_price, of which 1040131 have CATALOG_SUBSET_ID you're looking for -
so half the table - the index on that column should not be used as it's really unselective.
Oracle thinks it is selective though:
|* 9 | INDEX RANGE SCAN | IX_CATALOG_SUBSET | 225 | | 1 |
Oracle thinks the index range scan will find approx 225 rows, which is way out.
Also given this:
SELECT count(distinct(catalog_subset_id)) FROM line_item_price;
COUNT(DISTINCT(CATALOG_SUBSET_ID))
----------------------------------
37
I'm not sure why you've got an index on that column at all.
Make sure your stats are up to date and if the problem persists try dropping the index on CATALOG_SUBSET_ID it doesn't appear to be helpful.
Its_me_ved wrote on Thu, 22 October 2009 08:11
Another concern here may be
9 - access(SYS_OP_DESCEND("C"."CATALOG_SUBSET_ID")=HEXTORAW('3DF9B2FF') )
filter("C"."CATALOG_SUBSET_ID"=576)
sys_op_descend
--------------
An internal function that takes a value and
returns the form that would be stored for
that value in a descending index. Essentially
doing a one's complement on the bytes and
appending an 0xFF byte
That's there because IX_CATALOG_SUBSET is a descending index.
|
|
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427374 is a reply to message #426926] |
Thu, 22 October 2009 05:02 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I don't think the fact that it's DESC is the problem. I think the fact that it's being used at all is the problem.
Indexes are usefull when selecting a small subset of rows.
When you're selecting half a large table as the OP is you want a full table scan.
The only way the DESC bit might be an issue is if this fact is what is fooling the optimiser into using the index in the first place - but I suspect the real reason is the stats are out of date.
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427390 is a reply to message #427374] |
Thu, 22 October 2009 06:19 |
manzoor14
Messages: 5
Registered: October 2009
|
Junior Member |
|
|
Hi,
The stats are up to date. Just to confirm i ran the count queries on the tables used in the Main query and obtained the following result.
select count(*) from line_item;
COUNT(*)
----------------------
1040131
1 rows selected
select count(*) from loc_line_item ;
COUNT(*)
----------------------
1040131
1 rows selected
select count(*) from line_item_price;
COUNT(*)
----------------------
2080262
1 rows selected
select count(*) from loc_manufacturer;
COUNT(*)
----------------------
2749
1 rows selected
select count(*) from line_item_image;
COUNT(*)
----------------------
1040131
1 rows selected
select count(*) from loc_product ;
COUNT(*)
----------------------
93819
1 rows selected
select count(*) from image;
COUNT(*)
----------------------
99203
1 rows selected
Also,
PFB the explain plan when i ran the query using Index Hint
EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 13695 | 2410 |
|* 1 | HASH JOIN | | 55 | 13695 | 2410 |
| 2 | NESTED LOOPS | | 55 | 12375 | 2406 |
| 3 | NESTED LOOPS | | 55 | 9240 | 2351 |
| 4 | NESTED LOOPS | | 55 | 8690 | 2296 |
|* 5 | HASH JOIN | | 562 | 32596 | 1172 |
| 6 | NESTED LOOPS | | 562 | 20232 | 1126 |
| 7 | TABLE ACCESS BY INDEX ROWID| LINE_ITEM_PRICE | 562 | 11240 | 2 |
|* 8 | INDEX RANGE SCAN | IX_CATALOG_SUBSET | 225 | | 1 |
| 9 | TABLE ACCESS BY INDEX ROWID| LINE_ITEM | 378 | 6048 | 2 |
|* 10 | INDEX UNIQUE SCAN | PK_LINE_ITEM | 1 | | 1 |
| 11 | TABLE ACCESS FULL | LOC_PRODUCT | 93815 | 2015K| 40 |
|* 12 | TABLE ACCESS BY INDEX ROWID | LOC_LINE_ITEM | 1 | 100 | 2 |
|* 13 | INDEX RANGE SCAN | IX_LOC_LINE_ITEM_ID | 1 | | 1 |
|* 14 | INDEX RANGE SCAN | PK_LINE_ITEM_IMAGE | 1 | 10 | 1 |
| 15 | TABLE ACCESS BY INDEX ROWID | IMAGE | 1 | 57 | 1 |
|* 16 | INDEX UNIQUE SCAN | PK_IMAGE | 1 | | |
| 17 | TABLE ACCESS FULL | LOC_MANUFACTURER | 2749 | 65976 | 3 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."MANUFACTURER_ID"="D"."MANUFACTURER_ID")
5 - access("A"."PRODUCT_ID"="F"."PRODUCT_ID")
8 - access(SYS_OP_DESCEND("C"."CATALOG_SUBSET_ID")=HEXTORAW('3DF9B2FF') )
filter("C"."CATALOG_SUBSET_ID"=576)
10 - access("A"."LINE_ITEM_ID"="C"."LINE_ITEM_ID")
12 - filter(UPPER("B"."SHORT_DESC") LIKE '%BLADES%' OR UPPER("B"."SHORT_DESC") LIKE '
%DIAMOND%')
13 - access("A"."LINE_ITEM_ID"="B"."LINE_ITEM_ID")
14 - access("A"."LINE_ITEM_ID"="E"."LINE_ITEM_ID")
16 - access("E"."IMAGE_ID"="G"."IMAGE_ID")
Note: cpu costing is off
39 rows selected
The Explain plan when using the rule hint is.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | LOC_MANUFACTURER | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | NESTED LOOPS | | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | | | |
| 6 | NESTED LOOPS | | | | |
| 7 | NESTED LOOPS | | | | |
| 8 | TABLE ACCESS FULL | LOC_PRODUCT | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| LINE_ITEM | | | |
|* 10 | INDEX RANGE SCAN | IX_PRODUCT_ID_1 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | LINE_ITEM_PRICE | | | |
|* 12 | INDEX UNIQUE SCAN | PK_LINE_ITEM_PRICE | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | LOC_LINE_ITEM | | | |
|* 14 | INDEX RANGE SCAN | IX_LOC_LINE_ITEM_ID | | | |
|* 15 | INDEX RANGE SCAN | PK_LINE_ITEM_IMAGE | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | IMAGE | | | |
|* 17 | INDEX UNIQUE SCAN | PK_IMAGE | | | |
|* 18 | INDEX RANGE SCAN | PK_LOC_MANUFACTURER | | | |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("A"."PRODUCT_ID"="F"."PRODUCT_ID")
12 - access("A"."LINE_ITEM_ID"="C"."LINE_ITEM_ID" AND "C"."CATALOG_SUBSET_ID"=576)
13 - filter(UPPER("B"."SHORT_DESC") LIKE '%BLADES%' OR UPPER("B"."SHORT_DESC") LIKE '%
DIAMOND%')
14 - access("A"."LINE_ITEM_ID"="B"."LINE_ITEM_ID")
15 - access("A"."LINE_ITEM_ID"="E"."LINE_ITEM_ID")
17 - access("E"."IMAGE_ID"="G"."IMAGE_ID")
18 - access("A"."MANUFACTURER_ID"="D"."MANUFACTURER_ID")
Note: rule based optimization
38 rows selected
I am not quite sure about the index IX_CATALOG_SUBSET is in desc mode as , the schema and the structure is in existence from long back.
Thanks.
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427396 is a reply to message #427390] |
Thu, 22 October 2009 06:49 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Quote:
The stats are up to date. Just to confirm i ran the count queries on the tables used in the Main query and obtained the following result.
No statistics are not upto date.
You can verify by selecting the LAST_ANALYZED from the USER_TABLES for the tables being used
also, you can look at USER_INDEXES for Indexes.
Verify if the plan looks good/and also the response time
If not, Drop the index suggested in the previous reply.
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427500 is a reply to message #427396] |
Thu, 22 October 2009 23:41 |
manzoor14
Messages: 5
Registered: October 2009
|
Junior Member |
|
|
the following are the results for the last analyzed query
20-OCT-09 LINE_ITEM
20-OCT-09 LINE_ITEM_DOCUMENT
17-OCT-09 LINE_ITEM_IMAGE
20-OCT-09 LINE_ITEM_PRICE
so i guess the stats are up to date. Please let me know if i need to do anything more.
Thanks,
Manzoor Elahi
|
|
|
|
|
|
| Re: Optimizing the query containing 7 table joins [message #427548 is a reply to message #427500] |
Fri, 23 October 2009 04:01 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You're having a bit of a problem supplying complete sets of data, aren't you.
The tables that your query uses are:line_item
loc_line_item
line_item_price
loc_manufacturer
line_item_image
loc_product
image
You've confirmed that over half of them have recent statistics - what about the rest?
As I said before, you're missing an index on LOC_PRODUCT(PRODUCT_ID) - that should help you avoid the FTS on Loc_Product
|
|
|
|
Goto Forum:
Current Time: Mon Nov 25 16:25:48 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
