Patent us20050065925 – query optimization in a multi-tenant database system – google patents

In general, one goal of a query optimizer is to minimize the amount of data that must be read from disk (e.g., because disk access may be a slow operation). Data recovery boston ma The optimizer therefore typically chooses tables or columns that are most “selective”—that is, will yield the fewest rows when the query condition is evaluated. Ipad 2 data recovery For instance, if a single query filters on two columns of a single table, and both columns are indexed, then the optimizer will use the index that has the highest number of distinct values because statistically for any given filter value a smaller number of rows are expected to be returned. Data recovery company If the optimizer knows that a certain column has a very high cardinality (number of distinct values) then the optimizer will choose to use an index on that column versus a similar index on a lower cardinality column. Data recovery network drive The optimizer assumes relatively even distribution of data and therefore reaches the conclusion that the high-cardinality column is likely to yield a smaller number of satisfying-rows for a given equality filter.

Several elements in the system shown in FIG. Data recovery yelp 1 include conventional, well-known elements that need not be explained in detail here. Database join table For example, each user system 12 could include a desktop personal computer, workstation, laptop, PDA, cell phone, or any WAP-enabled device or any other computing device capable of interfacing directly or indirectly to the Internet or other network connection. Google database User system 12 typically runs an HTTP client, e.g., a browsing program, such as Microsoft’s Internet Explorer™ browser, Netscape’s Navigator™ browser, Opera’s browser, or a WAP-enabled browser in the case of a cell phone, PDA or other wireless device, or the like, allowing a user (e.g., subscriber of a CRM system) of user system 12 to access, process and view information and pages available to it from MTS 16 over network 14. Database quizlet Each user system 12 also typically includes one or more user interface devices, such as a keyboard, a mouse, touch screen, pen or the like, for interacting with a graphical user interface (GUI) provided by the browser on a display (e.g., monitor screen, LCD display, etc.) in conjunction with pages, forms and other information provided by MTS 16 or other systems or servers. Database synonym As discussed above, the present invention is suitable for use with the Internet, which refers to a specific global internetwork of networks. Database management jobs However, it should be understood that other networks can be used instead of the Internet, such as an intranet, an extranet, a virtual private network (VPN), a non-TCP/IP based network, any LAN or WAN or the like.

According to one embodiment, each user system 12 and all of its components are operator configurable using applications, such as a browser, including computer code run using a central processing unit such as an Intel Pentium processor or the like. Iphone 6 data recovery free Similarly, MTS 16 (and additional instances of MTS’s, where more than one is present) and all of their components might be operator configurable using application(s) including computer code run using a central processing unit such as an Intel Pentium processor or the like, or multiple processor units.

Data recovery ubuntu Computer code for operating and configuring MTS 16 to intercommunicate and to process web pages and other data and media content as described herein is preferably downloaded and stored on a hard disk, but the entire program code, or portions thereof, may also be stored in any other volatile or non-volatile memory medium or device as is well known, such as a ROM or RAM, or provided on any media capable of storing program code, such as a compact disk (CD) medium, digital versatile disk (DVD) medium, a floppy disk, and the like. Database diagram tool Additionally, the entire program code, or portions thereof, may be transmitted and downloaded from a software source, e.g., over the Internet, or from another server, as is well known, or transmitted over any other conventional network connection as is well known (e.g., extranet, VPN, LAN, etc.) using any communication medium and protocols (e.g., TCP/IP, HTTP, HTTPS, Ethernet, etc.) as are well known. Database field types It will also be appreciated that computer code for implementing aspects of the present invention can be implemented in any programming language that can be executed on a server or server system such as, for example, in C, C+, HTML, Java, JavaScript, or any other scripting language, such as VBScript.

According to one embodiment, each MTS 16 is configured to provide web pages, forms, data and media content to user systems 12 to support the access by user systems 12 as tenants of MTS 16. Data recovery best buy As such, MTS 16 provides security mechanisms to keep each tenant’s data separate unless the data is shared. Raid 5 data recovery If more than one MTS is used, they may be located in close proximity to one another (e.g., in a server farm located in a single building or campus), or they may be distributed at locations remote from one another (e.g., one or more servers located in city A and one or more servers located in city B). Database administrator salary nyc As used herein, MTS’s could include one or more logically and/or physically connected servers distributed locally or across one or more geographic locations. Database p Additionally, the term “server” is meant to include a computer system, including processing hardware and process space(s), and an associated storage system and database application as is well known in the art. Cloud 9 database It should also be understood that “server system” and “server” are often used interchangeably herein. Data recovery on ssd Similarly, the databases described herein can be implemented as single databases, a distributed database, a collection of distributed databases, a database with redundant online or offline backups or other redundancies, etc., and might include a distributed database or storage network and associated processing intelligence.

