Re: Indexing Help

Thanks, Billy and Brian.

I ended up buy a few Performance Tuning books and hitting those pretty hard. I still need to understand some of the more of the basics, but I believe I have solved my indexing problem using those books and Oracle Expert.

I really appreciate your insight and help.

Happy New Year to you both.

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0312230227.e548eff_at_posting.google.com>...
> cxy2002um_at_yahoo.com (Anon) wrote :
>
> > There are no index on this table, however this table is partitioned
> > into A_TYPE in (3,4,5,16) all other A_TYPES go into another partition.
> > This was thought to help query performance.
>
> Yeah.. and maybe if we set fire to the Oracle manuals that will scare
> the Oracle software in working faster... ;-)
>
> Query performance is not something that needs a pentagram and spell
> books. I find the concept of "we thought this may help query
> performance" scary. Either you know. Or you don't.
>
> If you don't, then you fix *that* instead of guessing. IMO.
>
> Some thoughts on partitioning A_TYPE. How many values are there? What
> is the row distribution like per distinct A_TYPE value? Why are there
> no local indexes? In absense of any indexing currently, why is PQ
> (parallel query) not being used?
>
> Most importantly, how does the execution plan look like for that SQL?
>
> There are numerous way to address performance issues. But before you
> can get to that, you need to know *what* is happening and *why* there
> are performance issues.
>
> Else it is just shooting in the dark.
>
> > Thanks for the comments on the poor design. I will read. The source
> > of this table is not of my design (thank god). This table will be
> > truncated every night and rebuilt with the same about of roles each
> > time. That query mentioned about is fetching records for batch
> > processing.
>
> If (for whatever reason) the SUBSTR has to be used, a function index
> can be considered. However, I would rather address it via a proper
> design. SUBSTR'ing a column to retrieve other "intelligence" embedded
> in that column value is just plain wrong in my view.
>
> Sorry if I sound a bit harsh - but I see this type of thing too often
> for my liking with the developers I deal with. They are not stupid.
> But they are ignorant about basic RDBMS processing and then shoot from
> the hip. And the only thing that you hit like that is your own foot.
>
> Performance tuning is not black magic - it only requires logic and a
> basic understanding of how a database server goes about retrieving
> data (be that via indexes, partitions, full table scans, etc.)
>
> My suggestion is to look at the Oracle SQL command called EXPLAIN
> PLAN. Run that on this SQL statement and then *know* what Oracle is
> doing with the SQL. Knowledge is after all power.
Received on Wed Dec 31 2003 - 11:50:03 CST