Re: Strategies for dealing with (NOT EQUAL) conditions and indexes
From: Taral Desai <taral.desai_at_gmail.com>
Date: Wed, 16 Nov 2011 18:14:56 -0600
Message-ID: <CAO4+9HX69mhwPXwSSGCEMSB_r74xaYs=je94r9LTOKwxn3D52w_at_mail.gmail.com>
For me on my 32-bit windows DB 11.2.0.3 even i don't get index path
select val2, val3, val4, val5 from test1 where val1 = 12 and val2 = 'A12E'
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
select val2, val3, val4, val5 from test1 where val1 != 12 and val2 = 'A12E'
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
Date: Wed, 16 Nov 2011 18:14:56 -0600
Message-ID: <CAO4+9HX69mhwPXwSSGCEMSB_r74xaYs=je94r9LTOKwxn3D52w_at_mail.gmail.com>
For me on my 32-bit windows DB 11.2.0.3 even i don't get index path
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN -------------------- ------------------------------ ---- ------------ ---------- --------- ------ ------ TARAL TEST1 TAB 9997 50 0 0 31
SQL_ID 50sw8qujy3d49, child number 0
select val2, val3, val4, val5 from test1 where val1 = 12 and val2 = 'A12E'
Plan hash value: 4122059633
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 15 (100)| 100 |00:00:00.01 | 53 | |* 1 | TABLE ACCESS FULL| TEST1 | 1 | 100 | 15 (0)| 100 |00:00:00.01 | 53 |
--------------------------------------------------------------------------------------------------
SQL_ID 8cq7maz5zrzp9, child number 0
select val2, val3, val4, val5 from test1 where val1 != 12 and val2 = 'A12E'
Plan hash value: 4122059633
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 15 (100)| 0 |00:00:00.01 | 52 | |* 1 | TABLE ACCESS FULL| TEST1 | 1 | 99 | 15 (0)| 0 |00:00:00.01 | 52 |IND_NUMROWS IND_DISTINCTKEYS IND_CLUFAC
--------------------------------------------------------------------------------------------------
IND_NAME IND_STAT I IND_BLEVEL IND_LEAFBLOCKS
------------------------------ -------- - ---------- -------------- ----------- ---------------- ---------- TEST1_PK VALID Y 1 33 9997 9997 9794 TEST1_IDX01 VALID 1 32 9997 100 4309 TEST1_IDX02 VALID 1 32 9997 100 4309 TEST1_IDX03 VALID 1 31 9997 100 4323 TEST1_IDX04 VALID 1 32 9997 9997 9794 TEST1_IDX05 VALID 1 32 9997 9997 9797 TEST1_IDX06 VALID 1 40 9997 100 4309 TEST1_IDX07 VALID 1 34 9997 9997 9794 TEST1_IDX08 VALID 1 56 9997 9997 9794 TEST1_IDX09 VALID 1 54 9997 9997 9794 TEST1_IDX10 VALID 1 56 9997 9997 9794
On Wed, Nov 16, 2011 at 11:53 AM, Taylor, Chris David < ChrisDavid.Taylor_at_ingrambarge.com> wrote:
> SQL> select val2, val3, val4, val5 > 2 from test1 > 3 where val1 = 12 > 4 and val2 != 'A12E' >
-- Thanks & Regards, Taral Desai -- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 16 2011 - 18:14:56 CST