Oracle PL/SQL for DBAs

Chapter 7. Generating Random Values

Databases are supposed to be about predictable facts; we meticulously collect data that defines our business or activity and then go to any lengths to protect and enforce its integrity. Why would you want to generate something that is just the contrary—an unpredictable, random value? Let’s look at some common situations in which DBAs might need a randomizer:

• You are creating temporary passwords or generating web site user IDs for registered users.

• You are involved in the development of an application that needs thorough testing from all angles (performance, scalability, accuracy, and more), and as part of that development, you must create a test bed with some representative data. Representative here means that the data has to relate in some way to the actual entity it represents; for example, a column containing an account number must reflect the format of an actual account number, a name should be alphabetic, and so on.

• You want to test the effect of some important database structural components such as indexes. Answers to questions such as how many indexes, on what columns, how many histograms you should collect while analyzing, whether it will be beneficial to enable block sampling, and so on, all require a lot of data representing real-life scenarios. It might be impractical to get this set of data from another source, so you might need to generate it. The focus here is on creating a data set that is random enough but that closely follows a real-life ...