| Index Usage [message #292027] |
Mon, 07 January 2008 08:43  |
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 #292031 is a reply to message #292030] |
Mon, 07 January 2008 08:56  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
