Home » RDBMS Server » Performance Tuning » Selection of driving table
icon5.gif  Selection of driving table [message #307173] Tue, 18 March 2008 01:35 Go to next message
vkrn
Messages: 18
Registered: March 2008
Junior Member
Hi all,

Smile .I am tuning the queries with respect to CBO(Cost Based Optimiztion).How to choose a particular table as driving table? Even in CBO also do the selection of driving table effect the performance?can anyone give me claer idea .......

Thanks,
kiran.
Re: Selection of driving table [message #307178 is a reply to message #307173] Tue, 18 March 2008 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CBO chooses the driving table for you, you don't have to care about this.

Regards
Michel
Re: Selection of driving table [message #307188 is a reply to message #307178] Tue, 18 March 2008 01:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you don't like the way CBO chooses the driving table, you can use /*+LEADING(..)*/ or /*+ORDERED*/ hints to influence the join order.

Generally speaking the driving table should be the one with the most restrictive filtering WHERE clauses. If there are NO filetering predicates - or only non-restrictive predicates - then arrange the sort order that places the largest tables last.

Ross Leishman
Selection of driving table [message #307202 is a reply to message #307173] Tue, 18 March 2008 02:25 Go to previous messageGo to next message
vkrn
Messages: 18
Registered: March 2008
Junior Member
Thanks .
If i want to use indexes for fine tuning of the queries,i should know whether indexes are available for my tables or not.
For that how can i get the index statistics for my table.

Thanks,
Kiran.
Re: Selection of driving table [message #307214 is a reply to message #307202] Tue, 18 March 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If i want to use indexes for fine tuning of the queries

No you don't want, the optimizer may choose index or not.

Quote:
i should know whether indexes are available for my tables or not.

No, you don't have to know, the optimizer knows it for you.

Quote:
For that how can i get the index statistics for my table

You don't have to know the statistics on index, just gather them with dbms_stats.

Regards
Michel
Re: Selection of driving table [message #307218 is a reply to message #307173] Tue, 18 March 2008 02:51 Go to previous messageGo to next message
vkrn
Messages: 18
Registered: March 2008
Junior Member
can u give me the correct syntax for the usuage of dbms_stats.

Thanks,
Kiran.
Re: Selection of driving table [message #307227 is a reply to message #307218] Tue, 18 March 2008 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_stats

Regards
Michel
Re: Selection of driving table [message #307873 is a reply to message #307202] Thu, 20 March 2008 04:03 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
If you are looking at how to find Indexes on a table, you can use this

select index_name, column_position, column_name, table_name from user_ind_columns
where table_name like upper('&FL%')
order by index_name, column_position;

where FL is your table_name
Re: Selection of driving table [message #307902 is a reply to message #307873] Thu, 20 March 2008 05:25 Go to previous message
vkrn
Messages: 18
Registered: March 2008
Junior Member
Thanks msmallya.
Previous Topic: About Oracle Database Link
Next Topic: Efficient Data Management.
Goto Forum:
  


Current Time: Sat Dec 10 22:27:24 CST 2016

Total time taken to generate the page: 0.11910 seconds