TIP 58# : Column ordering in composite index

There are a myth about composite index which I `d like to address this here.

1. Whether SQL with condition on leading indexed column can only take advantage of composite index ?

Answer is no. SQL statement which has condition on columns which are not leading column in composite index may take advantage of using script.

Here is sample :

-- Create sample table

create table test as select * from user_objects;

-- Create composite index on tempory and object_id
create index test_idx on test(temporary,object_id);

-- Analyze new table with its index
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS');

-- Check simple query which has condition on both indexed columns.

As I think, optimizer should use existing index.


SQL> select object_id, object_name from test where temporary='Y' and object_id=19;

no rows selected



Index has been used.








-- Check simple query which has condition on second column .

SQL> select object_id, object_name from test where object_id=245630;

no rows selected


SQL> select object_id, object_name from test where object_id=245630;

no rows selected



Index has been used.








Interestingly, query uses index which its leading indexed column is not object_id
That is the beauty of INDEX SKIP SCAN


More testing ..... Creating composite index with same columns but with reverse order. (This time on object_id and then temporary).


-- Create composite index on object_id and temporary

create index test_idx on test(object_id,temporary);

-- Gather stats

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS');

-- Check simple query which has condition on both indexed columns.

SQL> select object_id, object_name from test where temporary='Y' and object_id=19;

no rows selected




Index has been used.








-- Check simple query which has condition on second column .


select object_id, object_name from test where TEMPORARY='Y';



Index has not been used.









optimizer does not choose composite index. It goes with full table scan

Conclusion :
---------------


In creating of composite index, order of columns is important.
If you put less selective columns as leading columns in index and put most selective columns at the end, other queries which have condition on non-leading index columns may also take advantage of INDEX SKIP SCAN.



For those who are interested, Send me Email for more test cases.


TIP#57 : How to use PL/SQL profiler

In tip#45, I introduced dbms_profiler which is a handy tool to tune PL/SQL code.In this post, steps to use this code is briely mentioned.
Here, I try to explain some practical notes when I use this profiler mostly for a client to tune code.

1. The following SQL statement shows the elapsed time of each piece of code and in addition to line number, also SQL text is shown which make finding bottleneck easier.


select p.unit_name, p.occured, p.tot_time, p.line# line,
substr(s.text, 1,75) text
from
(select u.unit_name, d.TOTAL_OCCUR occured,
(d.TOTAL_TIME/1000000000) tot_time, d.line#
from plsql_profiler_units u, plsql_profiler_data d
where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
and d.TOTAL_OCCUR >0
and u.runid= &runid) p,
user_source s
where p.unit_name = s.name(+) and p.line# = s.line (+)
order by p.unit_name, p.line#;



2. Anonymous block in result are parts of code which are not owned by the user who ran dbms_profiler.It is better to run profiler as user who owns PL/SQL code.




TIP #56 : Invalid username/password; logon denied

I faced with this issue when I was trying to login to database remotely with sys user.
First of all, I was suspicious to the password of sys user.
Checking with client, sys password was right but remote login to DB was not possible.
I found that REMOTE_LOGIN_PASSWORDFILE is NONE in database which means priviliged users can not login remotely to database.
In order to give permission to sys use to access to database remotely, this parameter should not be NONE.
For doing that, the following steps is recommended.

- Shutdown database.
- Move database password file (In Unix on $ORACLE_HOME/dbs) and create a new password file.
orapwd file= password= entries=
- Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE
- Startup database

Other possible reasons of this error are :

- The password file does not exist
- The password supplied does not match the one in the password file
- The password file been changed since the instance was started