Re: What does LIKE '%%' actually do in a WHERE clause?

From: Subodh Deshpande <deshpande.subodh_at_gmail.com>
Date: Thu, 6 Oct 2011 00:08:59 +0530
Message-ID: <CAJsOtB6x=ptxkGD=-rm-7uLkiXy3gq8i3gx_F3fmQK7bcBxnUA_at_mail.gmail.com>

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_at_environment-agency.gov.uk> 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
==============================


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 05 2011 - 13:38:59 CDT