Hello All,
I have just been hired by a new company and I have been exploring its database infrastructure. Interestingly, I see that multiple column function based indexed used for every tables. I found it strange but they told me 'We use Axapta, in order to connect Axapta with Oracle, function based indexes should be used for better performance. Therefore, our DBAs create multiple function based indexes for each table in the database.' Unfortunately, I cannot judge their business logic.
My question is, I have just created similar tables to my local database in order to understand the behaviour of multiple column function based indexes. In order to create function based index (substr and nls_lower) I have to declare columns as varchars2. Because, in my company our DBAs had created some number columns as varchar2 data type. I have created two excatly same table for my experiment. I create miltiple function based index to my_first table, then I create normal multiple index to my_sec table. The interesting thing is, index skip scan cannot be performed on multiple function based indexes (my_first table). However, it can be performed to normal multiple index on my_sec table. I hope I did express myself clearly.
Note: I also ask the logic of function based index rule, they told me when they indexing a column they do ((column length) * 2 + 1) formula. For example, I want to create index on areacode column, the column data type in varchar2(3), so I have to use 3*2+1 = 7, (substr(nls_lower(areacode), 1, 7). substr(nls_lower()) notation is used nested for all function based indexes. I know these things are very illogical but they told me, they use this kind of implementation for Axapta.
Anyway, in this thread my question is reletad to the function based indexes with index skip scanning, not bussiness logic, because I cannot change the business logic.
Also, can you please give the tricks or clues for multiple function based indexes?
Thanks for your help.
SQL> create table my_first as select '201' areacode, to_char(rownum+100) account_num, dbms_random.st
ring('A', 10) name from dual connect by level <= 5000;
Table created.
SQL> create table my_sec as select '201' areacode, to_char(rownum+100) account_num, dbms_random.st
ring('A', 10) name from dual connect by level <= 5000;
Table created.
SQL> alter table my_first modify account_num varchar2(12);
Table altered.
SQL> alter table my_sec modify account_num varchar2(12);
Table altered.
SQL> alter table my_first modify areacode varchar2(3);
Table altered.
SQL> alter table my_sec modify areacode varchar2(3);
Table altered.
SQL> create index my_first_i on my_first (substr(nls_lower(areacode), 1, 7), substr(nls_lower(account_num),1,15));
Index created.
SQL> create index my_sec_i on my_sec (areacode, account_num);
Index created.
SQL> analyze table my_first compute statistics for all indexed columns for all indexes;
Table analyzed.
SQL> analyze table my_sec compute statistics for all indexed columns for all indexes;
Table analyzed.
SQL> exec dbms_stats.gather_table_stats(USER,'MY_FIRST');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(USER,'MY_SEC');
PL/SQL procedure successfully completed.
SQL> desc my_first;
Name Null? Type
----------------------------------------- -------- ----------------------------
AREACODE VARCHAR2(3)
ACCOUNT_NUM VARCHAR2(12)
NAME VARCHAR2(4000)
SQL> desc my_sec
Name Null? Type
----------------------------------------- -------- ----------------------------
AREACODE VARCHAR2(3)
ACCOUNT_NUM VARCHAR2(12)
NAME VARCHAR2(4000)
SQL> select * from my_sec where account_num = '4000';
Execution Plan
----------------------------------------------------------
Plan hash value: 1838048852
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00
:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_SEC | 1 | 19 | 3 (0)| 00
:00:01 |
|* 2 | INDEX SKIP SCAN | MY_SEC_I | 1 | | 2 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ACCOUNT_NUM"='4000')
filter("ACCOUNT_NUM"='4000')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
543 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from my_first where substr(nls_lower(account_num),1,25) = '4000';
Execution Plan
----------------------------------------------------------
Plan hash value: 1110109060
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 9 (12)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MY_FIRST | 1 | 20 | 9 (12)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR(NLS_LOWER("MY_FIRST"."ACCOUNT_NUM"),1,15)='4000'
AND SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,25)='4000')
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
543 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ INDEX_SS(MY_FIRST) */ * from my_first where substr(nls_lower(account_num),1,25) = '4000';
Execution Plan
----------------------------------------------------------
Plan hash value: 2466066660
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 20 | 17 (6)|
00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| MY_FIRST | 1 | 20 | 17 (6)|
00:00:01 |
|* 2 | INDEX FULL SCAN | MY_FIRST_I | 1 | | 16 (7)|
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,25)='4000')
2 - access(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,15)='4000')
filter(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,15)='4000')
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
857 consistent gets
0 physical reads
0 redo size
543 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed