Re: Why is this query slow?
Date: Fri, 12 Mar 2010 05:46:23 -0800 (PST)
Message-ID: <f51bba3e-c9f1-4451-88db-5136d44a7cd8_at_g10g2000yqh.googlegroups.com>
On Mar 12, 5:34 am, vsevolod afanassiev
<vsevolod.afanass..._at_gmail.com> wrote:
> I have been doing SQL query tuning for a while and in most cases this
> is relatively straightforward excercise. For example: if a query
> selects only a few rows from large table then it should be accessed
> through index, if a query selects more than 10% of the rows the table
> should be accessed through full table scan. In many cases I was able
> to reduce number of buffer gets from something like 200,000,000 to
> less than 1000 per execution by creating indexes and adding a few
> hints.
The 10% rule is not suitable in all cases. Last year I posted to this group a test case that showed a full table scan was faster when selecting 0.06% of the rows in a 100,000,000 row table than was an index access when the index had a high clustering factor. However, 10% to 20% is probably an OK tipping point in most cases.
> The difficulty I have now is that the query is very simple and as far
> as I can see there is nothing wrong
> with plan. Still it is slow.
>
> The query is
>
> select * from transactions
> where tx_date between (date1 and date2) and
> status = 'AAA' and
> ID not in
> (select ID from transaction where tx_date between (date3 and date4)
> and status = 'AAA')
Columns that are not explicitly defined as NOT NULL might be causing
performance problems for you when the optimizer is not able to
transform the SQL statement into a more efficient form. See this blog
article:
http://hoopercharles.wordpress.com/2010/01/10/the-effects-of-potential-null-values-in-row-sources-during-updates-using-an-in-subquery/
(Notice the change in the execution plan in the above link)
I would be inclined to re-write the SQL statement to look something
like this:
select
T.*
from
transactions T,
(select DISTINCT
ID
from
temp_table) TT
where
T.tx_date between date1 and date2
and T.status = 'AAA'
AND t.ID=TT.ID(+)
AND TT.ID IS NULL;
You might also take a look at this AskTom thread:
http://asktom.oracle.com/pls/asktom/f?p=100:11:1096388716688582::::P11_QUESTION_ID:313616750808
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Mar 12 2010 - 07:46:23 CST