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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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) 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
AkenathonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) 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
AkenathonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.