Database Partitioning?

Good presentation on Database partitioning. Being a developer that was a new learning for me. I will post here a breif about what exactly this partitioning is.

A partition is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons.

A popular and favourable application of partitioning is in a distributed database management system. Each partition may be spread over multiple nodes, and users at the node can perform local transactions on the partition. This increases performance for sites that have regular transactions involving certain views of data, whilst maintaining availability and security.

The partitioning can be done by either building separate smaller databases (each with its own tables, indices, and transaction logs), or by splitting selected elements, for example just one table.

Horizontal partitioning (also see shard) involves putting different rows into different tables. Perhaps customers with ZIP codes less than 50000 are stored in CustomersEast, while customers with ZIP codes greater than or equal to 50000 are stored in CustomersWest. The two partition tables are then CustomersEast and CustomersWest, while a view with a union might be created over both of them to provide a complete view of all customers.

Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns. Normalization also involves this splitting of columns across tables, but vertical partitioning goes beyond that and partitions columns even when already normalized. Different physical storage might be used to realize vertical partitioning as well; storing infrequently used or very wide columns on a different device, for example, is a method of vertical partitioning. Done explicitly or implicitly, this type of partitioning is called "row splitting" (the row is split by its columns). A common form of vertical partitioning is to split (slow to find) dynamic data from (fast to find) static data in a table where the dynamic data is not used as often as the static. Creating a view across the two newly created tables restores the original table with a performance penalty, however performance will increase when accessing the static data e.g. for statistical analysis.

Partitioning criteria

Current high end relational database management systems provide for different criteria to split the database. They take a partitioning key and assign a partition based on certain criteria. Common criteria are:

Range partitioning 
Selects a partition by determining if the partitioning key is inside a certain range. An example could be a partition for all rows where the column zipcode has a value between 70000 and 79999.

List partitioning 
A partition is assigned a list of values. If the partitioning key has one of these values, the partition is chosen. For example all rows where the column Country is either Iceland, Norway, Sweden, Finland or Denmark could build a partition for the Nordic countries.

Hash partitioning 
The value of a hash function determines membership in a partition. Assuming there are four partitions, the hash function could return a value from 0 to 3.
Composite partitioning allows for certain combinations of the above partitioning schemes, by for example first applying a range partitioning and then a hash partitioning. Consistent hashing could be considered a composite of hash and list partitioning where the hash reduces the key space to a size that can be listed.

Visit http://www.oracle.com/us/products/database/options/partitioning/index.htm to know more about Oracle partitioning.

For more insight simply Google the term. The ultimate source :)

%UpdateStats in peoplecode

%updateStats is used to update Index table (database table) which stores key row (Like ROWID, INDEXFIELD DATA) information. Once you execute above statement your table & system table are sync. By above command index in query works properly and fetches data faster.

Crystal reports and Database Server

Crtstal Reports

Crystal Reports is a business intelligence application used to design and generate reports from a wide range of data sources. Several other applications, such as Microsoft Visual Studio, bundle an OEM version of Crystal Reports as a general purpose reporting tool. Crystal Reports became the defacto report writer when Microsoft released it with Visual Basic.

Users install Crystal Reports on a computer and use it to select specific rows and columns from a table of compatible data (see "Supported data sources" below). Users can then arrange the data on the report in the format needed.

Once the report layout is complete it is saved as a file with the extension RPT. A report can be rerun anytime by reopening the RPT file and 'refreshing' the data. If the source data has been updated then the refreshed report will reflect those updates.

The report can then be previewed on the screen, printed onto paper or exported to one of several different file formats such as PDF, Excel, text or CSV.

Report formats can vary from a simple column of values to layouts featuring pie charts, bar charts, cross-tab summary tables and nested subreports. Crystal Reports is designed for "presentation quality" reports so there are many options for enhanced formatting.

The users who design the reports can run them as needed from within the report designer. When running reports directly within the report designer the user has the ability to change any feature of the report. The user can also create variations of the report by saving the modified RPT file under another name.

It is also possible to run a Crystal Report without using the full Crystal Reports designer software. These alternate methods for running reports include locally installed viewers, schedulers, and report distribution tools.

These are typically third-party software programs (independent of Business Objects) that allow you to open, refresh, preview, print and export an RPT file. In 2007 Business Objects released their own viewer, Crystal Reports Viewer XI, but unlike the independent viewers it does not allow the user to refresh the report, but only to view static data saved in the RPT file.

Some of the independent viewers add other capabilities such as allowing the user to schedule a report to run automatically at certain times. Still others allow reports to be burst and/or distributed via Email.

Crystal reports is bundled with a set of ActiveX controls that, when embedded in a simple GUI, can provide an alternative user interface. These same controls allow reports to be deconstructed into their base objects. This, in turn, allows the same reports to be generated at run time, customizing them as necessary. Database Server

The PeopleSoft database is the repository for all information managed by PeopleSoft applications. Not only is application data stored in the database, but PeopleSoft metadata is also maintained in the database.

PeopleSoft Application Designer enables you to define and maintain this metadata that the system uses to drive the run-time architecture. The application server executes business logic based on the PeopleSoft metadata.

At execution time, the application server fetches the most recent application object definitions from the metadata repository, compiles and caches the application object into memory, and executes the business rules based on the definition.

PeopleSoft Enterprise Portal 8.x currently runs on the following relational database management system (RDBMS) products:

*Microsoft SQL Server for Windows NT/2000.
*Oracle for Solaris, HP-UX, AIX, Tru64 Unix, and Windows NT/2000.
*DB2/UDB for OS/390, AIX, Solaris, HP-UX, and Windows NT.

Note. PeopleSoft Enterprise Portal 8.x can retrieve content from PeopleSoft applications that are running on other RDBMS products, such as Sybase or Informix.

In general, PeopleSoft Enterprise Portal 8.x can retrieve content from any RDBMS-based application as long as the content that is being retrieved is URL-accessible and is HTML-based.