Home » RDBMS Server » Performance Tuning » Index Usage
Index Usage [message #292027] Mon, 07 January 2008 08:43 Go to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
I have a table with two columns (x,y) i created index on two columns when i use in the select Query in where clause [where y=1] then my index is not being used only after analyzing the index and table it is using the index.

need to know why is this behaviour??? why it is not using the Index before analyzing .

Please find the below simulation steps for further reference



------Creating a sample table

SQL> create table gaut111(x int,y int);

Table created.

Elapsed: 00:00:00.00

----------Creating index on the two columns

SQL> create index gaut111_indx on gaut111(x,y);

Index created.

Elapsed: 00:00:00.00


--------Inserting values in the table


SQL> insert into gaut111 values(&x,&y);
Enter value for x: 1
Enter value for y: 1
old 1: insert into gaut111 values(&x,&y)
new 1: insert into gaut111 values(1,1)

1 row created.

Enter value for x: 1
Enter value for y: 1
old 1: insert into gaut111 values(&x,&y)
new 1: insert into gaut111 values(1,1)

1 row created.

Enter value for x: 5555
Enter value for y: 5555
old 1: insert into gaut111 values(&x,&y)
new 1: insert into gaut111 values(5555,5555)

1 row created.

Enter value for x: 444
Enter value for y: 444
old 1: insert into gaut111 values(&x,&y)
new 1: insert into gaut111 values(444,444)

1 row created.


SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> select * from gaut111 where x=1;

X Y
---------- ----------
1 1
1 1

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'GAUT111_INDX' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> select * from gaut111 where y=1;

X Y
---------- ----------
1 1
1 1

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'GAUT111'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> select x,y from gaut111 where y=1;

X Y
---------- ----------
1 1
1 1

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'GAUT111'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL>


/********************But after doing the analyze of the Index ******************//

analyze table gaut111 compute statistics
for table
for all indexed columns
for all indexes

Table analyzed.

Elapsed: 00:00:00.00



/*****************************Now it is using the Index after analyzing**************************/

SQL> select a.clustering_factor,b.blocks from user_indexes a,user_tables b where a.table_name=b.table_name and a.index_name='G
AUT111_INDX';

CLUSTERING_FACTOR BLOCKS
----------------- ----------
1 1




SQL> select x,y from gaut111 where y=1;

X Y
---------- ----------
1 1
1 1

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=12)
1 0 INDEX (FULL SCAN) OF 'GAUT111_INDX' (NON-UNIQUE) (Cost=1 C
ard=2 Bytes=12)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed


Re: Index Usage [message #292030 is a reply to message #292027] Mon, 07 January 2008 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

need to know why is this behaviour??? why it is not using the Index before analyzing .

Because with statistics Oracle optimizer can then better estimate the cost of using the index.

Don't use ANALYZE to get statistics use DBMS_STATS.

Next time, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Index Usage [message #292031 is a reply to message #292030] Mon, 07 January 2008 08:56 Go to previous messageGo to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
since i created index on both columns(x,y) why it is not using the index when i use y column

This is my doubt exactly ?

Leave about after analyzing the table and indexes.


Re: Index Usage [message #292034 is a reply to message #292031] Mon, 07 January 2008 09:07 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because it is not the leading column.

Regards
Michel
Previous Topic: Where to Start the Performance Tuning?
Next Topic: A question on Interpreting 10046 trace and the waits
Goto Forum:
  


Current Time: Sat Dec 10 16:42:08 CST 2016

Total time taken to generate the page: 0.07927 seconds