Grumble - is this really the best they can do?
Date: Wed, 22 Jun 2016 13:53:10 +0100
Message-Id: <222879CD-69E7-49D7-AEC1-13F506B8EEE7_at_strychnine.co.uk>
OK, as I see things:
- SQL is allegedly a declarative language.
- Oracle is touted as the most performant, mature, ......, product in the marketplace.
- The Oracle CBO makes execution plan/path decisions based on the 'cost' of a number different execution path candidates.
- The Oracle optimiser can't even rewrite the most simple of algebraic expressions to result in a query with a low cost execution path.
- My 9 year old daughter has the skills to rewrite simple algebraic expressions.
And yes, I have my grumpy old man hat on, and yes I do understand why the 'cost' of the two following queries is so different, and yes I have read many of manuals, but none of this means that the following behaviour (see below) still isn't crap. I am quite confident that even a mediocre developer would be able to rewrite some of the optimiser code to accommodate for this type of simple algebra, with the ultimate goal of ensuring that an indexed column was used when it made sense.
~
Mike
http://www.strychnine.co.uk <http://www.strychnine.co.uk/>
SQL> -- Table name anonymised but largeTable is large and partitioned on partDate SQL> -- largeTab_id is the PK SQL> ------------------------------------------------------------------------------- SQL> SQL> SQL> set linesize 132 SQL> set wrap off SQL> set trunc off SQL> set tab off SQL> SQL> explain plan for
2 select *
3 from largeTable
4 where largeTab_id+1 = 13536978456 5 and partDate = date '2014-12-2';
Explained.
SQL> select *
2 from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 3163595393
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 52841 | 6347K| 12712 (3)| 00:02:33 | | | | 1 | PARTITION RANGE SINGLE| | 52841 | 6347K| 12712 (3)| 00:02:33 | 1 | 1 | | 2 | PARTITION LIST ALL | | 52841 | 6347K| 12712 (3)| 00:02:33 | 1 | 6 | |* 3 | TABLE ACCESS FULL | largeTable | 52841 | 6347K| 12712 (3)| 00:02:33 | 1 | 6 |
-----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
3 - filter("largeTab_id"+1=13536978456 AND "partDate"=TO_DATE(' 2014-12-02 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
16 rows selected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> explain plan for
2 select *
3 from largeTable
4 where largeTab_id = 13536978456-1 5 and partDate = date '2014-12-2';
Explained.
SQL> select *
2 from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 1525276650
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 | 24 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 123 | 24 (0)| 00:00:01 | 1 | 1 | | 2 | PARTITION LIST ALL | | 1 | 123 | 24 (0)| 00:00:01 | 1 | 6 | |* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| largeTable | 1 | 123 | 24 (0)| 00:00:01 | 1 | 6 | |* 4 | INDEX RANGE SCAN | largeTable_IX01 | 1 | | 23 (0)| 00:00:01 | 1 | 6 |
-----------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
3 - filter("partDate"=TO_DATE(' 2014-12-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 4 - access("largeTab_id"=13536978455)
17 rows selected.
SQL> SQL> SQL> SQL> SQL> select * from v$version;
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Solaris: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
SQL> SQL> SQL>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 22 2016 - 14:53:10 CEST