problem statement: Optimizing database query performance as also underlying storage efficiency refer to the document "opportunity_DWH_BI_product_orchrestrization.txt" understand and efficiently map or exploit the concept of partitioned query in the below context. Illustrative example: Refer to as described in 847) as the size of the data grows in a table ..irrespective of the presence of the of index's ....amount of time required or query performance degrades ....reason being I/O of the underlying disk's depending on nature and type of the database (properitery those that use raw disk space) and those that use filesystem. Ex: Take the case scenario of the Tax payers or filer's, supposed that they are stored in a table TAXPAY ...as the years pass by the amount of info stored in the table gets accrued,resulting growth of the size ...as ex Opportunity :a -------------- Ideally all objects with in a database are confined to a specific schema, and cannot extend beyond a tablespace or container. Given that objects can be relocated from a schema or tablespace ... a innovative way of purging the data is to , move non active or any data that is not current to a different tablespace. Better way to handle the scenario is to enable objects to span or extend beyond a tablespace, a table can be split and located across tablespaces (well objects or tables share the same name across the tablespaces) ...in the cataloges or reference tables setting a tablespace as active ...were in all queries are directed over active tablespace .... Provide interface's via SQL to query data in non active tablespace's ...simply letting the where , group by clause's decide where to look for the data ...designing a means of organizing and appropriately cataloguing the same ...to fix the above problem. unix cut,paste, awk,sed utilities. given that most operating systems support virtual filesystems, in much the manner in which a filesystem reorganization or instance scandisk works, using a highly level query(at database level) ....identify the set of pages in the underlying disk that are effected ....move these set of pages to different disk, given the manner in which virtual filesystems work ...re-draw or rely the filesystem map .... Given that some databases use raw disk space and some use filesystems ....embedded the above feature appropriately as part of the database or at the virtual filesystem management level ...given the complexity ...accomplishing it in a automated manner would be the ideal proposition (well data to be purged or relocated [to a contigious disk-space on different lun or disk]...to be identified using a query though ...and using automated tools), thus keeping the tablespace virtually in tact ...and extents contigious ...which requires minimal of modifications ...in database interface levels (SQL or schema or catalogues] ...this way partitioning of the table data is thus feasible ...ability to set a specific partition as active , lets are queries to redirected over the active partition (ideally speaking data could as well potentially be located across tablespaces) ....alternatively given the scope ...since it is possible to specify default index's for queries ...ability to restrict or specify the range (criteria ...ability or means to define current data ...say in the given example above data pertaining to current financial year) ...when a index is being created ....thus limit ...what is being queried [to a specifci data set ...within a table ...though logically the table may consist of the wholeo of the data] ...ideal most probable fix. summary objective: ability to span objects across tablespaces , catalogue datasets ...ability to query the datasets as a whole or independently ....achive the various objectives as described above ...the datasets share the same symentics (DDL ...constraints ...etc in common ...except that they are logically stored as described above) Much like who alias or views to a table work ...viz. based on the view defition, given that in the above data-sets are organized or stored logically based on a specific criteria ...for instance all records of tax payers of a year 'YXXX' ....could potentially be addressed with same constraints as on the table 'TAXPAY', but using a independent ...or alias name say 'YXXX' ....some of the objectives of the performance could / should be percieved straitway. ability to do a DDL on a table using partitioned logic ...in a innovative way ...letting the system to choose or organize or automatically create and manage partition's, The DBA or the user define the partioning logic using logical operator's in programming context and a construct similar on the line's of the analogical scenario being 'where clause' or the 'break on' ...eg: all record before the period '1989' i.e. either using absolute figure or a logical constant ...potentialy using substitution variables and relative variable (here period) on which a partition needs to be created. eg: create a partition for all data aggregatable for every one year or 10year period. the above proposed solution's should be in sync with problem as described in 847), a understanding of Raid [0] concepts would be advantagous to understand the above ...viz. a virtual file system a top raid. Also contrary to how index's work ...above explores more than partially on how the data is stored (physical organization of records in the context of a table ...to achieve the objectives specified or the problem statement. Also when database's support functionalities like re-organization of objects within ....above principles , by providing a means configure through a interface ...enhance the performance to a great extent. The above assumes sufficient physical disk space ...is available , concepts of RAID,Virtual filesystems are embedded as part of the database or OS based on the nature of database(those that use raw disk space, those that use filesystems) ----------------------------------------------------------------------------------------------------------------------------- Opportunity B:) Most DBMS append records to a table ....along with timestamp, when a index (sort) is created on a attribute(colum) of the table ...it points to the record pointer. An innovative means of auto index or identity creation to a record ...would be , given the above every record or row being an offset from the beinging (start address) of the table record number * size of the record = physical address at which the tuple begins .....ability to encode/include the viz. the physical address at which a tuple is located as part of index building or hashing mechanisms .....greatly improves ...data access mechanisms. Given ability to define a active-data-set on the table or object or cube-view data ....viz. the ability to anchor a record-pointer in a index to table or cube-view data ...such that the record-pointer in the index point's to or is anchored at physical-address at which the active-data-set begin's in the data-organization on the disk-medium ....ability to gain of query-execution-plan or data-access-plan. Note: The above problem statement having been encountered in various scenarios and detailed in various 'Proof of concepts' as mentioned in http://uk.geocities.com/ravivenkatus/projects.pdf http://ravishankarkv.tripod.com/projects.pdf ....apply appropriate 'use-case' modeling, rationalize and arrive at a workable and feasible solution both commercially and techinically viable. ------------------------------------------------------------------------------------------------------------------------------ ProblemStatement : Understanding Row level, table level.. lock's .... Implications on 2-tier architechture Implications on 3-tier (Internet) architechture In some envisible scenario's where appending records into a table ...database ...for lock's at various levels. ...measuring or countering the same on performance of the applications or systems. viz. No of transaction that can be percieved or that can be clocked at a unit of measure of degree (say a 30sec) For instance appending or inserting records into a envisible TaxPay (borrowing from allow illustration) and the various architectures as described above ...say online filing of taxes ....It makes or would be obivious to generate a thread level(context) uniqueid and accept or record data into a temporary ....table ...tempdatabase which can be later merged into the main table ...which can generate a uniqueid in the context of table Note: In the above context ...performance hindrance's resulting out of primary or unique key's at table level. ....borrowing from the above it makes sense to load over multiple thread (defined at application level), and also spread I/o to sperate spindle's or physical hardisk's or such that each application thread use's a independt physical lun to gain over I/o through put. Note: The above problem statement having been encountered in various scenarios and detailed in various 'Proof of concepts' as mentioned in http://uk.geocities.com/ravivenkatus/projects.pdf http://ravishankarkv.tripod.com/projects.pdf ....apply appropriate 'use-case' modeling, rationalize and arrive at a workable and feasible solution both commercially and techinically viable. ------------------------------------------------------------------------------------------------------------------------------ In the above discussion ...any other medium's of storage like flash memory or storage cards kind design used in mobile device's extendible for massage storage can be ideally be ruled out ...because of the flexiblility of the disk as storage medium that lets to vary the buffer size or I/O block ....transalting to storage address-map ...filesystem etc. For online systems as described above ...which attract massive traffic ..... a ideal percievable or envisible system would be to do client side validation's of few attributes like tax payer's unique-id ...etc viz. which are readonly attributes from a database.... ....upload data (ideally XML-DTD format , or other format that may keep or other parsable data format that makes data readily assimlatable into the database structure) into a file-servers (Example ...email) ...which can be aggre-gated into a database .....a process that works offline ....acknowledge's to the end-user ....while some constraints of using a relational database viz. locks can be over-come still ....a I/O through put ...with efficient using raid or as discribed above need to be taken into account viz. spread I/o to sperate spindle's or physical hardisk's or such that each application thread use's a independt physical lun to gain over I/o through put. or much better ....refer else where as described in the document http://uk.geocities.com/ravivenkatus/projects.pdf ....viz. a database engine sitting atop a filesystem. --------------------------------------------------------------------------------------------------------------------------------- As has been referred above and the document http://uk.geocities.com/ravivenkatus/projects.pdf electronic processing systems ...as explained above viz. online filing or tax returns or other applications ...easy way to handle is email as an interface to upload or transmit data ....given that email-server's are stable ....on the server side the quequing of the incomming request's to server's are taken care of ....since incomming requests are already serialized (thread schronized) .....server side applications ...a proc processor or a perl or java based program can parse the documents or data mailed aspart of the attachments ....interact with the database (some the above constraints are over come.)!! -------------------------------------------------------------------------------------------------------------------------------- Given that routing of data is as explained over email ...server's take care of 'NO' of retries required to above to relay data to server.... in the context of a web-based systems ...the client-side programs (for instance xml-dtd-rss) ....can use a aschronous client side ....curosor ...data-sets ....that can save or sychronize data to (i.e. upload data) ....as an when it is able to make connection to the server. --------------------------------------------------------------------------------------------------------------------------------- opportunity : further to as published or as envisage elsewhere by this individual. understanding and interpreting from table level and row-level lock's. ability to use the update clause in conjuction with the where clause and potentially usage of join clause's to arrive at the right row to be update with precision.