Hello All,

I have a table having 30 fields and more than 50 million row,
in one of the reuirement i have to fetch the rows based on date range.
This query is taking more than half an our, please suggest,
how do i improve the performace ??


Thanks!!

-Pd

Hi,

Define index over date fields

Marcelo E.

Hello Marcelo,

Thank you.
It's a datamart and we have no contraints and no indexes on the same, hence the problem.
Please suggest, what do i do in this situation?

Thanks !!
PD

Hi,

Two possible solutions.

1. Redefine extent size for tha table, first unload the table , second drop table, create again the table with a new extent definition and load it.

2. Physical reorganitation, to do that, only for once you must create a cluster index, after the data is reallocated, drop the index.



Marcelo E.

Hello Marcelo,

Thank you.
I will opt for 1st option, as i need to ask my superiors for creating index.

Currently the extent allocation is done like this:

extent size 16 next size 16 lock mode page;

for all the tables.

What should i change it to ? shall i make it big 32 ?

Thanks!!
PD

To give an correct extent size, give me your table structure, but, I insist, use an index in that fields, evaluate how many time take: create an index before your select run and your select run, versus original time to select run.

Marcelo E.

Hello Marcelo,

Thank you.
This is the status info of the table

row size 167
no. of row 47150521
columns 37

This table is fragmented by expression.
Just wondering how much performace will be gained by index.

In where clause i am including, fragment and date.

Thanks!!
PD

Hi,

The estent size must be 512, to store 3066 rows aprox. , and the next size must be 256, when query read that rows, the time will be reduced.

The unload and load task, put all data together.

Don´t forget see onconfig DDS parameters to improve performance.

Marcelo E.

Given your extent size I'd guess that that large a table is fragmented amoung hundreds of extents. If you're going to unload and reload the table you need to make your initial extent size large enough to hold the entire table or an entire fragment (if fragmenting). Next extents should then be a percent based upon expected growth - usually 25% if the table is going to grow at all. The game, if you can play it, is to have the table and a years worth of growth in <10 extents.

Next question, you are fragmenting, why? With no index all you're accomplishing is to have multiple scans going that are walking through every record looking for your date criteria. Unless you've laid out your fragments on disk perfectly, you're just thrashing against each other.

I think you need to rethink how this table is structured vs. queried.

Thank you, Fred.

I was trying to unload the data and gives me problem of filesystem full.
Is there any way i can break the file and give path from different filesystem?

I never tried this with unload, but external table is there any way giving different files for unloading data ? May be i will try one of them.

As this is kind of data warehousing/datamart but the updates are done on daily basis. So, we have to be careful on next extent size as well.

But as this is test table for me i can change it to appropriate extent size for me. Daily updates are not done on this table.

Regards,
PD