hi Norman, to get the first 100 rows..whats criteria..any..then rownnum will work..otherwise..top-n queries should be used.. am sure top-n queries will be faster.. randomly I found two sites about top n queries.. http://www.orafaq.com/faq/how_does_one_select_the_top_n_rows_from_a_table http://www.oracle-base.com/articles/misc/TopNQueries.php thanks..subodh On 5 October 2011 20:55, Dunbar, Norman (Capgemini) < norman.dunbar.capgemini@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote: > Hi Subodh, > > >> and if we are going to use rownum then it will be always > >> faster I suppose.. > Both queries used the rownum < 101 to just get the first 100 rows. > > The one with % in the WHERE clause (and the one with nothing except the > joins between tables) takes over 7 minutes - it has huge cardinalities and > I'm sure that it's joining all 4 tables and then returning the first 100 > rows. > > The one with %% joins the tables and bungs back the same 100 rows in a > couple of seconds. > > > <SNIP> > > > >> not very sure randomly guess may be '%%' will ignore nulls.. > Yes, % won't match nulls. > > > Thanks. > > > Cheers, > Norm. > > Norman Dunbar > Contract Senior Oracle DBA > Capgemini Database Build Team > Internal : 7 28 2051 > External : 0113 231 2051 > > Information in this message may be confidential and may be legally > privileged. If you have received this message by mistake, please notify the > sender immediately, delete it and do not copy it to anyone else. > > We have checked this email and its attachments for viruses. But you should > still check any attachment before opening it. > We may have to make this message and any reply to it public if asked to > under the Freedom of Information Act, Data Protection Act or for litigation. > Email messages and attachments sent to or from any Environment Agency > address may also be accessed by someone other than the sender or recipient, > for business purposes. > > If we have sent you information and you wish to use it please read our > terms and conditions which you can get by calling us on 08708 506 506. Find > out more about the Environment Agency at www.environment-agency.gov.uk > -- ============================== DO NOT FORGET TO SMILE TODAY ============================== -- //www.freelists.org/webpage/oracle-l