I have often seen it asked online many times how to get a list of invalid indexes. The information was mostly incomplete, and often contained misinformation. Determined to solve this problem once and for all I wrote a query to get a list of invalid indexes on our Oracle 10g database.
Before we get on to the query, I'd like to note what makes finding invalid indexes so difficult, and how to overcome these obstacles.
Finding invalid global indexes is rather easy. Global indexes are your standard indexes you use and love every day. They are simply an index on a column, or columns in a standard non-partitioned table. Oracle provides a nice little view called all_indexes, which has a column named status. This view will return a list of all indexes that the user running the query has permissions to view. There is also the dba_indexes view and the user_indexes view. The status column can be one of three values, VALID, INVALID, or N/A. Global indexes will never have a status of N/A.
We run into trouble if we try to query WHERE status != 'VALID' if our database has any partitioned tables with partitioned indexes and potentially bring back valid indexes. Partitioned tables, and their indexes, save the lives of millions of babies every year, so we must give them our full respect, and do the necessary extra work that is required to account for the quirks they sometimes bring to our code.
Partitioned indexes will always have a status of N/A in the all_indexes view. Each of these indexes will have a partitioned index, which we can see by querying the all_ind_partitions view. Again, we also have the dba_ind_partitions and user_ind_partitions views available to us as well. This table contains all partitioned indexes, along with a status column. This status column works a little bit differently. Its possible values are USABLE, UNUSABLE, and N/A.
If our index'es partitioned indexes are marked as usable then we know our index is valid. However, we could again run into a case where our status column is marked as N/A. Partitioned indexes can go a level deeper and have their own subpartitioned indexes. Determined, we pick up our shovel and keep digging deeper.
Oracle provides us with a third view named all_ind_subpartitions. Like the above views, we also have user_ind_subpartitions and dba_ind_subpartitions. Again, we have a status column. This time, though, we can only have two possible values, USABLE, and UNUSABLE. At this point, we can determine if our index is valid or not by looking at its partition's subpartition's status.
Okay, so this is all well and good, but what we really need is a query that will give us the name of indexes that need rebuilding. Global indexes do not have partitions, and not all partitioned indexes have subpartitions, yet we want to know all invalid indexes, regardless of their makeup. The following query accomplishes this.
select
index_name
from
all_indexes
where
owner not in ('SYS', 'SYSTEM')
and
status != 'VALID'
and (
status != 'N/A'
or
index_name in (
select
index_name
from
all_ind_partitions
where
status != 'USABLE'
and (
status != 'N/A'
or
index_name in (
select
index_name
from
all_ind_subpartitions
where
status != 'USABLE'
)
)
)
);
I'll leave dissecting how this query works as a lesson to the reader. Here I am also leaving out the indexes owned by SYS and SYSTEM, but you can remove that line if you need those indexes listed as well.
I do presently have an anonymous block that makes a cursor out of this query, loops through it, and builds all indexes, partitioned indexes, and subpartitioned indexes, as is appropriate. I am still working on making sure that it is battle hardened. After that I'll post about how I accomplish this. Combined they provide an elegant solution to finding all of your invalid indexes and rebuild them.
If you find this helpful, or have some additional information to provide, please do so in the comment section below.
Edit: The script is available at Rebuilding invalid indexes in Oracle
Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts
Friday, September 3, 2010
Thursday, August 5, 2010
Dynamic column ordering and paging in PL/SQL
Update 09/30/2010: There was a problem with how I was doing the dynamic ordering. I have resolved this and updated the post.
Update 10/08/2013: Oracle 12c finally has support for sane paging without jumping through all of these hoops. Until I get a chance to update this post you can read more at http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php
The following demonstrates how a PL/SQL function or procedure can take in parameters, and dynamically sort based on those parameters.
Update 10/08/2013: Oracle 12c finally has support for sane paging without jumping through all of these hoops. Until I get a chance to update this post you can read more at http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php
The following demonstrates how a PL/SQL function or procedure can take in parameters, and dynamically sort based on those parameters.
Let's start with an example. This example accepts two parameters, a VARCHAR2 that will be the column we want to sort on, and a REF CURSOR. The REF CURSOR is an out variables that the calling program can use to iterate through to get the result set.
TYPE cursortype IS REF CURSOR;
PROCEDURE sort_example
(
sort_column VARCHAR2,
result_set OUT cursortype
) AS
BEGIN
OPEN result_set FOR
SELECT column_one,
column_two
FROM example_table
ORDER BY DECODE
(
UPPER(sort_column),
'COLUMN_ONE',
column_one,
'COLUMN_TWO',
column_two,
column_one
);
ORDER BY DECODE
(
UPPER(sort_column),
'COLUMN_ONE',
column_one,
'COLUMN_TWO',
column_two,
column_one
);
EXCEPTION
WHEN OTHERS THEN
NULL;//Real error handling should go in this exception block.
END;
The DECODE statement examines the sort_column passed in and matches it to an actual column in the table(s) that we are selecting from. If the variable passed in is not in our list of columns then it will default, in this example, to column_one.
Using this technique we have to define, up front, all of the columns that we are going to allow the calling procedure to order by. If we want to allow ordering by any column, we must go through and list them out one-by-one.
It is also a good idea to add some extra validation, beyond the DECODE clause, to make sure that sort_column is an actual column in the table. In our real packages that do this, we have an out variable named message. If an invalid column name is passed in we notify the calling program by sticking a message to that effect in our message variable.
Using this technique we have to define, up front, all of the columns that we are going to allow the calling procedure to order by. If we want to allow ordering by any column, we must go through and list them out one-by-one.
It is also a good idea to add some extra validation, beyond the DECODE clause, to make sure that sort_column is an actual column in the table. In our real packages that do this, we have an out variable named message. If an invalid column name is passed in we notify the calling program by sticking a message to that effect in our message variable.
To make our procedure even more robust, it is nice to have the ability to decide if the column should be sorted in ascending or descending order.
This gets a little tricker, and to accomplish we need to understand how ROWNUM works in PL/SQL. When a query is executed, Oracle assigns each returned result a pseudo-column nmaed ROWNUM, with the first returned row having a ROWNUM of 1, the second 2, etc.
Through a subquery, and ROWNUM, we can dynamically sort in either ascending or descending order.
TYPE cursortype IS REF CURSOR;
PROCEDURE sort_example
(
sort_column VARCHAR2,
sort_direction VARCHAR2,
result_set OUT cursortype
) AS
BEGIN
OPEN result_set FOR
SELECT *
FROM
(
SELECT column_one,
column_two
FROM example_table
ORDER BY DECODE
(
UPPER(sort_column),
'COLUMN_ONE',
column_one,
'COLUMN_TWO',
column_two,
column_one
)
)
ORDER BY
CASE
WHEN UPPER(sort_direction) = 'DESC' THEN
ROWNUM * -1
ELSE
ROWNUM
END;
EXCEPTION
WHEN OTHERS THEN
NULL;//Real error handling should go in this exception block.
END;
Here we have our original query, initially sorting on the passed in column, that is subqueried and then sorted again, either on the rownum, or the opposite of rownum. If the calling program passed in 'DESC' for the sort_direction, then we sort descending, if anything else is passed in we sort ascending.
We can take this maddening query a step further, utilizing rownum again, to handle paging. We use this in our applications to push paging out of our interface code, and onto the database. It is much quicker for our database to only return ten or so results we want to show on each page, then to return the whole set of data, and have our PHP application parse through the data.
So here is our final procedure, with dynamic sorting and paging.
TYPE cursortype IS REF CURSOR;
PROCEDURE sort_example
(
sort_column VARCHAR2,
sort_direction VARCHAR2,
start_row NUMBER,
end_row NUMBER,
result_set OUT cursortype
) AS
BEGIN
IF start_row < 1 THEN
start_row = 1
END IF;
IF start_row > end_row THEN
end_row = start_row;
ELSIF end_row IS NULL THEN
end_row = start_row + 10;
END IF;
OPEN result_set FOR
SELECT *
FROM
(
SELECT ROWNUM rnum,
column_one,
column_two
FROM
(
SELECT *
FROM
(
SELECT column_one,
column_two
FROM example_table
ORDER BY DECODE
(
UPPER(sort_column),
'COLUMN_ONE',
column_one,
'COLUMN_TWO',
column_two,
column_one
)
)
ORDER BY
CASE
WHEN UPPER(sort_direction) = 'DESC' THEN
ROWNUM * -1
ELSE
ROWNUM
END
)
)
WHERE rnum >= start_result
AND rnum <= end_result
ORDER BY rnum;
EXCEPTION
WHEN OTHERS THEN
NULL;//Real error handling should go in this exception block.
END;
Yes, this one is a beast. Starting at the child-most query, we have our SELECT statement, ordering the data by the column passed in. In the parent query we then use ROWNUM, and the sort_direction argument to sort either ascending or descending. We subquery these results, capturing the ROWNUM into an aliased column named rnum, so that in it's parent query we can refer to it's ROWNUM, and not some other ROWNUM from any other part of the query. In the parent-most query we use our new rnum column to get only the data from the rows asked for.
Before you do any querying, we check the validity of the start_result and end_result arguments to make sure they actually make sense. If they don't then we do some manual patching. You may want to handle this differently, such as throwing an exception. It is really up to you. We have a DEFAULT_PAGING environmental variable we store in our database. When end_result is NULL, then we set it to start_result plus our DEFAULT_PAGING variable.
You may look at this and think it is complicated, and probably performs very poorly. In our testing, as long as your inner-most query has the proper indexes and constraints, the performance is actually pretty good. In our environment, it is definitely much faster than having our PHP parse through all the results, sort and order the whole thing, and then cut out only the rows we actually want, especially when we have result sets that are well over 100,000 rows long.
There is one final step we take, so that our interface application knows how many results there are in the whole query, so that it knows how many pages it can expect there to be. This example won't show it, but we actually check for a cached rowcount first, and we have all sorts of logic to try to guarantee integrity of those results, but I'm not going to get into all that here.
TYPE cursortype IS REF CURSOR;
PROCEDURE sort_example
(
sort_column VARCHAR2,
sort_direction VARCHAR2,
start_row NUMBER,
end_row NUMBER,
result_set OUT cursortype,
rowcount OUT NUMBER
) AS
BEGIN
IF start_row < 1 THEN
start_row = 1
END IF;
IF start_row > end_row THEN
end_row = start_row;
ELSIF end_row IS NULL THEN
end_row = start_row + 10;
END IF;
OPEN result_set FOR
SELECT *
FROM
(
SELECT ROWNUM rnum,
column_one,
column_two
FROM
(
SELECT *
FROM
(
SELECT column_one,
column_two
FROM example_table
ORDER BY DECODE
(
UPPER(sort_column),
'COLUMN_ONE',
column_one,
'COLUMN_TWO',
column_two,
column_one
)
)
ORDER BY
CASE
WHEN UPPER(sort_direction) = 'DESC' THEN
ROWNUM * -1
ELSE
ROWNUM
END
)
)
WHERE rnum >= start_result
AND rnum <= end_result
ORDER BY rnum;
SELECT COUNT(*)
INTO rowcount
FROM example_table;
EXCEPTION
WHEN OTHERS THEN
NULL;//Real error handling should go in this exception block.
END;
With any luck this will help someone out. With better luck, someone will drop by and offer some tips on how to do this even better.
Subscribe to:
Posts (Atom)