RE: Large IN LIST in an OBIEE query
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mwf@xxxxxxxx>, <mdinh@xxxxxxxxx>, <greg@xxxxxxxxxxxxxxxxxx>, <Hemant.Chitale@xxxxxx>
- Date: Sun, 30 Oct 2011 19:38:52 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Sunday, October 30, 2011 7:20 PM
To: mdinh@xxxxxxxxx; greg@xxxxxxxxxxxxxxxxxx; Hemant.Chitale@xxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Michael Dinh
Sent: Sunday, October 30, 2011 5:34 PM
To: greg@xxxxxxxxxxxxxxxxxx; Hemant.Chitale@xxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Greg Rahn [greg@xxxxxxxxxxxxxxxxxx]
Sent: Sunday, October 30, 2011 1:13 PM
To: Hemant.Chitale@xxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
