OraFAQ Forum: Performance Tuning » Tuning: multiple table inner join query with partitioning

Home » RDBMS Server » Performance Tuning » Tuning: multiple table inner join query with partitioning

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning: multiple table inner join query with partitioning [message #156084]

Fri, 20 January 2006 12:24

arnematt
Messages: 24
Registered: November 2005
Location: Netherlands

Junior Member

Hi,

I am facing a query that is quite bulky. Art Metzer already helped out big time but now its coming down to getting this to run smoothly.

The query is used in a partition by selection statement (for summation) and then joined with another set from those same tables that meets the same criteria (but cant be included in first select because of different groupings)

Here is the select statement, without the partition by.

I would need to optimize this because it runs like a turtle. It would make sense to hint some ordering in the inner joins given the way the data is organized. However I cant seem to convince the optimizer to do anything else than left to right.

SELECT <Fields>
FROM hist
INNER JOIN stat USING (k1)
INNER JOIN cl_art ON stat.k2=cl_art.k2
INNER JOIN cl_loc USING (k3)
INNER JOIN loc ON loc.k4=cl_loc.k4
INNER JOIN sale ON sale.k4 = loc.k4
                AND sale.k2 = stat.k2
WHERE sale.<has 3 restrictions>
AND stat.<has 2 restrictions>
AND hist.<has 1 restriction>
AND cl_art.(has 4 restrictions>
AND cl_loc.<has 4 restrictions>
AND loc.<has 2 restrictions>

Now with ANSI JOINS it is said you can use parentheses to indicate ordering. Here I run into two issues:

- I cant get it to work
- I have read that the optimizer decides what path to follow by itself anyway, no matter how many parentheses you place.

Ideally I would want to do the following:

inner join hist with stat with cl_art.
inner join cl_loc with loc.
inner join those two subsets.
inner join this subset with sale.

At least I am assuming inner join is the best option, of all tables, >60% will be used EXCEPT from the sale table, of which about .1% will be used (its HUGE but partitioned usefully by date).

Using parentheses it would look like this if I understand correctly:

SELECT <Fields> FROM
((((hist
INNER JOIN stat USING (k1))
INNER JOIN cl_art ON stat.k2=cl_art.k2)
INNER JOIN cl_loc USING (k3)
(INNER JOIN loc ON loc.k4=cl_loc.k4))
INNER JOIN sale ON sale.k4 = loc.k4 AND sale.k2 = stat.k2)
WHERE ...

This doesnt work... missing right parenthesis at 6th line...

any suggestions? Other ways to optimize? Is it true that I am loosing a lot of time on parsing so should I hint /*+ordered*/, optimizer_search_limit, or anything else?

(using 9.2...)

Report message to a moderator

Re: Tuning: multiple table inner join query with partitioning [message #156097 is a reply to message #156084]

Fri, 20 January 2006 13:09

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Moved your question here, have a look at the sticky and get back with us, also provide more info like your table structures and sizes and explain plan.

Also, what do you mean by "assuming inner join is best"? That is a results type question, not a matter of choice. If you need an outer join to get the results you want, then you must use an outer join and have no choice in the matter. Otherwise can use inner join.

I don't use the "ANSI" join syntax, but I would be quite surprised if the parenthesis affected the join order. The CBO is going to pick the join order for you.

Report message to a moderator

Re: Tuning: multiple table inner join query with partitioning [message #156169 is a reply to message #156097]

Mon, 23 January 2006 05:09

arnematt
Messages: 24
Registered: November 2005
Location: Netherlands

Junior Member

smartin wrote on Fri, 20 January 2006 20:09

That is ... not a matter of choice.

If your returned subset exists of up to 60% of all rows used in a table it may be wise to use "WHERE EXISTS IN(" instead of inner joins so there is a choice as far as I can see.

smartin wrote on Fri, 20 January 2006 20:09

I would be quite surprised if the parenthesis affected the join order. The CBO is going to pick the join order for you.

Look atOracle SQL*Plus Pocket Reference by O'Reily, 1.3.5.3. Join Order:
When joining more than two tables, use parentheses to control the join order. If you omit parentheses, Oracle processes the joins from left to right. Example used is that of an inner join.

However I do recall reading somewhere that the CBO will pick its own order anyhow, despite of such parentheses... Sad

As to the structure of tables and explain plan: unfortunately I cannot give too many details as I am not at the client site momentarily. Maybe you could say something about general order: first put smaller tables together, then to large ones, etc...

I am thinking of using the /*+ORDERED*/ or /*+STAR*/ hints...

Thank you.

Report message to a moderator

Re: Tuning: multiple table inner join query with partitioning [message #156214 is a reply to message #156169]

Mon, 23 January 2006 11:36

SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium

Junior Member

Dear,

What is your Oracle release ?

Quote:

It would make sense to hint some ordering in the inner joins given the way the data is organized

In most of the cases you should NOT use hints.
These days you should work with CBO and have stats on your tables and indexes. The optimizer will choose for the most appropriate execution plan. Using hints is very often - despite exceptions - somewhat arrogant, it is as if you know it better, as if overruling the optimizer' s choice will bring you good luck. (there are exceptions)

What is your Oracle release ?
What is your SGA about ? (shared_pool, db_block_size ,db_cache_size, db_block_buffers, sga_target, sga_max_size, ...)
What are your optimizer parameters "SQL> show parameter optimizer"
What is your pga_aggregate_target, sort_area_size, hash_area_size, db_file_multiblock_read_count ?
Is your system OLTP either DSS

Can you post us the execution plan ?
Number of records, selectivity of columns

Have you TRACED with event 10046 at level 8 in order to know the waits,
Have you formatted your trace files with tkprof ?

Quote:

WHERE sale.<has 3 restrictions>
AND stat.<has 2 restrictions>
AND hist.<has 1 restriction>
AND cl_art.(has 4 restrictions>
AND cl_loc.<has 4 restrictions>
AND loc.<has 2 restrictions>

You may want to use composite indexes for the multi restriction

Quote:

If your returned subset exists of up to 60% of all rows used in a table it may be wise to use "WHERE EXISTS IN(" instead of inner joins so there is a choice as far as I can see.

WHERE EXISTS IN
it is on of the two : (not)exists operator or (not)in operator

Bye the way, if you wanna play with hints, this ones will process the from list from left to right
...
SQL> select /*+ ORDERED USE_MERGE (a,b,c) */ a.col1,a.col2,b.col1,b.col2,c.col1,c.col2 from t1 a,t2 b,t3 c where ...
SQL> select /*+ ORDERED USE_NL (a,b,c) */ a.col1,a.col2,b.col1,b.col2,c.col1,c.col2 from t1 a,t2 b,t3 c where ...
SQL> select /*+ ORDERED USE_HASH (a,b,c) */ a.col1,a.col2,b.col1,b.col2,c.col1,c.col2 from t1 a,t2 b,t3 c where ...
...
Regards
Guy Lambregts

Report message to a moderator

Previous Topic:	Performance tuning - Help
Next Topic:	Tuning Update Statements

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 14:30:21 CST 2024