Descending Indexes

Descending indexes are a special type of function-based index, in which index entries are sorted in descending order. Using the DESC clause does this. When creating an index, as in the example below:

create index ix_desc on phonebook2(zip desc);

There are a couple special considerations when using descending indexes: • Descending indexes cannot be used on reverse indexes

• The DESC option is ignored if used in the creation of bitmap indexes • The COMPATIBLE initialization parameter must be set to 8.1.0

Knowing when to Rebuild Indexes Where is the index now?

the index. This can be accomplished by using the ANALYZE INDEX VALIDATE STRUCTURE command. Normally, the ANALYZE INDEX command creates either computed or estimated statistics for the index that can be seen in the DBA_INDEXES view. This action may produce unintentional side effects, especially if the index has not previously been analyzed. The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.

Below is an example of ANALYZE INDEX VALIDATE STRUCTURE and sample output from INDEX_STATS: ANALYZE INDEX shopping_basket_pk VALIDATE STRUCTURE;

SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;

NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW DISTINCT_K USED_SPACE --- --- --- --- --- --- ---

SHOPPING_BASKET_PK 2 1 3 1 1 65

I have the information, now what?

There are two rules of thumb to help determine if the index needs to be rebuilt. If it is determined that the index needs to be rebuilt, this can easily be accomplished by the ALTER INDEX REBUILD command. Although not necessarily recommended, this command could be executed during normal operating hours. Rebuilding the index uses the existing index as a basis. The alternative is to drop and re-create the index. Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation.

First rule of thumb is if the index has height greater than four, rebuild the index. For most indexes, the height of the index will be quite low, i.e. one or two. I have seen an index on a 3 million-row table that had height three. An index with height greater than four may need to be rebuilt as this might indicate a skewed tree structure. This can lead to unnecessary database block reads of the index. It is helpful to know the data structure for the table and index. Most times, the index height should be two or less, but there are exceptions.

The second rule of thumb is that the deleted leaf rows should be less than 20% of the total number of leaf rows. An excessive number of deleted leaf rows indicates that a high number of deletes or updates have occurred to the index column(s). The index should be rebuilt to better balance the tree. The INDEX_STATS table can be queried to determine if there are excessive deleted leaf rows in relation to the total number of leaf rows. Let’s look at an example:

ANALYZE INDEX item_basket_pk VALIDATE STRUCTURE; SELECT name,height,lf_rows,del_lf_rows,

