Menu
There is next partitioned table:
CREATE TABLE "ERMB_LOG_TEST_BF"."OUT_SMS"( "TRX_ID" NUMBER(19,0) NOT NULL ENABLE, "CREATE_TS" TIMESTAMP (3) DEFAULT systimestamp NOT NULL ENABLE, /* other fields... */ ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ERMB_LOG_TEST_BF" PARTITION BY RANGE ("TRX_ID") INTERVAL (281474976710656) (PARTITION "SYS_P1358" VALUES LESS THAN (59109745109237760) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ERMB_LOG_TEST_BF"); CREATE INDEX "ERMB_LOG_TEST_BF"."OUT_SMS_CREATE_TS_TRX_ID_IX" ON "ERMB_LOG_TEST_BF"."OUT_SMS" ("CREATE_TS" DESC, "TRX_ID" DESC) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL (PARTITION "SYS_P1358" PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ERMB_LOG_TEST_BF"); I have sql query, which select 20 records ordered by date and transaction:
select rd from ( select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd from OUT_SMS where TRX_ID between 34621422135410688 and 72339069014638591 and CREATE_TS between to_timestamp('2013-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2013-03-06 08:57:00', 'yyyy-mm-dd hh24:mi:ss') order by CREATE_TS DESC, TRX_ID DESC ) where rownum <= 20 Oracle has generated next plan:
----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 240 | | 4788K (1)| 00:05:02 | | | |* 1 | COUNT STOPKEY | | | | | | | | | | 2 | VIEW | | 312M| 3576M| | 4788K (1)| 00:05:02 | | | |* 3 | SORT ORDER BY STOPKEY | | 312M| 9G| 12G| 4788K (1)| 00:05:02 | | | | 4 | PARTITION RANGE ITERATOR| | 312M| 9G| | 19 (0)| 00:00:01 | 1 | 48 | |* 5 | COUNT STOPKEY | | | | | | | | | |* 6 | INDEX RANGE SCAN | OUT_SMS_CREATE_TS_TRX_ID_IX | 312M| 9G| | 19 (0)| 00:00:01 | 1 | 48 | ----------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=20) 3 - filter(ROWNUM<=20) 5 - filter(ROWNUM<=20) 6 - access(SYS_OP_DESCEND("CREATE_TS")>=HEXTORAW('878EFCF9F6C5FEFAFF') AND SYS_OP_DESCEND("TRX_ID")>=HEXTORAW('36F7E7D7F8A4F0BFA9A3FF') AND SYS_OP_DESCEND("CREATE_TS")<=HEXTORAW('878EFDFEF8FEF8FF') AND SYS_OP_DESCEND("TRX_ID")<=HEXTORAW('36FBD0E9D4E9DBD5F8A6FF') ) filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))<=TIMESTAMP' 2013-03-06 08:57:00,000000000' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))<=72339069014638591 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))>=34621422135410688 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))>=TIMESTAMP' 2013-02-01 00:00:00,000000000') It works perfectly.
By the way, table OUT_SMS is partitioned by TRX_ID field and OUT_SMS_CREATE_TS_TRX_ID_IX is local index (CREATE_TS DESC, TRX_ID DESC) on each partition.
But if I convert this query to prepared statement:
select rd from ( select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd from OUT_SMS where TRX_ID between ? and ? and CREATE_TS between ? and ? order by CREATE_TS DESC, TRX_ID DESC ) where rownum <= 20 Oracle generates next plan:
---------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 240 | 14743 (1)| 00:00:01 | | | |* 1 | COUNT STOPKEY | | | | | | | | | 2 | VIEW | | 1964 | 23568 | 14743 (1)| 00:00:01 | | | |* 3 | SORT ORDER BY STOPKEY | | 1964 | 66776 | 14743 (1)| 00:00:01 | | | |* 4 | FILTER | | | | | | | | | 5 | PARTITION RANGE ITERATOR| | 1964 | 66776 | 14742 (1)| 00:00:01 | KEY | KEY | |* 6 | INDEX RANGE SCAN | OUT_SMS_CREATE_TS_TRX_ID_IX | 1964 | 66776 | 14742 (1)| 00:00:01 | KEY | KEY | ---------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=20) 3 - filter(ROWNUM<=20) 4 - filter(TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss')<=TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss') AND TO_NUMBER(:ABC)<=TO_NUMBER(:EBC)) 6 - access(SYS_OP_DESCEND("CREATE_TS")>=SYS_OP_DESCEND(TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss')) AND SYS_OP_DESCEND("TRX_ID")>=SYS_OP_DESCEND(TO_NUMBER(:EBC)) AND SYS_OP_DESCEND("CREATE_TS")<=SYS_OP_DESCEND(TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss')) AND SYS_OP_DESCEND("TRX_ID")<=SYS_OP_DESCEND(TO_NUMBER(:ABC))) filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))>=TO_NUMBER(:ABC) AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))<=TO_NUMBER(:EBC) AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))>=TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))<=TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss')) Operation COUNT STOPKEY disappears from plan. This operation should be after index was analyzed for getting 20 rows from each partition like the first query.
How can I compose prepared statement to have COUNT STOPKEY in the plan?
561
When you use bind variables, Oracle is forced to use dynamic partition pruning instead of static partition pruning. The result of this is that Oracle doesn't know at parse time which partitions will be accessed, as this changes based on your input variables.
This means that when using literal values (instead of bind variables), we know which partitions will be accessed by your local index. Therefore the count stopkey can be applied to the output of the index before we prune the partitions.
When using bind variables, the partition range iterator has to figure out which partitions you're accessing. It then has a check to ensure that the first of your variables in the between operations do actually have a lower value then the second one (the filter operation in the second plan).
This can easily be reproduced, as the following test case shows:
create table tab ( x date, y integer, filler varchar2(100) ) partition by range(x) ( partition p1 values less than (date'2013-01-01'), partition p2 values less than (date'2013-02-01'), partition p3 values less than (date'2013-03-01'), partition p4 values less than (date'2013-04-01'), partition p5 values less than (date'2013-05-01'), partition p6 values less than (date'2013-06-01') ); insert into tab (x, y) select add_months(trunc(sysdate, 'y'), mod(rownum, 5)), rownum, dbms_random.string('x', 50) from dual connect by level <= 1000; create index i on tab(x desc, y desc) local; exec dbms_stats.gather_table_stats(user, 'tab', cascade => true); explain plan for SELECT * FROM ( SELECT rowid FROM tab where x between date'2013-01-01' and date'2013-02-02' and y between 50 and 100 order by x desc, y desc ) where rownum <= 5; SELECT * FROM table(dbms_xplan.display(null, null, 'BASIC +ROWS +PARTITION')); -------------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 1 | | | | 3 | SORT ORDER BY STOPKEY | | 1 | | | | 4 | PARTITION RANGE ITERATOR| | 1 | 2 | 3 | | 5 | COUNT STOPKEY | | | | | | 6 | INDEX RANGE SCAN | I | 1 | 2 | 3 | -------------------------------------------------------------------- explain plan for SELECT * FROM ( SELECT rowid FROM tab where x between to_date(:st, 'dd/mm/yyyy') and to_date(:en, 'dd/mm/yyyy') and y between :a and :b order by x desc, y desc ) where rownum <= 5; SELECT * FROM table(dbms_xplan.display(null, null, 'BASIC +ROWS +PARTITION')); --------------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 1 | | | | 3 | SORT ORDER BY STOPKEY | | 1 | | | | 4 | FILTER | | | | | | 5 | PARTITION RANGE ITERATOR| | 1 | KEY | KEY | | 6 | INDEX RANGE SCAN | I | 1 | KEY | KEY | --------------------------------------------------------------------- As in your example, the second query can only filter the partitions to a key at parse time, rather than the exact partitions as in the first example.
This is one of those rare cases where literal values can provide better performance than bind variables. You should investigate whether this is a possibility for you.
Finally, you say you want 20 rows from each partition. Your query as stands won't do this, it'll just return you the first 20 rows according to your ordering. For 20 rows/partition, you need to do something like this:
select rd from ( select rowid rd, row_number() over (partition by trx_id order by create_ts desc) rn from OUT_SMS where TRX_ID between ? and ? and CREATE_TS between ? and ? order by CREATE_TS DESC, TRX_ID DESC ) where rn <= 20 UPDATE
The reason you're not getting the count stopkey is to do with the filter operation in line 4 of the "bad" plan. You can see this more clearly if you repeat the example above, but with no partitioning.
This gives you the following plans:
---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | COUNT STOPKEY | | | 2 | VIEW | | |* 3 | SORT ORDER BY STOPKEY| | |* 4 | TABLE ACCESS FULL | TAB | ---------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5) 3 - filter(ROWNUM<=5) 4 - filter("X">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "X"<=TO_DATE(' 2013-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Y">=50 AND "Y"<=100) ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | COUNT STOPKEY | | | 2 | VIEW | | |* 3 | SORT ORDER BY STOPKEY| | |* 4 | FILTER | | |* 5 | TABLE ACCESS FULL | TAB | ---------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5) 3 - filter(ROWNUM<=5) 4 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B) AND TO_DATE(:ST,'dd/mm/yyyy')<=TO_DATE(:EN,'dd/mm/yyyy')) 5 - filter("Y">=TO_NUMBER(:A) AND "Y"<=TO_NUMBER(:B) AND "X">=TO_DATE(:ST,'dd/mm/yyyy') AND "X"<=TO_DATE(:EN,'dd/mm/yyyy')) As you can see, there's an extra filter operation when you use bind variables appearing before the sort order by stopkey. This happens after accessing the index. This is checking that the values for the variables will allow data to be returned (the first variable in your between does actually have a lower value than the second). This isn't necessary when using literals because the optimizer already knows that 50 is less than 100 (in this case). It doesn't know whether :a is less than :b at parse time however.
Why exactly this is I don't know. It could be intentional design by Oracle - there's no point doing the stopkey check if the values set for the variables result in zero rows - or just an oversight.
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
