Database Reference
In-Depth Information
benefits of an index when we select from
msg_txt
, we would want to create an index mentioning
msg_txt
as the first column, for example (
msg_txt, update_ts
).
The Oracle database optimizer may actually use any index to improve the performance of a query by
doing a
skip scan
. A skip scan can improve performance in simple queries, potentially reducing the
reliance on full-table scans (those cause very bad performance). You can also use a skip scan hint to
explicitly name an index for use. It may take some trial and error and a bit of engineering to get the best
performance from a skip scan hint. Here is an example, taken from the Oracle documentation. The
comment section (
/* */
) with the plus symbol and hint name serve as a hint to the optimizer.
SELECT
/*+ INDEX_SS(e emp_name_ix) */
last_name
FROM employees e
WHERE first_name = 'Steven';
Notice that the skip scan hint,
INDEX_SS
directs the optimizer to use the index
emp_name_ix
for our
query. Even though we're selecting records where the
first_name
column is 'Steven,' we ask to benefit
from the index on
last_name
,
first_name
. For a better understanding of skip search optimization, I
recommend doing an Internet search for examples.
You might want to sort or select from
t_appsec_errors
table based on different columns, but unless
there is a frequent query that requires this column to be ordered, you will not need an index. Because
this table is really only intended for troubleshooting after an error, we don't expect to have other
indexes—we will always select the most recent records (based on
update_ts
).
We will create a view of the table that we are prepared to grant select on; however, we don't have
anyone in mind at present who may need to view it. Perhaps down the road we will have a savvy app
developer who wants to help debug his application's use of the
appsec
packages. We may arrange for her
to select from our view. Execute the following:
CREATE OR REPLACE VIEW appsec.v_appsec_errors AS SELECT * FROM appsec.t_appsec_errors;
Creating a Table for Managing Our Error Log Table
Remember, we only gave our application security,
appsec
, user two megabytes of space in the
USERS
tablespace. It would be inconsiderate, if not negligent, to create a table, especially a log table, without
any provision for regular cleaning and maintenance.
Now to let you in on a secret: we are building a robot. Not a mechanical contraption to fetch our
coffee, but a software sentinel to help us manage the error log table, especially when we are not looking.
We are going to do automatically remove old records from our table with a trigger that runs whenever we
insert a record into our table.
The tricky thing is, there is some effort involved in managing our table, so we want to minimize how
frequently the management tasks occur. In fact, we only want to manage our error log table once a day.
We also won't mind knowing when the table was last managed. The best way to accomplish both those
goals is to create another table to store the date whenever our error log table is managed. Execute the
code in Listing 7-3 to create the error log maintenance table.
Listing 7-3.
Creating the Application Security Error Log Table and Index,
t_appsec_errors_maint
CREATE TABLE appsec.t_appsec_errors_maint (
update_ts DATE DEFAULT SYSDATE
);
CREATE UNIQUE INDEX i_appsec_errors_maint00 ON appsec.t_appsec_errors_maint (
update_ts );
