Answer / kumar srinivasalu

BY USING HINTS,
HASH JOINS,
PARTITION TABLES AND INDEXS,
RESERVE KEY INDEX,
BIT MAP INDEX,
COST BASED OPTIMIZATION,
Nested loops,full table scans,query rewrite with
materialisation views
BASED ON THIS WE OPTIMIZE THE SQL QUERIES.

Answer / abhijit

Use query analyzer to see the execution path and find
bottlenecks such as full table scans for tables with huge
number of rows.

Make sure you use the indexed columns in where clause, this
can lead to a phenomenal jump in query execution speed.

Answer / hutashan

Avoid implicit datatype conversion
Avoid using function on an indexed column
Avoid comparing column value with NULL
Outer Join
Use OR carefully

Always use NOT EXISTS instead of NOT IN


Use UNION ALL whenever possible (no implicit sorts)
UNION will sort the result set for each SELECT before
combining the final result set
Remove DISTINCT keyword from SELECT if UNION is used

Answer / asr

Avoid the sub queries.
Where condition should be based on primary key and index,
use primary column for join.
Try to avoid more tables.
Use temp tabels.
Create procedure.

Answer / shail

Use function based index.
Ex: you have a date column and a simple index on it.
maximum queries referring to this column of this table
are like trunc(date_column). Then the index on this column
will be ignored. For this you can have function based index
like trunc(date_column) on this column.
This will speed up your sqls.

Answer / rupa

Most often, Databases have bad designs and are not
normalized. This can greatly affect the speed of your
Database. As a general case, learn the 3 Normal Forms and
apply them at all times. The normal forms above 3rd Normal
Form are often called de-normalization forms but what this
really means is that they break some rules to make the
Database faster.

What I suggest is to stick to the 3rd normal form except if
you are a DBA (which means you know subsequent forms and
know what you're doing). Normalization after the 3rd NF is
often done at a later time, not during design.

for more information go Through below link
http://www.serverwatch.com/tutorials/article.php/2175621

What is offset in sql query?

0 Answers  

Is it mandatory for the primary key to be given a value when a new record is inserted?

0 Answers  

What is sql comments?

0 Answers  

Can you load data into multiple tables at once? : aql loader

0 Answers  

how to create a table index in mysql? : Sql dba

0 Answers  

What are the different schemas objects that can be created using pl/sql?

0 Answers  

Does truncate table reset auto increment?

0 Answers  

How do you optimize a stored procedure query?

0 Answers  

What is a 'instead of trigger'?

3 Answers   Eenadu, TCS,

function can return value ,procedure also return value through out parameter then what is the difference?

3 Answers   3i Infotech,

can i create trigger on synonym is it possible or not please help me

4 Answers   CGI,

Write a query to find the employees from EMP table those who have joined in Monday. (there is a column as hiredate which is a date column i.e values are like 03-DEC-81)

5 Answers