index a column when creating an inline view 1

[cmmrfrds]

I am creating a derived table in an inline view and I would like to index a column while creating the inline view. Is this possible and what is the syntax.

The reason for this is I have a derived table in a Business Objects Universe and in order to take advantage of "index awareness" in the Universe the derived table column needs to be indexed.

 

[SantaMufasa]

I am creating a derived table in an inline view...

I don't know what this means, Cmmrfrds. Can you post the guts of the code that illustrates what you are trying to do? (One cannot create an INDEX on a VIEW since a VIEW is simply a query. INDEXes must be on the underlying object.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cmmrfrds]

Here is what I would like to do.

-- The with clause is what the derived table is in the Universe - it is dynamic like an inline view.
With tDimension as
(
Select
Location
From Clarity.X_PB_GL_FULLRUN f_gl
where post_period = to_char(last_day(add_months(sysdate, -1)),'YYYYMM')
Group By Location
Order By Location ASC
)

-- The Universe will generate code like this when I filter on location
Select
f_gl.location
From Clarity.X_PB_GL_FULLRUN f_gl
inner join tDimension td
On td.location = f_gl.location
where post_period = to_char(last_day(add_months(sysdate, -1)),'YYYYMM')
and td.location = '003'

-- I want the Universe to generate code like this - leaving out the join.
Select
f_gl.location
From Clarity.X_PB_GL_FULLRUN f_gl
where post_period = to_char(last_day(add_months(sysdate, -1)),'YYYYMM')
and f_gl.location = '003'

-- The Universe will only do this if I can make it "index aware" but the
-- location column in tDimension needs to be an index to make this work.

 

[Dagon]

Why can't you just create the index on the table using the "create index" command and leave it there ? I don't know of any way to create an index "on the fly" in a query and I have no idea why you would want to. To keep re-creating an index every time you run a query would be very expensive.

 

[sem]

You may index MATERIALIZED VIEW instead and use it as your dimension. This is not exactly what you want but it may improve the performance.

BTW your last 2 queries are not identical as your dimension table is filtered by date while the main [fact] one is not.

Regards, Dima

http://seminihin.narod.ru

 

[cmmrfrds]

Thank you Dima, good idea, that could work and I may use it sometime in the future. I started out harvesting the dimensions from the actual data. Although, it was dynamic it was too slow. I found some tables in the database where I could build the dimensions more efficiently and switched over to that method. It is performing acceptable now for the 6 dimension tables I needed.