Re: Is is possible to tune this SQL statement?

Without seeing the view definition and the underlying tables, it's hard to tell exactly, but here are some basic guidelines:

No, you cannot build an index directly on a view, but you can build indexes on the tables that make up the view. Assuming the view columns you reference in the WHERE clause of your query have corresponding columns in the tables (i.e., they aren't functions, although I've read that Oracle8i supports indexes on functions), those indexes will speed your query.

I tried to make that last sentence clear, but I can see it's not. So here's an example:

Say your view is this:

CREATE VIEW empdept AS
SELECT e.name, d.deptname
FROM emp e, dept d
WHERE e.deptno = d.deptno

and dept has an index on deptname. If your query is this:

SELECT *
FROM empdept
WHERE deptname = 'ACCOUNTING'

the index can help you. But if your view is this:

CREATE VIEW empdept AS
SELECT e.name, UPPER(d.deptname) deptname FROM emp e, dept d
WHERE e.deptno = d.deptno

that same query WON'T use the dept(deptname) index.

You didn't mention whether you're using the cost-based optimizer (CBO) or the rule-based optimizer (RBO). If CBO, you could put hints into the view, but Oracle doesn't recommend this, and in this case, neither do I. Since your WHERE clause changes a lot, hints in the view are likely to slow some of your queries. On the other hand, hints in the queries themselves might be helpful. I've found that the CBO sometimes makes poor choices in complex views, and a five-table join can fall in that category. To help out the CBO, make sure you have fresh statistics on your tables & indexes (i.e., run ANALYZE TABLE & ANALYZE INDEX).

One last thing -- you mention putting indexes on the columns you're retrieving. If you don't also have the columns from your WHERE clause in the index, this doesn't help you. If both your WHERE columns and your SELECT columns are in an index, Oracle can return your data from the index alone, without even reading from the table.

Good luck, Jimmy!

Joseph

In article <37DBE75A.A005EDF7_at_comp.polyu.edu.hk>,   Jimmy <c6635500_at_comp.polyu.edu.hk> wrote:
> Hello all,
>
> I create a view AAA joining 5 tables and the resulting rows is
over
> 50000. I am doing query on AAA with the following SQL statements:
>
> select distinct(field) from AAA where condition...
>
> (View AAA has 50 columns, only the column with varchar2 data type
is
> retrieved)
> The condition part is different each time. Is there any way to
> improve the above query time? If the fields to retrieve is only on two
> or three columns (each time one field is retrieved but the condition
> part is again different each time), can I built index on these columns
> (i.e. can I built index on view column)? Or is there other ways can
> improve the query time?
>
> Thanks,
> Jimmy
>
>

--
Joseph Thvedt
jthvedt_at_my-deja.com

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sun Sep 12 1999 - 00:07:24 CDT