Oracle programming guru rumor: The story of Bitmap Indexing

If you are familiar with Oracle databases, I think you will not be familiar with ThomasKyte. The asktom.oracle.com website hosted by Tomas has been well-known in the Oracle field for decades and is far from Lucky. Recently in the library by the Oracle top master prepared by the "ExpertOracleDatabaseArchitecture-9iand10gprogrammingTechniquesandSolutions", flip, really good reputation without virtual, although can not say word Pearl, however, the author's understanding and practices of Oracle architecture have indeed reached a superb realm. If you have time and interest, we strongly recommend that you read this book. The biggest feature of this book is that the language is lively, reasonable, and almost every explanation of a principle. The author provides specific examples to make people read it unsightly.

In addition, I admire Thomas for his humble attitude. Thomas has been working on Oracle databases for 16 years and has been involved in the development of earlier Oracle versions, but he is still modestly speaking, he learns new things from Oracle documents every day.

Here are some highlights of this book for your appreciation.

BitmapIndex

One day, a group of Java developers found Mr. Tom and said that their new system had been launched, but its performance was poor. They asked Mr. Tom if he could help them find out what the problem was. They told Tom that their system adopts a typical three-tier architecture of JSP + EJB + Oracle, where the SQL statements in EJB are generated by third-party tools. As soon as he heard about EJB, Tom knew that the system could not use the SQL code Tracking Method for performance optimization. As a result, Comrade Tom told these eager Java developers that the problem with your system must be between the browser and the database, but what is the problem? I need to check your database.

As a result, Tom remotely connected to their test database and checked several dynamic performance views (V $ LOCK and V $ SQL), and finally found the problem. Comrade Tom found that a BitmapIndex in their database is the most suspicious. This index is built on a PROCESS_FLAG field, and the PROCESS_FLAG field indicates whether the record has been processed, there may be only two values, one being unprocessed (N) and the other being processed (Y ). When a record is inserted into the database for the first time, the value of this field is N, but after other processes read and process the unprocessed records (the value is N, the value of this field is updated to Y.

Tom asked these Java developers why do you need to create Bitmap indexes on this PROCESS_FLAG field?

One developer said it was to improve the search speed. Once a bitmap index is created, our program can quickly find records whose values are N and then process them. Later, he took out a big-headed Oracle Database reference manual and told Comrade Tom that this book all said so. For those fields with very few values, for example, if our PROCESS_FLAG field has only two values, a bitmap index should be created. Is there any problem?

Comrade Tom smiled and did not answer the question directly. I saw him open SQLPlus, connect to his local Oracle instance, and demonstrate the following simple SQL code to this group of developers.

C:\DocumentsandSettings\carlwu>sqlplusscott/tiger@carl SQL*Plus:Release11.1.0.6.0-ProductiononWedApr2318:15:342008 Copyright(c)1982,2007,Oracle.Allrightsreserved. Connectedto: OracleDatabase11gEnterpriseEditionRelease11.1.0.6.0-Production WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions SQL>createtablet(processed_flagvarchar2(1)); Tablecreated. SQL>createbitmapindext_idxont(processed_flag); Indexcreated. SQL>insertintotvalues('N'); 1rowcreated. SQL>

I'm not convinced that the well-intentioned developer just said, isn't it normal? Then Tom opened a SQLPlus window, connected to the local database, and typed the following SQL statement. The strange thing is that this SQL statement is not executed, but is always waiting. Below is one of the SQL statements:

'20180101') this. width = '20180101'; if (this. height> '20180101') this. height = '20180101'; "border = 0>

These Java developers are stunned to see this. One Developer hesitated to say that it seems that this bitmap index only allows one user to operate. If other users want to operate the index at the same time, they must wait for the first user to complete the request processing, this operation can only be performed after submission. If the first user does not submit the application, the user must wait. Tom nodded and gave them a detailed explanation:

BitmapIndex of the Oracle database is indeed for those fields with sparse values (low-cardinality, low base, it is applicable to fields whose values do not change frequently. In practice, if the value of a field needs to be updated frequently, it is not suitable for creating a bitmap index on it. In a bitmap index, If you update or insert a record whose value is N, there may be hundreds of records whose value is N in the corresponding table.) All the records are locked by Oracle, this means that other users cannot update records whose values are N at the same time. Other users must wait for the first user to submit the records before obtaining the lock, update or insert data.

If the problem is found, the correction is simple. Tom suggests that these developers remove the bitmap index and create a function index on the PROCESS_FLAG field, create a simple B-tree index for records whose values are N.

After returning, these developers finally solved the problem after some tests according to Tom's instructions.

But the story is not over yet. These developers are not satisfied. They wrote an email to Tom complaining that the Oracle database is really "bad" and cannot even handle this simple bitmap index problem, you see, Oracle has wasted a lot of time and energy debugging our Java program. Tom sent them an email with a feeling:

I have encountered issues such as this happens times when an application is being moved from database A to database B. when an application that worked flawlessly in database A does not work, or works in an apparently bizarre fashion, on database B, the first thought is that database B is "bad" database. the simple truth is that database B just works differently. neither database is wrong or "bad; they are just different. knowing and understanding how they both work will help you immensely in dealing with these issues.

(When people migrate an application from one database to another, they often complain about the same problem. Originally, this application runs well on Database A. When it is migrated to database B, there is A problem. So they decided that database B was really bad. However, this is not the case because database B works in A different way than database. No database in the world is a "bad" database. The key is that we must have an in-depth understanding of the database's architecture and features to avoid such problems. If you understand the applicability of Bitmap indexes, would you say Oracle is a "bad" database ?)

Finally, I suggest Tom take the opportunity to give them a simple three-day training if you like. These Java programmers listened to Comrade Tom's suggestion. After three days of training, they were surprised by what Oracle could do, it turns out that Oracle is not suitable for creating temporary tables. You can use my program to delete and create temporary tables there. ", "If I use a materialized view (MateralizedView), my data backup code will be exceptionally simple .", "I still don't know that connectby has such powerful functions !".

1. Explain several paging query statements of Oracle
2. How to create and process images in Oracle
3. Oracle DBA responsibilities and daily work analysis