(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO --- --- --- --- --- ITEM_BASKET_PK 1 235 74 31.4893617

In this example, the ratio of deleted leaf rows to total leaf rows is clearly above 20%. This is a good candidate for rebuilding. Let’s rebuild the index and examine the results.

ALTER INDEX item_basket_pk REBUILD;

ANALYZE INDEX item_basket_pk VALIDATE STRUCTURE; SELECT name,height,lf_rows,del_lf_rows,

FROM INDEX_STATS;

NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO --- --- --- --- --- ITEM_BASKET_PK 1 161 0 0

The index is rebuilt and validated once again. Examining the INDEX_STATS table shows that the 74 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 235 to 161, which is a difference of 74 leaf rows. This index should provide better performance for the application.

Script to find indexes to rebuild

Below is a sample script that can be run to determine which indexes need to be rebuilt. For those indexes that need to be rebuilt, the ALTER INDEX REBUILD command is dynamically generated as output. The user can tailor the height and percentage of deleted leaf rows by altering the vMaxHeight and vMaxDel

variables. The output of this script can be spooled to a file. This file can then be run to rebuild the indexes.

-- validate_idx.sql --

-- This script will check indexes to find candidates for rebuilding. -- Run this script in SQL*Plus as a user with SELECT ANY TABLE -- privileges.

--

-- This script can be used and modified without permission. Run this -- script at your own risk! The script author is not responsible for -- any problems that may arise from running this script.

set serveroutput on size 100000 DECLARE

vOwner dba_indexes.owner%TYPE; /* Index Owner */ vIdxName dba_indexes.index_name%TYPE; /* Index Name */ vAnalyze VARCHAR2(100); /* String of Analyze Stmt */ vCursor NUMBER; /* DBMS_SQL cursor */ vNumRows INTEGER; /* DBMS_SQL return rows */ vHeight index_stats.height%TYPE; /* Height of index tree */ vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */ vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */ vDLfPerc NUMBER; /* Del lf Percentage */

vMaxHeight NUMBER; /* Max tree height */ vMaxDel NUMBER; /* Max del lf percentage */ CURSOR cGetIdx IS SELECT owner,index_name

FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%'; BEGIN

/* Define maximums. This section can be customized. */ vMaxHeight := 3;

vMaxDel := 20;

/* For every index, validate structure */ OPEN cGetIdx;

LOOP

FETCH cGetIdx INTO vOwner,vIdxName; EXIT WHEN cGetIdx%NOTFOUND; /* Open DBMS_SQL cursor */

vCursor := DBMS_SQL.OPEN_CURSOR;

/* Set up dynamic string to validate structure */

vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE'; DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);

vNumRows := DBMS_SQL.EXECUTE(vCursor); /* Close DBMS_SQL cursor */

DBMS_SQL.CLOSE_CURSOR(vCursor); /* Does index need rebuilding? */ /* If so, then generate command */

SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows FROM INDEX_STATS;

IF vDLfRows = 0 THEN /* handle case where div by zero */ vDLfPerc := 0;

ELSE

vDLfPerc := (vDLfRows / vLfRows) * 100; END IF;

IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN

DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;'); END IF; END LOOP; CLOSE cGetIdx; END; / Database Comparision Table of Contents 1.Introduction 2.Operational Concerns a.Scalability b.Platform Availability

c.Networking & Internet Readiness 3.Vendor Related Issues

b.Support and Maintenance 4.User Considerations

a.DBA Concerns b.Programmability 5.Conclusion

Appendix A - System Requirements for Microsoft SQL Server 7.0 Appendix B - Maximum Sizes and Numbers of SQL Server 7.0

Appendix C - Scorecard of Microsoft’s SQL Server & Oracle’s 8i

Appendix D - Summary of Features of Microsoft’s SQL Server & Oracle’s 8i Appendix E - Oracle 8 and Oracle 8i Standard Edition Platform Availability

SQL Server 7.0 Introduction

SQL Server 7.0 is designed to scale from laptop databases and small business servers all the way up to terabyte-size databases.SQL Server 7.0 is designed to operate on Microsoft’s Windows 95, 98 and NT operating systems.However, the Windows 95 & 98 versions of SQL Server 7.0 only support desktop, laptop and small workgroup applications.SQL Server 7.0 for Windows NT Workstation has been developed for applications that involve a large numbers of users and transactions.

Microsoft SQL Server had 30% of the Windows NT database market in 1998, while Oracle had 46.1% of the Windows NT market, which was a 55% growth in 1998.This statistic must be understood in the

context that it applies only to the Windows NT platform, since that is the only platform that both Microsoft and Oracle have in common.

Although, Microsoft has taken a big step forward in enterprise capability with the SQL Server 7.0, it seems like their product is more appropriate for departmental and small to mid-sized companies.“Rewritten for ease of use, Microsoft’s SQL Server 7.0 is far and away the best choice for smaller organizations or branch offices that need a full-featured relational database.Larger organizations will find SQL Server a better performer than ever before, although competing databases including Oracle 8 and DB2 continue to provide better programmability and scalability.”[ii]

In speaking with a few experienced database administrators and programmers, SQL Server really is not known as a competitor with the “big boys” of the relational database software market.The well-known database software products include Oracle, IBM’s DB2, Sybase and Informix.These companies remain leaders in database technology at the enterprise level.

Oracle 8i Introduction

Oracle 8i is a database product that is built upon a mature Oracle 8 product, but also brings increased capabilities to develop and integrate with Internet applications.Oracle’s databases have been developed and proven to handle the largest of enterprise databases.But Oracle also targets smaller, mid-tier companies who find it necessary to have 24 hours by 7 days availability due to increasing Internet business needs.Oracle’s 8i databases are available on a wide variety of platforms.Dell, IBM, Linux, Sun, Fujitsu and Unisys are a few of the 21 listed on Oracle’s web site.

Oracle’s products are definitely not the cheapest on the market. If an evaluation of the application necessitates a high need for reliability, scalability, security and performance then Oracle should be

considered. Oracle is the world’s leading supplier of software for information management, holding 27% of the database market share across all platforms.Oracle is the undisputed database leader on UNIX

platforms, commanding 60.9% of the market share according to Dataquest.[iii]_,[iv] 2.Operational Concerns

a. Scalability

Scalability in the context of database software is defined as the software’s ability to continue to perform at a similar level with a larger amount of data and a growing number of users and transactions.

Amount of Data

Both SQL Server 7.0 and Oracle 8i are designed to be client-server database products that can take advantage of distributed database architecture.

A distributed database is a network of databases managed by multiple database servers that appears to a user as a single database. This means the database could be distributed across several disks and servers with multiple processors.The data of all databases in the distributed databases can be simultaneously accessed and modified. The database architecture on the server will dictate how fast the transaction response time is.The speed of transactions can vary greatly based on the database design as well as server hardware configurations, including RAM, the number and speed of the CPUs.

SQL Server 7.0 can grow up to 1,048,516 terra-bytes.Microsoft uses SMP (systems with 4 processors) technology to distribute databases. Other maximum sizes and numbers can be referenced in Appendix C, which outlines other technical specifications of SQL Server 7.0.

Oracle 8i is scalable up to hundreds of terabytes to manage very large databases (VLDB).Oracle takes advantage of distributed processing and storage capabilities through architectural features that use more than one processor to divide the processing for a set of related jobs.This distributed architecture is a good example of the expression “the sum of the parts is greater than the whole”, because as individual

processors work on a subset of related tasks, performance of the whole system is improved.

Number of Simultaneous Users

Theoretically, there is no limit to the number of users that can access either the Oracle 8i or SQL Server 7.0 database servers at one time, given infinite processors and infinite speed.In practical terms, there is a limit, but it should not pose any real issues to be concerned with in terms of concurrent data.One

consideration is that SQL Servers will require ODBC software to connect with clients that are not PC based.This will require some overhead, but should be pretty negligible.

In a “Score Card” published by ZDNet[v]_{, which is fully documented in Appendix D, the following ratings} were published.These ratings really demonstrate the equality of SQL Server and Oracle for these performance criteria.

PC Microsoft SQL Server

7.0 Oracle8i Standard Edition

Server Engine Excellent Good

Join and index selection Excellent Excellent

Degree of concurrency Good Excellent

Database Design Good Excellent

Distributed transactions Excellent Excellent

These issues of are fundamental topics when performance is being discussed. b.Platform Availability

As discussed in the Introduction, SQL Server 7.0 is designed to operate on Microsoft’s Windows 95, 98 and NT 4.0 operating systems.However, the Windows 95 & 98 versions of SQL Server 7.0 will only support desktop, laptop and small workgroup applications and requires an Intel platform.

SQL Server 7.0 for Windows NT Workstation has been developed for applications that involve a large numbers of users and transactions and is limited to Intel or Alpha platforms.

This limitation could cause hurdles for large corporations in terms of performance that can be expected.Brian McCarthy, CEO of Insurance Holdings said “Microsoft said we’d need an all-Microsoft application if we wanted full scalability, but who’s going to rebuild the whole system?”[vi]

It’s especially important to note that although other platforms can be used for clients to access the SQL Server 7.0, a third party ODBC software must be used.ODBC is an interface that allows for data to be accessed in relational databases, independent of the database vendor.

Oracle 8i is supported by a large number of hardware manufacturers, as well as several operating systems.There are currently 21 hardware vendors listed on Oracle’s website, with at least 6 operating systems, counting UNIX as one although the flavors may vary by manufacturer.Reference Appendix F for details on some of the platforms and operating systems that Oracle runs on.Further details can be found on specific platforms at Oracle’s dedicated website:www.platforms.oracle.com.

Networking & Internet Readiness

The need for a database to be Internet ready is quickly becoming a necessity in today’s rapidly growing web based world. At every turn in the software industry, the topic of e-business and e- commerce is a top issue for companies wanting to compete in the future. Therefore, the need for database software to support and enhance Internet application development is a must.

SQL Server 7.0 still lags behind in its ability to support multimedia data support and in

programmability, which are necessary for many Internet applications. Third party software will have to be used to store special images, sound, video or geographic data support. SQL Server 7.0 doesn’t support Java, which is an industry standard for developing network applications.

Oracle 8i is the best product for companies wanting to move their database applications to the Web. Oracle leads the market in handling of multimedia objects. Multimedia support is

multimedia items such as pictures or video clips of items for sale. Oracle uses a product call JServer, which brings Java and relational databases together. It allows for controlling the database through Java and supports the creation of JavaBeans. JavaBeans are the basic building blocks for Java-based Internet applications, and are (or will be) supported by just about every high-end Internet application server on the market.

In the ZDNet Scorecard (Appendix D), SQL Server 7.0 and Oracle 8i were rated as follows, in respect to their internet readiness features:

Microsoft SQL Server 7.0 Oracle8i Standard Edition

Multimedia Data Handling

Poor Excellent

Web connectivity Poor Excellent

Support for sound,

media, video, images Poor Excellent

Full text search Good Excellent

3. Vendor Related Issues