asked on

How to optimize this query?

select m.* from AQTIT01.TQT_IT_MATERIAL m
left outer join AQTIT01.TQT_IT_MATRL_ALIASES ma
on m.MATRL_PK=ma.MATRL_PK
where
(m.MATRL_ID='6688300' or (ma.MATRL_ALIAS_ID='6688300' and ma.MATRL_ALIAS_SCHEME='ZZ')) and m.owner_plant_id = 'USSC';
Info
Right now this query takes a long time to run.
On the Material table, matrl_pk is the primary key, on alias table the matrl_pk and matrl_alias_id is the primary key.
Q
What indexes can I create to bring back the data faster?

slightwv (䄆 Netminder)

Please post the execution plan:

explain plan for
select m.* from AQTIT01.TQT_IT_MATERIAL m
...

then display it:
select * from table(dbms_xplan.display);

Rao_S

ASKER

Here is the explain plan:

"PLAN_TABLE_OUTPUT"
Plan hash value: 4162483167

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1742 | 418K| 49 (0)| 00:00:01 | | |
| 1 | PARTITION LIST ALL| | 1742 | 418K| 49 (0)| 00:00:01 | 1 | 15 |
| 2 | TABLE ACCESS FULL| TQT_IT_MATERIAL | 1742 | 418K| 49 (0)| 00:00:01 | 1 | 15 |
------------------------------------------------------------------------------------------------------

slightwv (䄆 Netminder)

Sorry for the confusion: The '...' meant for you to post the rest of your select statement. I just didn't want to copy the entire thing.

explain plan for
select m.* from AQTIT01.TQT_IT_MATERIAL m 
left outer join AQTIT01.TQT_IT_MATRL_ALIASES ma 
on m.MATRL_PK=ma.MATRL_PK 
where 
(m.MATRL_ID='6688300' or (ma.MATRL_ALIAS_ID='6688300' and ma.MATRL_ALIAS_SCHEME='ZZ')) and m.owner_plant_id = 'USSC';

Open in new window

Rao_S

ASKER

Info
The material table is partitioned.There are several indexes on both tables but none for owner_plant_id and material_id and material_pk together.
Suppose I create indexes on both tables, how does the join use the index?

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

Sorry, here is the full explain:

"PLAN_TABLE_OUTPUT"
Plan hash value: 1110965258

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 552 | 29 (4)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 2 | 552 | 29 (4)| 00:00:01 | | |
| 3 | TABLE ACCESS FULL | TQT_IT_MATRL_ALIASES | 118 | 2242 | 14 (0)| 00:00:01 | | |
| 4 | PARTITION LIST SINGLE| | 716 | 179K| 14 (0)| 00:00:01 | KEY | KEY |
| 5 | TABLE ACCESS FULL | TQT_IT_MATERIAL | 716 | 179K| 14 (0)| 00:00:01 | 13 | 13 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("M"."MATRL_ID"='6688300' OR "MA"."MATRL_ALIAS_ID"='6688300' AND
"MA"."MATRL_ALIAS_SCHEME"='ZZ')
2 - access("M"."MATRL_PK"="MA"."MATRL_PK"(+))

Rao_S

ASKER

I dont understand how to read the expalin plan, if can expalin, that would be great!

ASKER CERTIFIED SOLUTION

Akenathon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

I am still waiting to test the solution.
As soon as it is tested, will update the thread and accept the solution.

SOLUTION

Akenathon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

Thank you for all the information!
The query works much faster and we will create one more index, as it i still doing one full scan on the aliases table.
|* 8 | TABLE ACCESS FULL | TQT_IT_MATRL_ALIASES | 1 | 19 | 14 (0)| 00:00:01 | | |
After that, index it should be much better.

slightwv (䄆 Netminder)

I'm afraid I have to object to closing as proposed.

Several Experts here suggested indexes and since this is what you went with, the points should be divided among those Experts.

Rao_S

ASKER

My mistake, what did I do wrong? Did not mean to, Should I click on the blue buttom?

slightwv (䄆 Netminder)

No problems.

You asked the question be closed by accepting your last post as correct.

Just click the 'Accept Multiple Solutions' on the primary post you want to accept then divide up/assign points as you wish.

Rao_S

ASKER

Hope I did this correct this time. (this is the first time i am using ee properly).
Want to thank you all again!