Re: Large IN LIST in an OBIEE query

From: Jared Still <jkstill_at_gmail.com>
Date: Sat, 29 Oct 2011 18:01:14 -0700
Message-ID: <CAORjz=OGwnFs_jNt9utQ4VzU=chN2A_pzcirO42bfDfqFJWkZA_at_mail.gmail.com>

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.
>
>

There are alternatives to consider, such as sys.odcivarchar2list.

I have used this in testing, but not yet in production. It should however work quite well .

with mylist as (

   select column_value owner
   from (

      table(
         sys.odcivarchar2list( 'RT', 'TSMSYS', 'SCOTT', 'RMAN10G',
'RMAN11G', 'JKSTILL')
      )

   )
)
select t.owner, t.table_name
from all_tables t
join mylist m on m.owner = t.owner
order by owner, table_name
/

No tables required, though it will still require changes.

There are also sys.odcinumberlist and sys.odcidatelist.

This requires 10g+

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

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

Received on Sat Oct 29 2011 - 20:01:14 CDT