Creating an Oracle index on a column which has none
- Article Type: General
- Product: Aleph
- Product Version: 18.01
Description:
Is there a problem with creating Oracle indexes on columns which have none?
I need to do a periodic check to make sure the ALEPH database and the database of our remote storage system are in sync. The only common data element between the two is the barcode. At this point, I already have a list of barcodes which are in the remote storage system which do not appear in the current Z30 table, so I want to check the Z30H table for these barcodes. I have a query that works but it takes 1.6 minutes per barcode. With a list of around 5,000 exception cases to process, that would take over 5 days to run.
Resolution:
There are possible problems with creating an Oracle index on a column which has none. See the Additional Information section of this Article in this regard. Aside from these possible problems, the addition of such an index is a trade-off between space and time: it requires additional Oracle space but should improve the retrieval time.
Here are the steps:
1. Create a file $alephm_root/sql_tab z30h_create_index_88.sql:
CREATE INDEX &1.Z30H_ID88 ON &1.Z30H (Z30H_BARCODE ASC)
STORAGE (INITIAL &2 NEXT &3 MINEXTENTS 1 PCTINCREASE 0)
NOLOGGING
PCTFREE 5
TABLESPACE &4;
(this is modeled after sql_tab z30_create_index_1.sql.)
2. Add this line to the abc50 $data_root/file_list:
IND z30h_id88 128M 0K TS3X
3. Do util a/17/2 for z30h_id88.
The same principle could be used to create an Oracle index for *any* column (which doesn't already have one).
Note: There's a (very small) danger that Ex Libris could add an Oracle index with the same number as the one you have chosen. That is why we specified "id88" as the index number in this example.
Additional Information
A summary of the Rule Based Optimizer (RBO) which Aleph uses can be found at http://docs.oracle.com/cd/B10500_01/...a96533/rbo.htm, but it does not cover certain dangers associated with RBO..... When the RBO was actively supported by Oracle, it was always bit of a black art in getting SQL queries to use the indexes they were meant to use. There are various reasons why you should be careful about adding indexes to a table when using RBO. One example is (I am quoting from an O?Reilly Oracle SQL Tuning pocket reference for Oracle 9i, and this will be relevant to Oracle 11, as the RBO was made obsolete at Oracle 9): If all columns in an index are specified in the WHERE clause, that index will be used in preference to other indexes for which some columns are referenced. Consider: select col1, ? from emp where emp_name = v_emp_name and emp_no = v_emp_no and dept_no = v_dept_no Index1 (emp_no, dept_no, cost_center) Index2 (emp_name) Only Index2 is used, because the WHERE clause includes all columns for that index, but does not include all columns for Index1. Now if your application was tuned to use Index1, and a customer created Index2, it could have detrimental effect on performance. Another example, is that the order in which indexes are created can become important. If multiple indexes can be applied to a WHERE clause, and they all have an equal number of columns specified, only the index created last will be used. Consider: select col1, ? from emp where emp_name = v_emp_name and emp_no = v_emp_no and dept_no = v_dept_no and emp_category = v_emp_category Index1 (emp_name, emp_category) Created date1 Index2 (emp_no, dept_no) Created date2 (date2 > date1) Only Index2 is used, because it was created after Index1, which could again change the performance of your application unexpectedly. This obviously also raises the interesting question that you should be careful about the order in which you rebuild your indexes!! There plenty of other scenarios, but in summary, the RBO can be a minefield. In my opinion you should tread very carefully with adding indexes to established application tables. Many vendors forbid you from adding indexes to their tables, as it will interfere with carefully crafted execution plans. [Note: there may be such cases in Aleph.]
- Article last edited: 12/10/2014