Fast retrival of SQL

[ashdiip]

Hi!

Q1. Can table and index for it kept in the same table space improve the speed of SQL retrival?

Q2. What are the factors besides indexing of a table, that speed up SQL retrival?


Thanks in advance
Ashish

[skhanal]

1. If you can, it's better to put tables and indexes in separate tablespaces (in separate disks)

2. It's a wide open question,
- Create selective indexes
- Analyze indexes regularly
- If data changes frequently, rebuild indexes frequently
- Allocate as much RAM as possible to DB_BUFFERS
- Use faster and more no of CPU
- Schedule batch jobs at off hours
- and the list goes on

[ashdiip]

Thanx buddy,

What does "scheduling of batch job at off hours" mean by?

[skhanal]

If you have large jobs like batch printing, reports summarizing data, loading data, then you should schedule them to run at night.

[ashdiip]

Thanx dear,

I do the same thing....shall appreciate anyother precious advise given.

I take further opportunity to ask you one more querry as:

Q2. Optimising database/SQL statement is the same as retriving faster SQL result?

Q2.If SQL statement is fired from the client machine, does it process at client end OR Server end ? and what do we call firing of an SQL as Client process / Server process?

Thanx once again.

[skhanal]

1. Optimizing database should eventually lead to faster query but it is not limited to do that. You may tune database for fault tolerance, faster backup/restore, ability to handle more concurrent connections etc.

2. The SQL statement always executes at the server, but client process has to handle the result set returned. You can process all your sql statement in the server and only return the final result to client, or you may fetch intermediate results in client and do further processing in client to get the final result. The first one is the preferred method.

[ashdiip]

Thank you very much for sparing your valuable time and replying my queris.

I've added you as My Buddy!

Thank you once again.

Best Regards.

[ashdiip]

I am tempted to ask you furthre query as follows, hope you would not mind replying my querry.


Datawarehousing

Apart from my regular activities of management of co.'s I.T. and I.T. related infrastructure, I am currently deputed as project Incharge for my co.'s ERP modification/enhancement, which is being developed under Oracle 8x/Power Builder 7.

This proejct is under Oracle 8x/Power Builder 7 set up.

My prime roles and responsibilities include as follows:

1) To understand the end user's requirement.
2) Preparation of SRS documentation
3) Preparation of Design documentation
4) Liasoning with S/W. developers
5) Testing/Implementation of the work assigned to the developers.
6) Training of end users on the customized S/W.

Does the above 1 to 6 points listed above can be termed as "DataWarehousing activities".

Thanks in advance

[skhanal]

They are activities in any application development, so it could be used in data wareshousing too. But this list does not have activities like extracting data, scrubbing/cleansing data, loading data, backup/recovery planning for datawarehouse, maintaining meta data, design of cubes, etc.

[ashdiip]

Thanx for everything !