In preferred aspects, each application server 100 is configured to handle requests for any user/organization. Database query tools Because it is desirable to be able to add and remove application servers from the server pool at any time for any reason, there is preferably no server affinity for a user and/or organization to a specific application server 100. Database 2000 In one embodiment, therefore, an interface system (not shown) implementing a load balancing function (e.g., an F5 Big-IP load balancer) is communicably coupled between the servers 100 and the user systems 12 to distribute requests to the servers 100. Database business rules In one aspect, the load balancer uses a least connections algorithm to route user requests to the servers 100. Iphone 5 data recovery Other examples of load balancing algorithms, such as are round robin and observed response time, also can be used. Database implementation For example, in certain aspects, three consecutive requests from the same user could hit three different servers, and three requests from different users could hit the same server. Raid 1 data recovery software In this manner, MTS 16 is multi-tenant, wherein the MTS 16 handles storage of different objects and data across disparate users and organizations.

As a specific example of the importance of table joins, consider the sharing feature in the service. Seagate data recovery The sharing feature allows a specific list of users to have access to privileged data, such as specific accounts or opportunities. Database report In one aspect, a Many-to-Many (MTM) physical table serves as the storage for this sharing information. Data recovery specialist The MTM table specifies that a user has access to a particular entity (e.g., account or opportunity) row. Data recovery iphone 6 When displaying a list of all rows that the current user can see (possibly with a filter on the entity rows, such as the name of the account or the dollar amount of the opportunity) the query optimizer must choose between accessing this MTM table from the user or the entity side of the relationship. Database building If the entity filter is highly selective (for instance, a particular account name such as “XYZ Corp”) it will generally make more sense to begin the query access path from this side. Data recovery top 10 If, however, the entity is not filtered selectively, but the current user has access to a small amount of data, then the query optimizer should access rows in the MTM table through the user side of this relationship.

In certain aspects, the present invention configures or tunes a query optimizer, such as the Oracle query optimizer, by supplying appropriate “hints.” For example, when SQL is generated programmatically by the MTS, the tenant-level statistics are consulted and a dynamic decision is made as to the syntax of the query. Database hosting The tenant-level statistics preferably mirror the underlying relational database statistics in many ways (for example, in one aspect they track the total number of distinct values for indexed columns) but the statistics are kept on a per-tenant basis (e.g., in tables in tenant database storage areas 112). Data recovery best Similarly for important application functionality, such as the sharing feature, the MTS tracks the approximate number of rows to which each user has access and stores such statistics (e.g., tables stored in user storage areas 114 of database 108). Data recovery program Then, when a filtered sharing query arrives, the dynamically generated SQL includes the appropriate hints and structure to force a query plan that is optimal.

To focus on how the “boss” vs “lower level employee” decision should drive an appropriate query plan, consider a query of the form: “Show me all accounts that I can see” in a private account sharing model. 7 data recovery 94fbr An example of a data model for sharing appears in FIG. Database languages 3 (middle table is sharing table, final table is the user/group “blowout” table which describes which users are contained in a group, or above a user in the role hierarchy (UG=User or Group)). Database ranking According to one aspect, for a “lower level employee” user it is typically most advantageous to join these tables starting from the right, filtering on users Id to form a temporary result of the rows that can be seen. Data recovery youtube Because the user can not see many rows, this will yield a relatively selective path. 911 database An example query follows:

In one embodiment, each update or insert into an entity table passes through an application server 100. Data recovery download Therefore as the data is being processed in Java counters are incremented and decremented for individual picklist values. Data recovery after factory reset Inserts increment a counter, while updates to a different value decrement a counter for the old value and increment a counter for the new value. Database schema design Since these statistics do not need to be exact, the statistics metadata is preferably not updated with each and every database insert or update (which might affect performance). Database uml Rather, in one aspect, an in-memory cache server (which already contains the metadata for valid picklist values) is augmented with the counters for these values, with the ability to update the database values periodically to persist the changes. Data recovery ntfs An example of such a cache server can be found in U.S. Database error patent application Ser. Database functions No. Top 10 data recovery 10/418,961, filed Apr. Database job titles 17, 2003, titled “Java Object Cache Server for Databases”, the contents of which are hereby incorporated by reference in its entirety.