Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 41
Pages: 41
Authors: Richard Laermer, Michael Prichinello
The inspiration for the material contained in this book comes from my experiences developing Oracle software and working with fellow Oracle developers, helping them build reliable and robust applications based on the Oracle database. The book is basically a reflection of what I do everyday and of the issues I see people encountering each and every day.
I covered what I felt was most relevant - namely the Oracle database and its architecture. I could have written a similarly-titled book explaining how to develop an application using a specific language and architecture - for example, one using Java Server Pages that speak to Enterprise Java Beans, that use JDBC to communicate with Oracle. However, at the end of the day, you really do need to understand the topics covered here in order to build such an application successfully. This book deals with what I believe needs to be universally known to develop successfully with Oracle, whether you are a Visual Basic programmer using ODBC, a Java programmer using EJBs and JDBC, or a Perl programmer using DBI Perl. This book does not promote any specific application architecture; it does not compare 3-tier to client-server. Rather, it covers what the database can do and what you must understand about the way it works. Since the database is at the heart of any application architecture, the book should have a broad audience.
One of the problems with having plenty of development options is in trying to figure out which one might be the best choice for your particular needs. Everyone wants as much flexibility as possible (as many choices as they can possibly have) but they also want things to be very cut and dry; in other words, easy. Oracle presents the developer with almost unlimited choice. No one ever says 'you can't do that in Oracle'- they say 'how many different ways would you like to do that in Oracle?' I hope that this book will help you make the correct choice.
It is a book for those people who appreciate the choice but would also like some guidelines and practical implementation details on Oracle features and functions. For example, Oracle has a really neat feature called the virtual private database. Oracle documentation tells you how to use this feature and what it does. Oracle documentation does not, however, tell you when you should use this feature and, perhaps even more importantly, when you should not use this feature. It does not always tell you the implementation details of this feature, and if you're not aware of them, this can really come back to haunt you (I'm not referring to bugs, but really the way it is supposed to work and what the feature was really designed to do).
The target audience for this book is anyone who develops applications with Oracle as the database backend. It is a book for professional Oracle developers who need to know how to get things done in the database. The practical nature of the book means that many sections should also be very interesting to the DBA. Most of the examples in the book use SQL*PLUS to demonstrate the key features, so you won't find out how to develop a really cool GUI - but you will find out how the Oracle database works, what its key features can do and when they should (and should not) be used.
It is a book for anyone who wants to get more out of Oracle with less work. It is for anyone who wants to see new ways to use existing features. It is for anyone who wants to see how these features can be applied in the real world (not just examples of how to use the feature but why the feature is relevant in the first place). Another category of people that would find this book of interest would be the technical manager in charge of the developers who work on Oracle projects. In some respects, it is just as important that they understand why knowing the database is crucial to success. This book can provide ammunition for the manager who would like to get their personnel trained in the correct technologies, or in ensuring that they already know what they need to know.
In order to get the most out of this book, the reader should have:
Knowledge of SQL. You don't have to be the best SQL coder ever, but a good working knowledge would help.
An understanding of PL/SQL. This is not a pre-requisite but will help you to 'absorb' the examples. This book will not, for example, teach you how to program a FOR loop or declare a record type - the Oracle documentation and numerous books cover this well. However, that's not to say that you won't learn a lot about PL/SQL by reading this book. You will. You'll become very intimate with many features of PL/SQL and you'll see new ways to do things, become aware of packages/features that perhaps you did not know existed.
Exposure to some 3GL language such as C or Java. I believe that anyone who can read and write code in a 3GL language will be able to successfully read and understand the examples in this book.
Familiarity with the Oracle Server Concepts Manual.
A few words on that last point: due to its vast size, many people find the Oracle documentation set to be somewhat intimidating. If you are just starting out or haven't read any of it as yet, I can tell you that the Oracle8i Concepts manual is exactly the right place to start. It is about 800 pages long and touches on many of the major Oracle concepts that you need to know about. It may not give you each, and every technical detail (this is what the other 10,000 to 20,000 pages of documentation are for) but it will educate you on all of the important concepts. This manual touches the following topics (to name a few):
The structures in the database, how data is organized and stored.
Distributed processing.
Oracle's memory architecture.
Oracle's process architecture.
Schema objects you will be using (tables, indexes, clusters, and so on).
Built-in data types and user-defined data types.
SQL-stored procedures.
How transactions work.
The optimizer.
Data integrity.
Concurrency control.
I will come back to these topics myself time and time again. These are the fundamentals - without knowledge of them, your Oracle applications will be prone to failure. I encourage you to read through the manual and get an understanding of some of these topics.
To help you use this book, it is organized into six discrete sections (described below). These are not rigid divisions, but they will help you navigate quickly to the area you need most. This book has 23 chapters and each is like a 'mini-book'- a virtually standalone component. Occasionally, I refer to examples or features in other chapters (the Security section, in particular, relies a little more on examples and concepts that are built up over several chapters) but you could pretty much pick a chapter out of the book and read it standalone. You will not have to read Chapter 10 to understand or make use of Chapter 14, for example.
The format and style of each chapter is virtually identical:
An introduction to the feature or capability.
Why you might want to use it (or not). I outline the times you would consider using this feature and when you would not want to use it.
How to use this feature. Not just a copy of the SQL reference here but rather step by step - here is what you need, here is what you have to do, these are the switches you need to go through to get started. Things covered in this section will be:
How to implement it
Examples, examples, and examples
Debugging this feature
Caveats of using this feature
Handling errors (proactively)
A summary to bring it all together.
There will be lots of examples, and lots of code, all of which will be available for download at http://www.apress.com/ Following is a detailed breakdown of the content of each section:
Chapter 1, Developing Successful Oracle Applications. This chapter sets out my essential approach to database programming. All databases are not created equal and in order to develop database-driven applications successfully and on time, you need to understand exactly what your particular database can do and how it does it. If you do not know what your database can do, you run the risk of continually 're-inventing the wheel' - developing functionality that the database already provides. If you do not know how your database works you are likely to develop applications that perform poorly and do not behave in a predictable manner.
The chapter takes an empirical look at some applications where a lack of basic understanding of the database has lead to project failure. With this example-driven approach, the chapter discusses the basic features and functions of the database that you, the developer, need to understand. The bottom line is that you cannot afford to treat the database as a black box that will simply 'churn out the answers' and take care of scalability and performance by itself.
Chapter 2, Architecture. The Oracle database is a highly complex tool. Every time you connect to a database or issue an UPDATE command, a whole host of processes occur in the background to make sure that you're application runs smoothly and that data integrity is maintained. For example, the database ensures that it has enough information to restore the data to its original state should it need to. It will cache program data and automatically re-use it where appropriate. And so on. Most of the time all of this is occurs transparently (to the developer, at least) but when problems occur, half the battle is knowing where to look to fix them.
This chapter covers the three major components of the Oracle architecture - its memory structures (specifically, the System Global Area), its physical processes, and its set of files (parameter files, redo log files...). Understanding the Oracle architecture is fundamental to understanding the unique way in which Oracle implements certain features and how this will affect your application.
Chapter 3, Locking and Concurrency. Different databases have different ways of doing things (what works well in SQL Server may not work as well in Oracle) and understanding how Oracle implements locking and concurrency control is absolutely vital to the success of your application.
This chapter discussed Oracle's basic approach to these issues, the types of locks that can be applied (DML, DDL, latches...) and the problems that can arise if locking is not implemented carefully (deadlocking, blocking and escalation). The concurrency control section discusses the functions provided by Oracle that allow us to control how users can access and modify the database.
Chapter 4, Transactions. Transactions are a fundamental feature of all databases - they are part of what distinguishes a database from a file system. And yet, they are often misunderstood and many developers do not even know that they are accidentally not using them. This chapter examines how transactions should be used in Oracle and also exposes some 'bad habits' that have been picked up when developing with other databases. In particular, we look at the implications of atomicity and how it affects statements in Oracle. We then move on to discuss transaction control statements (COMMIT, SAVEPOINT, ROLLBACK), integrity constraints and distributed transactions (the two-phase commit). Finally, we look at some real world issues surrounding use of transactions - how they are logged, and the role of redo and undo.
Chapter 5, Redo and Rollback. It can be said that the developer does not need to understand the detail of redo and rollback as much as the DBA, but developers do need to know the roles they play in the database. After first defining redo, we examine what exactly a COMMIT does. We also consider issues such as how much redo is being generated, turning off logging and also analyzing redo.
In the rollback section of the chapter we first look what generates the most and least undo, before looking at the set transaction SQL statement. This is generally used to pick a large rollback section for some very large operation. Then we focus on the infamous 'ORA-01555 snapshot too old' error, looking at causes and solutions.
Chapter 6, Tables. Oracle now supports numerous types of table. This chapter looks at each different type - heap organized (the default, 'normal' table), index organized, index clustered, hash clustered, nested, temporary, and object - and discusses when, how, and why you should use them. Most of time the heap-organized table is sufficient, but you should be able to recognize when one of the other types might be more appropriate.
Chapter 7, Indexes. Indexes are a crucial aspect of your application design. Correct implementation requires an in-depth knowledge of the data, how it is distributed, how it will be used. Too often, indexes are treated as an afterthought in application development, and performance suffers as a consequence.
This chapter we look in detail at the different types of indexes, including B*Tree, bitmap, function-based, and application domain indexes, and discuss where they should and should not be used. We'll also answer some of those common queries in the Frequently Answered Questions section, such as 'Do indexes work on views?' and 'Why isn't my index getting used?
Chapter 8, Import and Export. Import and export are two of the oldest tools supplied with Oracle, used to extract tables, schemas or entire database definitions from one Oracle instance to be imported into another instance or schema, yet many developers do not how to use them properly. We cover topics such as large exports, sub-setting and transporting data, and using them as backup or reorganization tools. The chapter finishes by highlighting some of the potential pitfalls and problems in the use of these tools.
Chapter 9, Data Loading. This chapter focuses on SQLLDR and covers the various ways in which we can use this tool to load and modify data in the database. Issues covered include loading delimited data, updating existing rows and inserting new ones, unloading data, and how to call SQLLDR from a stored procedure. Again, SQLLDR it is a well-established and crucial tool but is the source of many questions with regard to its practical use.
Chapter 10, Tuning Strategies and Tools. This is one of my 'specialist topics' and here I detail my approach to tuning Oracle applications and then embark on a highly practical guide to the tools and techniques that I use. The opening section concentrates on application tuning, covering topics such as bind variables and parsing, SQL_TRACE, TIMED_STATISTICS and TKPROF, the DBMS_PROFILER, and the importance of logging in your applications. With the application fully tuned, attention turns to the database and specifically to the StatsPack group of utilities and the V$ tables you will use in tuning efforts.
Chapter 11, Optimizer Plan Stability. Developers using Oracle 8i (and later) now have the ability to save a set of 'hints to the server', known as an optimizer plan, detailing how best to execute a specific SQL statement in the database. This has obvious performance benefits and we take a detailed look at how you can generate these outlines and how to manage them.
Chapter 12, Analytic Functions. Certain questions are asked of the database very regularly, but the queries that can answer them are difficult to write in straight SQL (and will not always perform quickly, anyway). Oracle 8.1.6 introduced analytic functions. These functions add extensions to the SQL language that make such queries easier to code, and dramatically increase performance over the equivalent straight SQL query. This chapter deals with the way in which analytical functions work, looking at the full syntax (including the function, partition and windowing clauses) and then gives full, practical examples of how these functions can be used.
Chapter 13, Materialized Views. Certain 'aggregate' queries must process potentially terabytes of data to produce an answer. The performance implications are clear - especially if it is a common query, meaning that a vast amount of data has to be processed each and every time the question is asked. With this feature, we simply do some of the work beforehand - we summarize the data needed to answer a certain query in a materialized view and future queries are directed at this summary data. Furthermore, the database can recognize similar queries that make use of this summary data, and automatically re-writes the query to allow them to do so. This chapter discusses how all this works and how to set up materialized views, including use of constraints, dimensions, and the DBMS_OLAP package.
Chapter 14, Partitioning. Partitioning is designed to facilitate the management of very large tables and indexes, by implementing a 'divide-and-conquer' logic - basically breaking up a table or index into many smaller, and more manageable, pieces. It is an area where the DBA and developer must work together to maximize application availability and performance. This chapter covers both table and index partitioning. We look at partitioning using local indexes (common in data warehouses) and global indexes (common in OLTP systems).
Chapter 15, Autonomous Transactions. With this feature, we can create a sub-transaction that can commit or rollback changes independently of its parent transaction. We look at the situations when this might be desired, such as when auditing an 'illegal' attempt to modify secure information, to avoid mutating a table or as a way of performing DDL in triggers. The discussion will span issues such as transactional control, scope, ending an autonomous transaction, and savepoints.
Chapter 16, Dynamic SQL. In this chapter, we compare two methods of using SQL statements in our programs: 'normal' static SQL and dynamic SQL. Dynamic SQL is the SQL executed at run-time, but was not known at compile time. We will look at two methods of using dynamic SQL in your programs, namely with the supplied built-in package DBMS_SQL and native dynamic SQL, a declarative method for use with PL/SQL. There are various reasons why you would choose one over the other, such as whether the bind variables are known at compile time, whether you know the outputs at compile time, and whether a given statement will be executed once, or many times in a session and we will explore these issues in detail.
Chapter 17, interMedia. This chapter focuses on interMedia Text. Rather than a detailed 'how to use interMedia Text', we will cover what it is and what it provides, and the features of the database that enable this functionality. We look at how to search for text, manage a variety of documents, index text from many data sources, and search XML applications. The chapter finishes with a look at some of the caveats of interMedia, including the synchronization of indexes and indexing information outside of the database.
Chapter 18, C-Based External Procedures. With Oracle 8.0 came the ability to implement procedures in the database server that were written in languages other than PL/SQL- for example, C or Java. These are referred to as external procedures. In this chapter, we will cover C-based procedures from an architectural perspective. We see how to configure your server to use these procedures, test the installation, and create an example procedure for passing and manipulating various types of variables. We also examine the LOB to File (LOB_IO) external procedure, which writes the CLOBs, BLOBs, and BFILEs to disk.
Chapter 19, Java Stored Procedures. With judicious use of small amounts of Java, we can achieve a great deal of useful functionality that is beyond the reach of PL/SQL. In this chapter, we look at practical examples of where this ability is useful - such as when getting a directory listing or running an operating system command. Again, we round off the chapter with some of the errors that you may encounter when you try to use this feature, and some possible solutions.
Chapter 20, Using Object Relational Features. The availability of object-relational features in the database (with Oracle 8i onwards) greatly extends the set of data types available to the developer - but when should they be used (and, equally, when shouldn't they be used)? In this chapter, we show how to add new data types to your system (we create a new PL/SQL data type) and look at the unique uses for collections. Finally we look at object relational views, which are for those of you who want to work with object relational features but still present a relational view of the data to the application.
Chapter 21, Fine Grained Access Control. This feature allows you to attach a predicate, at run-time, to all queries issued to a database. The fact that this feature is implemented on the server means that any application that can access the database can use the feature. Further reasons to use this feature include ease of maintenance and the ability to host an application as an ASP. You will also see how it works by testing a couple of examples, one based on implementing a security policy and one using application contexts. The chapter is rounded off with a section on caveats, which include referential integrity, import and export issues, and a section on errors.
Chapter 22, n-Tier Authentication. In this chapter we will discuss the effects of the Web, which gives rise to situations where your client presents their credentials to a middle-tier application server, before actually accessing your database. We will see how this feature can be implemented and how it works. We will look at how you can grant privileges and audit proxy accounts.
Chapter 23, Invoker and Definer Rights. Starting with Oracle 8i, we can now grant a different set of privileges to different users of a stored procedure. With invoker rights, we can now develop a stored procedure that executes with the privilege set of the invoker at run-time We examine why this feature might be useful, such as in the development of generic utilities and data dictionary applications and why, in the majority of cases, definer rights is still the correct choice. In the 'how it works' section, we look at what exactly happens when we compile definers and invokers rights procedures.
Appendix A, Necessary Supplied Packages. Many of these packages are overlooked in development efforts - or their intention not truly understood. Here I try to make sense of them, show you how to use them and extend them.
We have used a number of different styles of text and layout in this book to help you differentiate between the different kinds of information. Here are examples of the styles we use and an explanation of what they mean:
Code has several fonts. If it's a word that we're talking about in the text, for example when discussing a PL/SQL SELECT query, it's in this font. If it's a block of code that you can type as a program and run, then it is in a gray box:
tkyte@DEV816> create or replace procedure StaticEmpProc(p_job in varchar2) 2 as 3 begin 4 for x in (select ename from emp where job = p_job) 5 loop 6 dbms_output.put_line( x.ename ); 7 end loop; 8 end; 9 / Procedure created.
In this book we have also shown line numbers directly from the SQL*PLUS session, for ease of reference.
| Note | Advice, hints, and background information comes in this type of font. |
| Important | Important pieces of information come in boxes like this. |
Bullets appear indented, with each new bullet marked as follows:
Important Words are in a bold type font
Words that appear on the screen in menus like File or Window, are in a similar font to that you would see on a Windows desktop
Keys that you press on the keyboard like Ctrl and Enter, are in italics
As you work through the examples in this book, you may decide that you prefer to type in all the code by hand. Many readers prefer this because it is a good way to get familiar with the coding techniques that are being used.
Whether you want to type the code in or not, we have made all the source code for this book available at our web site at the following address:
http://www.apress.com/
If you're one of those readers who likes to type in the code, you can use our files to check the results you should be getting they should be your first stop if you think you might have typed in an error. If you're one of those readers who does not like typing, then downloading the source code from our web site is a must!
Also, errata sheets are available for all our books at http://www.apress.com/ on each book's page. If you find an error that hasn't already been reported, please let us know.