Do Storage Indexes Work with Bind Variables?
I saw a post today where the subject of Exadata Storage Indexes were being discussed. One of the things that caught my eye was a discussion of whether Storage Indexes worked with Bind Variables. One of the posters observed that since smart scan was aimed at data warehouse type queries, bind variables were pretty much irrelevant. Which is true. Still it’s an interesting question. So I thought I’d give it a quick test.
As usual I used a couple of scripts:
fsx.sql – queries v$sql and shows whether a statement has been offloaded or not (slightly modified to remove 2 columns)
mystats.sql – just queries v$mystat
We’ll look at a test with a number column first.
SYS@LABRAT1> -- Do SI's work with bind variables? - Yes
SYS@LABRAT1>
SYS@LABRAT1> -- first here's basic info on my test table (SKEW3)
SYS@LABRAT1>
SYS@LABRAT1> desc kso.skew3
Name Null? Type
-------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
PK_COL NUMBER
COL1 NUMBER
COL2 VARCHAR2(30)
COL3 DATE
COL4 VARCHAR2(1)
SYS@LABRAT1> select count(*) from kso.skew3;
COUNT(*)
----------
384000048
1 row selected.
Elapsed: 00:00:26.53
SYS@LABRAT1> -- 27 seconds to do a full scan with no where clause (there are no indexes)
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 0
SYS@LABRAT1> -- no Storage Index usage by this session yet
SYS@LABRAT1> -- let's try a query using a variable
SYS@LABRAT1> set echo on
SYS@LABRAT1> @test_bv_si
SYS@LABRAT1>
SYS@LABRAT1> variable X NUMBER
SYS@LABRAT1>
SYS@LABRAT1> begin
2
3 :X := -1;
4
5 end;
6
7 /
PL/SQL procedure successfully completed.
SYS@LABRAT1>
SYS@LABRAT1> select count(*) from kso.skew3 where col1 = :x;
COUNT(*)
----------
0
Elapsed: 00:00:00.08
SYS@LABRAT1> set echo off
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 16025346048
SYS@LABRAT1> -- so it used the storage index
SYS@LABRAT1> @fsx
Enter value for sql_text: select count(*) from kso.skew3 where col1 = :x
Enter value for sql_id:
Enter value for inst_id:
INST SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------ ----------- ---------- --------------------------------------------------
1 1nsxv1zpawmsa 0 2684249835 2 .08 0 Yes 100.00 select count(*) from kso.skew3 where col1 = :x
1 row selected.
SYS@LABRAT1> @dplan
Enter value for sql_id: 1nsxv1zpawmsa
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1nsxv1zpawmsa, child number 0
-------------------------------------
select count(*) from kso.skew3 where col1 = :x
Plan hash value: 2684249835
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 533K(100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW3 | 385 | 1925 | 533K (1)| 01:46:43 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1"=:X)
filter("COL1"=:X)
20 rows selected.
So the Storage Index was clearly used on this statement using a SQL*Plus number variable. Here’s some 10046 trace data to show that smart scan wait event was used – note also the “enq: KO – fast object checkpoint” wait event which is done before the direct path reads (replaced by the “cell smart table scan” event in Exadata land).
...
PARSING IN CURSOR #2 len=46 dep=0 uid=0 oct=3 lid=0 tim=1284254192882293 hv=3937292042 ad='76742aa20' sqlid='1nsxv1zpawmsa'
select count(*) from kso.skew3 where col1 = :x
END OF STMT
PARSE #2:c=1000,e=299,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1284254192882292
WAIT #2: nam='ges message buffer allocation' ela= 5 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192882398
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192882442
WAIT #2: nam='library cache lock' ela= 228 handle address=31804186896 lock address=31727714984 100*mode+namespace=315619966779394 obj#=73486 tim=1284254192882696
WAIT #2: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192882741
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192882764
WAIT #2: nam='library cache pin' ela= 176 handle address=31804186896 pin address=31727714728 100*mode+namespace=315619966779394 obj#=73486 tim=1284254192882963
EXEC #2:c=1000,e=1611,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2684249835,tim=1284254192883951
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=73486 tim=1284254192883982
WAIT #2: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192884289
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192884322
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 100 name|mode=1263468550 2=65584 0=1 obj#=73486 tim=1284254192884443
WAIT #2: nam='reliable message' ela= 1287 channel context=31898270672 channel handle=31492015160 broadcast message=31556682800 obj#=73486 tim=1284254192885850
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192885899
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 142 name|mode=1263468550 2=65584 0=1 obj#=73486 tim=1284254192886063
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192886118
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 117 name|mode=1263468545 2=65584 0=2 obj#=73486 tim=1284254192886271
WAIT #2: nam='cell smart table scan' ela= 240 cellhash#=379339958 p2=0 p3=0 obj#=73486 tim=1284254192887559
WAIT #2: nam='cell smart table scan' ela= 222 cellhash#=2133459483 p2=0 p3=0 obj#=73486 tim=1284254192888038
WAIT #2: nam='cell smart table scan' ela= 212 cellhash#=3176594409 p2=0 p3=0 obj#=73486 tim=1284254192888531
WAIT #2: nam='cell smart table scan' ela= 1038 cellhash#=379339958 p2=0 p3=0 obj#=73486 tim=1284254192894795
WAIT #2: nam='cell smart table scan' ela= 1061 cellhash#=2133459483 p2=0 p3=0 obj#=73486 tim=1284254192895927
WAIT #2: nam='cell smart table scan' ela= 962 cellhash#=3176594409 p2=0 p3=0 obj#=73486 tim=1284254192896956
WAIT #2: nam='cell smart table scan' ela= 1121 cellhash#=379339958 p2=0 p3=0 obj#=73486 tim=12842541928982088
...
So it looks like a definite yes for offloading with bind variables and using Storage Indexes with bind variables. At least with numeric variables. Now let’s check out a varchar2 column and while we’re at it let’s check wild carding using % and the LIKE operator.
SYS@LABRAT1> select col2, count(*) from kso.skew3 group by col2;
COL2 COUNT(*)
------------------------------ ------------
12
2342 36
asddsadasd 384000000
3 rows selected.
Elapsed: 00:00:41.90
SYS@LABRAT1> select count(*) from kso.skew3 where col2 like '2342';
COUNT(*)
------------
36
1 row selected.
Elapsed: 00:00:00.10
SYS@LABRAT1> -- you should guess from the elapsed time this one used the Storage Index (it did)
SYS@LABRAT1>
SYS@LABRAT1> select count(*) from kso.skew3 where col2 like '234%';
COUNT(*)
------------
36
1 row selected.
Elapsed: 00:00:14.98
SYS@LABRAT1> -- and you should guess from the elapsed time that this one didn't (it didn't)
SYS@LABRAT1> -- so wildcards are not good for Storage Indexes
SYS@LABRAT1> -- let's try varchar2 variables now
SYS@LABRAT1>
SYS@LABRAT1> @test_bv_si
SYS@LABRAT1>
SYS@LABRAT1> variable W varchar2(10)
SYS@LABRAT1> variable X varchar2(10)
SYS@LABRAT1> variable Y varchar2(10)
SYS@LABRAT1> variable Z varchar2(10)
SYS@LABRAT1>
SYS@LABRAT1> begin
2
3 :X := '1111';
4 :Y := '2342';
5 :Z := '234%';
6
7 end;
8
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SYS@LABRAT1>
SYS@LABRAT1> select /* 1111 */ count(*) from kso.skew3 where col1 = :x;
COUNT(*)
----------
0
Elapsed: 00:00:15.25
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
Elapsed: 00:00:00.00
SYS@LABRAT1> -- no joy - looks like this one should use Storage Index, why not???
SYS@LABRAT1>
SYS@LABRAT1> select /* 2342 */ count(*) from kso.skew3 where col2 = :y;
COUNT(*)
----------
36
Elapsed: 00:00:00.10
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 1.6000E+10
Elapsed: 00:00:00.00
SYS@LABRAT1> --this one worked
SYS@LABRAT1>
SYS@LABRAT1> select /* 2342 */ count(*) from kso.skew3 where col2 like :y;
COUNT(*)
----------
36
Elapsed: 00:00:15.11
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 1.6000E+10
Elapsed: 00:00:00.00
SYS@LABRAT1> -- this one doesn't work due to the LIKE
SYS@LABRAT1>
SYS@LABRAT1> select /* 234% */ count(*) from kso.skew3 where col2 like :z;
COUNT(*)
----------
36
Elapsed: 00:00:15.19
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 1.6000E+10
Elapsed: 00:00:00.00
SYS@LABRAT1> -- this one also doesn't work due to the LIKE
SYS@LABRAT1> -- let's try rerunning the same statement but changing the values
SYS@LABRAT1>
SYS@LABRAT1> exec :Y := '2342';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SYS@LABRAT1> select /* various */ count(*) from kso.skew3 where col2 = :y;
COUNT(*)
----------
36
Elapsed: 00:00:00.08
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 3.2000E+10
Elapsed: 00:00:00.01
SYS@LABRAT1> -- used the Storage Index as expected
SYS@LABRAT1>
SYS@LABRAT1> exec :Y := '1111';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SYS@LABRAT1> select /* various */ count(*) from kso.skew3 where col2 = :y;
COUNT(*)
----------
0
Elapsed: 00:00:00.07
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 4.8026E+10
Elapsed: 00:00:00.00
SYS@LABRAT1> -- This is odd - it used the Storage Index again, even though our original test with '1111' didn't
SYS@LABRAT1> -- is this similar to bind variable peeking in that the statement has a locked in approach?
SYS@LABRAT1> -- I'm not sure.
SYS@LABRAT1>
SYS@LABRAT1> exec :Y := 'asddsadasd';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SYS@LABRAT1> select /* various */ count(*) from kso.skew3 where col2 = :y;
COUNT(*)
----------
384000000
Elapsed: 00:00:32.01
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 4.8026E+10
Elapsed: 00:00:00.00
SYS@LABRAT1> -- doesn't appeat that this one got any benefit,
SYS@LABRAT1> -- but there may not be any blocks that don't contain that value
SYS@LABRAT1> -- here the stats on the statements in this example
SYS@LABRAT1>
SYS@LABRAT1> set echo off
SYS@LABRAT1>@fsx
Enter value for sql_text: %skew3%
Enter value for sql_id:
Enter value for inst_id:
INST SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------ ----------- ---------- ----------------------------------------------------------------------
1 14a1chcq10j8q 0 2684249835 1 15.23 0 Yes 99.99 select /* 1111 */ count(*) from kso.skew3 where col1 = :x
1 367zpt07qh2d6 0 2684249835 1 15.17 0 Yes 99.99 select /* 234% */ count(*) from kso.skew3 where col2 like :z
1 f834t319m48vw 0 2684249835 3 10.72 0 Yes 86.45 select /* various */ count(*) from kso.skew3 where col2 = :y
1 ftrtpg2xcdp0t 0 2684249835 1 15.11 0 Yes 99.99 select /* 2342 */ count(*) from kso.skew3 where col2 like :y
1 gcnvsm28bnu4p 0 2684249835 1 .09 0 Yes 100.00 select /* 2342 */ count(*) from kso.skew3 where col2 = :y
5 rows selected.
Elapsed: 00:00:00.05
So these results indicate the following:
- Storage Indexes Can be Used with Bind Variables on Varchar2 variables
- Storage Indexes Don’t Appear to be Used with any Wild Carding (%)
- Storage Indexes Aren’t Used with the Like Operator when Bind Variables are used
Now let’s take a quick look at how Storage Indexes work with date fields.
SYS@LABRAT1> flush_pool
System altered.
SYS@LABRAT1> select min(col3),max(col3) from kso.skew3;
MIN(COL3) MAX(COL3)
--------- ---------
20-OCT-05 01-JAN-09
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 0
SYS@LABRAT1> set timing on
SYS@LABRAT1> select count(*) from kso.skew3 where col3 = '20-OCT-05';
COUNT(*)
----------
4
Elapsed: 00:00:15.13
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 0
Elapsed: 00:00:00.01
SYS@LABRAT1> -- so no Storage Index usage???
SYS@LABRAT1>
SYS@LABRAT1> select count(*) from kso.skew3 where col3 < '19-OCT-05';
COUNT(*)
----------
0
Elapsed: 00:00:15.07
SYS@LABRAT1> -- still no Storage Index usage
SYS@LABRAT1>
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 0
Elapsed: 00:00:00.00
SYS@LABRAT1> select count(*) from kso.skew3 where col3 > '01-jan-10';
COUNT(*)
----------
0
Elapsed: 00:00:15.09
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 0
Elapsed: 00:00:00.00
SYS@LABRAT1> -- still nothing
SYS@LABRAT1>
SYS@LABRAT1> select count(*) from kso.skew3 where col3 is null;
COUNT(*)
----------
12
Elapsed: 00:00:00.08
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 16012763136
Elapsed: 00:00:00.00
SYS@LABRAT1> -- so this time we used the Storage Index
SYS@LABRAT1> -- why is it different?
SYS@LABRAT1>
SYS@LABRAT1> @fsx
Enter value for sql_text: %skew3%
Enter value for sql_id:
Enter value for inst_id:
INST SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------ ----------- ---------- ----------------------------------------------------------------------
1 2sycry6jd7cus 0 2684249835 1 15.06 0 Yes 99.99 select count(*) from kso.skew3 where col3 < '19-OCT-05'
1 6n5y91cxw4yzu 0 2684249835 1 .08 0 Yes 100.00 select count(*) from kso.skew3 where col3 is null
1 asfmw4ccsv2u9 0 2684249835 1 28.45 0 Yes 67.15 select min(col3),max(col3) from kso.skew3
1 fuhmg9hqdbd84 0 2684249835 1 15.12 0 Yes 99.99 select count(*) from kso.skew3 where col3 = '20-OCT-05'
1 gkbzsmx4w57ym 0 2684249835 1 15.09 0 Yes 99.99 select count(*) from kso.skew3 where col3 > '01-jan-10'
5 rows selected.
Elapsed: 00:00:00.06
SYS@LABRAT1> @dplan
Enter value for sql_id: fuhmg9hqdbd84
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuhmg9hqdbd84, child number 0
-------------------------------------
select count(*) from kso.skew3 where col3 = '20-OCT-05'
Plan hash value: 2684249835
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 537K(100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW3 | 383 | 3064 | 537K (2)| 01:47:25 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL3"='20-OCT-05')
filter("COL3"='20-OCT-05')
20 rows selected.
Elapsed: 00:00:00.09
SYS@LABRAT1> @dplan
Enter value for sql_id: 6n5y91cxw4yzu
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6n5y91cxw4yzu, child number 0
-------------------------------------
select count(*) from kso.skew3 where col3 is null
Plan hash value: 2684249835
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 533K(100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW3 | 12 | 96 | 533K (1)| 01:46:44 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL3" IS NULL)
filter("COL3" IS NULL)
20 rows selected.
Elapsed: 00:00:00.02
SYS@LABRAT1> -- I wonder if the date format is disabling the Storage Index
SYS@LABRAT1>
SYS@LABRAT1>
SYS@LABRAT1> select count(*) from kso.skew3 where col3 = '20-OCT-2005';
COUNT(*)
----------
4
1 row selected.
Elapsed: 00:00:00.08
SYS@LABRAT1> -- ha, that did it!
SYS@LABRAT1>
SYS@LABRAT1> @fsx
Enter value for sql_text: %skew3%
Enter value for sql_id:
Enter value for inst_id:
INST SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------ ----------- ---------- ----------------------------------------------------------------------
1 2s58n6d3mzkmn 0 2684249835 1 .07 0 Yes 100.00 select count(*) from kso.skew3 where col3 = '20-OCT-2005'
1 2sycry6jd7cus 0 2684249835 1 15.06 0 Yes 99.99 select count(*) from kso.skew3 where col3 < '19-OCT-05'
1 6n5y91cxw4yzu 0 2684249835 1 .08 0 Yes 100.00 select count(*) from kso.skew3 where col3 is null
1 asfmw4ccsv2u9 0 2684249835 1 28.45 0 Yes 67.15 select min(col3),max(col3) from kso.skew3
1 fuhmg9hqdbd84 0 2684249835 1 15.12 0 Yes 99.99 select count(*) from kso.skew3 where col3 = '20-OCT-05'
1 gkbzsmx4w57ym 0 2684249835 1 15.09 0 Yes 99.99 select count(*) from kso.skew3 where col3 > '01-jan-10'
6 rows selected.
Elapsed: 00:00:00.06
SYS@LABRAT1> @dplan
Enter value for sql_id: 2s58n6d3mzkmn
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2s58n6d3mzkmn, child number 0
-------------------------------------
select count(*) from kso.skew3 where col3 = '20-OCT-2005'
Plan hash value: 2684249835
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 533K(100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW3 | 383 | 3064 | 533K (1)| 01:46:48 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL3"=TO_DATE(' 2005-10-20 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
filter("COL3"=TO_DATE(' 2005-10-20 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
22 rows selected.
Elapsed: 00:00:00.03
So like regular B-Tree indexes, implicit conversion, functions applied to columns, etc … can disable Storage Indexes. Not too surprising. It’s also interesting is that the Storage Indexes on dates are a little persnickety. Looks like literals work fine (at least in SQL*Plus) as long as the full 4 digit year is specified. You can see the format that Oracle converts it to is ‘syyyy-mm-dd hh24:mi:ss’. I was unable to get Storage Indexes to work with date columns using SQL*Plus varchar2 variables though. Any one got any ideas?
quote
SYS@LABRAT1> select /* 1111 */ count(*) from kso.skew3 where col1 = :x;
/quote
Is that a copy/paste error or the reason why the index is not used for ‘1111’? (note that col1 is of type NUMBER, all other tests in the block use col2)
quote
Any one got any ideas?
/quote
What if you convert the variable explicitly, as in
exec :dd:=’20-OCT-2005′
select count(*) from kso.skew3 where col3 = to_date(:dd,’DD-MON-RRRR’);
?
That’s the “trick” I use for normal indexes…
Cheers!
Flado
Good catch Flado. That was the problem. I left the example as is, but if I changed it use the correct column (col2) it behaves as it should, using the storage index.
SYS@LABRAT1> select /* 1111 */ count(*) from kso.skew3 where col2 = :x; COUNT(*) ---------- 0 Elapsed: 00:00:00.07Thanks for pointing that out. Just makes the point a little finer by the way. That point being that implicit conversions can easily disable Storage Indexes. It’s a little counter intuitive since we’re not used to worrying about this issue when we’re expecting full scans anyway.
Kerry
[…] for this update goes to Kerry Osborne’s blog post, who did some additional research. As a result, this update comes from […]
Hi Kerry,
Thanks for following up on these questions. I’ve updated my blog as well with some extra information as well.
[…] is just a quick post to note that I’ve corrected my blog on Storage Indexes here, after a follow up blog from Kerry Osborne indicating an error on my […]
Thanks for the update Christo. The implicit conversions are definitely a little counter intuitive.
Kerry
Indeed, but then again so is writing queries to obtain a full scan. Great explanation above and thank you.
Thank you so much for sharing this precious information with us.
Hi Kerry,
I was testing with EHCC compression and storage indexes. In my testing, first I compressed my subpartitions for query high and I ended up with 74K blocks for about 9M rows. I created another test table with the same structure of the previous (same storage attributes) and loaded the data from the partition mentioned above (that has 9M rows) with sorting on two columns. Now, after firing the query with the predicate on the sorted columns, I could get the storage indexes working perfectly.
But, when I checked the number of blocks occupied for the partition that has the sorted data, it was 20K blocks more than the unsorted partition.
Do you know, from your testing if it shows such a behavior? Would it be a bug? Please help me out on this as you have pulled me out of trouble several times in the past with your rich information on your blog. Thanks in advance.
–Kumar Ramalingam
Hi Kumar,
Sorting data can affect the compression ratio – so yes the number of blocks could be bigger or smaller depending on the order of the rows.
Kerry