Re: view vs table and a developer w/o a dba

You may be giving the optimizer a choice between two indexes and it's not choosing the correct one.

Ex:

select stuff
  from table
 where name = 'foo'
   and city = 'NY';

You could have an index on name and city and another index on just city. Name is the better way to go but the optimizer chooses city. You could then defeat the city index by using and city||'' = 'NY' making it an expression and thus not choosable. Just a thought. You should run the query in sqlplus with tracing turned on to look at what indexes are being selected.

In article <svbhoth9g5gb03_at_corp.supernews.com>,   mr_oatmeal <mr_oatmeal_at_hotmail.com> wrote:
> Hello Reader,
>
> I am yet another rouge developer, let loose on developing and
 maintaining
> the database without a dba.
>
> Ok I was wondering if someone could give me some information to help
 me
> out...
>
> I have a web page that has five embedded sql statements. Each one
 hits
> the same table, the table has about 500,000 rows in it. (The table
 is
> already indexed.) Right now, the page takes too long to load and it
 is
> due to these queries. The query only selects five rows for each of
 the
> five categories. So, i was wondering if it would speed things up if I
> created a view to hold these 5 rows and just selecte from the view.
 Do
> you think it would be faster? Oh and how about maintaining the view
 if I
> go that route, is that a pain to update it everytime the table
 changes?
>
> Oh it helps, we are using Oracle 8i
>
> Thanks,
> mr_oatmeal
>
> --
> Posted via CNET Help.com
> http://www.help.com/
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 25 2000 - 15:20:40 CDT