RE: Large IN LIST in an OBIEE query
Date: Sun, 30 Oct 2011 19:38:52 -0400
Message-ID: <011c01cc975d$158915c0$409b4140$_at_rsiz.com>
Hoping I'm not beating a dead horse, if this happens to be a list table that contains multiple different lists for different list type codes and the list type code column is indexed, it *may* be helpful to write the inline view as
(select list_value_column from list_table
where list_type_code = 'the code you know it is from'
and list_value_column in <list_of_values>)
That literal likely is not usefully a bind value.
Recognizing that these sorts of multiple value lists by type code tables exist is not the same as me endorsing them as a design concept. That is a different conversation.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark W. Farnham
Sent: Sunday, October 30, 2011 7:20 PM
To: mdinh_at_XIFIN.Com; greg_at_structureddata.org; Hemant.Chitale_at_sc.com
Cc: oracle-l_at_freelists.org
Subject: RE: Large IN LIST in an OBIEE query
If I understand you correctly, you're getting something like a dropdown list from a table and the user highlights one or a few or a lot of choices from some pointy-clicky interface and then the choices become the in list.
For that special case (even though it might seem redundant to use the list source table you've already pruned in the query), it can be quite effective to add the inline view
(select list_column from list_table where list_column in (<list of values>)) list
and
<some_column.some_table> = list.list_column>
as a predicate. If the plan is always better (meaning less actual cost) doing the join this way than what the CBO gets when you have the predicate <some_column.some_table> in (<list of values>), but sometimes estimated cardinality yields a transformed plan that is the filter rather than the join, then writing the inline view as
(select
--+ no_merge
list_column from list_table where list_column in (<list of values>)) list
may be useful.
Greg's point that having a validated source of the possible choices for the IN LIST is dead bang on, and the optimizer gets to use the stats on that table when it exists. When that is the case the chances that the CBO gets a good plan rises from the rule generated estimated cardinality of function returns. So even though in an information sense including a table that the user has already filtered for you by his choice in the application is redundant, it is extra information the CBO does not otherwise have and it gives the CBO another plan resolution choice that is not available without having the table present as a join source. If I understood your meaning correctly, you don't have to add anything to the schema to have this work, nor do you have to construct an artificial conversion of the in list into a table row source, since you have an actual to table to prune.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Michael Dinh
Sent: Sunday, October 30, 2011 5:34 PM
To: greg_at_structureddata.org; Hemant.Chitale_at_sc.com
Cc: oracle-l_at_freelists.org
Subject: RE: Large IN LIST in an OBIEE query
LOL - I asked the same question.
Would it surprise you to know the IN list is from querying against the tables in application schema?
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn [greg_at_structureddata.org] Sent: Sunday, October 30, 2011 1:13 PM
To: Hemant.Chitale_at_sc.com
Cc: oracle-l_at_freelists.org
Subject: Re: Large IN LIST in an OBIEE query
I know there have been numerous comments on possible solutions thus far, but
I'm going the opposite direction (not *what* is being done, but *why* it's
being done):
Where does this list come from?
It's probably safe to say no user keys in 1000 values, so do they have their
own discrete list they copy/paste or is it a hard coded discrete list in
OBIEE?
Either way, it seems it would make more sense to have that in a table, which
seems to avoid the problem you observe, no?
On Fri, Oct 28, 2011 at 1:50 AM, Chitale, Hemant Krishnarao
<Hemant.Chitale_at_sc.com> wrote:
> I have a few OBIEE queries that "perform poorly". Apparently, the
> users are allowed to "insert" a list of values to query for. OBIEE
> then constructs the query with a large IN LIST.
> If I move the IN LIST values into a temporary table and then join the
> temporary table, I get better performance. However, making this
> change in OBIEE requires a change to the OBIEE data model.
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sun Oct 30 2011 - 18:38:52 CDT