Oracle_learner

• Sep 20th, 2006

 

The composite index is not useful if only 1 of the columns is used .It actually adds to the overhead and hampers performance .But indexes cant be decided based on every single query ,so the better idea is to create the composite index on the columns when frequently the combination of the columns are used in retreiving unique rows ...

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Oct 9th, 2006

 

It will be usefull

GG

• Oct 25th, 2006

 

To be useful you MUST use the first col of the index.  To be even more useful use the second col...and to be most useful use all three.

BUT, to be clear, even if you only have one col and it is the first of the index it will be useful (assuming Oracle uses the index at all of course <g>).

GG

madhaviguthula

 Profile Answers by madhaviguthula 

• Mar 6th, 2007

 

A new feature included with Oracle9i called Skip Scan Index Access allows the Oracle to use a composite index even when the index prefix column in a SQL statement has been omitted. Using this new feature, you will not have to create an additional index to provide faster access to your data.Oracle will attempt to use the new Skip Scan Index Access feature and scan a composite index when the SQL does not contain the index prefix column. This new feature can be used for the default B-tree index, however, you cannot use the skip scan index access technology for bitmap indexes, function-bases indexes, domain indexes, and for reverse key keys. Oracle9i does, however, supporting the new skip scan index access feature on clustered indexes, descending indexes, and using the CONNECT BY clause of the SQL statement.

hemant.agarwal03

• Mar 8th, 2007

 

Hi Madhvi..How we will use that one special feature?Regards,Hemant

madhaviguthula

 Profile Answers by madhaviguthula 

• Mar 8th, 2007

 

Hi Hemant, Please go through the following code for better understanding.First, create and populate a test table with a concatenated index.CREATE TABLE test_objects ASSELECT * FROM all_objects;CREATE INDEX test_objects_i ON test_objects (owner, object_name, subobject_name);EXEC DBMS_STATS.gather_table_stats(USER, 'TEST_OBJECTS', cascade => TRUE);Next, run a query that hits the leading edge of the index. Notice the range scan on the index.SQL> SET AUTOTRACE ONSQL> SELECT owner, object_name 2 FROM test_objects 3 WHERE owner = 'SYS' 4 AND object_name = 'DBMS_OUTPUT';OWNER OBJECT_NAME------------------------------ ------------------------------SYS DBMS_OUTPUT1 row selected.Execution Plan----------------------------------------------------------Plan hash value: 3650344004-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_OBJECTS_I | 1 | 32 | 2 (0)| 00:00:01 |-----------------------------------------------------------------------------------Next, run a query that does not hit the leading edge of the index. Notice the index skip scan on the index.SQL> SET AUTOTRACE ONSQL> SELECT owner, object_name 2 FROM test_objects 3 WHERE object_name = 'DBMS_OUTPUT';OWNER OBJECT_NAME------------------------------ ------------------------------PUBLIC DBMS_OUTPUTSYS DBMS_OUTPUT2 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1293870291-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 64 | 14 (0)| 00:00:01 ||* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 2 | 64 | 14 (0)| 00:00:01 |-----------------------------------------------------------------------------------