Query and index modeling in 10g

  • From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Sep 2006 12:16:23 +0530

Hi



We are trying to do a Query and index modeling exercise by attempting to
MAP/CLASSIFY a majority of our Product's SQL Queries to some
corresponding baseline / Standard set of SQL Queries for SMALL, MEDIUM,
BIG & Very BIG Tables using different Types of Indexes, as mentioned
below:-



Our Product is a Hybrid Banking Application.



Currently mapping has been done TO baseline / Standard Queries which use
a Single Table only:-



*          Baseline / Standard SQLs containing SELECT, UPDATE, DELETE of
the following types have been mapped:-

-         Point (returns 1 row)

-         Multipoint (returns more than 1 row)

-         Range scan (where Clause has a range specification)

-         Prefix and suffix

-         Conjunctive point (where Clause using all the key fields of
the  unique index, up to 3 fields)

-         Extremal



*          INSERT



*          Additionally

-         Update using ROWID

-         Select using ROWNUM



Types of Indexes Used:-

      *          Normal index

*          Index organized table

*          B+ tree cluster

*          Hash cluster



Qs 1 Is our approach, of Mapping/Classifying, itself appropriate or NOT?



Qs. 2 How do we extend the approach to define "the standard SQLs set"
involving a JOIN between multiple Tables (i.e. 2, 3 Tables)



Qs. 3 How do we extend this to Sub-queries, In-line queries etc.?



Qs. 4 Any Docs, Links, Books on similar work done before?



Thanks indeed





**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely 
for the use of the addressee(s). If you are not the intended recipient, please 
notify the sender by e-mail and delete the original message. Further, you are 
not to copy, disclose, or distribute this e-mail or its contents to any other 
person and any such actions are unlawful. This e-mail may contain viruses. 
Infosys has taken every reasonable precaution to minimize this risk, but is not 
liable for any damage you may sustain as a result of any virus in this e-mail. 
You should carry out your own virus checks before opening the e-mail or 
attachment. Infosys reserves the right to monitor and review the content of all 
messages sent to or from this e-mail address. Messages sent to or from this 
e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Other related posts: