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?
Rao_SAsked:
Who is Participating?
 
AkenathonConnect With a Mentor Commented:
Try this version:

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' and m.owner_plant_id = 'USSC'
UNION
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 
ma.MATRL_ALIAS_ID='6688300' and ma.MATRL_ALIAS_SCHEME='ZZ' and m.owner_plant_id = 'USSC';

Open in new window


Check that you have indexes:

- On the material table by matrl_id (optionally coupled with owner_plant_id, but probably not needed)
- On the alias table by matrl_pk, and another one by matrl_alias_id (optionally coupled with matrl_alias_scheme, but probably not needed)
0
 
slightwv (䄆 Netminder) Commented:
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);
0
 
Rao_SAuthor Commented:
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 |
------------------------------------------------------------------------------------------------------
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
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

0
 
Rao_SAuthor Commented:
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?
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>Suppose I create indexes on both tables, how does the join use the index?

Oracle's cost based optimizer will determine if the index is used or not.  There are many factors used in this calculation.  

In a nutshell:  If the number of blocks read (index and table) to achieve the results is within a percentage, Oracle may choose a Full Table Scan over index use.  I forget the exact percentage.  I think it's published out there somewhere if you want to look for it.
0
 
Rao_SAuthor Commented:
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"(+))
0
 
Rao_SAuthor Commented:
I dont understand how to read the expalin plan, if can expalin, that would be great!
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>if can expalin, that would be great!

"Predicate Information"  This section shows the values the where clause.

What I typically look for in the plan is the FULL TABLE SCANS.  I just wanted to make sure the select statement isn't trying to use any existing index.

The COST column is a good 'gauge' to look at.  I say 'gauge' because it really doesn't mean much in the grand scheme of tuning.  Smaller costs do not mean faster SQL.

Look at the cost of:
TABLE ACCESS FULL   | TQT_IT_MATERIAL      

as Akenathon suggested:
I would look at a possible index on owner_plant_id or matrl_id possible a combination but that would be based on the other queries that might be referencing those columns.


Since it is partitioned, you need to also decide if a LOCAL of GLOBAL index would be better here.  This is also based on the other queries that might be using these columns.
0
 
Rao_SAuthor Commented:
I am still waiting to test the solution.
As soon as it is tested, will update the thread and accept the solution.
0
 
AkenathonConnect With a Mentor Commented:
Cool, but remember that for my solution to work, you *MUST* have the indexes I mentioned available. Global or local, it doesn't make any difference for your query :-)
0
 
Rao_SAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
Rao_SAuthor Commented:
My mistake, what did I do wrong? Did not mean to, Should I click on the blue buttom?
0
 
slightwv (䄆 Netminder) Commented:
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.

0
 
Rao_SAuthor Commented:
Hope I did this correct this time. (this is the first time i am using ee properly).
Want to thank you all again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.