Re: Indexing Help

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.

--
Billy