Phaneesh .B

• Feb 8th, 2006

 

Stored Queries Increase the Speed of Access Queries. They are usually referred to as "Stored Queries", similar to SQL Server's "Stored Procedures", but less powerful. They are only SQL statements, no more, no less; they cannot do what Stored Procedures do on SQL Server

 

   There are several ways to Speed up the Query mainly

 

• By database join tables, union tables, uses indexes, choose access paths
• Aggregate tables
• Aggregate navigators/query redirectors: This is the technology that automatically directs a query to aggregated data if such data are available and appropriate for the query
• Partitioning: partitioning comes in many ways, shapes, and forms. At the very least, it is dividing one table into several tables usually based on the time the table data represent. Note that both tables and indexes may be partitioned.
• B-tree indexing: Adding numerous indexes is another common method for speeding up queries.
• Dimensional modeling: With certain database technologies, this modeling can reduce the amount of sort/merging that goes on when joining tables. And, some query tools may generate more efficient SQL if data are modeled dimensionally. Also, if you use surrogate keys in conjunction with dimension modeling, joins may be more efficient.
• Parallelizing query execution
• Bit mapped indexing: This technique can work well when a field takes on a low number of distinct values (i. e., low cardinality) and tends to be in WHERE clauses often.
• Defragmentation of table and index files
• Query accelerators: These help you generate more efficient SQL. Note that they are probably more helpful to those who report off of highly normalized databases.
• Query nannies: This is my term for technologies that warn (scold?) the user if he submits an inefficient query. Some of these provides hints about how to make the query more efficient and some (I have heard) actually try to fix up the queries.

dhivyavetriselvan

• Jul 26th, 2007

 

Select query can be made faster by using indexes.

prathap

• Sep 14th, 2012

 

We can use "parallel" in the query and in the 11g we can use dynamic sampling.